error in UPDATE statement / VB2010 and MSACCESS 2007-VBForums
Results 1 to 8 of 8

Thread: error in UPDATE statement / VB2010 and MSACCESS 2007

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2013
    Posts
    4

    error in UPDATE statement / VB2010 and MSACCESS 2007

    These are my codes. It does not go through. I am having error in the UPDATE.
    After clicking Edit, do my edit, and when i click on Save and choose "Yes";
    that is where the UPDATE error occurs.

    I need help.

    Thanks.

    Watertubig
    -----------------------------------------------------------------------

    Public Class Frm_StealStreet

    Private Sub Frm_StealStreet_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'TODO: This line of code loads data into the 'StealStreetDataSet.Employees' table. You can move, or remove it, as needed.
    Me.EmployeesTableAdapter.Fill(Me.StealStreetDataSet.Employees)

    End Sub

    Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click
    Me.Close()
    End Sub

    Private Sub EditToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EditToolStripButton.Click

    TBx_EmplLast.ReadOnly = False
    TBx_EmplFirst.ReadOnly = False
    TBx_EmplAddress.ReadOnly = False
    TBx_EmplCity.ReadOnly = False
    TBx_EmplState.ReadOnly = False
    TBx_EmplZip.ReadOnly = False
    TBx_EmplPhone1.ReadOnly = False
    TBx_EmplPhone2.ReadOnly = False
    TBx_EmplFaxNo.ReadOnly = False
    TBx_EmplEmlAddress.ReadOnly = False
    TBx_EmplPosition.ReadOnly = False

    End Sub


    Private Sub SaveToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveToolStripButton.Click
    Dim v_result As Integer

    v_result = MsgBox("Do you want to save record?", vbYesNo)

    If v_result = MsgBoxResult.Yes Then
    Dim v_EmplCode As String
    Dim v_EmplLast As String
    Dim v_EmplFirst As String
    Dim v_EmplAddress As String
    Dim v_EmplCity As String
    Dim v_EmplState As String
    Dim v_EmplZip As String
    Dim v_EmplPhone1 As String
    Dim v_EmplPhone2 As String
    Dim v_EmplFaxNo As String
    Dim v_EmplEmlAddress As String
    Dim v_EmplPosition As String

    Dim v_ConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Joseph\Documents\MS Access 2007\StealStreet.accdb"
    Dim v_strupdate As String

    v_strupdate = "UPDATE Employees " & _
    "SET LastName = v_EmplLast, FirstName = v_EmplFirst, " & _
    "Address = v_EmplAddress, City = v_EmplCity, " & _
    "State = v_EmplState, Zip_Code = v_EmplZip, " & _
    "Phone_No1 = v_EmplPhone1, Phone_No2 = v_EmplPhone2, Fax_No = v_EmplFaxNo, " & _
    "Email_Address = v_EmplEmlAddress, Position = v_EmplPosition " & _
    "WHERE (Employee_Code = v_EmplCode)"

    Dim v_comBldr As New OleDb.OleDbCommand(v_strupdate)

    v_EmplCode = TBx_EmplCode.Text
    v_EmplLast = TBx_EmplLast.Text
    v_EmplFirst = TBx_EmplFirst.Text
    v_EmplAddress = TBx_EmplAddress.Text
    v_EmplCity = TBx_EmplCity.Text
    v_EmplState = TBx_EmplState.Text
    v_EmplZip = TBx_EmplZip.Text
    v_EmplPhone1 = TBx_EmplPhone1.Text
    v_EmplPhone2 = TBx_EmplPhone2.Text
    v_EmplFaxNo = TBx_EmplFaxNo.Text
    v_EmplEmlAddress = TBx_EmplAddress.Text
    v_EmplPosition = TBx_EmplPosition.Text

    v_comBldr.Connection = New OleDb.OleDbConnection(v_ConnectString)
    v_comBldr.Connection.Open()
    v_comBldr.ExecuteNonQuery()
    v_comBldr.Connection.Close()

    MsgBox("Record is saved!")
    Else

    MsgBox("Record not saved!")
    End If

    TBx_EmplLast.ReadOnly = True
    TBx_EmplFirst.ReadOnly = True
    TBx_EmplAddress.ReadOnly = True
    TBx_EmplCity.ReadOnly = True
    TBx_EmplState.ReadOnly = True
    TBx_EmplZip.ReadOnly = True
    TBx_EmplPhone1.ReadOnly = True
    TBx_EmplPhone2.ReadOnly = True
    TBx_EmplFaxNo.ReadOnly = True
    TBx_EmplEmlAddress.ReadOnly = True
    TBx_EmplPosition.ReadOnly = True
    End Sub
    End Class



    System.Data.OleDb.OleDbException was unhandled
    ErrorCode=-2147217900
    Message=Syntax error in UPDATE statement.
    Source=Microsoft Office Access Database Engine

  2. #2
    PowerPoster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    2,306

    Re: error in UPDATE statement / VB2010 and MSACCESS 2007

    I don't know Access but the error indicates a simple syntax error. Plugging the string into MS SQL highlights State and address. Although that would still work the highlighting can be changed by putting them in brackets. Maybe access considers them reserved words? Try the Access equivilants of brackets:

    Code:
    UPDATE employees 
    SET    lastname = v_empllast, 
           firstname = v_emplfirst, 
           [address] = v_empladdress, 
           city = v_emplcity, 
           [state] = v_emplstate, 
           zip_code = v_emplzip, 
           phone_no1 = v_emplphone1, 
           phone_no2 = v_emplphone2, 
           fax_no = v_emplfaxno, 
           email_address = v_emplemladdress, 
           position = v_emplposition 
    WHERE  ( employee_code = v_emplcode )

  3. #3
    Fanatic Member IanRyder's Avatar
    Join Date
    Jan 2013
    Location
    Healing, UK
    Posts
    850

    Re: error in UPDATE statement / VB2010 and MSACCESS 2007

    Hi,

    No, you are going it all wrong and even if your SQL expression was valid it would not have worked anyway. The reason for this is that your are currently doing things in this order:-

    1) Create string variables for the SQL query
    2) Build SQL query using created string variables above
    3) Populate string variables with the values in TextBoxes
    4) Execute Query
    When it should have been:-

    1) Create string variables for the SQL query
    2) Populate string variables with the values in TextBoxes
    3) Build SQL query using created string variables above
    4) Execute Query
    The way you have currently built the SQL expression you have added the string variables (which are totally unnecessary) as Literals to the SQL expression as apposed to the values of the variables. In this case Access will interpret those values as missing field names in the data table and throw an exception.

    The way to do this correctly is to use Parameter markers in your SQL expression and actually add Parameters to the Command Object to build a valid SQL expression. Have a look here:-

    Code:
    'here we structure the SQL expression using the @ Character to define Parameter placements
    Const v_strupdate As String = "UPDATE Employees SET LastName = @LastName WHERE (Employee_Code = @Employee_Code)"
    
    'Here we setup the connection information to the database
    Dim v_ConnectString As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Joseph\Documents\MS Access 2007\StealStreet.accdb")
    Dim v_comBldr As New OleDb.OleDbCommand(v_strupdate, v_ConnectString)
    
    'Here is where we apply the values of the TextBoxes to the values of the Parameters in the SQL expression above - notice, no additional string variables!
    With v_comBldr.Parameters
      .AddWithValue("@Employee_Code", TBx_EmplCode.Text)
      .AddWithValue("@LastName", TBx_EmplLast.Text)
    End With
    
    'Here we run the actual query
    v_ConnectString.Open()
    v_comBldr.ExecuteNonQuery()
    v_ConnectString.Close()
    All you need to do now is expand on this example to add all your fields to the SQL expression and then add all the Parameters to the Command object.

    Hope that helps.

    Cheers,

    Ian

  4. #4

    Thread Starter
    New Member
    Join Date
    Jan 2013
    Posts
    4

    Re: error in UPDATE statement / VB2010 and MSACCESS 2007

    Hi, Ian.

    It worked. No more error. Thanks.
    The update shows in the dataset.
    But it does not update my database.

    Should there be like commit transaction?

    Can you help me?


    Thank you.

    Watertubig

  5. #5
    Fanatic Member IanRyder's Avatar
    Join Date
    Jan 2013
    Location
    Healing, UK
    Posts
    850

    Re: error in UPDATE statement / VB2010 and MSACCESS 2007

    Hi,

    It worked. No more error. Thanks.
    The update shows in the dataset.
    But it does not update my database.
    Your post does not make any sense? Using an SQL query, as in this example, with a valid connection string to your database has nothing to do with a DataSet object in Visual Studio and will only interact with your backend database. So how can a DataSet update correctly and yet the backend not be updated??

    Maybe you need to expand and explain a bit more on the issue that your are now having.

    Cheers,

    Ian

  6. #6

    Thread Starter
    New Member
    Join Date
    Jan 2013
    Posts
    4

    Re: error in UPDATE statement / VB2010 and MSACCESS 2007

    Hi, Ian.

    I run the samples you gave me.
    It is working on the dataset but not updating the database.
    The ExecuteNonQuery value is 0.

    Can you help me?

    Thanks.

    watertubig

  7. #7

    Thread Starter
    New Member
    Join Date
    Jan 2013
    Posts
    4

    Re: error in UPDATE statement / VB2010 and MSACCESS 2007

    Sorry.
    As far as I know it is not updating the database.
    The value of ExecuteNonQuery is 0.

  8. #8
    Fanatic Member IanRyder's Avatar
    Join Date
    Jan 2013
    Location
    Healing, UK
    Posts
    850

    Re: error in UPDATE statement / VB2010 and MSACCESS 2007

    Hi,

    I run the samples you gave me.
    It is working on the dataset but not updating the database.
    As I have already mentioned, running an SQL query against a database has nothing to do with updating a DataSet within VS, so that portion of your comment still does not make any sense? Putting that aside for the moment, this next statement does make sense:-

    Sorry.
    As far as I know it is not updating the database.
    The value of ExecuteNonQuery is 0.
    This tells me two things. Firstly, that you have structured your SQL query correctly and secondly, that the query has been executed against the database is returning a value of zero indicating that no records were updated.

    In this case, your issue is more than likely to be a problem with the Where clause in the SQL statement so you are going to have to do some digging into your own code to find out what is going on. The best way to solve this is to replicate your SQL query within MS Access itself using the Query Builder. You can then test your values that you are trying to pass from Visual Studio and hopefully find out where you are going wrong.

    Hope that helps.

    Cheers,

    Ian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.