 |
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0 |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

November 18th, 2006, 09:58 AM
|
Authorized User
|
|
Join Date: Sep 2006
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
SQL Syntax Error I can't figure out
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
|

November 19th, 2006, 02:16 AM
|
Wrox Author
|
|
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
|
|
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
|

November 19th, 2006, 11:09 AM
|
Authorized User
|
|
Join Date: Sep 2006
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I still get the same error :(
|

November 19th, 2006, 07:27 PM
|
Friend of Wrox
|
|
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
|
|
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
|

November 20th, 2006, 11:53 AM
|
Friend of Wrox
|
|
Join Date: May 2006
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

November 20th, 2006, 07:36 PM
|
Friend of Wrox
|
|
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
|
|
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
|

November 23rd, 2006, 08:27 PM
|
Authorized User
|
|
Join Date: Sep 2006
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

November 24th, 2006, 02:25 AM
|
Friend of Wrox
|
|
Join Date: May 2006
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

November 25th, 2006, 09:43 PM
|
Authorized User
|
|
Join Date: Sep 2006
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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?
|

November 25th, 2006, 11:57 PM
|
Friend of Wrox
|
|
Join Date: May 2006
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
 |