Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page