By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,286 Members | 2,443 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,286 IT Pros & Developers. It's quick & easy.

passing null value as parameter in update stored procedure

P: n/a
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.
Mar 7 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
David Shorthouse wrote:
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
--
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"
Mar 7 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.