Problem with handling table insert from ASP page

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 programmaticall y 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_proce ss.asp, line 94

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

conn.execute(st rSQL)

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


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

<%

ApplicantIntID = Request.Cookies ("ApplicantIntI D")
Dim l_p_Agency
Dim l_p_lstOffense
Dim l_p_CountTotalO ffenses
Dim l_p_CountCleare dByArrestOrExce ptionalMeans
Dim l_p_CountOffens eReportedBySlec
Dim l_p_CountAlcoho lRelated
Dim l_p_CountDrugRe lated
Dim l_p_CountOffens eCommittedByJuv enile
Dim l_p_lstMonth
Dim l_p_lstYear

l_AgencyCode = Request.Form("c boAgency")

l_OffenseCode = Request.Form("c boOffense")

l_p_CountTotalO ffenses = Request.Form("t xt_CountTotalOf fenses")

l_p_CountCleare dByArrestOrExce ptionalMeans =
Request.Form("t xt_CountCleared ByArrestOrExcep tionalMeans")

l_p_CountOffens eReportedBySlec =
Request.Form("t xt_CountOffense ReportedBySlec" )

l_p_CountAlcoho lRelated = Request.Form("t xt_CountAlcohol Related")

l_p_CountDrugRe lated = Request.Form("t xt_CountDrugRel ated")

l_p_CountOffens eCommittedByJuv enile =
Request.Form("t xt_CountOffense CommittedByJuve nile")

l_Month = Request.Form("c boMonth")

l_Year = Request.Form("c boYear")

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

