Building an INSERT statement in ASP 30

[Dave]

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.

 

[Mike Brind]

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.

 

[Bob Barrows [MVP]]

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&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

 

[Dave]

Thanks guys. The parameter queries work well.

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&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]


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