I have a simple vb.net app and I'm trying to update a one row Access table with data from some textboxes. This is a learning exercise for me as I am coming back from not programming for almost 10 years, and know there are lots of other ways to manage this data, but I'm just trying to understand different aspects of ADO.net a little better.

The ERROR I'm trapping is:

"Syntax error in UPDATE Statement"

Any thought's as to what is going on? (See CODE and stuff below)

Much Thanks, in advance!!!

Here's the Scheme printed from ACCESS showing the exact spelling of the database table and column names:

Table: MaxTime
Name Type Size

ID Long Integer 4
Container Text 10
Vehicle Text 10
Exterior Text 10
Interior Text 10

The SQL UPDATE string looks like this (from the debugger just before it's executed):

UPDATE MaxTime SET Container = '1111', Vehicle = '2222', Exterior = '3333', Interior = '4444';

And here's the code

Dim strSQL As String

        strSQL = String.Concat("UPDATE MaxTime SET Container = '", txtMaxContainerTime.Text, "', Vehicle = '", txtMaxVehicleTime.Text, "', Exterior = '", txtMaxExteriorTime.Text, "', Interior = '", txtMaxInteriorTime.Text, "';")


        Try
            dbConnection = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = ShowData.mdb")
            dbCommand = New OleDbCommand(strSQL, dbConnection)

            dbCommand.Connection.Open()
            dbCommand.ExecuteNonQuery()

            dbCommand.Dispose()
            dbConnection.Close()

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

I created a simple update in an Access db and it had double quotes around my update values you may want to try that.

UPDATE MaxTime SET Container = "1111", Vehicle = "2222", Exterior = "3333", Interior = "4444";

Also You really should use a Where Clause unless you are wanting to Update every single record in your datatable.

O.P. said it was a one row Access table so updating all records wouldn't be a problem. SQL usually uses single quotes around string fields. Double quotes are acceptable but it makes it a little messy when creating strings like

query = "select * from table where username=""" & user & """"

It's clearer to write

query = "select * from table where username='" & user & "'"

Try This. Put [] around Container field. According to M$ Container is a reserved word.

UPDATE MaxTime SET [Container] = '1111', Vehicle = '2222', Exterior = '3333', Interior = '4444';

Comments
Nice catch.

Amazing....

[Container] fixed the problem ---- THANKS!!

Just want to make sure I understand what you are saying... Container is a reserved word that can be used in SQL "UPDATE" statement?

Amazing....

[Container] fixed the problem ---- THANKS!!

Just want to make sure I understand what you are saying... Container is a reserved word that can be used in SQL "UPDATE" statement?

U have missed the "+" sign in ur update query
Try this...

Try
                    Dim MessageText As String
                    Dim myCommand As OleDbCommand
                    myCommand = New OleDbCommand("UPDATE MaxTime SET Container = '"+txtMaxContainerTime.Text+"', Vehicle = '"+ txtMaxVehicleTime.Text+ "', Exterior = '"+ txtMaxExteriorTime.Text+ "', Interior = '"+txtMaxInteriorTime.Text+ "'", dbConnection)
                    myCommand.ExecuteNonQuery()
                    MessageText = "Information Updated Successfully !! "
                    MsgBox(MessageText)
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
Comments
Read the post carefully then argue. You are confusing the OP....

I'm not trying to pick nits but, he's using the String.ConCat method

strSQL = String.Concat("UPDATE MaxTime SET Container = '", txtMaxContainerTime.Text, "', Vehicle = '", txtMaxVehicleTime.Text, "', Exterior = '", txtMaxExteriorTime.Text, "', Interior = '", txtMaxInteriorTime.Text, "';")

The '+' sign would just wreck the query.

Edited 4 Years Ago by Phasma: n/a

Comments
eagle eye:=+1
This article has been dead for over six months. Start a new discussion instead.