[SOLVED] Correct Update Command for datagridview w/ Join in Select command SQL server

Correct Update Command for datagridview w/ Join in Select command SQL server

Not sure what i am doing wrong here keep getting a syntax error near "InTime". sqlmodDA.Update(sqlmodDS.Tables("XXX")) One button gets data (subBindDataGridView) to display on Data Grid View then edit is made next button (CommitChanges) writes changes to DB. Can't use command builder due to join in Select command. Am i better off just going to an all stored procedure solution?

Imports System.Data
Imports System.Data.SqlClient

Module SQLSever
Private sqlmodCon As SqlConnection
Private sqlmodDA As SqlDataAdapter
Private sqlmodTA As New DataTable
Private sqlmodDS As New DataSet


Sub subBindDataGridView()
    Dim strConStr As String
    Dim SQLState As String
    Dim SQLUpdate As String

    strConStr = "Server=sql2008;Database=TimeClock;User ID=VBA;Password=CanIJustUseDA0;"
    sqlmodCon = New SqlConnection(strConStr)
    sqlmodCon.Open()
    SQLState = "SELECT * FROM dbo.tblEvents INNER JOIN dbo.tblPersons ON dbo.tblEvents.PersonID = dbo.tblPersons.PersonID;"
    SQLUpdate = "UPDATE dbo.tblEvents" & _
                "SET InTime = @InTime, LunchStart = @LunchStart, LunchFinish = @LunchFinish, OutTime = @OutTime WHERE Pkey = @Pkey;"
    sqlmodDA = New SqlDataAdapter(SQLState, sqlmodCon)
    sqlmodDA.UpdateCommand = New SqlCommand(SQLUpdate, sqlmodCon)
    sqlmodDA.UpdateCommand.Parameters.Add("@InTime", SqlDbType.DateTime, 8, "InTime")
    sqlmodDA.UpdateCommand.Parameters.Add("@LunchStart", SqlDbType.DateTime, 8, "LunchStart").SourceColumn = "LunchStart"
    sqlmodDA.UpdateCommand.Parameters.Add("@LunchFinish", SqlDbType.DateTime, 8, "LunchFinish").SourceColumn = "LunchFinish"
    sqlmodDA.UpdateCommand.Parameters.Add("@OutTime", SqlDbType.DateTime, 8, "OutTime").SourceColumn = "OutTime"
    sqlmodDA.UpdateCommand.Parameters.Add("@PKey", SqlDbType.BigInt, 8, "Pkey").SourceColumn = "PKey"
    sqlmodDA.Fill(sqlmodDS, "XXX")
    Form1.DataBox.DataSource = sqlmodDS.Tables("XXX")
    sqlmodCon.Close()
End Sub

Sub CommitChanges()
    sqlmodCon.Open()
    sqlmodDA.Update(sqlmodDS.Tables("XXX")) 'Error here
    sqlmodDS.AcceptChanges()
    sqlmodCon.Close()
End Sub

End Module

Solution

Your statement has no space between "dbo.tblEvents" and "SET", so it is "dbo.tblEventsSET".