Insert NULL if Form Field Blank

Discussion in 'ASP General' started by Keith, Jun 9, 2004.

  1. Keith

    Keith Guest

    I am Using Dreamweaver MX to create my site and have come accross a problem
    no one in the DW groups seems to be able to help with.

    When I submit an insert to my SQL database, any form value which is left
    blank is NOT inserted as a NULL value into the DB as I require but as blank
    data.

    How can I modify the DW-created statement below to insert a NULL if the
    field is empty?

    Thanks

    ' create the sql insert statement
    MM_tableValues = ""
    MM_dbValues = ""
    For i = LBound(MM_fields) To UBound(MM_fields) Step 2
    FormVal = MM_fields(i+1)
    MM_typeArray = Split(MM_columns(i+1),",")
    Delim = MM_typeArray(0)
    If (Delim = "none") Then Delim = ""
    AltVal = MM_typeArray(1)
    If (AltVal = "none") Then AltVal = ""
    EmptyVal = MM_typeArray(2)
    If (EmptyVal = "none") Then EmptyVal = ""
    If (FormVal = "") Then
    FormVal = EmptyVal
    Else
    If (AltVal <> "") Then
    FormVal = AltVal
    ElseIf (Delim = "'") Then ' escape quotes
    FormVal = "'" & Replace(FormVal,"'","''") & "'"
    Else
    FormVal = Delim + FormVal + Delim
    End If
    End If
    If (i <> LBound(MM_fields)) Then
    MM_tableValues = MM_tableValues & ","
    MM_dbValues = MM_dbValues & ","
    End if
    MM_tableValues = MM_tableValues & MM_columns(i)
    MM_dbValues = MM_dbValues & FormVal
    Next
    MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ")
    values (" & MM_dbValues & ")"
    Keith, Jun 9, 2004
    #1
    1. Advertising

  2. Keith

    Hans Guest

    Hi!

    Well I haven't looked into your code that much and I don't know which
    database you use but you will have to check if the field is empty and then
    insert null instead. Note that you should not have quotes around the value
    null so "insert into my_table (field1, field2) values ('some value', null)"
    should be OK but "insert into my_table (field1, field2) values ('some
    value', 'null')" will insert the string value 'null' into the field2.

    Also make sure your database allow null values in the columns if that is
    what you want.

    Regards
    /Hans
    Hans, Jun 9, 2004
    #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. Replies:
    5
    Views:
    26,596
    Mike Schilling
    Mar 29, 2006
  2. sai krishna
    Replies:
    0
    Views:
    1,623
    sai krishna
    Oct 24, 2008
  3. GavMc
    Replies:
    4
    Views:
    323
    Evertjan.
    Sep 22, 2005
  4. Replies:
    1
    Views:
    88
    Matt Garrish
    Dec 30, 2004
  5. rob c
    Replies:
    4
    Views:
    317
    McKirahan
    Dec 30, 2005
Loading...

Share This Page