format empty as null prior to insert

[David Shorthouse]

Hey folks,

This one's probably been asked before but I'm tired of trying to find a
solution (if there is one!). I have an asp update page and I'd like to
convert all empty fields to NULL prior to updating the Access db rather than
permitting zero length fields in the db. Is this possible? Is there any way
to globally declare this for all text fields on the asp page rather than
having to do a whole whack of "if then" formats? Is there something I can
set in the field's properties in Access that will deny zero length yet won't
cause the asp update to choke and return an error?

Thanks for any feedback,

Dave

 

[Bob Barrows [MVP]]

Hey folks,

This one's probably been asked before but I'm tired of trying to
find a solution (if there is one!). I have an asp update page and I'd
like to convert all empty fields to NULL prior to updating the Access
db rather than permitting zero length fields in the db. Is this
possible?

Sure, just pass Null when the user does not enter data.

Is there any way to globally declare this for all text
fields on the asp page rather than having to do a whole whack of "if
then" formats?
No

Is there something I can set in the field's properties
in Access that will deny zero length yet won't cause the asp update
to choke and return an error?

No.

 

[David Shorthouse]

Bob,

Thanks for the quick response. How then does one pass null when the
field is empty? I'm sad to hear there isn't any way to declare this for all
variables in one shot, say at the very beginning of the script on the the
asp. Could you provide a vbscipt I ought to include on the asp?

Thanks.

Dave

 

[Bob Barrows [MVP]]

Bob,

Thanks for the quick response. How then does one pass null when the
field is empty? I'm sad to hear there isn't any way to declare this
for all variables in one shot, say at the very beginning of the
script on the the asp. Could you provide a vbscipt I ought to include
on the asp?

It depends. Are you using a recordset (not recommended)? Dynamic SQL (also
not recommended)? Parameters?

 

[David Shorthouse]

Bob,

I am indeed using a recordset with UPDATE and SET. Why is this not
recommended? What's the better alternative?

Dave

 

[Bob Barrows [MVP]]

Bob,

I am indeed using a recordset with UPDATE and SET. Why is this not
recommended? What's the better alternative?

This is a contradiction. Using a recordset, you would open a recordset on a
table or select statement, point the cursor at a record, set the field
values and use the Update method to send the changes to the database. This
is not recommended because cursors are resource-intensive and slow. In ASP,
they should only be used to retrieve read-only data for display purposes.

Using "UPDATE and SET" seems to imply that you are using dynamic sql. This
is not recommended due to the security concerns brought on by leaving
yourself open to sql injection attacks by hackers:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf


Perhaps you should read these:

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e


Bob Barrows

 

[Roland Hall]

: David Shorthouse wrote:
: > Bob,
: >
: > I am indeed using a recordset with UPDATE and SET. Why is this not
: > recommended? What's the better alternative?
<snip>
: Using "UPDATE and SET" seems to imply that you are using dynamic sql. This
: is not recommended due to the security concerns brought on by leaving
: yourself open to sql injection attacks by hackers:

I'd be happy to test those for ya', David. (O:=

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp