Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002

    Unhappy Unanswered: updating & null values


    I have an admin interface on my client's site from which he can update the prices of his products.

    My problem is that is he updates the prices and one of the fields [currency format] is left blank then i cannot perform the UPDATE statement with the null value. I can set them to zero but then i have to rewrite some code in the front end of the site which i am not keen on doing right now.

    this is my SQL statment with the ASP:

    Dim strSQL,curWholesale,curWholesaleL
    curWholesale = request.querystring("wholesale")
    curWholesaleL = request.querystring("wholesaleL")

    strSQL = "UPDATE products SET Wholesale = " & curWholesale & ",WholesaleL = " & curWholesaleL & " WHERE ID = [x]"

    it works fine until one of the variables is null.

    Thanks in advance!!:

  2. #2
    Join Date
    Aug 2002
    Québec, Canada
    Use the IIF statement:

    UPDATE products SET Wholesale = IIF(param1 is null,0,param1), WholesaleL = IIF(param2 is null, 0, param2) WHERE ID = x
    You can use that very SQL, or the VB IIF:

    strSQL = "UPDATE products SET Wholesale = " & _      iif(isnull(curWholesale),0,curWholesale) & ",WholesaleL = " & _
        iif(isnull(curWholesaleL),0,curWholesaleL) & " WHERE ID = [x]"
    The IIF statement exist in SQL Language, and in VB Language

    JefB - hope it helps

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts