asp to ms access - Syntax error in INSERT INTO statement

Hi

I'm making a simple form in asp which then updates a database table. I'm using code I've used before with different values and variables. Very confused why it's doing this, please help1
<%
'
Dim conn, rs, sql
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:\Inetpub\ftproot\hgca\data\surveys\agronomist.mdb"
set rs=Server.CreateObject("ADODB.recordset")
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' link questions to database names
'
Dim q1, q2, q3, q4, q5, q6, q7, q8, q9, comments, user_name, user_address, user_postcode, user_email, cereals_ha, cereals_units, oilseeds_ha, oilseeds_units, region, d1, d2, d3, d4
' set default values
comments = ""
user_name = ""
user_address = ""
user_postcode = ""
user_email = ""
cereals_ha = ""
cereals_units = ""
oilseeds_ha = ""
oilseeds_units = ""
region = ""
d1 = ""
d2 = ""
d3 = ""
d4 = ""
'
q1 = Request.Form("q1")
q2 = Request.Form("q2")
q3 = Request.Form("q3")
q4 = Request.Form("q4")
q5 = Request.Form("q5")
q6 = Request.Form("q6")
q7 = Request.Form("q7")
q8 = Request.Form("q8")
q9 = Request.Form("q9")
comments = Request.Form("comments")
user_name = Request.Form("user_name")
user_address = Request.Form("user_address")
user_postcode = Request.Form("user_postcode")
user_email = Request.Form("user_email")
cereals_ha = Request.Form("cereals_ha")
cereals_units = Request.Form("cereals_units")
oilseeds_ha = Request.Form("oilseeds_ha")
oilseeds_units = Request.Form("oilseeds_units")
region = Request.Form("region")
d1 = Request.Form("d1")
d2 = Request.Form("d2")
d3 = Request.Form("d3")
d4 = Request.Form("d4")
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
sql = "INSERT INTO agronomist (q1, q2, q3, q4, q5, q6, q7, q8, q9, comments, user_name, user_address, user_postcode, user_email, cereals_ha, cereals_units, oilseeds_ha, oilseeds_units, region, d1, d2, d3, d4) VALUES ('"&q1&"', '"&q2&"', '"&q3&"', '"&q4&"', '"&q5&"', '"&q6&"', '"&q7&"', '"&q8&"', '"&q9&"', '"&comments&"', '"&user_name&"', '"&user_address&"', '"&user_postcode&"', '"&user_email&"', '"&cereals_ha&"', '"&cereals_units&"', '"&oilseeds_ha&"', '"&oilseeds_units&"', '"&region&"', '"&d1&"', '"&d2&"', '"&d3&"', '"&d4&"')"
'
rs.Open sql, conn
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
%>
<p>Thank you for completing the questionnaire.</p>

Open in new window

andrew_perry_hgcaAsked:
Who is Participating?
 
dosthConnect With a Mentor Commented:
wow, table is a reserved key word

just add a [] bracket

sql = "INSERT INTO [table] (land, areaunit, cropsgrown, location, inforeceived, by_post, by_email, by_fax, hgca_event, hgca_website, agronomist, exhibition, farming_press, trader, pubaware, question1, question2, question3, question4, question5, question6, question7, question8, question9, question10, question11, question12, question13, question14, question15, question16, question17, question18, question19, question20, question21, question22, question23, question24, question25, question26, anyotherinfo) VALUES ('"&land&"', '"&areaunit&"', '"&cropsgrown&"', '"&location&"', '"&inforeceived&"', '"&by_post&"', '"&by_email&"', '"&by_fax&"', '"&hgca_event&"', '"&hgca_website&"', '"&agronomist&"', '"&exhibition&"', '"&farming_press&"', '"&trader&"', '"&pubaware&"', '"&question1&"', '"&question2&"', '"&question3&"', '"&question4&"', '"&question5&"', '"&question6&"', '"&question7&"', '"&question8&"', '"&question9&"', '"&question10&"', '"&question11&"', '"&question12&"', '"&question13&"', '"&question14&"', '"&question15&"', '"&question16&"', '"&question17&"', '"&question18&"', '"&question19&"', '"&question20&"', '"&question21&"', '"&question22&"', '"&question23&"', '"&question24&"', '"&question25&"', '"&question26&"', '"&anyotherinfo&"')"


0
 
CWS (haripriya)Commented:
what was the error message?

also, can you post the table structure?
0
 
dosthCommented:
you are trying to insert the data for all data type as TEXT, like i see Quote sorrounding, some time datatype like int, numbers wont allow

can you try changings?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
andrew_perry_hgcaAuthor Commented:
cyberwebservice:
Microsoft JET Database Engine error '80040e14'
Syntax error in INSERT INTO statement.
/hgca/surveys/farmerfeedback/form_submit.asp, line 123

That's a screen shot of my database, don't know what the database structure is. Apologies, I've jsut been copying someone elses work.
http://www.hgca.com/hgca/misc/database.jpg

dosth:
change where? on the database?

MANY THANKS FOR QUICK REPLY TO BOTH PEOPLE!
0
 
CWS (haripriya)Commented:
Ok. The screen shot does not show any of the fields mentioned in your INSERT statement.

In your INSERT statement you have enclosed all the values within single quotes, which is valid for 'text' data type.

If the data type for any of the fields is 'NUMBER' then, you should remove the " ' " surrounding that variable. If the data type is 'DATE' then, instead of " ' " enclose that variable with " # ".
0
 
dosthCommented:
cereals_units
oilseeds_units

there are number in database right?

so you need to change your sql insert statement.

