Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > passing null value as parameter in update stored procedure

Reply
Thread Tools

passing null value as parameter in update stored procedure

 
 
David Shorthouse
Guest
Posts: n/a
 
      03-07-2006
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.


 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      03-07-2006
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"


 
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
Passing a null value to a stored procedure from Java (DBMS_SQL.Varchar2_Table) rafalmd@gmail.com Java 3 03-09-2007 10:40 AM
[BUG?] Update database using stored procedure and OleDbDataAdapter.Update joun ASP .Net 9 11-30-2004 04:57 AM
Stored procedure parameter output value Steven ASP .Net 5 05-27-2004 03:16 AM
Passing null values to a SQL Stored Procedure. John Hoge ASP .Net 1 05-12-2004 07:20 PM
Using query string to pass a value to a stored procedure parameter Machelle Chandler ASP .Net Datagrid Control 0 10-20-2003 11:22 PM



Advertisments