What is wrong with my SQL string?

[KUvbc#]

I get a syntax error while trying to update a datatable using this syntax string:
"SELECT * FROM ClientContacts WHERE MembershipID = '" & stringVariable & "' "

I can't figure out what is wrong with it

[Joacim Andersson]

There doesn't seem to be anything wrong with the query string, at least not if your ClientContacts table has a MembershipID. So what is the syntax error you're getting? Maybe its the way you use it, can you post some more of your code please.

[jmcilhinney]

If that SQL code is indeed the issue then the only explanation is that there's something amiss with the value you're inserting into it. For that reason (and others) you should never use string concatenation to build SQL strings. You should always use parameters. For more information, follow the last link in my signature.

[KUvbc#]

The error is:

System.Data.OleDb.OleDbException was unhandled
Message="Syntax error (missing operator) in query expression '((MembershipID = ?) AND ((? = 1 AND First Name IS NULL) OR (First Name = ?)) AND ((? = 1 AND Last Name IS NULL) OR (Last Name = ?)) AND ((? = 1 AND Phone IS NULL) OR (Phone = ?)) AND ((? = 1 AND Street IS NULL) OR (Street = ?)) AND ((? = 1 AND City IS NUL'."
Source="Microsoft Office Access Database Engine"
ErrorCode=-2147217900

My button event saves changes made to a datagridview
dt and connStr are declared properly at a class level and I get no errors or exceptions from the Visual Studio.

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim search = Form2.txtID.Text.ToUpper
Dim changes As Integer

'Open a connection to the database for updating

Dim dataAdapter As New OleDb.OleDbDataAdapter("SELECT * FROM ClientContacts WHERE MembershipID = '" & search & "' ", connStr)
Dim commandBuilder As New OleDb.OleDbCommandBuilder(dataAdapter)

'Update the database with changes from the data table

changes = dataAdapter.Update(dt)
dataAdapter.Dispose()
'Display the number of changes made
If changes > 0 Then
MessageBox.Show(changes & " changed rows were stored in the database.")
Else
MessageBox.Show("No changes made.")
End If
End Sub

[techgnome]

Sigh... the problem is your field names.... you can't (or more accurately SHOULDN'T) use spaces in the middle of field names... "first name" should be "firstname" .... that's what the problem is...

solve it one of two ways: 1) change your field names so that they don't have spaces in them. 2) in your select, EXPLICITLY specify your fields, and fields with spaces, surround them with square brackets [First Name]

-tg

[jmcilhinney]

OK, so you're not getting a syntax error in the SQL you posted at all. This is why it's important to provide a FULL and CLEAR description of EXACTLY what's happening, which includes the code you're using, the error message and EXACTLY where the error occurs.

The problem is that the command builder is basing the SQL code it generates off your query and some of your column names contain spaces. The best solution is to rename those columns by removing the spaces and NEVER use spaces in column names again, e.g. 'Last Name' becomes 'LastName'. If that's not possible for some reason then you have two choices:

1. Don't use a wildcard for the column list in your query. Enter the name of each column explicitly, in which case you'll be forced to escape those troublesome names, i.e. wrap them in brackets. The command builder will then follow your lead.

2. Don't use a command builder. Create your own DELETE, INSERT and/or UPDATE statements and escape the column names as required.

[KUvbc#]

Thanks so much guys, I figured it out right as I refreshed the page and saw your comments.