4 Replies - 2694 Views - Last Post: 29 March 2012 - 09:35 PM Rate Topic: -----

#1 Brandon222  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 99
  • Joined: 16-November 08

OleDbException was unhandled: Syntax error in UPDATE statement.

Posted 29 March 2012 - 08:38 AM

I'm getting the error "Syntax error in UPDATE statement." When I run it and place something in the txtPass box. However I have checked this everywhere and cant seem to find to find the problem. Everything else works fine except for that part. Someone please help:

Public Class Form6

    Private Sub btnView_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnView.Click
        MsgBox(Form2.firstname)
        Dim constr As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data source = test.accdb"
        Dim sqlstr As String = "SELECT FirstName, LastName, Telephone, Password FROM Customer WHERE FirstName Like'" & Form2.firstname & "' And LastName Like '" & Form2.lastname & "'"
        Dim da As New OleDb.OleDbDataAdapter(sqlstr, constr)
        Dim vt As New DataTable()
        Dim found As Integer = 0
        da.Fill(vt)
        da.Dispose()
        dgvInfo.DataSource = vt
    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Dim constr As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data source = test.accdb"
        Dim constr1 As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data source = test.accdb"
        Dim Connect As New OleDb.OleDbConnection(constr)
        Dim Connect1 As New OleDb.OleDbConnection(constr1)
        Dim firstname As String = Form2.firstname
        Dim lastname As String = Form2.lastname
        Dim telephone As String = Form2.telephone
        Dim password As String = Form2.password
        If (txtFirstName.Text <> "") Then
            Connect.Open()
            Form2.firstname = txtFirstName.Text
            Dim sqlUpdate As String = "UPDATE [Customer] SET FirstName ='" & txtFirstName.Text & "' WHERE FirstName ='" + firstname + "'"
            Dim da2 As New OleDb.OleDbDataAdapter(sqlUpdate, constr)
            da2.UpdateCommand = New OleDb.OleDbCommand(sqlUpdate)
            da2.UpdateCommand.Connection = Connect
            da2.UpdateCommand.ExecuteNonQuery()
            Connect.Close()
        End If
        If (txtLastName.Text <> "") Then
            Connect.Open()
            Form2.lastname = txtLastName.Text
            Dim sqlUpdate As String = "UPDATE [Customer] SET LastName ='" & txtLastName.Text & "' WHERE LastName ='" + lastname + "'"
            Dim da2 As New OleDb.OleDbDataAdapter(sqlUpdate, constr)
            da2.UpdateCommand = New OleDb.OleDbCommand(sqlUpdate)
            da2.UpdateCommand.Connection = Connect
            da2.UpdateCommand.ExecuteNonQuery()
            Connect.Close()
        End If

        If (txtPass.Text <> "") Then
            Connect.Open()
            Form2.password = txtPass.Text
            Dim sqlUpdate As String = "UPDATE [Customer] SET Password ='" & txtPass.Text & "' WHERE Password ='" + password + "'"
            Dim da2 As New OleDb.OleDbDataAdapter(sqlUpdate, constr)
            da2.UpdateCommand = New OleDb.OleDbCommand(sqlUpdate)
            da2.UpdateCommand.Connection = Connect
            da2.UpdateCommand.ExecuteNonQuery()
            Connect.Close()
        End If

        If (txtNumber.Text <> "") Then
            Connect.Open()
            Form2.telephone = txtNumber.Text
            Dim sqlUpdate As String = "UPDATE [Customer] SET Telephone ='" & txtNumber.Text & "' WHERE Telephone ='" + telephone + "'"
            Dim da2 As New OleDb.OleDbDataAdapter(sqlUpdate, constr)
            da2.UpdateCommand = New OleDb.OleDbCommand(sqlUpdate)
            da2.UpdateCommand.Connection = Connect
            da2.UpdateCommand.ExecuteNonQuery()
            Connect.Close()
        End If

    End Sub
End Class


Is This A Good Question/Topic? 0
  • +

Replies To: OleDbException was unhandled: Syntax error in UPDATE statement.

#2 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 205
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Re: OleDbException was unhandled: Syntax error in UPDATE statement.

Posted 29 March 2012 - 08:56 AM

Debug and check which UPDATE query is throwing the error.

In the meantime, you might wanna take a look at these things. In your select query, you should use the = instead of the LIKE operator. LIKE is for searching records which matches a pattern, not a single value. So you might get multiple records.
And I would highly recommend having a primary key field like CustomerID if you're not already to maintain database integrity.

