Update Access data from textbox.text

[tenorjazz]

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

[Phasma]

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.

[kingsonprisonic]

I Use Access Many years ago. I think....

Don't use semicolon after the query....

[Reverend Jim]

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 & "'"

[Phasma]

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';

[tenorjazz]

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?

[Phasma]

Yep. According to their list
http://support.microsoft.com/kb/286335 I noticed I could execute your sql within the Access app but not from Visual Studio so I Saved the query, closed it, and when I reopened it [Container] showed up in the sql view.

Glad you got it working

[poojavb]

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

[Phasma]

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.