Something wrong with my INSERT INTO

What is wrong with the below code?

It gives me error:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/w/results.asp, line 24

Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; (R1 1.3); .NET CLR
1.1.4322; .NET CLR 2.0.50727)

Page:
POST 65 bytes to /w/results.asp

POST Data:
T3=ssdf&T1=sdf&D1=No&D2=Sabre&D3=Greaves+Travel&T2=&SUBMIT=Submit

----------------------------------
I have a file protected.asp and the below line to submit data into
database.
<form action="results.asp" METHOD="POST">

Following is the contents of results.asp
----------------------------------

<%@ Language=VBScript %>
<%

if Request.ServerVariables("REQUEST_METHOD") = "POST" then

dim dDate,dPNR,dSale,dGDS,dWebsite,dOWebsite,sql,connString
dDate = Request.Form("T3")
dPNR = Request.Form("T1")
dSale = Request.Form("D1")
dGDS = Request.Form("D2")
dWebsite = Request.Form("D3")
dOWebsite = Request.Form("T2")
connString = "your connection string"
'conn.Open connString

sql = "INSERT INTO data ( Date, PNR_Number, Sale, GDS, Website,
Other_Website) VALUES ('" & dDate & "','" & dPNR & "','" & dSale &
"','" & dGDS & "','" & dWebsite & "','" & doWebsite & "');"

cst = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("DB\db1.mdb")
set conn = CreateObject("ADODB.Connection")
conn.open cst


conn.Execute sql
conn.Close
Response.Redirect("protected.asp")

end if

%>

Maxi
Tue Apr 11 11:36:34 CDT 2006

I did something with the code and changed it a bit. Now there is no
error but the data is not getting recorded in the database. Any
ideas???

Here is the modified version of the code

<%@ Language=VBScript %>
<%
dim dDate,dPNR,dSale,dGDS,dWebsite,dOWebsite,sql
dDate = Request.Form("T3")
dPNR = Request.Form("T1")
dSale = Request.Form("D1")
dGDS = Request.Form("D2")
dWebsite = Request.Form("D3")
dOWebsite = Request.Form("T2")
cst = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Server.MapPath("DB\db1.mdb")
sql = "INSERT INTO Data ( Date, PNR_Number, Sale, GDS, Website,
Other_Website) VALUES ('" & dDate & "','" & dPNR & "','" & dSale &
"','" & dGDS & "','" & dWebsite & "','" & doWebsite & "');"
set conn = CreateObject("ADODB.Connection")
conn.open cst
On error resume next
conn.Execute sql
conn.Close
Set Conn = Nothing
Response.Redirect ("protected.asp")
%>

Bob
Tue Apr 11 12:28:56 CDT 2006

Maxi wrote:
> I did something with the code and changed it a bit.

Yeah, you added On Error resume Next, which is now masking your error. :-)

> Now there is no
> error but the data is not getting recorded in the database. Any
> ideas???
>
> Here is the modified version of the code
<snip>
> sql = "INSERT INTO Data ( Date, PNR_Number, Sale, GDS, Website,
> Other_Website) VALUES ('" & dDate & "','" & dPNR & "','" & dSale &
> "','" & dGDS & "','" & dWebsite & "','" & doWebsite & "');"
> set conn = CreateObject("ADODB.Connection")
> conn.open cst

This should help:
You use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries as
they are known in Access:

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Slim
Tue Apr 11 12:44:09 CDT 2006

Its hard to tell when not knowing more about your database

If a fields is in your database is text, then you need to enclose the value
in '' if it is numeric you must not use ''

my guess is that '" & dPNR & "' should be " & dPNR & "



"Maxi" <maheshchindarkar@gmail.com> wrote in message
news:1144768787.196018.73100@u72g2000cwu.googlegroups.com...
> What is wrong with the below code?
>
> It gives me error:
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in INSERT INTO statement.
> /w/results.asp, line 24
>
> Browser Type:
> Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; (R1 1.3); .NET CLR
> 1.1.4322; .NET CLR 2.0.50727)
>
> Page:
> POST 65 bytes to /w/results.asp
>
> POST Data:
> T3=ssdf&T1=sdf&D1=No&D2=Sabre&D3=Greaves+Travel&T2=&SUBMIT=Submit
>
> ----------------------------------
> I have a file protected.asp and the below line to submit data into
> database.
> <form action="results.asp" METHOD="POST">
>
> Following is the contents of results.asp
> ----------------------------------
>
> <%@ Language=VBScript %>
> <%
>
> if Request.ServerVariables("REQUEST_METHOD") = "POST" then
>
> dim dDate,dPNR,dSale,dGDS,dWebsite,dOWebsite,sql,connString
> dDate = Request.Form("T3")
> dPNR = Request.Form("T1")
> dSale = Request.Form("D1")
> dGDS = Request.Form("D2")
> dWebsite = Request.Form("D3")
> dOWebsite = Request.Form("T2")
> connString = "your connection string"
> 'conn.Open connString
>
> sql = "INSERT INTO data ( Date, PNR_Number, Sale, GDS, Website,
> Other_Website) VALUES ('" & dDate & "','" & dPNR & "','" & dSale &
> "','" & dGDS & "','" & dWebsite & "','" & doWebsite & "');"
>
> cst = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & Server.MapPath("DB\db1.mdb")
> set conn = CreateObject("ADODB.Connection")
> conn.open cst
>
>
> conn.Execute sql
> conn.Close
> Response.Redirect("protected.asp")
>
> end if
>
> %>
>

Maxi
Tue Apr 11 12:48:47 CDT 2006


Bob Barrows [MVP] wrote:
> Maxi wrote:
> > I did something with the code and changed it a bit.
>
> Yeah, you added On Error resume Next, which is now masking your error. :-)

