Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Syntax Error in UPDATE statement??

Discussion in 'Information Technology' started by James, Jul 28, 2006.

  1. James

    James
    Expand Collapse
    Guest

    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!
     
  2. Loading...


  3. RoyVidar

    RoyVidar
    Expand Collapse
    Guest

    "James" <jamesburkett@gmail.com> wrote in message
    <1153428031.069339.305860@s13g2000cwa.googlegroups.com>:
    > 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?

    --
    Roy-Vidar
     
  4. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On 20 Jul 2006 13:40:31 -0700, "James" <jamesburkett@gmail.com> wrote:

    >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]
     
  5. James

    James
    Expand Collapse
    Guest

    '?' 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!


    John Vinson wrote:
    > On 20 Jul 2006 13:40:31 -0700, "James" <jamesburkett@gmail.com> wrote:
    >
    > >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]
     
  6. RoyVidar

    RoyVidar
    Expand Collapse
    Guest

    "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    <07svb2tnb8cucputueg4s4q7u9mah6l59f@4ax.com>:

    > 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

    --
    Roy-Vidar
     
  7. James

    James
    Expand Collapse
    Guest

    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!


    RoyVidar wrote:
    > "John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    > <07svb2tnb8cucputueg4s4q7u9mah6l59f@4ax.com>:
    >
    > > 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
    >
    > --
    > Roy-Vidar
     

Share This Page