Syntax error in update statement - novice question

[sean]

Hi,

I am trying to update an access database using an SQL statement, I keep
getting an syntax error in update statment. I am passing in strChkBoxValue
as a string value which is declared in another function. Could someone help
me fix my code.

Sean
Dim oComm As OleDbDataAdapter
Dim sConn As String
Dim oDataSet As New DataSet
Dim word as string

'Build the connection string
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
sConn += "Data Source =" & Server.MapPath("email.mdb") & ";"
sConn += "Persist Security Info=False"
Dim strSQL as String = _
"Update members Set Email='" & _
strChkBoxValue & "', " & _
"' WHERE email='" & cstr(strChkBoxValue)

Dim oConn as New OleDBConnection(sConn)
Dim objCmd as New OLEDBCommand(strSQL, oConn)

oConn.open
objCmd.ExecuteNonQuery()
oConn.Close

[Gerald]

Could you post the response.Write of your SqlString.
But it appears it is false. Too many single quotes and comma.
So one reason could be this.
Yours is :
Dim strSQL as String = "Update members Set Email='" & strChkBoxValue & "', '
WHERE email='" & cstr(strChkBoxValue)

Dim strSQL as String = "Update members Set Email='" & strChkBoxValue & "'
WHERE email='" & cstr(strChkBoxValue) & "'"

Second is what the point to change the value of the email only when the
email is equal to the new value. It wont change anything as the new value
equals the old one.

G
"sean" <se********@shopsmart.com.au> wrote in message
news:uB**************@TK2MSFTNGP09.phx.gbl...

Hi,

I am trying to update an access database using an SQL statement, I keep
getting an syntax error in update statment. I am passing in strChkBoxValue
as a string value which is declared in another function. Could someone help me fix my code.

Sean
Dim oComm As OleDbDataAdapter
Dim sConn As String
Dim oDataSet As New DataSet
Dim word as string

'Build the connection string
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
sConn += "Data Source =" & Server.MapPath("email.mdb") & ";"
sConn += "Persist Security Info=False"
Dim strSQL as String = _
"Update members Set Email='" & _
strChkBoxValue & "', " & _
"' WHERE email='" & cstr(strChkBoxValue)

Dim oConn as New OleDBConnection(sConn)
Dim objCmd as New OLEDBCommand(strSQL, oConn)

oConn.open
objCmd.ExecuteNonQuery()
oConn.Close

[Kevin Spencer]

You're missing the last single quote after the words "' WHERE email='" &
cstr(strChkBoxValue)

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.
"sean" <se********@shopsmart.com.au> wrote in message
news:uB**************@TK2MSFTNGP09.phx.gbl...

Hi,

I am trying to update an access database using an SQL statement, I keep
getting an syntax error in update statment. I am passing in strChkBoxValue
as a string value which is declared in another function. Could someone help me fix my code.

Sean
Dim oComm As OleDbDataAdapter
Dim sConn As String
Dim oDataSet As New DataSet
Dim word as string

'Build the connection string
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
sConn += "Data Source =" & Server.MapPath("email.mdb") & ";"
sConn += "Persist Security Info=False"
Dim strSQL as String = _
"Update members Set Email='" & _
strChkBoxValue & "', " & _
"' WHERE email='" & cstr(strChkBoxValue)

Dim oConn as New OleDBConnection(sConn)
Dim objCmd as New OLEDBCommand(strSQL, oConn)

oConn.open
objCmd.ExecuteNonQuery()
oConn.Close