strSQL = "INSERT INTO tblAgencyOffens eStats(Applican tIntID, AgencyID,
OffenseID, CountTotalOffen ses,
CountOfOffenses ClearedByArrest OrExceptionalMe ans,
CountOfOffenses ReportedBySLEC, CountOfAlcoholR elatedOffenses,
CountOfDrugRela tedOffenses, CountOfOffenses CommittedByJuve nile, Month, Year)
VALUES ("& ApplicantIntID & ", "& l_AgencyCode &", "& l_OffenseCode &", "&
l_p_CountTotalO ffenses &", "& l_p_CountCleare dByArrestOrExce ptionalMeans &",
"& l_p_CountOffens eReportedBySlec &", "& l_p_CountAlcoho lRelated &", "&
l_p_CountDrugRe lated &", "& l_p_CountOffens eCommittedByJuv enile &", "&
l_Month &", "& l_year &")"

Response.Write strSQL & " "

conn.execute(st rSQL)

Response.Write " "
Response.Write "Your record has been updated." & " "
%>
<A HREF="mainentry .asp?ApplicantI ntID=<%=Applica ntIntID%>">Plea se click here
for the next submission</A>

</BODY>
</HTML>

Mar 3 '06 #1

Subscribe Reply

1794

Mike Brind

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 programmaticall y 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_proce ss.asp, line 94

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

conn.execute(st rSQL)

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


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

<%

ApplicantIntID = Request.Cookies ("ApplicantIntI D")
Dim l_p_Agency
Dim l_p_lstOffense
Dim l_p_CountTotalO ffenses
Dim l_p_CountCleare dByArrestOrExce ptionalMeans
Dim l_p_CountOffens eReportedBySlec
Dim l_p_CountAlcoho lRelated
Dim l_p_CountDrugRe lated
Dim l_p_CountOffens eCommittedByJuv enile
Dim l_p_lstMonth
Dim l_p_lstYear

l_AgencyCode = Request.Form("c boAgency")

l_OffenseCode = Request.Form("c boOffense")

l_p_CountTotalO ffenses = Request.Form("t xt_CountTotalOf fenses")

l_p_CountCleare dByArrestOrExce ptionalMeans =
Request.Form("t xt_CountCleared ByArrestOrExcep tionalMeans")

l_p_CountOffens eReportedBySlec =
Request.Form("t xt_CountOffense ReportedBySlec" )

l_p_CountAlcoho lRelated = Request.Form("t xt_CountAlcohol Related")

l_p_CountDrugRe lated = Request.Form("t xt_CountDrugRel ated")

l_p_CountOffens eCommittedByJuv enile =
Request.Form("t xt_CountOffense CommittedByJuve nile")

l_Month = Request.Form("c boMonth")

l_Year = Request.Form("c boYear")

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

strSQL = "INSERT INTO tblAgencyOffens eStats(Applican tIntID, AgencyID,
OffenseID, CountTotalOffen ses,
CountOfOffenses ClearedByArrest OrExceptionalMe ans,
CountOfOffenses ReportedBySLEC, CountOfAlcoholR elatedOffenses,
CountOfDrugRela tedOffenses, CountOfOffenses CommittedByJuve nile, Month, Year)
VALUES ("& ApplicantIntID & ", "& l_AgencyCode &", "& l_OffenseCode &", "&
l_p_CountTotalO ffenses &", "& l_p_CountCleare dByArrestOrExce ptionalMeans &",
"& l_p_CountOffens eReportedBySlec &", "& l_p_CountAlcoho lRelated &", "&
l_p_CountDrugRe lated &", "& l_p_CountOffens eCommittedByJuv enile &", "&
l_Month &", "& l_year &")"

Response.Write strSQL & " "

conn.execute(st rSQL)

Response.Write " "
Response.Write "Your record has been updated." & " "
%>
<A HREF="mainentry .asp?ApplicantI ntID=<%=Applica ntIntID%>">Plea se 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/micro...UTF-8&oe=UTF-8
http://groups.google.com/group/micro...d322b882a604bd

--
Mike Brind

Mar 3 '06 #2

Roland Hall

"Mike Brind" wrote in message
news:11******** **************@ j33g2000cwa.goo glegroups.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 programmaticall y 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_proce ss.asp, line 94
: >
: > I am attaching the processing code here where line 94 is the following
: > statement:
: >
: > conn.execute(st rSQL)
: >
: > I have no idea why this is producing an error. Any help is appreciated.
: > CODE:
: >
: >
: > 
: > 
: > <HTML>
: > <HEAD>
: > <META NAME="GENERATOR " Content="Micros oft Visual Studio 6.0">
: > </HEAD>
: > <BODY>
: >
: > <%
: >
: > ApplicantIntID = Request.Cookies ("ApplicantIntI D")
: >
: >
: > Dim l_p_Agency
: > Dim l_p_lstOffense
: > Dim l_p_CountTotalO ffenses
: > Dim l_p_CountCleare dByArrestOrExce ptionalMeans
: > Dim l_p_CountOffens eReportedBySlec
: > Dim l_p_CountAlcoho lRelated
: > Dim l_p_CountDrugRe lated
: > Dim l_p_CountOffens eCommittedByJuv enile
: > Dim l_p_lstMonth
: > Dim l_p_lstYear
: >
: >
: >
: >
: > l_AgencyCode = Request.Form("c boAgency")
: >
: > l_OffenseCode = Request.Form("c boOffense")
: >
: > l_p_CountTotalO ffenses = Request.Form("t xt_CountTotalOf fenses")
: >
: > l_p_CountCleare dByArrestOrExce ptionalMeans =
: > Request.Form("t xt_CountCleared ByArrestOrExcep tionalMeans")
: >
: > l_p_CountOffens eReportedBySlec =
: > Request.Form("t xt_CountOffense ReportedBySlec" )
: >
: > l_p_CountAlcoho lRelated = Request.Form("t xt_CountAlcohol Related")
: >
: > l_p_CountDrugRe lated = Request.Form("t xt_CountDrugRel ated")
: >
: > l_p_CountOffens eCommittedByJuv enile =
: > Request.Form("t xt_CountOffense CommittedByJuve nile")
: >
: > l_Month = Request.Form("c boMonth")
: >
: > l_Year = Request.Form("c boYear")
: >
: > 'Now we are to build each row corresponding to each of the entry
: >
: > strSQL = "INSERT INTO tblAgencyOffens eStats(Applican tIntID,
AgencyID,
: > OffenseID, CountTotalOffen ses,
: > CountOfOffenses ClearedByArrest OrExceptionalMe ans,
: > CountOfOffenses ReportedBySLEC, CountOfAlcoholR elatedOffenses,
: > CountOfDrugRela tedOffenses, CountOfOffenses CommittedByJuve nile, Month,
Year)
: > VALUES ("& ApplicantIntID & ", "& l_AgencyCode &", "& l_OffenseCode &",
"&
: > l_p_CountTotalO ffenses &", "& l_p_CountCleare dByArrestOrExce ptionalMeans
&",
: > "& l_p_CountOffens eReportedBySlec &", "& l_p_CountAlcoho lRelated &", "&
: > l_p_CountDrugRe lated &", "& l_p_CountOffens eCommittedByJuv enile &", "&
: > l_Month &", "& l_year &")"
: >
: > Response.Write strSQL & " "
: >
: > conn.execute(st rSQL)
: >
: > Response.Write " "
: > Response.Write "Your record has been updated." & " "
: > %>
: > <A HREF="mainentry .asp?ApplicantI ntID=<%=Applica ntIntID%>">Plea se 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/micro...UTF-8&oe=UTF-8
:
http://groups.google.com/group/micro...d322b882a604bd

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

Mar 4 '06 #3

Mike Brind

Roland Hall wrote:

"Mike Brind" wrote in message
news:11******** **************@ j33g2000cwa.goo glegroups.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 programmaticall y 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_proce ss.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/micro...UTF-8&oe=UTF-8
:
http://groups.google.com/group/micro...d322b882a604bd

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

Mar 6 '06 #4

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.

"Mike Brind" wrote:

Roland Hall wrote:
"Mike Brind" wrote in message
news:11******** **************@ j33g2000cwa.goo glegroups.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 programmaticall y 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_proce ss.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/micro...UTF-8&oe=UTF-8
:
http://groups.google.com/group/micro...d322b882a604bd

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

Mar 7 '06 #5

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

Mar 7 '06 #6

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

Mar 8 '06 #7

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

Mar 8 '06 #8

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.

Mar 8 '06 #9

Bob Barrows [MVP]

Mike Brind wrote:

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.

Mar 8 '06 #10

Similar topics

2098

Form handling with PHP

by: Glyphman | last post by:

I have a bunch of pages with long forms, with lots of input types-text, radios, textareas, and the debugging process has become overwhelming. What I need to happen is to make sure that 1. Every field on the page is filled out-if not, a list of incorrect fields is displayed, and any radio boxes that were checked still are 2. The fields are queried to a table in a database. (The table has the same column names as the field names) Here's...

PHP

1170

Query problem...

by: Net Virtual Mailing Lists | last post by:

Hello, I have 3 tables which are joined that I need to create a summation for and I just cannot get this to work. Here's an example: CREATE table1 ( id1 INTEGER, title1 VARCHAR

PostgreSQL Database

13535

FormView, SqlDataSource and handling exceptions

by: Rob | last post by:

Hey all, So.. a simple FormView/SqlDataSource to handle inserting records into a table. The table has a primary key that the user enters (eg DiscountCode). If the user enters a duplicate the database complains about a primary key violation (which is what I want) and an exception is thrown. The OnInserted event of the SqlDataSource provides access to the exception so, presumably, you can provide nice handling for various errors. For...

ASP.NET

5766

inserting into two tables and transaction problem

by: Habib | last post by:

CREATE PROCEDURE SimpleInsert ( @custname custname_type, @carname carname_type) AS BEGIN TRANSACTION INSERT Customers (CustName) VALUES (@custname)

Microsoft SQL Server

2949

An OleDbParameter with ParameterName '@ID' is not contained by thisOleDbParameterCollection problem

by: DC | last post by:

The Code <%@ import namespace="System" %> <%@ import namespace="System.Web" %> <%@ import namespace="System.Web.UI" %> <%@ import namespace="System.Web.UI.HtmlControls" %> <%@ import namespace="System.Web.UI.WebControls" %> <%@ import namespace="System.Data" %> <%@ import namespace="System.Data.OleDb" %>

ASP.NET

3619

SqlDataSource Error Handling

by: dhyder | last post by:

I'm working on an admin page for a SQL Server 05 db. The page is in ASP.NET 2.0/C#. The db has multiple tables with foreign keys/constraints. I have multiple SqlDataSources and GridViews, which are doing the select/update/delete methods. I manually did the Insert method for each Gridview and I am able perform error handling on it. Textboxes for data to add <asp:Button ID="btnInsertNewService runat="server" Text="Submit"...

.NET Framework

4070

GridView on Delete Method problem

by: Ed Dror | last post by:

Hi there, I'm using ASP.NET 2.0 and SQL Server 2005 with VS 2005 Pro. I have a Price page (my website require login) with GridView with the following columns PriceID, Amount, Approved, CrtdUser and Date And Edit and Delete buttons

ASP.NET

2629

strange update problem .net

by: sirdavethebrave | last post by:

Hi guys - I have written a form, and a stored procedure to update the said form. It really is as simple as that. A user can go into the form, update some fields and hit the update button to update the information which is stored in a SQL database. In testing we noticed that the form was updating correctly but the update mechanism was also updating the first record of the table in the sql database every time. No error messages are on...

.NET Framework

2213

dropdown problem

by: coolv | last post by:

Hello I have problem in my page that the dropdown box is not displaying data according to selection of first dropdown.Please help me. Below is my code. thanks.............. <?php session_start(); if (!isset($_SESSION)) {

PHP

8427

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8746

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

8627

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

7356

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6179

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5649

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4335

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

1975

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

1737

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General