Problem with handling table insert from ASP page

3 Mar 2006 5:24 PM

Jack

Hi,
I am gathering the input values to a form using Request.form method from the
processing page. After all the data is captured, I am building sql statement
out of it. Using a response.write statement, I am generating the output of
the sql statement which I can ran against the table to insert the row.
However, when I am trying to programmatically use the sql statement for the
insert, I am having the following error:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/indianland/mainentry_process.asp, line 94

I am attaching the processing code here where line 94 is the following
statement:

conn.execute(strSQL)

I have no idea why this is producing an error. Any help is appreciated.
CODE:



<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>

<%

ApplicantIntID = Request.Cookies("ApplicantIntID")

Dim l_p_Agency
Dim l_p_lstOffense
Dim l_p_CountTotalOffenses
Dim l_p_CountClearedByArrestOrExceptionalMeans
Dim l_p_CountOffenseReportedBySlec
Dim l_p_CountAlcoholRelated
Dim l_p_CountDrugRelated
Dim l_p_CountOffenseCommittedByJuvenile
Dim l_p_lstMonth
Dim l_p_lstYear

l_AgencyCode = Request.Form("cboAgency")

l_OffenseCode = Request.Form("cboOffense")

l_p_CountTotalOffenses = Request.Form("txt_CountTotalOffenses")

l_p_CountClearedByArrestOrExceptionalMeans =
Request.Form("txt_CountClearedByArrestOrExceptionalMeans")

l_p_CountOffenseReportedBySlec =
Request.Form("txt_CountOffenseReportedBySlec")

l_p_CountAlcoholRelated = Request.Form("txt_CountAlcoholRelated")

l_p_CountDrugRelated = Request.Form("txt_CountDrugRelated")

l_p_CountOffenseCommittedByJuvenile =
Request.Form("txt_CountOffenseCommittedByJuvenile")

l_Month = Request.Form("cboMonth")

l_Year = Request.Form("cboYear")

'Now we are to build each row corresponding to each of the entry

