passing null value as parameter in update stored procedure

Discussion in 'ASP General' started by David Shorthouse, Mar 7, 2006.

  1. 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.
     
    David Shorthouse, Mar 7, 2006
    #1
    1. Advertising

  2. 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"
     
    Bob Barrows [MVP], Mar 7, 2006
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Steven
    Replies:
    5
    Views:
    5,946
    Jeff Louie
    May 27, 2004
  2. John Hoge
    Replies:
    1
    Views:
    16,856
    Ken Cox [Microsoft MVP]
    May 12, 2004
  3. joun
    Replies:
    9
    Views:
    2,247
    W.G. Ryan eMVP
    Nov 30, 2004
  4. joun
    Replies:
    5
    Views:
    2,675
  5. Replies:
    3
    Views:
    4,120
    Dimitri Kurashvili
    Mar 9, 2007
Loading...

Share This Page