Building an INSERT statement in ASP 30

Discussion in 'ASP General' started by Dave, Feb 19, 2007.

  1. Dave

    Dave Guest

    I have an old web app that ues an Access database and ASP 3.0.

    I need to build an INSERT statement based on the contents of a form.

    What is the best way to handle blank text boxes that are submitted with the
    form?

    For example, I collect all my name/value pairs that are submitted with the
    form like this...

    sExample=Request.Form("txaExample")
    sNote=Request.Form("txtNote")
    iSourceID=Request.Form("cboSourceID")
    iPageNo=Request.Form("txtPageNo")
    sSourceRef=Request.Form("txtSourceRef")


    ....and then I build my INSERT statement like this...

    sSQL = "INSERT INTO example (example, sourceid, sourceref, pageno, note)"
    sSQL = sSQL & " VALUES ('" & sExample & "', "
    sSQL = sSQL & cstr(iSourceID) & ", "
    sSQL = sSQL & "'" & sSourceRef & "', "
    sSQL = sSQL & cstr(iPageNo) & ", "
    sSQL = sSQL & "'" & sNote & "' "
    sSQL = sSQL & ")"

    ....but if some of the controls are left blank, I get an INSERT atatement
    llike this...

    INSERT INTO example (example, sourceid, sourceref, pageno, note) VALUES
    ('asgfgdsfhg', 6, '', , '' )

    What is the value when an empty control is submitted?

    isempty and isnull both return false even though nothing was submitted with
    the form. I can test for a zero length (IF len(Note)=0) but is this the
    best way to test?

    IOW, test each value for zero length and if true, set the value equal to
    NULL to get something like this...

    INSERT INTO example (example, sourceid, sourceref, pageno, note) VALUES
    ('asgfgdsfhg', 6, NULL,NULL ,NULL )

    Thanks for any insights.
    Dave, Feb 19, 2007
    #1
    1. Advertising

  2. Dave

    Mike Brind Guest

    "Dave" <> wrote in message
    news:u0OoOp$...
    >I have an old web app that ues an Access database and ASP 3.0.
    >
    > I need to build an INSERT statement based on the contents of a form.
    >
    > What is the best way to handle blank text boxes that are submitted with
    > the form?
    >
    > For example, I collect all my name/value pairs that are submitted with the
    > form like this...
    >
    > sExample=Request.Form("txaExample")
    > sNote=Request.Form("txtNote")
    > iSourceID=Request.Form("cboSourceID")
    > iPageNo=Request.Form("txtPageNo")
    > sSourceRef=Request.Form("txtSourceRef")
    >
    >
    > ...and then I build my INSERT statement like this...
    >
    > sSQL = "INSERT INTO example (example, sourceid, sourceref, pageno, note)"
    > sSQL = sSQL & " VALUES ('" & sExample & "', "
    > sSQL = sSQL & cstr(iSourceID) & ", "
    > sSQL = sSQL & "'" & sSourceRef & "', "
    > sSQL = sSQL & cstr(iPageNo) & ", "
    > sSQL = sSQL & "'" & sNote & "' "
    > sSQL = sSQL & ")"
    >
    > ...but if some of the controls are left blank, I get an INSERT atatement
    > llike this...
    >
    > INSERT INTO example (example, sourceid, sourceref, pageno, note) VALUES
    > ('asgfgdsfhg', 6, '', , '' )
    >
    > What is the value when an empty control is submitted?
    >
    > isempty and isnull both return false even though nothing was submitted
    > with the form. I can test for a zero length (IF len(Note)=0) but is this
    > the best way to test?
    >
    > IOW, test each value for zero length and if true, set the value equal to
    > NULL to get something like this...
    >
    > INSERT INTO example (example, sourceid, sourceref, pageno, note) VALUES
    > ('asgfgdsfhg', 6, NULL,NULL ,NULL )
    >
    > Thanks for any insights.
    >


    The values that are being passed can be checked using the TypeName()
    function. There's no reason why you can't execute "NSERT INTO example
    (example, sourceid, sourceref, pageno, note) VALUES ('asgfgdsfhg', 6, '', ,
    '' )". The Access database will put default values into the empty fields.

    An easier way to do this kind of thing is to create a saved parameter query
    in access and use that. It saves having to delimit values, escaping quotes
    etc.

    --
    Mike Brind
    Mike Brind, Feb 19, 2007
    #2
    1. Advertising

  3. Dave wrote:
    > I have an old web app that ues an Access database and ASP 3.0.
    >
    > I need to build an INSERT statement based on the contents of a form.
    >
    > What is the best way to handle blank text boxes that are submitted
    > with the form?
    >
    > For example, I collect all my name/value pairs that are submitted
    > with the form like this...
    >
    > sExample=Request.Form("txaExample")
    > sNote=Request.Form("txtNote")
    > iSourceID=Request.Form("cboSourceID")
    > iPageNo=Request.Form("txtPageNo")
    > sSourceRef=Request.Form("txtSourceRef")
    >
    >
    > ...and then I build my INSERT statement like this...
    >
    > sSQL = "INSERT INTO example (example, sourceid, sourceref, pageno,
    > note)" sSQL = sSQL & " VALUES ('" & sExample & "', "
    > sSQL = sSQL & cstr(iSourceID) & ", "
    > sSQL = sSQL & "'" & sSourceRef & "', "
    > sSQL = sSQL & cstr(iPageNo) & ", "
    > sSQL = sSQL & "'" & sNote & "' "
    > sSQL = sSQL & ")"
    >

    Further points to consider:
    Your 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:

    Access:
    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=

    http://groups.google.com/groups?hl=...=1&selm=


    --
    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"
    Bob Barrows [MVP], Feb 19, 2007
    #3
  4. Dave

    Dave Guest

    Thanks guys. The parameter queries work well.


    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Dave wrote:
    >> I have an old web app that ues an Access database and ASP 3.0.
    >>
    >> I need to build an INSERT statement based on the contents of a form.
    >>
    >> What is the best way to handle blank text boxes that are submitted
    >> with the form?
    >>
    >> For example, I collect all my name/value pairs that are submitted
    >> with the form like this...
    >>
    >> sExample=Request.Form("txaExample")
    >> sNote=Request.Form("txtNote")
    >> iSourceID=Request.Form("cboSourceID")
    >> iPageNo=Request.Form("txtPageNo")
    >> sSourceRef=Request.Form("txtSourceRef")
    >>
    >>
    >> ...and then I build my INSERT statement like this...
    >>
    >> sSQL = "INSERT INTO example (example, sourceid, sourceref, pageno,
    >> note)" sSQL = sSQL & " VALUES ('" & sExample & "', "
    >> sSQL = sSQL & cstr(iSourceID) & ", "
    >> sSQL = sSQL & "'" & sSourceRef & "', "
    >> sSQL = sSQL & cstr(iPageNo) & ", "
    >> sSQL = sSQL & "'" & sNote & "' "
    >> sSQL = sSQL & ")"
    >>

    > Further points to consider:
    > Your 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:
    >
    > Access:
    > http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=
    >
    > http://groups.google.com/groups?hl=...=1&selm=
    >
    >
    > --
    > 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"
    >
    Dave, Feb 19, 2007
    #4
    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. =?Utf-8?B?T2ZmX1JvdXRl?=

    ASP Error on INSERT INTO Statement

    =?Utf-8?B?T2ZmX1JvdXRl?=, Oct 21, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    3,058
  2. =?Utf-8?B?T2ZmX1JvdXRl?=

    ASP Error on INSERT INTO Statement

    =?Utf-8?B?T2ZmX1JvdXRl?=, Oct 21, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    301
    =?Utf-8?B?T2ZmX1JvdXRl?=
    Oct 21, 2004
  3. =?Utf-8?B?T2ZmX1JvdXRl?=

    ASP Error on INSERT INTO Statement

    =?Utf-8?B?T2ZmX1JvdXRl?=, Oct 21, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    263
    =?Utf-8?B?T2ZmX1JvdXRl?=
    Oct 21, 2004
  4. Jason Melville
    Replies:
    4
    Views:
    116
    PB4FUN
    Sep 16, 2003
  5. J. Muenchbourg
    Replies:
    3
    Views:
    230
    Aaron Bertrand - MVP
    Sep 30, 2003
Loading...

Share This Page