Problem with handling table insert from ASP page

Discussion in 'ASP General' started by Jack, Mar 3, 2006.

  1. Jack

    Jack Guest

    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>
     
    Jack, Mar 3, 2006
    #1
    1. Advertising

  2. Jack

    Mike Brind Guest

    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

    --
    Mike Brind
     
    Mike Brind, Mar 3, 2006
    #2
    1. Advertising

  3. Jack

    Roland Hall Guest

    "Mike Brind" wrote in message
    news:...
    :
    : 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
     
    Roland Hall, Mar 4, 2006
    #3
  4. Jack

    Mike Brind Guest

    Roland Hall wrote:
    > "Mike Brind" wrote in message
    > news:...
    > :
    > : 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.

    --
    Mike Brind
     
    Mike Brind, Mar 6, 2006
    #4
  5. Jack

    Jack Guest

    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:...
    > > :
    > > : 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.
    >
    > --
    > Mike Brind
    >
    >
     
    Jack, Mar 7, 2006
    #5
  6. Jack

    Mike Brind Guest

    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
     
    Mike Brind, Mar 7, 2006
    #6
  7. Jack

    Roland Hall Guest

    : > 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
     
    Roland Hall, Mar 8, 2006
    #7
  8. Jack

    Mike Brind Guest

    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
     
    Mike Brind, Mar 8, 2006
    #8
  9. 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.
     
    Bob Barrows [MVP], Mar 8, 2006
    #9
  10. 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.
     
    Bob Barrows [MVP], Mar 8, 2006
    #10
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. cannontrodder
    Replies:
    1
    Views:
    734
    cannontrodder
    Jul 25, 2006
  2. D
    Replies:
    3
    Views:
    693
    Mark Rae
    Sep 3, 2006
  3. =?Utf-8?B?SmFu?=

    refresh page causes a new insert into table

    =?Utf-8?B?SmFu?=, Nov 10, 2006, in forum: ASP .Net
    Replies:
    1
    Views:
    411
    Steve B.
    Nov 15, 2006
  4. Replies:
    1
    Views:
    455
  5. PT
    Replies:
    1
    Views:
    398
    Ken Schaefer
    Oct 7, 2004
Loading...

Share This Page