473,326 Members | 2,124 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

passing null value as parameter in update stored procedure

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
1 11432
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: WGW | last post by:
Though I am a novice to MS SQL server (2000 I believe), I can do almost! everything I need. Maybe not efficiently, but usefully. However, I have a problem -- a complex query problem... I can...
4
by: js | last post by:
I have a stored procedure named "processInventory" like the following. Depending on the passed in parameters, I would like to add a WHERE clause for "select" action. For example, if any varchar...
10
by: Resant | last post by:
I have a query : Exec 'Select * From Receiving Where Code In (' + @pCode + ')' @pCode will contain more than one string parameter, eg : A1, A2, A3 How can i write that parameters, I try use :...
6
by: dharmadam | last post by:
Is it possible to pass a column name or the order of the column name in the DB2 table table function. For example, I want to update the address of a person by passing one of the address column name...
6
by: dew | last post by:
I am using sql to modify data, using parameters. However, if I set the datatime to any other than a varchar, it bombs if the user enters a null value. How am I supposed to handle this. Set...
6
by: Paul M | last post by:
Hi All, I'm currently writing a z/OS DB2 Stored Proc in C, using an example from the IBM Stored Procedure guide (SG24-7083-00). The database calls to read and update the database work...
4
by: Ranginald | last post by:
Hi, I'm having trouble passing a parameter from my default.aspx page to my default2.aspx page. I have values from a query in a list box and the goal is to pass the "catID" from default.aspx...
17
by: Mark A | last post by:
DB2 8.2 for Linux, FP 10 (also performs the same on DB2 8.2 for Windoes, FP 11). Using the SAMPLE database, tables EMP and EMLOYEE. In the followng stored procedure, 2 NULL columns (COMM) are...
1
by: jkeel | last post by:
If I try to Update a record with the following code using a stored procedure I get an error: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.