Building an INSERT statement in ASP 30

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.

Re: Building an INSERT statement in ASP 30

"Dave" wrote in message
news:u0OoOp$UHHA.3948@TK2MSFTNGP05.phx.gbl...
>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

Re: Building an INSERT statement in ASP 30

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.inetser ver.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&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl


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

Re: Building an INSERT statement in ASP 30

Thanks guys. The parameter queries work well.


"Bob Barrows [MVP]" wrote in message
news:edzbWTCVHHA.4844@TK2MSFTNGP03.phx.gbl...
> 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.inetser ver.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&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
>
>
> --
> 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"
>