Update database in vb.net causing syntax error

Question

I am trying to update a database record when a user amends it. I have a textbox called 'txtColsTextBox' which stores the value and a button called 'btnSaveExit'. On the button click, I need to update the db with new value.

How would I do this based on my code. I am thinking , i need to use me.validate function but not sure how to code. Thanks

Dim connetionString As String
Dim oledbCnn As OleDbConnection
Dim oledbCmd As OleDbCommand
Dim sql As String

connetionString = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=C:\domain\storage.accdb"
sql = "SELECT Cols FROM Racks Where [Rack code] = '" & buttonName & "'"
    oledbCnn = New OleDbConnection(connetionString)
Try
    oledbCnn.Open()
    oledbCmd = New OleDbCommand(sql, oledbCnn)
    Dim oledbReader As OleDbDataReader = oledbCmd.ExecuteReader()
    While oledbReader.Read
        'MsgBox(oledbReader.Item(0))
        txtColsTextBox.Text = oledbReader.Item(0)
    End While
    oledbReader.Close()
    oledbCmd.Dispose()
    oledbCnn.Close()
Catch ex As Exception
    MsgBox(ex.Message)
End Try

EDIT: Code to Update db

Private Sub btnSaveExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveExit.Click
        Try
            connetionString = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=C:\domain\storage.accdb"

            oledbCnn.Open()
            oledbCmd = New OleDbCommand(sql, oledbCnn)
            oledbCmd.CommandText = "UPDATE [Racks] SET [Cols] VALUES (?)"
            oledbCmd.Parameters.AddWithValue("@Cols", txtColsTextBox.Text)
            oledbCmd.ExecuteNonQuery()

            MessageBox.Show("Record succesfully updated" + txtColsTextBox.Text)

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try
    End Sub

Why aren't you using query parameters in your first sample?

Joel Coehoorn
– Joel Coehoorn

2013-11-06 19:56:24 +00:00
Commented Nov 6, 2013 at 19:56 — Joel Coehoorn
– Joel Coehoorn, Commented Nov 6, 2013 at 19:56
Also, oledbCnn.Close() should be in a finally block.

Joel Coehoorn
– Joel Coehoorn

2013-11-06 19:57:55 +00:00
Commented Nov 6, 2013 at 19:57 — Joel Coehoorn
– Joel Coehoorn, Commented Nov 6, 2013 at 19:57

Markus · Accepted Answer · 2013-11-06 19:54:54Z

1

Change your UPDATE statement to the following form:

UPDATE [Racks] SET [Col1] = ?, [Col2] = ? WHERE [IdCol] = ?

The sample assumes that you want to update Col1 and Col2 with new values and only for one record (the one where IdCol equals the id). Add the parameters in an order that corresponds to the order in the UPDATE statement.

answered Nov 6, 2013 at 19:54

Markus

22.7k4 gold badges34 silver badges61 bronze badges

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

Comments

nabuchodonossor · Accepted Answer · 2013-11-06 15:32:10Z

1

To show you were to start, you can use something like this (adjust tablename, fieldnames, and parameters)

oledbCnn.Open()
oledbCmd = New OleDbCommand(sql, oledbCnn)
oledbCmd.CommandText = "INSERT INTO [TableName] ([Fieldname1], [Fieldname2]) VALUES (?,?)"
oledbCmd.Parameters.Add( .... )
oledbCmd.Parameters.Add( .... )
oledbCmd.ExecuteNonQuery()

answered Nov 6, 2013 at 15:32

nabuchodonossor

2,07020 silver badges19 bronze badges

3 Comments

user1532468 Over a year ago

Please see amended code based on your example which gives me syntax error in update statement. Thanks

logixologist Over a year ago

try putting @cols instead of ?

nabuchodonossor Over a year ago

I wrote the INSERT syntax, which is basically INSERT INTO TABLENAME (fieldlist) values (valuelist). The UPDATE syntax is different: UPDATE TABLENAME SET fielname1 = value1, fieldname2 = value2 .... WHERE clause (very important, because omitting the where clause updates every record.)

Collectives™ on Stack Overflow

Update database in vb.net causing syntax error

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related