if you have problem, just post the mdb database so we can get the query changed
0
 
andrew_perry_hgcaAuthor Commented:
oh my, I'm so sorry, I included the code I was copying from. Trying to do things by the end of the day.

I've included the code I'm using below.
<%
'''''''''''''''''''''''''''''''''''''''''''''''''''
'''''takes the single quotes and replaces them with double
Function SafeSQL( _
   ByVal strToRenderSafe _
   )
 
   SafeSQL = Replace(strToRenderSafe, "'", "''")
 
End Function
 
''''''''''''''''''''''''Check this
strVar = Request.Form("anyotherinfo")
strVar =SafeSQL(strVar)
%> 
 
<%
'
Dim conn, rs, sql
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:\Inetpub\ftproot\hgca\data\surveys\farmerfeedback.mdb"
set rs=Server.CreateObject("ADODB.recordset")
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' link questions to database names
'
Dim land, areaunit, cropsgrown, location, inforeceived, by_post, by_email, by_fax, hgca_event, hgca_website, agronomist, exhibition, farming_press, trader, pubaware, question1, question2, question3, question4, question5, question6, question7, question8, question9, question10, question11, question12, question13, question14, question15, question16, question17, question18, question19, question20, question21, question22, question23, question24, question25, question26, anyotherinfo
' set default values
 
land = ""
areaunit = ""
cropsgrown = ""
location = ""
inforeceived = ""
by_post = ""
by_email = ""
by_fax = ""
hgca_event = ""
hgca_website = ""
agronomist = ""
exhibition = ""
farming_press = ""
trader = ""
pubaware = ""
question1 = ""
question2 = ""
question3 = ""
question4 = ""
question5 = ""
question6 = ""
question7 = ""
question8 = ""
question9 = ""
question10 = ""
question11 = ""
question12 = ""
question13 = ""
question14 = ""
question15 = ""
question16 = ""
question17 = ""
question18 = ""
question19 = ""
question20 = ""
question21 = ""
question22 = ""
question23 = ""
question24 = ""
question25 = ""
question26 = ""
anyotherinfo = ""
 
 
' request from form
land = Request.Form("land")
areaunit = Request.Form("areaunit")
cropsgrown = Request.Form("cropsgrown")
location = Request.Form("location")
inforeceived = Request.Form("inforeceived")
by_post = Request.Form("by_post")
by_email = Request.Form("by_email")
by_fax = Request.Form("by_fax")
hgca_event = Request.Form("hgca_event")
hgca_website = Request.Form("hgca_website")
agronomist = Request.Form("agronomist")
exhibition = Request.Form("exhibition")
farming_press = Request.Form("farming_press")
trader = Request.Form("trader")
pubaware = Request.Form("pubaware")
question1 = Request.Form("question1")
question2 = Request.Form("question2")
question3 = Request.Form("question3")
question4 = Request.Form("question4")
question5 = Request.Form("question5")
question6 = Request.Form("question6")
question7 = Request.Form("question7")
question8 = Request.Form("question8")
question9 = Request.Form("question9")
question10 = Request.Form("question10")
question11 = Request.Form("question11")
question12 = Request.Form("question12")
question13 = Request.Form("question13")
question14 = Request.Form("question14")
question15 = Request.Form("question15")
question16 = Request.Form("question16")
question17 = Request.Form("question17")
question18 = Request.Form("question18")
question19 = Request.Form("question19")
question20 = Request.Form("question20")
question21 = Request.Form("question21")
question22 = Request.Form("question22")
question23 = Request.Form("question23")
question24 = Request.Form("question24")
question25 = Request.Form("question25")
question26 = Request.Form("question26")
anyotherinfo = strVar
'
 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
sql = "INSERT INTO table (land, areaunit, cropsgrown, location, inforeceived, by_post, by_email, by_fax, hgca_event, hgca_website, agronomist, exhibition, farming_press, trader, pubaware, question1, question2, question3, question4, question5, question6, question7, question8, question9, question10, question11, question12, question13, question14, question15, question16, question17, question18, question19, question20, question21, question22, question23, question24, question25, question26, anyotherinfo) VALUES ('"&land&"', '"&areaunit&"', '"&cropsgrown&"', '"&location&"', '"&inforeceived&"', '"&by_post&"', '"&by_email&"', '"&by_fax&"', '"&hgca_event&"', '"&hgca_website&"', '"&agronomist&"', '"&exhibition&"', '"&farming_press&"', '"&trader&"', '"&pubaware&"', '"&question1&"', '"&question2&"', '"&question3&"', '"&question4&"', '"&question5&"', '"&question6&"', '"&question7&"', '"&question8&"', '"&question9&"', '"&question10&"', '"&question11&"', '"&question12&"', '"&question13&"', '"&question14&"', '"&question15&"', '"&question16&"', '"&question17&"', '"&question18&"', '"&question19&"', '"&question20&"', '"&question21&"', '"&question22&"', '"&question23&"', '"&question24&"', '"&question25&"', '"&question26&"', '"&anyotherinfo&"')"
'
response.Write(sql)
rs.Open sql, conn
 
 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
%>
<p>Thank you for completing the survey.</p>

Open in new window

farmerfeedback.mdb
0
 
dosthCommented:
got it working?
0
 
andrew_perry_hgcaAuthor Commented:
I think you just saved my job dosth, yes it worked brilliantly. And just before 4.

Thank you
0
 
andrew_perry_hgcaAuthor Commented:
dosth was very patient and brilliant
0
 
dosthCommented:
ok, cool
0
All Courses

From novice to tech pro — start learning today.