|
Home > Archive > IIS ASP > February 2007 > Building an INSERT statement in ASP 30
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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.
| |
| Mike Brind 2007-02-19, 7:19 am |
|
"Dave" <davefrick@newsgroup.nospam> 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
| |
| Bob Barrows [MVP] 2007-02-19, 7:19 am |
| 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...2e36562fee7804e
Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:
Access:
http://www.google.com/groups?hl=en&...FTNGP12.phx.gbl
http://groups.google.com/groups?hl=...ftngp13.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"
| |
|
|
|
|
|