Results 1 to 5 of 5

Thread: SQL Update Query - Cannot get it to work!

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Oct 2000
    Posts
    68

    Angry

    Here is the problem:

    I am trying to execute the action query UPDATE but I get an error "Too few parameters, expected '1' "

    Here is the code:

    Dim qdfAppend As QueryDef
    Dim AppendDb As Database
    Dim AppendRs As Recordset
    Dim AppendCon As String

    Dim strSQLAppend As String

    AppendCon = "ODBC;DRIVER={SQL Server};UID=;PWD=;DATABASE=MAV-DB;SERVER=Exch_Serv"

    Set AppendDb = DAO.OpenDatabase("", 0, 0, AppendCon)
    Set qdfAppend = AppendDb.CreateQueryDef("", strSQLAppend)


    strSQLAppend = "UPDATE " & DataTable & " SET " & Exist & " = " & Chr(39) & "1" & Chr(39) & " WHERE " & PartNo_Field & " = " & PartNumber


    ' qdfAppend.SQL = strSQLAppend
    AppendDb.Execute strSQLAppend

    This line is where it errors

    Thanks for your help

    Simon

  2. #2
    Addicted Member Skeen's Avatar
    Join Date
    Jul 2000
    Location
    Abingdon, Oxon
    Posts
    138
    I don't think you need to put the flags on all of that, only the variables:

    strSQLAppend = "UPDATE DataTable " _
    & "SET Exist = '" & Chr(39) & "' " _
    & " '1' '" & Chr(39) & "' " _
    & "WHERE PartNo_Field = '" & PartNumber & "' "


    Try That, I rekon it should work, remember the tags go round variables:

    = " '" & variablename & "' "
    Hope this helps

    Cheers 'n' beers
    Skeen
    "It wasn't the booze that made me snooze, It was the Gin that did me in!"

  3. #3
    Fanatic Member
    Join Date
    Oct 2000
    Location
    London
    Posts
    1,008
    Simon,

    See my e-mail. The problem is definitely in the SQL String. There is some problem with synbolic substitution i.e. DataTable etc.

    Cheers,

    Paul.
    Not nearly so tired now...

    Haven't been around much so be gentle...

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Oct 2000
    Posts
    68
    Chr(39) = '
    Therefore where ever a chr(39) exists the is the flag surrounding the variable.

    Thanks for your help but I have used the same SQL string method for all my select statements and they work fine.

    I think that I have found the problem in that there are no "'" surrounding the last variable in the UPDATE statement.

    Let me try that

    Cheers Anyway

    Simon

  5. #5
    Fanatic Member
    Join Date
    Oct 2000
    Location
    London
    Posts
    1,008
    So PartNo_Field is a text field? Leaving off "" IS a problem with your SQL String.

    BTW Do you have Access? If so, do a bench test with the code on simple test tables, it is quick and easy and often indicates initial problems which you can then correct quickly and easily.

    Cheers,

    Paul
    Not nearly so tired now...

    Haven't been around much so be gentle...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width