CODEWITHSUNDEEP
vb.netms-access
Aaron Pister
Aaron Pister

Reputation: 13

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

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

Upvotes: 1

Views: 1851

Answers (3)

Dillie-O
Dillie-O

Reputation: 29725

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.

Upvotes: 0

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107498

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:

  1. Use prepared statements (parameterize your SQL to avoid SQL injection attacks)
  2. 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.
  3. Use Using statements. Your OleDbConnection class implements IDisposable. You aren't properly disposing this connection.

Upvotes: 0

LarsTech
LarsTech

Reputation: 81610

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.

Upvotes: 4

Related Questions