Problem with handling table insert from ASP page

J

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:


<!-- #include file="connection.asp" -->
<!-- #include file="adovbs.inc" -->
<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 & "<br>"

conn.execute(strSQL)

Response.Write "<br>"
Response.Write "Your record has been updated." & "<br>"
%>
<A HREF="mainentry.asp?ApplicantIntID=<%=ApplicantIntID%>">Please click here
for the next submission</A>

</BODY>
</HTML>
 
M

Mike Brind

Jack said:
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:


<!-- #include file="connection.asp" -->
<!-- #include file="adovbs.inc" -->
<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 & "<br>"

conn.execute(strSQL)

Response.Write "<br>"
Response.Write "Your record has been updated." & "<br>"
%>
<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/micr.../713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8
http://groups.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd
 
R

Roland Hall

in message
:
: 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:
: >
: >
: > <!-- #include file="connection.asp" -->
: > <!-- #include file="adovbs.inc" -->
: > <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 & "<br>"
: >
: > conn.execute(strSQL)
: >
: > Response.Write "<br>"
: > Response.Write "Your record has been updated." & "<br>"
: > %>
: > <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/micr.../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
 
M

Mike Brind

Roland said:
in message
:
: 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/micr.../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.
 
J

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

Mike Brind

Jack said:
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
 
R

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
 
M

Mike Brind

Roland said:
: > 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.....

;-)
 
B

Bob Barrows [MVP]

Roland said:
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 ...
 
B

Bob Barrows [MVP]

Mike said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top