I removed the On error line and now I get error:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/v/results.asp, line 14

What is wrong with the INSERT INTO statement. Or is it that the error
is somewhere else? like the variable declaration, or whether data from
the form is getting stored in the variable etc..... please help.


> You use of dynamic sql is leaving you vulnerable to hackers using sql
> injection:

I am not worried about hackers as I am not going to host this. This is
for my employees. The reason why I selected ASP is because these guys
don't have excel to enter data.

> See here for a better, more secure way to execute your queries by using
> parameter markers:

> Personally, I prefer using stored procedures, or saved parameter queries as
> they are known in Access:

I don't want to make my code better, efficient or effective. Its just
that it should work irrespective of it being vulnerable.

Bob
Tue Apr 11 13:02:54 CDT 2006

Maxi wrote:
> Bob Barrows [MVP] wrote:
>> Maxi wrote:
>>> I did something with the code and changed it a bit.
>>
>> Yeah, you added On Error resume Next, which is now masking your
>> error. :-)
>
> I removed the On error line and now I get error:
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in INSERT INTO statement.
> /v/results.asp, line 14
>
> What is wrong with the INSERT INTO statement.

The only way to find out is to look at it. the only way to look at it is to
response.write it.

sql = "INSERT ... "
Response.Write sql
Response.End

Run the page and look at the statement in the browser. Try opening your
database in Access and running the sql statement there. If you still can't
figure it out, look at the rules for delimiters in sql statements which I
talk about in this post:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

If you still can't figure it out, post the datatypes of the fields in your
table and the ssql statement that got written to the browser window.

> Or is it that the error
> is somewhere else?
No. the error message is very specific. It's definitely coming from the
database (note the source: JET)

>
>> You use of dynamic sql is leaving you vulnerable to hackers using sql
>> injection:
>
> I am not worried about hackers as I am not going to host this. This is
> for my employees. The reason why I selected ASP is because these guys
> don't have excel to enter data.

The majority of hacks are done by disgruntled employees ...

>
>> See here for a better, more secure way to execute your queries by
>> using parameter markers:
>
>> Personally, I prefer using stored procedures, or saved parameter
>> queries as they are known in Access:
>
> I don't want to make my code better, efficient or effective.

How about easier-to-write? Doesn't that appeal?

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Maxi
Tue Apr 11 13:21:19 CDT 2006

> If you still can't figure it out, post the datatypes of the fields in your
> table and the ssql statement that got written to the browser window.

after response.write, the sql statement written to the browser window
is
INSERT INTO Data ( Date, PNR_Number, Sale, GDS, Website, Other_Website)
VALUES ('01/01/06','PUMJID','No','Sabre','Greaves Travel','');

Last field is kept blank.

Datatypes:
Date :- Date/Time
PNR_Number :- Text
Sale :- Yes/No
GDS :- Text
Website :- Text
Other_Website :- Text (This is optional - only filled if the the option
is not available in the Website dropdown)

Bob
Tue Apr 11 13:52:43 CDT 2006