strSQL = "INSERT INTO tblAgencyOffenseStats(ApplicantIntID, AgencyID,
OffenseID, CountTotalOffenses,
CountOfOffensesClearedByArrestOrExceptionalMeans,
CountOfOffensesReportedBySLEC, CountOfAlcoholRelatedOffenses,
CountOfDrugRelatedOffenses, CountOfOffensesCommittedByJuvenile, Month, Year)
VALUES ("& ApplicantIntID & ", "& l_AgencyCode &", "& l_OffenseCode &", "&
l_p_CountTotalOffenses &", "& l_p_CountClearedByArrestOrExceptionalMeans &",
"& l_p_CountOffenseReportedBySlec &", "& l_p_CountAlcoholRelated &", "&
l_p_CountDrugRelated &", "& l_p_CountOffenseCommittedByJuvenile &", "&
l_Month &", "& l_year &")"

Response.Write strSQL & " "

conn.execute(strSQL)

Response.Write " "

Response.Write "Your record has been updated." & " "
%>

<A HREF="mainentry.asp?ApplicantIntID=<%=ApplicantIntID%>">Please click here
for the next submission</A>

</BODY>
</HTML>

3 Mar 2006 7:56 PM

Mike Brind

Jack wrote:
Show quote

> Hi,
> I am gathering the input values to a form using Request.form method from the
> processing page. After all the data is captured, I am building sql statement
> out of it. Using a response.write statement, I am generating the output of
> the sql statement which I can ran against the table to insert the row.
> However, when I am trying to programmatically use the sql statement for the
> insert, I am having the following error:
>
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in INSERT INTO statement.
> /indianland/mainentry_process.asp, line 94
>
> I am attaching the processing code here where line 94 is the following
> statement:
>
> conn.execute(strSQL)
>
> I have no idea why this is producing an error. Any help is appreciated.
> CODE:
>
>
> 
> 
> <HTML>
> <HEAD>
> <META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
> </HEAD>
> <BODY>
>
> <%
>
> ApplicantIntID = Request.Cookies("ApplicantIntID")
>
>
> Dim l_p_Agency
> Dim l_p_lstOffense
> Dim l_p_CountTotalOffenses
> Dim l_p_CountClearedByArrestOrExceptionalMeans
> Dim l_p_CountOffenseReportedBySlec
> Dim l_p_CountAlcoholRelated
> Dim l_p_CountDrugRelated
> Dim l_p_CountOffenseCommittedByJuvenile
> Dim l_p_lstMonth
> Dim l_p_lstYear
>
>
>
>
> l_AgencyCode = Request.Form("cboAgency")
>
> l_OffenseCode = Request.Form("cboOffense")
>
> l_p_CountTotalOffenses = Request.Form("txt_CountTotalOffenses")
>
> l_p_CountClearedByArrestOrExceptionalMeans =
> Request.Form("txt_CountClearedByArrestOrExceptionalMeans")
>
> l_p_CountOffenseReportedBySlec =
> Request.Form("txt_CountOffenseReportedBySlec")
>
> l_p_CountAlcoholRelated = Request.Form("txt_CountAlcoholRelated")
>
> l_p_CountDrugRelated = Request.Form("txt_CountDrugRelated")
>
> l_p_CountOffenseCommittedByJuvenile =
> Request.Form("txt_CountOffenseCommittedByJuvenile")
>
> l_Month = Request.Form("cboMonth")
>
> l_Year = Request.Form("cboYear")
>
> 'Now we are to build each row corresponding to each of the entry
>
> strSQL = "INSERT INTO tblAgencyOffenseStats(ApplicantIntID, AgencyID,
> OffenseID, CountTotalOffenses,
> CountOfOffensesClearedByArrestOrExceptionalMeans,
> CountOfOffensesReportedBySLEC, CountOfAlcoholRelatedOffenses,
> CountOfDrugRelatedOffenses, CountOfOffensesCommittedByJuvenile, Month, Year)
> VALUES ("& ApplicantIntID & ", "& l_AgencyCode &", "& l_OffenseCode &", "&
> l_p_CountTotalOffenses &", "& l_p_CountClearedByArrestOrExceptionalMeans &",
> "& l_p_CountOffenseReportedBySlec &", "& l_p_CountAlcoholRelated &", "&
> l_p_CountDrugRelated &", "& l_p_CountOffenseCommittedByJuvenile &", "&
> l_Month &", "& l_year &")"
>
> Response.Write strSQL & " "
>
> conn.execute(strSQL)
>
> Response.Write " "
> Response.Write "Your record has been updated." & " "
> %>
> <A HREF="mainentry.asp?ApplicantIntID=<%=ApplicantIntID%>">Please click here
> for the next submission</A>
>
> </BODY>
> </HTML>

First of all, ppost the result of response.write(strSQL), and while
people are chewing over that, have a look at Bob's various posts on
using saved parameter queries:

http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8
http://groups.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd

--
Mike Brind

4 Mar 2006 5:09 AM

Roland Hall

"Mike Brind" wrote in message
Show quote

news:1141415769.127053.233350@j33g2000cwa.googlegroups.com...
:
: Jack wrote:
: > Hi,
: > I am gathering the input values to a form using Request.form method from
the
: > processing page. After all the data is captured, I am building sql
statement
: > out of it. Using a response.write statement, I am generating the output
of
: > the sql statement which I can ran against the table to insert the row.
: > However, when I am trying to programmatically use the sql statement for
the
: > insert, I am having the following error:
: >
: > Error Type:
: > Microsoft JET Database Engine (0x80040E14)
: > Syntax error in INSERT INTO statement.
: > /indianland/mainentry_process.asp, line 94
: >
: > I am attaching the processing code here where line 94 is the following
: > statement:
: >
: > conn.execute(strSQL)
: >
: > I have no idea why this is producing an error. Any help is appreciated.
: > CODE:
: >
: >
: > 
: > 
: > <HTML>
: > <HEAD>
: > <META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
: > </HEAD>
: > <BODY>
: >
: > <%
: >
: > ApplicantIntID = Request.Cookies("ApplicantIntID")
: >
: >
: > Dim l_p_Agency
: > Dim l_p_lstOffense
: > Dim l_p_CountTotalOffenses
: > Dim l_p_CountClearedByArrestOrExceptionalMeans
: > Dim l_p_CountOffenseReportedBySlec
: > Dim l_p_CountAlcoholRelated
: > Dim l_p_CountDrugRelated
: > Dim l_p_CountOffenseCommittedByJuvenile
: > Dim l_p_lstMonth
: > Dim l_p_lstYear
: >
: >
: >
: >
: > l_AgencyCode = Request.Form("cboAgency")
: >
: > l_OffenseCode = Request.Form("cboOffense")
: >
: > l_p_CountTotalOffenses = Request.Form("txt_CountTotalOffenses")
: >
: > l_p_CountClearedByArrestOrExceptionalMeans =
: > Request.Form("txt_CountClearedByArrestOrExceptionalMeans")
: >
: > l_p_CountOffenseReportedBySlec =
: > Request.Form("txt_CountOffenseReportedBySlec")
: >
: > l_p_CountAlcoholRelated = Request.Form("txt_CountAlcoholRelated")
: >
: > l_p_CountDrugRelated = Request.Form("txt_CountDrugRelated")
: >
: > l_p_CountOffenseCommittedByJuvenile =
: > Request.Form("txt_CountOffenseCommittedByJuvenile")
: >
: > l_Month = Request.Form("cboMonth")
: >
: > l_Year = Request.Form("cboYear")
: >
: > 'Now we are to build each row corresponding to each of the entry
: >
: > strSQL = "INSERT INTO tblAgencyOffenseStats(ApplicantIntID,
AgencyID,
: > OffenseID, CountTotalOffenses,
: > CountOfOffensesClearedByArrestOrExceptionalMeans,
: > CountOfOffensesReportedBySLEC, CountOfAlcoholRelatedOffenses,
: > CountOfDrugRelatedOffenses, CountOfOffensesCommittedByJuvenile, Month,
Year)
: > VALUES ("& ApplicantIntID & ", "& l_AgencyCode &", "& l_OffenseCode &",
"&
: > l_p_CountTotalOffenses &", "& l_p_CountClearedByArrestOrExceptionalMeans
&",
: > "& l_p_CountOffenseReportedBySlec &", "& l_p_CountAlcoholRelated &", "&
: > l_p_CountDrugRelated &", "& l_p_CountOffenseCommittedByJuvenile &", "&
: > l_Month &", "& l_year &")"
: >
: > Response.Write strSQL & " "
: >
: > conn.execute(strSQL)
: >
: > Response.Write " "
: > Response.Write "Your record has been updated." & " "
: > %>
: > <A HREF="mainentry.asp?ApplicantIntID=<%=ApplicantIntID%>">Please click
here
: > for the next submission</A>
: >
: > </BODY>
: > </HTML>
:
: First of all, ppost the result of response.write(strSQL), and while
: people are chewing over that, have a look at Bob's various posts on
: using saved parameter queries:
:
:

http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8

:

http://groups.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd

Translation: SQL injection

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

6 Mar 2006 8:17 AM

Mike Brind

Roland Hall wrote:
Show quote

> "Mike Brind" wrote in message
> news:1141415769.127053.233350@j33g2000cwa.googlegroups.com...
> :
> : Jack wrote:
> : > Hi,
> : > I am gathering the input values to a form using Request.form method from
> the
> : > processing page. After all the data is captured, I am building sql
> statement
> : > out of it. Using a response.write statement, I am generating the output
> of
> : > the sql statement which I can ran against the table to insert the row.
> : > However, when I am trying to programmatically use the sql statement for
> the
> : > insert, I am having the following error:
> : >
> : > Error Type:
> : > Microsoft JET Database Engine (0x80040E14)
> : > Syntax error in INSERT INTO statement.
> : > /indianland/mainentry_process.asp, line 94

> :
> : First of all, ppost the result of response.write(strSQL), and while
> : people are chewing over that, have a look at Bob's various posts on
> : using saved parameter queries:
> :
> :
> http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8
> :
> http://groups.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd
>
> Translation: SQL injection
>
> --

Partly, but I've found that since I started taking Bob's recommendation
on saved parameter queries, I've pretty much eliminated Syntax error in
INSERT INTO statement errors.

--
Mike Brind

7 Mar 2006 3:57 AM

Jack

Thanks to both you guys for all the help. However, I got this problem solved.
All I had to do
was put [] between the month as well as year in the sql syntax. Since these
are reserved words with the above approach the problem got solved. Somebody
from a different forum suggested me this solution and it indeed did work. In
any event, thanks to both of you. Regards.

Show quote

"Mike Brind" wrote:

>
> Roland Hall wrote:
> > "Mike Brind" wrote in message
> > news:1141415769.127053.233350@j33g2000cwa.googlegroups.com...
> > :
> > : Jack wrote:
> > : > Hi,
> > : > I am gathering the input values to a form using Request.form method from
> > the
> > : > processing page. After all the data is captured, I am building sql
> > statement
> > : > out of it. Using a response.write statement, I am generating the output
> > of
> > : > the sql statement which I can ran against the table to insert the row.
> > : > However, when I am trying to programmatically use the sql statement for
> > the
> > : > insert, I am having the following error:
> > : >
> > : > Error Type:
> > : > Microsoft JET Database Engine (0x80040E14)
> > : > Syntax error in INSERT INTO statement.
> > : > /indianland/mainentry_process.asp, line 94
>
> > :
> > : First of all, ppost the result of response.write(strSQL), and while
> > : people are chewing over that, have a look at Bob's various posts on
> > : using saved parameter queries:
> > :
> > :
> > http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8
> > :
> > http://groups.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd
> >
> > Translation: SQL injection
> >
> > --
>
> Partly, but I've found that since I started taking Bob's recommendation
> on saved parameter queries, I've pretty much eliminated Syntax error in
> INSERT INTO statement errors.
>
> --
> Mike Brind
>
>

7 Mar 2006 8:19 AM

Mike Brind

Jack wrote:

> Thanks to both you guys for all the help. However, I got this problem solved.
> All I had to do
> was put [] between the month as well as year in the sql syntax. Since these
> are reserved words with the above approach the problem got solved. Somebody
> from a different forum suggested me this solution and it indeed did work. In
> any event, thanks to both of you. Regards.
>

A good reason to avoid using reserved words, or spaces in field names.
Here's a link you might want to bookmark for future reference:

http://www.aspfaq.com/show.asp?id=2080

--
Mike Brind

8 Mar 2006 12:28 AM

Roland Hall

: > Translation: SQL injection
:
: Partly, but I've found that since I started taking Bob's recommendation
: on saved parameter queries, I've pretty much eliminated Syntax error in
: INSERT INTO statement errors.

Bob is pretty handy. I tried to put him on retainer but he said he had
enough money. (O:=

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

8 Mar 2006 9:14 AM

Mike Brind

Roland Hall wrote:

> : > Translation: SQL injection
> :
> : Partly, but I've found that since I started taking Bob's recommendation
> : on saved parameter queries, I've pretty much eliminated Syntax error in
> : INSERT INTO statement errors.
>
> Bob is pretty handy. I tried to put him on retainer but he said he had
> enough money. (O:=
>

I already have him on free retainer in this group, but don't tell
him.....

;-)

--
Mike Brind

8 Mar 2006 3:40 PM

Bob Barrows [MVP]

Mike Brind wrote:
Show quote

> Roland Hall wrote:
>>>> Translation: SQL injection
>>>
>>> Partly, but I've found that since I started taking Bob's
>>> recommendation on saved parameter queries, I've pretty much
>>> eliminated Syntax error in INSERT INTO statement errors.
>>
>> Bob is pretty handy. I tried to put him on retainer but he said he
>> had enough money. (O:=
>>
>
> I already have him on free retainer in this group, but don't tell
> him.....
>
> ;-)
>

Thanks guys, but I've got to stop reading this stuff before somebody notices
my head swelling.

Besides, there are several people in these groups that I've learned from ...
including Roland.

Enough of the mutual admiration society ... back to work.

--
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.

8 Mar 2006 3:37 PM

Bob Barrows [MVP]

Roland Hall wrote:

>>> Translation: SQL injection
>>
>> Partly, but I've found that since I started taking Bob's
>> recommendation on saved parameter queries, I've pretty much
>> eliminated Syntax error in INSERT INTO statement errors.
>
> Bob is pretty handy. I tried to put him on retainer but he said he
> had enough money. (O:=
>
LOL

I'm not sure that was exactly what I said ...

--
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.