SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Jul 2001
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    null values in insert statement

    Hi

    There are different variables which i am passing in the insert statement. Now i want to have an option of passing null values in the insert statement.

    the columns in the database can take null values.

    But when the null value is confronted in the insert statement, it considers that variable to be missing and then there is a problem

    i want to find out a function like nvl in ms-access which converts null values to something else.

    thanks
    nikhil

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    You don't need to specify all the fields on an insert. You only need to specify those with a values. The rest will be filled with nulls if allowed.

    Thus, only pass the variables if they actually have a value and only execute the insert is any of the values are null. In other words, do something like this(field3 is a string to show how to add that line):

    Code:
    Dim InsertSQL, InsertSQLLen, ValueSQL, SQL
    InsertSQL = "INSERT INTO TableName("
    InsertSQLLen = Len(InsertSQL)
    ValueSQL = " VALUES ("
    
    field1 = Request("Field1")
    If field1 <> "" then
       InsertSQL = InsertSQL & "field1"
       ValueSQL = ValueSQL & field1
    end if
    
    field2 = Request("Field2")
    if field2 <> "" then
       IF Len(InsertSQL) > InsertSQLLen then
           InsertSQL = InsertSQL & ", "
           ValueSQL = ValueSQL & ", "
       end if
       InsertSQL = InsertSQL & "field2"
       ValueSQL = ValueSQL & field2
    end if
    
    field3 = Request("Field3")
    if field3 <> "" then
       IF Len(InsertSQL) > InsertSQLLen then
           InsertSQL = InsertSQL & ", "
           ValueSQL = ValueSQL & ", "
       end if
       InsertSQL = InsertSQL & "field3"
       ValueSQL = ValueSQL & "'" & field3 & "'"
    end if
    
    If Len(InsertSQL) > InsertSQLLen then
       ' Fields need to be inserted
       SQL = InsertSQL & ")" & ValueSQL & ")"
       my_Conn.Execute (SQL)
    end if

    Hope this helps. Good luck.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse


Bookmarks

Posting Permissions

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