Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Building an INSERT statement in ASP 30

Reply
Thread Tools

Building an INSERT statement in ASP 30

 
 
Dave
Guest
Posts: n/a
 
      02-19-2007
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.






 
Reply With Quote
 
 
 
 
Mike Brind
Guest
Posts: n/a
 
      02-19-2007

"Dave" <(E-Mail Removed)> wrote in message
news:u0OoOp$(E-Mail Removed)...
>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


 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-19-2007
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/...e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl

http://groups.google.com/groups?hl=e...tngp13.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"


 
Reply With Quote
 
Dave
Guest
Posts: n/a
 
      02-19-2007
Thanks guys. The parameter queries work well.


"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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/...e36562fee7804e
>
> Personally, I prefer using stored procedures, or saved parameter queries
> as
> they are known in Access:
>
> Access:
> http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl
>
> http://groups.google.com/groups?hl=e...tngp13.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"
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
ASP Error on INSERT INTO Statement =?Utf-8?B?T2ZmX1JvdXRl?= ASP .Net 1 10-21-2004 11:43 PM
ASP Error on INSERT INTO Statement =?Utf-8?B?T2ZmX1JvdXRl?= ASP .Net 0 10-21-2004 09:51 PM
ASP Error on INSERT INTO Statement =?Utf-8?B?T2ZmX1JvdXRl?= ASP .Net 0 10-21-2004 09:51 PM
INSERT statement contains fewer items than the insert list J. Muenchbourg ASP General 3 09-30-2003 09:24 PM
What am I doing wrong; problem with INSERT statement (ASP/MS ACCESS) Jason Melville ASP General 4 09-16-2003 04:54 PM



Advertisments