Syntax error with SQL 'Insert into ...' in VbScript ASP with Access database

Posted on 2006-05-25
Last Modified: 2008-01-09
I have successfully added data into Access databases previously using SQL 'insert into ...' using VbScript on ASP pages.
I have an application that does logging of user activity on my website, and I am trying to use Access for this purpose.
For some reason I can not figure out, I am hitting syntax errors this time, and all my effort to fix it.

My code is:
      ' Write the form results to the file.
      ' declare your variables

      Dim dbPath, objConn, objRS, sSQL, aRS, SFile, SUser
      dbPath = "C:\SpiderLog\spiderlog.mdb"

           Set objConn = CreateObject("ADODB.Connection")
           Set objRS = CreateObject("ADODB.Recordset")

      ' declare SQL statement that will insert into the database
      'sSQL="select User, File from SpiderLog"
      'objRS.Open sSQL, objConn
      'aRS = objRS.GetRows()
      'response.write aRS(0,0) & "<br>"
      'response.write aRS(1,0) & "<br>"
      'response.write "<br>read from SpiderLog completed<br><br>"      

      sSQL = "INSERT into SpiderLog "
      sSQL = sSQL & "(User,File) "
      sSQL = sSQL & " Values "
      sSQL = sSQL & "("

      sSQL = sSQL & "'" & SUser  & "', "
      sSQL = sSQL & "'" & SFile  & "'"
      ' sSQL = sSQL & "'" & date() & "', "
      ' sSQL = sSQL & "'" & time() & "'"

      sSQL = sSQL & ")"

      response.write sSQL & "<br><br>"

      ' execute the SQL


      ' now that the log file has been written, tell the client browser to open the requested file
      response.write "logging : completed"
      'Response.Write("<script language = ""Javascript"">var""" & SFile & """);</script>")

When I call it using ' to mdb.asp?LogUser=pvbredow&LogSubject=somefile

it appears that the parameters are being passed in correctly.

You will notice some lines (commented out) in which I read from the database.     When those lines were active, they should that the database location & name & fields were all correct, since I had no trouble READING from that database.

Can any spot what I am doing wrong?       This is really getting frustrating!        Worse than teleconferences!

Question by:PvBredow
    LVL 9

    Expert Comment

    Could be a couple things. It might be helpful to post the sSQL output of the SQL you built to see if there's anything obvious. You have a response.write for it already in your code.

    If there's single quotes in either SUser or SFile that could cause an issue.

    Check the Access database and make sure there's no unique index on the SUser or SFile columns. It could be the unique index preventing you from inserting multiple records for the same user or file.


    Author Comment

            The sSQL value is:
    INSERT into SpiderLog (User,File) Values ('pvbredow','somefile')

    Is there anything wrong with that syntax?     I have experimented with variations on that, but nothing seems to work.
    The fields User & File are both text fields in my MDB.


    Author Comment

    The exact error I get is:

    Microsoft JET Database Engine error '80040e14'

    Syntax error in INSERT INTO statement.

    /submit to mdb.asp, line 44

    in case there is any valuable information in that.

    LVL 9

    Expert Comment

    I quickly created a version of your table in Access, copied the SQL and it inserted the row (in Access) so the SQL appears fine.

    I don't recall if Access would tell you that there's a constraint (unique index) on some column or it says Syntax error, which would be odd. Did you check to make sure you don't have unique indexes on either column?

    Author Comment


    The database is a single table, 5 columns, autonumber for unique key, text for User & File, a date field & a time field.       That's it!       I just created the table by doing an import of some data from Excel, so essentially all settings are at the default value.

    Earlier I wonder if I could hit an error from text values overfilling the fields, but the fields are set to permit 255 characters, and the example I sent in is far less than that, but still fails.

    I have also tried inserting either field separately, but that also failed.

    I have no trouble displaying the data (from the original import) in Access, so I don't think the table has been corrupted.       Perhaps I should try creating a new database, in case there is some corruption of the MDB?

    Thanks for your efforts.
    LVL 9

    Accepted Solution

    If you do like I did and create a new query in Access, go to View / SQL and then copy/paste in:

    INSERT into SpiderLog (User,File) Values ('pvbredow','somefile')

    does it generate an error?

    I created a table with just 2 columns for User and File. See what the above SQL does on your 5 column table w/ AutoNumber.

    Author Comment

    I tried your suggestion, and it does NOT cause an error.        That seems like progress, but I'm still not sure what that leads to as a next step.

    Author Comment

           I found a solution!       The problem was that it seems that File & User are reserved words, so when I tried to use them as field names, the SQL didn't parse properly.       As soon as I renamed them to SFile & SUser, no more problems!

    Thanks for all your efforts to help me.        Hope it strikes you as fair if I accept your answer, but only award 250 points?      (I can't award myself points for answering my own question - too bad!)

    Author Comment

           I don't seem to have the skills to give you only half marks, so you get the whole amount.      In any case, you were the only person to offer any suggestions.

            thanks very much.
    LVL 9

    Expert Comment

    Something else to try if you want, is to put the column names in []'s. An example is:
    INSERT into SpiderLog ([User],[File]) Values ('pvbredow','somefile')

    What I find odd is why it's handled via Access directly but not through ASP. I should have suggested the above as I've used it in the past but I thought it was only if it choked on it when using it within Access to.

    I've moved to MySQL for all web development because it's just easier to deal with the schema aspects of modifying the MDB once it's on a real web server that you can't access from a client.
    Thanks for the points.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Title # Comments Views Activity
    Verify person is in the US 6 90
    I starting with php 11 68
    Age between date range query (SP) 13 38
    Update field in order 21 93
    When pages do not download correctly, and you don't know why, the first thing you do is to look at the HTML source code of that page, but not all the downloaded files appear always clearly. If your source includes a javascript that computes the name…
    Accessibility and Usability are two concepts that seem to be closely related.  But, too many people seem to have a distorted perception of them. During last five years, those two words have come to the day-to-day work of almost every web develope…
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now