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

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!

Who is Participating?
david_levineConnect With a Mentor Commented:
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.
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.

PvBredowAuthor Commented:
        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.

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

PvBredowAuthor Commented:
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.

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?
PvBredowAuthor Commented:

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.
PvBredowAuthor Commented:
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.
PvBredowAuthor Commented:
       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!)
PvBredowAuthor Commented:
       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.
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.