SQL Syntax Error I can't figure out

Syster Tara · November 18th, 2006, 09:58 AM

Hello gurus,

I am trying to implement a guestbook. I already have the MS Access database for it with the following fields: Date, Name, Email, Website, and Msg. The script file that accesses the database and lists every entry works perfectly. The problem is with my "Sign Guestbook" script that inserts a new entry into the database. I keep getting the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

/signGuestbook.asp, line 23

The following is my code:

Code:

<%@ LANGUAGE = JavaScript%>
<html>
<head>
<title>Sign Guestbook</title>
</head>
<body>
<%
  var adoConnection = Server.CreateObject("ADODB.Connection");
  adoConnection.Open("DSN=MusicDb");
  var adoRecordset;
  var d = new Date();
  d = (d.getMonth() + 1) + "-" + d.getDate() + "-" + d.getFullYear();
  var n, e, w, m; 
  n = Request.Form("name");
  e = Request.Form("email");
  w = Request.Form("website");
  m = Request.Form("msg"); 
  var mysql = "insert into Guestbook " + "(Date, Name, Email, Website, Msg)" + " values ('";
  mysql = mysql + d + ",'";
  mysql = mysql + n + "','" + e + "','" + w + "','" + m;
  mysql = mysql + "')";

  adoConnection.Execute(mysql);

  Response.Write ("You have added the following entry to the guestbook.<br /><br />");
  var newsql = "select * from guestbook where Name = n";
  adoRecordset = adoConnection.Execute(newsql);

  while (adoRecordset.Eof == false)
  {
  %>
    <p>
    Date: <%=adoRecordset("Date").Value%><br />
    Name: <%=adoRecordset("Name").Value%><br />
    Email: <%=adoRecordset("Email").Value%><br />
    Message: <%=adoRecordset("Msg").Value%><br />
    </p>
  <%
    adoRecordset.MoveNext();
  }

  adoRecordset.Close();
  adoRecordset = null;
  adoConnection.Close();
  adoConnection = null;
 
%>
</body>
</html>

I really hope you gurus can help me. Your help will be greatly appreciated.

Syster Tara

dparsons · November 19th, 2006, 02:16 AM

Change this:
mysql = mysql + d + ",'";

to this:
mysql = mysql + d + "','";

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature

Syster Tara · November 19th, 2006, 11:09 AM

I still get the same error :(

mat41 · November 19th, 2006, 07:27 PM

Your style of coding is very different to myn, why you need so many semi colons in your statement?

Anyhow, I dont see a reason why you need to build your statement over several lines. I do this if building a conditional query, from looking at your code I can not see why you can not:

var mysql = "insert into Guestbook (Date, Name, Email, Website, Msg) values ('" + d + "','" + n + "','" + e + "','" + w + "','" + m + "')";

Wind is your friend
Matt

woodyz · November 20th, 2006, 11:53 AM

I suggest you Response.Write your mysql value to verify that it creates a valid insert statement.
You will then be able to read it, and even run it in your db tool to expose there error.

Are dashes allowed as a separator in dates in an mdb database?

Woody Z http://www.learntoprogramnow.com

mat41 · November 20th, 2006, 07:36 PM

Depending which version, I do believe they are allowed.

Havnt used access for a while however I would suggest using the same very good piece of advice you gave Syster Tara in this post. Use your Access query tool, by trial and error it will answer your question.

BTW: Dont forget to surround your date values with #'s

Wind is your friend
Matt

Syster Tara · November 23rd, 2006, 08:27 PM

Woody,

I have already done that. Could it be that my hosting company does not allow me to have insert permissions for my database? I swear, that's all in the world I can think of that could be the reason behind the error. I still hope you guys can help me. It seems that every time I come here with a problem, I can't be helped.

woodyz · November 24th, 2006, 02:25 AM

Quote:

quote:Originally posted by Syster Tara
Woody,

I have already done that. Could it be that my hosting company does not allow me to have insert permissions for my database? I swear, that's all in the world I can think of that could be the reason behind the error. I still hope you guys can help me. It seems that every time I come here with a problem, I can't be helped.

Most definately you need to get your hosting company to enable write access to the database.

BUT - the error you are getting is that you have a syntax error in the insert statement, which most likely points to a poorly formatted sql statement.

Are you 100% sure you are building a valid insert statement??? Will the javascript date object you are using in your variable named d return a date string as you are using it? Have you tested that?

Woody Z http://www.learntoprogramnow.com

Syster Tara · November 25th, 2006, 09:43 PM

Hi gurus!

I just thought about something. In my Access db for my guestbook, I have an AutoNumber EntryID as the first field, and it's also the primary key. In my code where I build up the "insert" statement, I've been starting with the date and all other fields, so it's probably looking for that autonumbered EntryID. And since it's an autonumber field, will I get an error if I try to insert a number into it with my "insert" statement?

woodyz · November 25th, 2006, 11:57 PM

Quote:

quote:Originally posted by Syster Tara
Hi gurus!

I just thought about something. In my Access db for my guestbook, I have an AutoNumber EntryID as the first field, and it's also the primary key. In my code where I build up the "insert" statement, I've been starting with the date and all other fields, so it's probably looking for that autonumbered EntryID. And since it's an autonumber field, will I get an error if I try to insert a number into it with my "insert" statement?

Your insert statement has named columns and matching values, which should allow the autonumber field to populate itself... thus the "auto" in autonumber - the db will create the new ID and insert it into the field. Each value in the value list is being matched with an item in the column list in your insert statement - so the date is going into the Date column and so on...

Which brings up a question. It might be that ADO does not allow the name "Date" for a column, where apparently Access is allowing it. Try enclosing the word "Date" in your columns list with square brackets:
Insert into whatever ([Date], email, etc) values ...

A better thing will be to NOT USE THE WORD "DATE" as the name of a column. Try to use unique names that are not likely to be keywords or names in any of the underlying technologies you are using (the database, the various languages, etc)

Woody Z http://www.learntoprogramnow.com