SQL Syntax Error I can't figure out

Syster Tara · November 26th, 2006, 12:53 AM

Now I get the following error:

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

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

/signGuestbook.asp, line 44

It points to the "adoConnection.Execute(mySQL);" line. Attached is my most recently revised code that generated this error:

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)";
   mySQL = mySQL + " VALUES ('" + d + "','";
   mySQL = mySQL + Request.Form("name") + "','";
   mySQL = mySQL + Request.Form("email") + "','";
   mySQL = mySQL + Request.Form("website") + "','";
   mySQL = mySQL + Request.Form("msg") + "','";
   mySQL = mySQL + "')";
*/
   var mySQL = "INSERT INTO [Guestbook] ([Date]," 
            + " [Name]," 
            + " [Email]," 
            + " [Website],"  
            + " [Msg])" 
            + " VALUES ('" + d + "'" 
            + ", '" + n + "'" 
            + ", " + e
            + ", '" + w + "'" 
            + ", '" + m + "')";

 /* 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>

Sorry to be such a pest

woodyz · November 26th, 2006, 11:04 AM

Quote:

quote:Originally posted by Syster Tara
var mySQL = "INSERT INTO [Guestbook] ([Date],"
            + " [Name],"
            + " [Email],"
            + " [Website],"
            + " [Msg])"
            + " VALUES ('" + d + "'"
            + ", '" + n + "'"
            + ", " + e
            + ", '" + w + "'"
            + ", '" + m + "')";

It looks like the email variable(e) is no longer enclosed in single quotes as it was in the earlier code.

Woody Z http://www.learntoprogramnow.com

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

Syster Tara

When I ask a question that gets ignored I tend to sway from a post. Take woodyz advise on your naming of field names. There are very few 'Reserved Words' you should steer clear of. There are very good reasons for this, and so so many others to choose from. Yes you can use [] but why? For a full list of reserved words ask google 'access reserved words' FYI 'Date' is one of them no matter what version you are using.

Wind is your friend
Matt

Syster Tara · November 29th, 2006, 04:23 PM

I still get too few parameters, even after changing "Date" to "EntryDate" in both the db and asp script.

I have uploaded a screencap of my guestbook structure with field names and stuff at http://www.tarasdemos.com/images/guestbook.gif, so please look at it and then look at my code and see if everything looks correct to you as it does to me.

Here is yet another look at my script after the above changes were made:

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 " + 
      "(EntryDate, Name, Email, Website, Msg)";
   mySQL = mySQL + " VALUES ('" + d + "','";
   mySQL = mySQL + Request.Form("name") + "','";
   mySQL = mySQL + Request.Form("email") + "','";
   mySQL = mySQL + Request.Form("website") + "','";
   mySQL = mySQL + Request.Form("msg") + "','";
   mySQL = mySQL + "')";
*/
   var mySQL = "INSERT INTO [Guestbook] ([EntryDate]," 
            + " [Name]," 
            + " [Email]," 
            + " [Website],"  
            + " [Msg])" 
            + " VALUES ('" + d + "'"
            + ", '" + n + "'" 
            + ", '" + e + "'"
            + ", '" + w + "'" 
            + ", '" + m + "')";

 /* var mysql = "insert into Guestbook " + "(EntryDate, 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("EntryDate").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>

woodyz · November 29th, 2006, 05:47 PM

Well. First of all, please let me know if it is still the same line of code that is causing the error, just to make sure.

The error "Too Few Parameters" usually points to a misspelled column name. It looks like you are not having that, but to make sure, you might want to output the mySql string that is created and run that directly in Access to see if any error occur there.

Woody Z http://www.learntoprogramnow.com

Syster Tara · November 30th, 2006, 08:07 PM

OK, I finally got it to add the entry to the guestbook, but now it's fussing about "too few parameters" in this other line (in bold):

Code:

var newsql = "select * from Guestbook where Name = n";
Response.Write (newsql);
adoRecordset = adoConnection.Execute(newsql);

As you can see above, I response.wrote the sql out, and again, I don't see anything wrong with it. Can your eagle eyes spot anything?

woodyz · November 30th, 2006, 08:10 PM

Quote:

quote:Originally posted by Syster Tara
OK, I finally got it to add the entry to the guestbook, but now it's fussing about "too few parameters" in this other line (in bold):

Code:

var newsql = "select * from Guestbook where Name = n";
Response.Write (newsql);
adoRecordset = adoConnection.Execute(newsql);

As you can see above, I response.wrote the sql out, and again, I don't see anything wrong with it. Can your eagle eyes spot anything?

Okay - I bet we are seeing the same problem... try

Code:

var newsql = "select * from Guestbook where [Name] = '" + n + "'";
Response.Write (newsql);
adoRecordset = adoConnection.Execute(newsql);

Or soemthing like that.

Woody Z http://www.learntoprogramnow.com

mat41 · November 30th, 2006, 08:32 PM

;;;As you can see above, I response.wrote the sql out
We want to see what the browser outputs not the line of code which executes the output

You have not taken advice given. Why use Acess Reserved words as field names?? You can
use them however here is a full list of the words you should not use:
http://support.microsoft.com/kb/286335

Your query:
var newsql = "select * from Guestbook where Name = n";
Change it to (note inclusion of single quotes for a string field comparison and move the semi colon inside your string):
var newsql = "select * from Guestbook where Name = 'n';"

Wind is your friend
Matt

mat41 · November 30th, 2006, 08:38 PM

Syster Tara

If 'n' is a variable use the syntax Woody posted however if its a hard coded string comparison keep it where it is but sourround it in single quotes, exactly as suggested in my last post. Either way, using ASP and SQL place the semi colon inside your string.

Wind is your friend
Matt

woodyz · November 30th, 2006, 11:09 PM

Quote:

quote:Originally posted by mat41
Syster Tara

If 'n' is a variable use the syntax Woody posted however if its a hard coded string comparison keep it where it is but sourround it in single quotes, exactly as suggested in my last post. Either way, using ASP and SQL place the semi colon inside your string.

Wind is your friend
Matt

Well... n is a variable:

Code:

n = Request.Form("name");

And since Javascript is being used here as the server side language, the semicolon is the line terminator for this code.

Of course, if this was VBScript, the semi-color would give an error.

Woody Z http://www.learntoprogramnow.com