UPDATE statement failing when attempting to update access database - VB.Net

Question

I am trying to create a simple ticketing software for myself with a VB.Net front end and an Access 2003 back end. I have with allowing new rows to be added, but when I try to code the Update row button, it is giving me the error that says Microsoft JET Database Engine - Syntax error in UPDATE statement. I cannot find what the problem is.

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
    Dim da As New OleDbDataAdapter
    Dim dt As New DataTable
    Dim ConnectString As String = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\aaron-pister\Desktop\New Role Notes\Issue Tracker Express\Issue Tracker Express\Issue Tracker.mdb")
    Dim con As New OleDbConnection(ConnectString)
    con.Open()
    Dim Green42 As String = "UPDATE Issues Basic Details SET [Company Name] = '" & txtClientName.Text & "', [Status] = '" & cbStatus.Text & "', [Company Contact] = '" & txtClientContact.Text & "', [Description] = '" & txtDesc.Text & "', [Notes] = '" & txtRes.Text & "' WHERE [TicketNum] = '" & txtTicket.Text & "'"
    'con.Open()
    If txtClientName.Text <> "" Then
        Try
            'MyCom.CommandText = "UPDATE [Issues Basic Details] SET Company Name = '" & txtClientName.Text & "', Status = '" & cbStatus.Text & "', Company Contact = '" & txtClientContact.Text & "', Description = '" & txtDesc.Text & "', Notes = '" & txtRes.Text & "' WHERE TicketNum = '" & txtTicket.Text & "')"
            da = New OleDbDataAdapter(Green42.ToString, ConnectString)
            da.Fill(dt)
            da.Update(EsInfo1, "Issues Basic Details")
            MsgBox("Your record has been updated successfully.", MsgBoxStyle.DefaultButton1, "New Ticket Submitted")
        Catch ex As Exception
            MsgBox(ex.Source & "-" & ex.Message)
            con.Close()
            Exit Sub
        End Try
    Else
        MsgBox("You must have an entry in the Client Name, Client Contact and Status fields. It is recommended to also have a value in the Description field.", MsgBoxStyle.OkOnly, "Issue Tracker Express")
        btnNewIncident_Click(sender, e)
        Exit Sub
    End If
End Sub

score 4 · Accepted Answer · answered Jan 08 '13 at 19:54

4

Your table name has to be bracketed too:

Dim Green42 As String = "UPDATE [Issues Basic Details] SET [Company Name]..."

Also, use parameters instead of putting the values into the string. It avoids SQL Injection.

answered Jan 08 '13 at 19:54

LarsTech

80,625
14
153
225

2

Just to add to this answer, as a general rule of thumb spaces, special characters and reserved keywords are all things to avoid when naming tables and columns. – Mike_OBrien Jan 08 '13 at 20:17

score 0 · Answer 2 · answered Jan 08 '13 at 19:54

0

This:

UPDATE Issues Basic Details SET ...

Is not valid SQL. You need to help it by qualifying your table name:

UPDATE [Issues Basic Details] SET ...

A few other suggestions:

Use prepared statements (parameterize your SQL to avoid SQL injection attacks)
Don't define this type of behavior in a click event handler -- have a helper class to do this work so it can be re-used and isn't coupled directly to the UI.
Use Using statements. Your OleDbConnection class implements IDisposable. You aren't properly disposing this connection.

answered Jan 08 '13 at 19:54

Cᴏʀʏ

105,112
20
162
194

I'm not sure I understand what you mean by parameterize the SQL. I think I understand your other two points, and understand them. Can you provide examples of how I could parameterize my SQL in the code provided? – Aaron Pister Jan 09 '13 at 14:30
@AaronPister: See the Example here: http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.prepare.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-2. It shows both the `Using` statement usage and the parameterization of a SQL statement. – Cᴏʀʏ Jan 09 '13 at 14:40

score 0 · Answer 3 · edited May 23 '17 at 12:09

0

While it's hard to read your code at the moment, it does look like you are trying to do an "AdHoc" query, which can cause a lot of problems.

I'd recommend first changing your statement to a parameterized query to help diagnose issues too.

edited May 23 '17 at 12:09

Community

1
1

answered Jan 08 '13 at 19:55

Dillie-O

29,277
14
101
140

UPDATE statement failing when attempting to update access database - VB.Net

3 Answers3