passing null value as parameter in update stored procedure

[David Shorthouse]

Hey folks,

I am attempting to pass null as the input value from a series of
textboxes if the user does not input a value prior to submit. To try and do
this, I am using a vbscript function on this asp as follows:

CommentsAdd = IIf(Request.Form("Comments")="",NULL,Request.Form("Comments"))

I was hoping this would convert those emptystring textboxes to null.

Then, these variables are input in an update stored procedure called from
the same asp upon submit. This handles ntext and like fields in the table I
am updating, but a null value passed in this way for a numeric field chokes.
The error on the asp is something like, "unable to convert value of type
text to numeric". Can't null be passed as a parameter value when the field
type is numeric?

How does one pass null for a parameter's value in a stored procedure on an
asp? Am I missing something here?

Thanks,

Dave
______________________________
Remove "_SPAM" to reply directly.

 

[Bob Barrows [MVP]]

Hey folks,

I am attempting to pass null as the input value from a series of
textboxes if the user does not input a value prior to submit. To try
and do this, I am using a vbscript function on this asp as follows:

CommentsAdd =
IIf(Request.Form("Comments")="",NULL,Request.Form("Comments"))
I was hoping this would convert those emptystring textboxes to null.

Then, these variables are input in an update stored procedure called
from the same asp upon submit. This handles ntext and like fields in
the table I am updating, but a null value passed in this way for a
numeric field chokes. The error on the asp is something like, "unable
to convert value of type text to numeric".

Sounds like you are using dynamic sql to execute your stored procedure...
Some like this technique, but, in addition to defeating one of the reasons
for using procedures (parameters) I consider it to be
1. hard to use
2. prone to sql injection

Can't null be passed as a
parameter value when the field type is numeric?

How does one pass null for a parameter's value in a stored procedure
on an asp? Am I missing something here?

Here is how I do it:

dim input
input = request.form("comments")
if len(input)=0 then input = null
dim conn
conn.open <connection string>
conn.NameOfProcedure input

For more details (assuming this is sql server ... please don't make us guess
what database you are using):
http://tinyurl.com/jyy0

Bob Barrows