Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2006
    Posts
    11

    Unanswered: INSERT not working properly

    I have a SELECT query that does a search criteria then it takes those values and places them into an Archive table. So inside a loop while going through the recordset (rstRecSet) I insert the values into that Archive table.

    My INSERT looks like this:

    SQLInsert = "INSERT INTO Archive (PLAYERNUMB, LNAME, FNAME) " & _
    " VALUES (" & rstRecSet("PLAYERNUMB") & ", '" & rstRecSet("LNAME") & "', '" & rstRecSet("FNAME") & "');"

    but I get the following error when I hit a single quote in a persons name:

    [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''O'CONNELL', 'AMY');'.

    It works perfectly fine except for when I hit the a name with an single quote in it.

    I tried a few approaches to this (wrapped them with double quotes, used double single qutoes), but still nothing.

    I know what my problem is, but just can't find the right solution. Any ideas?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the double single quotes will work

    what's of more concern is the loop driving a single-row insert

    why not run an INSERT SELECT and insert all the rows at once?
    Code:
    INSERT 
      INTO Archive 
         ( PLAYERNUMB, LNAME, FNAME )
    SELECT PLAYERNUMB, LNAME, FNAME
      FROM ...
     WHERE ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2006
    Posts
    11
    Ahh k, never realized you can do that with one statement. Thanks.

    As far as the INSERT

    ' ' " & rstRecSet("LNAME") & " ' '

    That should do the trick (used spaces so it isn't hard on the eyes) ?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    with the INSERT SELECT, your're not going to be using rstRecSet
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2006
    Posts
    11
    Thanks for the help, much appreciated. But one more question to bug you.

    SQLInsert = " INSERT " & _
    " INTO Archive " & _
    " (PLAYERNUMB, LNAME, FNAME) " & _
    " SELECT PLAYERNUMB, LNAME, FNAME " & _
    " FROM Player " & _
    " WHERE ACTIVE = FALSE;"

    If I do a response.write ""&SQLInsert&""

    INSERT INTO Archive (PLAYERNUMB, LNAME, FNAME) SELECT PLAYERNUMB, LNAME, FNAME FROM Player WHERE ACTIVE = FALSE;

    No loops needed?

    Sorry about this, never done an INSERT .. SELECT... before.

    EDIT: Ok, tried the actual query right inside MS Access and it works great. So I just answered my own question there, no loops needed at all. Still not working properly in the ASP page, so I'm going to have to keep trying different approaches. Kind of curious though, cause when I did it in MS Access I got a promprt asking if I want to continue with the APPEND query. Wonder if ASP sees that prompt when I run the query or if it just ignores it.

    Ok I got it, turns out that I'm just an idiot thats all.

    Thanks ever so much for your help, much appreciated. Just need to fire off a query to delete the records that were moved in the original table to the archive table and I'll be set.

    Thanks again.
    Last edited by davidevan; 11-05-06 at 21:37.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my pleasure
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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