Insert NULL if Form Field Blank

[Keith]

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 & ")"

 

[Hans]

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