Hi all, got a strange one. Trying to insert values from a form into a database through an asp page. I keep getting the message: "syntax error in INSERT INTO statement". This is the statement involved:

varname = Request.QueryString("Name")
varemail = Request.QueryString("E-mail")
varmessage = Request.QueryString("Message")
SQLQuery="INSERT INTO Source (Name,E-mail,Message) VALUES"
SQLQuery=SQLQuery & "('" & varname & "','" & varemail & "','" & varmessage & "')"
As implied varname is the variable for the Name field from the form and so on. I've tried adding an ";" at the end of the SQL statement as some versions of SQL like this but to no avail. Can anyone see the problem?

Cheers, Lol

Check around the part where you execute the sql statement ??

What is the language, what is the DB and what is the full error reported by database?

Just before you execute the SQL statement, you should dump the value of your SQLQuery. By the way, what can be included in your Message querystring variable? Be careful of single quotation being included in the message. This is one possible cause of crashing the SQL statement. Have Fun

Hi, the error message is:

Technical Information (for support personnel)

• Error Type:
  Microsoft JET Database Engine (0x80040E14)
  Syntax error in INSERT INTO statement.
  /17minutes/enter.asp, line 11
  
• Browser Type:
  Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)
• Page:
  POST 65 bytes to /17minutes/enter.asp
• POST Data:
  Name=Jim&E-mail=Jim@aol.com&textarea=further+tested&Submit=Su bmit
• Time:
  16 May 2006, 12:03:01
  
• More information:

This is where I tried hard coding the values rather than pulling from the form. It's all done in VBScript connecting to an MS Access database. I just don't know what the hell else to try after this.

Thanks, Lol

use

response.write SQLQuery
response.end

immediately after concatenating SQLQuery and show us the resultant query.

is the table you are inserting to called "source" BTW

Chris, I have to confess the table is NOT called Source, that's the db name I realised that at 11.30 in bed last night! I have included below fresh code I am trying but not getting very far.

<<A href="mailto:%@LANGUAGE="VBSCRIPT">%@LANGUAGE="VBS CRIPT" CODEPAGE="1252"%>
<%strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath ("db\Source.mdb") & ";" ' text for connection
Set cn17min = Server.Createobject("ADODB.Connection") ' set new ADODB connection
cn17min.Open strConnection ' open connection
varname = Request.QueryString("Name")
varemail = Request.QueryString("E-mail")
varmessage = Request.QueryString("Message")
SQL = "Insert Into tblGuest (Name,E-mail,Message) Values ('" & varname & "','" & varemail & "','" & varmessage & "' );"
cn17min.Execute (SQL)
Set cn17min = Nothing ' close connection
%>

This is in an asp page. I use an html page with form and form fields "Name","E-mail" and "Message" to Post to the asp page. The database is in a folder called "db". There's perhaps some nuance I'm missing here but I'm stumped. I'm actually self teaching my way through an HND in Business IT. it's self taught because the lecturers teach us nada! Hope you can be your usual saviour, cheers Lol.

p.s. this is not technically for my course but I do need to master the situation.

Forgot to add, this is the error message:

• Error Type:
  Microsoft JET Database Engine (0x80040E14)
  Syntax error in INSERT INTO statement.
  /17minutes/Guest_add.asp, line 10
  
• Browser Type:
  Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)
• Page:
  POST 58 bytes to /17minutes/Guest_add.asp
• POST Data:
  Name=Lol&E-mail=Lol@lol.com&textarea=testing&Submit=Submit
• Time:
  16 May 2006, 13:10:31
  
• More information:
  Microsoft Support

In bed at 11:30 what kind of talk is that ??

SQL doesn't allow column names with hyphens ( not sure why I didn't spot that before but hey ho! )

<added>

Access will quite happily allow these if you add square brackets [ ] around the column name in queries
</added>

11.30? After a DAY fighting with this bloody thing I was reaching for the rope! I've changed the field name to Email and I'll alter the statement to reflect this and try it. Here's hoping!

Jusat altered everything and I get this message:

Error Type:
Microsoft JET Database Engine (0x80004005)
Field 'tblGuest.Name' cannot be a zero-length string.
/17minutes/Guest_add.asp, line 10

Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)
Page:
POST 69 bytes to /17minutes/Guest_add.asp
POST Data:
Name=fwfwefwefwefwfw&Email=Jim@aol.com&textarea=te sting&Submit=Submit
Time:
16 May 2006, 13:46:49 Doesn't make sense as whilst the allow zero length is set to No in Access, I've entered a string and it isn't seeing anything?

Strange!

It's almost as if it's not picking up anything from the form on the html page. Any ideas?

Cheers, Lol

just set Name field on dbase to allow zero length entries. Chucks out a failure on email saying no zero length entries allowed. It's obviously not picking up off the form but I'm stumped as to why!

response.write out the query before you call the insert, then you'll see what is being sent to Access.

surprising what light that often sheds

Hi Chris, did the response.write and it showed nothing getting to the asp page. Then a friend suggested changing the "POST" to "GET" and everything worked!!
Probably a noob question but seeing as my form was on an HTML page I thought you would have to POST the data to the ASP page. Obviously not! What are the rules for POST and GET?

Thanks for all your help, Lol

p.s. I cannot give you anymore talkupation without spreading some love elsewhere

if you use "GET" then the values are sent in the URL and are retrieved by using request.querystring("name")

using "POST" you have to retrieve the values with request.form("name")

you can use the request object collection ( request("name") ) but that is slower, as it has to cycle through all the request objects (cookies, form, querystring & servervariables)

what would a more speedy solution be then?

Use GET if you are passing basic information, and use POST if you are passing sensitive data or long text blocks

But why did my pages work with GET but not with POST ?

You must specify the method inside the <form> tag in the html (method="POST") I believe it defaults to GET