Maxi wrote:
>> If you still can't figure it out, post the datatypes of the fields
>> in your table and the ssql statement that got written to the browser
>> window.
>
> after response.write, the sql statement written to the browser window
> is

Wow! That didn't take long for you to read and assimilate the information
provided in the post I cited ...

> INSERT INTO Data ( Date, PNR_Number, Sale, GDS, Website,
> Other_Website) VALUES ('01/01/06','PUMJID','No','Sabre','Greaves
> Travel','');
<snip>
> Datatypes:
> Date :- Date/Time

You didn't read the rules for delimiting data in the post I cited, did you?
If you had, you would have discovered that literal dates need to be
delimited with #, not '.

However, that is not the only problem. The word "Date" is a reserved keyword
and should be avoided when naming your database objects (see here for a full
list: http://www.aspfaq.com/show.asp?id=2080). if you cannot change it, then
you are going to need to remember to delimit it with brackets [] when using
it in a query run via ADO.

Also, you should not use an ambiguous date format when passing dates to Jet:
convert your date into yyyy-mm-dd format before concatenating it into your
statement (see here for help with formatting your dates:
http://www.aspfaq.com/show.asp?id=2313)

> PNR_Number :- Text
> Sale :- Yes/No

A Yes/No field actually stores numbers: -1 for True and Yes, 0 for False and
No. So you have to use 0 or -1 when putting data into the field.

> GDS :- Text
> Website :- Text
> Other_Website :- Text (This is optional - only filled if the the
> option is not available in the Website dropdown)

Hopefully you've set the "Allow empty strings" property to True in the
Access table design window ...

Anyways, to bring it all together, here is what you need to send to Jet:
INSERT INTO Data ( [Date], PNR_Number, Sale, GDS, Website, Other_Website)
VALUES (#2006-01-01#,'PUMJID',0,'Sabre','Greaves Travel','');

So your vbscript needs to be:
dDate= CDate(dDate)
dDate = YEAR(dDate) & _
"-" & Right("0" & Month(dDate),2) & _
"-" & Right("0" & Day(dDate),2)
If dSale = "No" then
dSale = "0"
else
dSale="-1"
end if

sql = "INSERT INTO Data ( [Date], PNR_Number, Sale, GDS, Website,
Other_Website) VALUES (&" & dDate &#"','" & dPNR & "'," & dSale &
",'" & dGDS & "','" & dWebsite & "','" & doWebsite & "');"

HTH,
Bob Barrows
PS. This is the hard way to accomplish your task.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Maxi
Tue Apr 11 14:09:14 CDT 2006

> Wow! That didn't take long for you to read and assimilate the information
> provided in the post I cited ...
>
> You didn't read the rules for delimiting data in the post I cited, did you?
> If you had, you would have discovered that literal dates need to be
> delimited with #, not '.
>

I don't know anything about ASP. Even if I had to go through the
articles you mentioned, I will not understand everything. My objective
is to get this done today itself as our developer is on leave. Thank
you for your co-operation.

I made the changes you mentioned, and the error that I get is:

Error Type:
Microsoft VBScript compilation (0x800A03EA)
Syntax error
/asr/results.asp, line 23, column 101

line 23, column 101 refers to &" & dDate &#"'

Thank you

Bob
Tue Apr 11 14:24:26 CDT 2006

Maxi wrote:
>
> I made the changes you mentioned, and the error that I get is:
>
> Error Type:
> Microsoft VBScript compilation (0x800A03EA)
> Syntax error
> /asr/results.asp, line 23, column 101
>
> line 23, column 101 refers to &" & dDate &#"'
>
Sorry, stupid typo. It should be:

sql = "INSERT INTO Data ( [Date], PNR_Number, Sale, GDS, Website,
Other_Website) VALUES (#" & dDate & "#,'" & dPNR & "'," & dSale &
",'" & dGDS & "','" & dWebsite & "','" & doWebsite & "');"

I will say it again: you are doing this the hard way. I've been doing this
for years and yet was unable to avoid the silly mistake I made above.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Evertjan
Tue Apr 11 14:26:44 CDT 2006

Bob Barrows [MVP] wrote on 11 apr 2006 in
microsoft.public.scripting.vbscript:

> sql = "INSERT INTO Data ( [Date], PNR_Number, Sale, GDS, Website,
> Other_Website) VALUES (&" & dDate &#"','" & dPNR & "'," & dSale &
> ",'" & dGDS & "','" & dWebsite & "','" & doWebsite & "');"
>

Should be:

sql = "INSERT INTO Data ( [Date], PNR_Number, Sale, GDS, Website,
Other_Website) VALUES (#" & dDate & "#,'" & dPNR & "'," & dSale &
",'" & dGDS & "','" & dWebsite & "','" & doWebsite & "');"

IMHO

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

Slim
Tue Apr 11 12:55:12 CDT 2006

The could also be that you are entering a value that contains a '

example if you enter the word bob's and put it in quotes 'bob's' the
database things you are entering 'bob' and s' will cause a error to fix this
you need to use a replace statement

instead of
' "& theValue &" '

use
' "& replace( theValue,"'","''" &" '

you are replacing 1' with 2 '' the text will be entered correctly


a good tip for checking sql statements is this

before you execute the sql string write these 2 lines

response.write sql
response.flush


this will write the sql string to screen before the error. you will then be
able to see what your entering


"Maxi" <maheshchindarkar@gmail.com> wrote in message
news:1144768787.196018.73100@u72g2000cwu.googlegroups.com...
> What is wrong with the below code?
>
> It gives me error:
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in INSERT INTO statement.
> /w/results.asp, line 24
>
> Browser Type:
> Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; (R1 1.3); .NET CLR
> 1.1.4322; .NET CLR 2.0.50727)
>
> Page:
> POST 65 bytes to /w/results.asp
>
> POST Data:
> T3=ssdf&T1=sdf&D1=No&D2=Sabre&D3=Greaves+Travel&T2=&SUBMIT=Submit
>
> ----------------------------------
> I have a file protected.asp and the below line to submit data into
> database.
> <form action="results.asp" METHOD="POST">
>
> Following is the contents of results.asp
> ----------------------------------
>
> <%@ Language=VBScript %>
> <%
>
> if Request.ServerVariables("REQUEST_METHOD") = "POST" then
>
> dim dDate,dPNR,dSale,dGDS,dWebsite,dOWebsite,sql,connString
> dDate = Request.Form("T3")
> dPNR = Request.Form("T1")
> dSale = Request.Form("D1")
> dGDS = Request.Form("D2")
> dWebsite = Request.Form("D3")
> dOWebsite = Request.Form("T2")
> connString = "your connection string"
> 'conn.Open connString
>
> sql = "INSERT INTO data ( Date, PNR_Number, Sale, GDS, Website,
> Other_Website) VALUES ('" & dDate & "','" & dPNR & "','" & dSale &
> "','" & dGDS & "','" & dWebsite & "','" & doWebsite & "');"
>
> cst = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & Server.MapPath("DB\db1.mdb")
> set conn = CreateObject("ADODB.Connection")
> conn.open cst
>
>
> conn.Execute sql
> conn.Close
> Response.Redirect("protected.asp")
>
> end if
>
> %>
>

Maxi
Wed Apr 12 00:40:11 CDT 2006

Thank you. It worked. Sorry, couldn't reply. Actually I fainted after
working long hours.

> > sql = "INSERT INTO Data ( [Date], PNR_Number, Sale, GDS, Website,
> > Other_Website) VALUES (&" & dDate &#"','" & dPNR & "'," & dSale &
> > ",'" & dGDS & "','" & dWebsite & "','" & doWebsite & "');"
> >
>
> Should be:
>
> sql = "INSERT INTO Data ( [Date], PNR_Number, Sale, GDS, Website,
> Other_Website) VALUES (#" & dDate & "#,'" & dPNR & "'," & dSale &
> ",'" & dGDS & "','" & dWebsite & "','" & doWebsite & "');"

Maxi
Wed Apr 12 01:40:29 CDT 2006

One more problem.
I added few fields in my database and again the same problem.

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/airs/results.asp, line 32

POST Data:
T5=1235&T3=01%2F01%2F01&D4=Abhishek&D5=Abhishek&T1=aaaaaa&D1=Yes&T4=111&D2=Sabre&D3=Greaves+Travel&T2=&SUBMIT=Submit


After writing the response, I get:
INSERT INTO Data ( User, When, Res_res, Res_sale, PNR_Number, Sale,
Amt, GDS, Website, Other_Website) VALUES
('1235',#2001-01-01#,'Abhishek','Abhishek','aaaaaa',-1,'111','Sabre','Greaves
Travel','');

Fields matches exactly with my database???

Maxi
Wed Apr 12 01:46:59 CDT 2006

More info:
I am picking value of <%=Your_UserName%> in duser. Is this creating a
problem?

I am not sure where to check its user type. I think it is a global
variable.

I am an MIS guy and my developer has not been coming from yesterday. I
wanted to finish this yesterday but couldn't do it. Can you help?

Bob
Wed Apr 12 06:00:31 CDT 2006

Maxi wrote:
> One more problem.
> I added few fields in my database and again the same problem.
>
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in INSERT INTO statement.
> /airs/results.asp, line 32
>
> POST Data:
> T5=1235&T3=01%2F01%2F01&D4=Abhishek&D5=Abhishek&T1=aaaaaa&D1=Yes&T4=111&D2=Sabre&D3=Greaves+Travel&T2=&SUBMIT=Submit
>
>
> After writing the response, I get:
> INSERT INTO Data ( User, When, Res_res, Res_sale, PNR_Number, Sale,
> Amt, GDS, Website, Other_Website) VALUES
> ('1235',#2001-01-01#,'Abhishek','Abhishek','aaaaaa',-1,'111','Sabre','Greaves
> Travel','');
>
> Fields matches exactly with my database???

It's likely another reserved keyword issue ... let's see ... yes, according
to http://www.aspfaq.com/show.asp?id=2080#u, "User" is a reserved keyword,
so you are either going to need to change its name to a non-reserved word
(highly recommended) or remember to surround it with brackets when using it
in a query run via ADO.

INSERT INTO Data ( [User], ...

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Maxi
Wed Apr 12 07:18:34 CDT 2006

> INSERT INTO Data ( [User], ...

Acutally I thought of it and already changed it to Employee instead of
User. But still it is not working.

I guess there is something wrong elsewhere

May be in

In the form the textbox name is T5 and the value is set to
<%=Your_Username%>

In the asp file, I tracked this value in a variable
duser = Request.Form("T5")

Slim
Wed Apr 12 08:01:56 CDT 2006


"Maxi" <maheshchindarkar@gmail.com> wrote in message
news:1144824029.351772.121720@g10g2000cwb.googlegroups.com...
> One more problem.
> I added few fields in my database and again the same problem.
>
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in INSERT INTO statement.
> /airs/results.asp, line 32
>
> POST Data:
> T5=1235&T3=01%2F01%2F01&D4=Abhishek&D5=Abhishek&T1=aaaaaa&D1=Yes&T4=111&D2=Sabre&D3=Greaves+Travel&T2=&SUBMIT=Submit
>
>
> After writing the response, I get:
> INSERT INTO Data ( User, When, Res_res, Res_sale, PNR_Number, Sale,
> Amt, GDS, Website, Other_Website) VALUES
> ('1235',#2001-01-01#,'Abhishek','Abhishek','aaaaaa',-1,'111','Sabre','Greaves
> Travel','');
>
> Fields matches exactly with my database???
>

bob has picked it in his answer, USER is a resilvered word. you need to use
[USER]

Slim
Wed Apr 12 08:05:35 CDT 2006


"Maxi" <maheshchindarkar@gmail.com> wrote in message
news:1144844314.833272.24990@g10g2000cwb.googlegroups.com...
>> INSERT INTO Data ( [User], ...
>
> Acutally I thought of it and already changed it to Employee instead of
> User. But still it is not working.
>
> I guess there is something wrong elsewhere
>
> May be in
>
> In the form the textbox name is T5 and the value is set to
> <%=Your_Username%>
>
> In the asp file, I tracked this value in a variable
> duser = Request.Form("T5")
>

As it says its a syntax error in your INSERT statement


INSERT INTO Data ( User, When, Res_res, Res_sale, PNR_Number, Sale,
Amt, GDS, Website, Other_Website) VALUES
('1235',#2001-01-01#,'Abhishek','Abhishek','aaaaaa',-1,'111','Sabre','Greaves
Travel','');

the last field has a blank entry, does this field ALLOW NULLS?

if that's not the problem, eliminate the new fields and add them one by one
until you find the field that is giving you the error

Maxi
Wed Apr 12 08:19:08 CDT 2006

> the last field has a blank entry, does this field ALLOW NULLS?
>

Yes it does.

> if that's not the problem, eliminate the new fields and add them one by one
> until you find the field that is giving you the error

Will try.

Actually my developer is back. He will work on it now. I will post what
the problem was when the issue is resolved.

Thank you for your co-operation.