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

Question

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

Thanks that fixed it, Free beer if you are ever in the Fairbanks Alaska area. — This Side of Zero
– This Side of Zero, Commented May 16, 2014 at 23:29

Andy G · Accepted Answer · 2014-05-16 22:13:47Z

2

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

answered May 16, 2014 at 22:13

Andy G

19.4k5 gold badges49 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related