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

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 )

---

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:-

Quote:

---

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:-

Quote:

---

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

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

Hi,

Quote:

---

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

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

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

Hi,

Quote:

---

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:-

Quote:

---

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