Syntax Error in UPDATE statement??

[James]

I keep getting that error msg with this statment:

UPDATE UserIDandPassword SET Password = ? WHERE UserID = ?;

UserIDandPassword is a table
Password is a column
UserID is a column


I didn't think there was an error in that format, but I could be wrong.
Thanks!

 

[RoyVidar]

I keep getting that error msg with this statment:

UPDATE UserIDandPassword SET Password = ? WHERE UserID = ?;

UserIDandPassword is a table
Password is a column
UserID is a column


I didn't think there was an error in that format, but I could be
wrong. Thanks!

Could we also see how you run it?

 

[John Vinson]

I keep getting that error msg with this statment:

UPDATE UserIDandPassword SET Password = ? WHERE UserID = ?;

UserIDandPassword is a table
Password is a column
UserID is a column


I didn't think there was an error in that format, but I could be wrong.
Thanks!

There is indeed an error in that format; ? is not a valid operator.
What are you expecting it to do?

Try

UPDATE UserIdAndPassword SET Password = [Enter new password:] WHERE
UserID = [Enter User ID:]

if you want prompts.


John W. Vinson[MVP]

 

[James]

'?' is just a place holder for a searchParam... i figured it out
though... Password is a reserved word in UPDATE functions... that's
what caused the error... Thanks for the help though!

I keep getting that error msg with this statment:

UPDATE UserIDandPassword SET Password = ? WHERE UserID = ?;

UserIDandPassword is a table
Password is a column
UserID is a column


I didn't think there was an error in that format, but I could be wrong.
Thanks!

There is indeed an error in that format; ? is not a valid operator.
What are you expecting it to do?

Try

UPDATE UserIdAndPassword SET Password = [Enter new password:] WHERE
UserID = [Enter User ID:]

if you want prompts.


John W. Vinson[MVP]

 

[RoyVidar]

There is indeed an error in that format; ? is not a valid operator.
What are you expecting it to do?

Syntax is (nearly) OK to utilize parameters collection with ADO
command.
It just lacks [brackets] around Password.

Sub AdoParamTest()

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim Prm1 As ADODB.Parameter
Dim Prm2 As ADODB.Parameter
Dim strSQL As String

strSQL = "UPDATE UserIDandPassword " & _
"SET [Password] = ? WHERE UserID = ?"

Set cn = CurrentProject.Connection

Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = cn
.CommandText = strSQL
.CommandType = adCmdText

Set Prm1 = .CreateParameter("prm1", adVarWChar, _
adParamInput, 25)
.Parameters.Append Prm1
Prm1.Value = "mypwd"

Set Prm2 = .CreateParameter("prm2", adInteger, adParamInput)
.Parameters.Append Prm2
Prm2.Value = 42

.Execute , , adExecuteNoRecords
End With

End Sub

 

[James]

I got it to work... but actually, my sql stmt is in a table and is read
in as a string... then my searchParams are concatenated into the string
inplace of the ?... it's pretty complicated, but makes for easy updates
and reuse of functions. Anyways, thanks for the help.. I just didn't
realize Password was a reserved SQL word. Thanks again!

There is indeed an error in that format; ? is not a valid operator.
What are you expecting it to do?

Syntax is (nearly) OK to utilize parameters collection with ADO
command.
It just lacks [brackets] around Password.

Sub AdoParamTest()

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim Prm1 As ADODB.Parameter
Dim Prm2 As ADODB.Parameter
Dim strSQL As String

strSQL = "UPDATE UserIDandPassword " & _
"SET [Password] = ? WHERE UserID = ?"

Set cn = CurrentProject.Connection

Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = cn
.CommandText = strSQL
.CommandType = adCmdText

Set Prm1 = .CreateParameter("prm1", adVarWChar, _
adParamInput, 25)
.Parameters.Append Prm1
Prm1.Value = "mypwd"

Set Prm2 = .CreateParameter("prm2", adInteger, adParamInput)
.Parameters.Append Prm2
Prm2.Value = 42

.Execute , , adExecuteNoRecords
End With

End Sub