This post has been edited by nK0de: 29 March 2012 - 08:58 AM

Was This Post Helpful? 0
  • +
  • -

#3 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: OleDbException was unhandled: Syntax error in UPDATE statement.

Posted 29 March 2012 - 09:09 AM

Password needs to be wrapped with [] also. It is being interpreted as a reserved word and confusing the interpreter.
Was This Post Helpful? 1
  • +
  • -

#4 Brandon222  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 99
  • Joined: 16-November 08

Re: OleDbException was unhandled: Syntax error in UPDATE statement.

Posted 29 March 2012 - 07:14 PM

OK thank you so much it worked perfectly. and ok well i have one more problem, now its in a different form... I want to edit directly from the db file using a data grid and when i click update button it should update it to the actual file. However I get the messege "The DataAdapter.SelectCommand property needs to be initialized." Here is my code:

Public Class Form10

    Dim constr As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data source = test.accdb"
    Dim sqlstr As String = "SELECT FirstName, LastName, Telephone, [Password] FROM Customer WHERE FirstName Like'" & Form2.first & "'And LastName Like'" & Form2.last & "'"
    Private da As New OleDb.OleDbDataAdapter(sqlstr, constr)
    Private vt As New DataTable()

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim firstname As String = txtFirstName.Text & "%"
        Dim lastname As String = txtLastName.Text & "%"
        Dim found As Integer = 0

        vt.Clear()
        da.Fill(vt)
        da.Dispose()
        dgvCustomer.DataSource = vt


        Dim constr1 As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data source = test.accdb"
        Dim sqlstr1 As String = "SELECT FirstName, LastName, ItemPurchased FROM TransactionsPurchased WHERE FirstName Like'" & firstname & "' AND LastName Like'" & lastname & "'ORDER BY FirstName, LastName "
        Dim da1 As New OleDb.OleDbDataAdapter(sqlstr1, constr1)
        Dim vt1 As New DataTable()
        da1.Fill(vt1)
        da1.Dispose()
        dgvPurchased.DataSource = vt1

        Dim constr2 As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data source = test.accdb"
        Dim sqlstr2 As String = "SELECT FirstName, LastName, ItemRented FROM TransactionsRent WHERE FirstName Like'" & firstname & "' AND LastName Like'" & lastname & "'ORDER BY FirstName, LastName DESC"
        Dim da2 As New OleDb.OleDbDataAdapter(sqlstr2, constr2)
        Dim vt2 As New DataTable()
        da2.Fill(vt2)
        da2.Dispose()
        dgvRented.DataSource = vt2

    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        'Dim firstname As String = txtFirstName.Text & "%"
        'Dim lastname As String = txtLastName.Text & "%"
        'Dim constr2 As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data source = test.accdb"
        'Dim sqlstr2 As String = "SELECT FirstName, LastName, ItemRented FROM TransactionsRent WHERE FirstName Like'" & firstname & "' AND LastName Like'" & lastname & "'ORDER BY FirstName, LastName DESC"
        'Dim da2 As New OleDb.OleDbDataAdapter(sqlstr2, constr2)
        'Dim vt2 As New DataTable()

        'Dim numchanged1 As Integer
        'Dim cmdb1 As New OleDb.OleDbCommandBuilder(da2)
        'numchanged1 = da2.Update(vt2)

        'Dim constr1 As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data source = test.accdb"
        'Dim sqlstr1 As String = "SELECT FirstName, LastName, ItemPurchased FROM TransactionsPurchased WHERE FirstName Like'" & firstname & "' AND LastName Like'" & lastname & "'ORDER BY FirstName, LastName "
        'Dim da1 As New OleDb.OleDbDataAdapter(sqlstr1, constr1)
        'Dim vt1 As New DataTable()

        'Dim numchanged2 As Integer
        'Dim cmdb2 As New OleDb.OleDbCommandBuilder(da1)
        'numchanged2 = da1.Update(vt1)

        Dim numchanged3 As Integer
        Dim cmdb3 As New OleDb.OleDbCommandBuilder(da)
        numchanged3 = da.Update(vt)
        da.Dispose()
    End Sub
End Class

Was This Post Helpful? 0
  • +
  • -

#5 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 205
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Re: OleDbException was unhandled: Syntax error in UPDATE statement.

Posted 29 March 2012 - 09:35 PM

You haven't initialized an instance of a Data Adapter inside the update button's code.

Dim da As New OleDb.OleDbDataAdapter()

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1