Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Basics
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 18th, 2006, 09:58 AM
Authorized User
 
Join Date: Sep 2006
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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

Reply With Quote
  #2 (permalink)  
Old November 19th, 2006, 02:16 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
Reply With Quote
  #3 (permalink)  
Old November 19th, 2006, 11:09 AM
Authorized User
 
Join Date: Sep 2006
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I still get the same error :(

Reply With Quote
  #4 (permalink)  
Old November 19th, 2006, 07:27 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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
Reply With Quote
  #5 (permalink)  
Old November 20th, 2006, 11:53 AM
Friend of Wrox
 
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old November 20th, 2006, 07:36 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

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
Reply With Quote
  #7 (permalink)  
Old November 23rd, 2006, 08:27 PM
Authorized User
 
Join Date: Sep 2006
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #8 (permalink)  
Old November 24th, 2006, 02:25 AM
Friend of Wrox
 
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #9 (permalink)  
Old November 25th, 2006, 09:43 PM
Authorized User
 
Join Date: Sep 2006
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

Reply With Quote
  #10 (permalink)  
Old November 25th, 2006, 11:57 PM
Friend of Wrox
 
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Error in SQL syntax phantom3008 ASP.NET 1.0 and 1.1 Basics 1 April 26th, 2007 07:00 AM
Example SQL causes syntax error Tom Rigby BOOK: Beginning SQL 1 April 18th, 2007 09:32 AM
SQL syntax error - Chapter 3 cutthroatchemist BOOK: Beginning PHP5, Apache, and MySQL Web Development ISBN: 978-0-7645-7966-0 2 January 15th, 2007 06:19 PM
Syntax error on SQL statement Chris1 Access VBA 1 September 7th, 2004 07:47 AM
$sql="CREATE TABLE syntax error cedtech23 Beginning PHP 3 August 24th, 2004 04:56 AM



All times are GMT -4. The time now is 07:21 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.