asked on

Update with null instead of empty string

I have a form that one can go to and edit information. If a user does not enter info on a field, it will show as an empty string in the database. How can I get it to update with a Null value instead of an empty string? However, there are certain fields that will update with a null value instead of a string. See code:

Here is a portion of my ASP code:
      if request("formcode") = "update" then
            if request("txtApplication") = "ON" then strApplication = 1 else strApplication = 0
            if request("txtEligible") = "ON" then strEligible = 1 else strEligible = 0
            if request("txtDelete") = "ON" then strActive = 0 else strActive = 1
            if isDate(request("txtDateDiscontinued")) then strDateDiscontinued = request("txtDateDiscontinued") else strDateDiscontinued = "Null"
            if isDate(request("txtDateEligible")) then strDateEligible = request("txtDateEligible") else strDateEligible = "Null"
            if isDate(request("txtDateOfBirth")) then strDateOfBirth = request("txtDateOfBirth") else strDateOfBirth = "Null"

            strSQL = "UPDATE tedp_consumer SET " & _
            "staffid = " & escapeSQL(request("txtStaffID")) & ", " & _
            "active = " & escapeSQL(stractive) & ", " & _
            "First_Name = " & escapeSQL(request("txtFirstName")) & ", " & _
            "Last_Name = " & escapeSQL(request("txtLastName")) & ", " & _
            "Address = " & escapeSQL(request("txtAddress")) & ", " & _
            "City = " & escapeSQL(request("txtCity")) & ", " & _
            "State = " & escapeSQL(request("txtState")) & ", " & _
            "Zipcode = " & escapeSQL(request("txtZipcode")) & ", " & _
            "County = " & escapeSQL(request("txtCounty")) & ", " & _
            "SSN = " & escapeSQL(request("txtSSN")) & ", " & _
            "Home_Phone = " & escapeSQL(request("txtHomePhone")) & ", " & _
            "Work_Phone = " & escapeSQL(request("txtWorkPhone")) & ", " & _
            "Consumer_Type = " & escapeSQL(request("txtConsumerType")) & ", " & _
            "Status = " & escapeSQL(request("txtStatus")) & ", " & _
            "Eligible = " & escapeSQL(strEligible) & ", " & _
            "Date_Eligible = " & escapeSQL(strDateEligible) & ", " & _
            "date_discontinued = " & escapeSQL(strDateDiscontinued) & ", " & _
            "reason_discontinued = " & escapeSQL(request("txtReasonDiscontinued")) & ", " & _
            "DateOfBirth = " & escapeSQL(strDateOfBirth) & ", " & _
            "Gender = " & escapeSQL(request("txtGender")) & ", " & _
            "Ethnic = " & escapeSQL(request("txtEthnic")) & ", " & _
            "OfficeID = " & escapeSQL(request("txtOffice")) & ", " & _
            "VerName = " & escapeSQL(request("txtVerName")) & ", " & _
            "Veraddress = " & escapeSQL(request("txtVeraddress")) & ", " & _
            "Verphone = " & escapeSQL(request("txtVerphone")) & ", " & _
            "Veroccupation = " & escapeSQL(request("txtVeroccupation")) & ", " & _
            "Case_Notes = " & escapeSQL(request("txtCaseNotes")) & ", " & _
            "Application_Complete = " & escapeSQL(strApplication) & " " & _
            "WHERE id = " & escapeSQL(strID)
            oConn.execute strSQL

Guy Hengel [angelIII / a3]

you put :
"date_discontinued = " & escapeSQL(strDateDiscontinued) & ", " & _

which is probably putting quotes around the value.
and THAT is the problem, in case the values passed is "NULL".
you could change the function escapeSQL to NOT put quotes wheneven the string value is "NULL"...

however, the preferred method would be to make a parametrized query.
that would take away from you all the issues of the quoting...

Guy Hengel [angelIII / a3]

http://www.google.lu/search?hl=en&safe=off&client=firefox-a&rls=org.mozilla:de:official&hs=L5C&sa=X&oi=spell&resnum=0&ct=result&cd=1&q=adodb+parameterized+query&spell=1

golfDoctor

You need to set the value to NULL in your escapeSQL() itlooks like. In SQL single quotes determine if empty string is entered or not, but I don't see any in your code. So, I guess you do that in escapeSQL function

DiscoNova

Write a general function like this one (I'm not entirely sure if that's the correct way to escape quotes, didn't have anything to test this on):

Function empty2null(s)
      If (s = """""")
            empty2null = "NULL"
      Else
            empty2null = s
      End If
End Function

... and instead of ...

escapeSQL(request("txtVerName"))

...you should use...

empty2null(escapeSQL(request("txtVerName")))

...for the fields where you wish to have NULL instead of empty string.

llputney

ASKER

I found the code of the escapeSQL function

' escapeSQL prepares variables for SQL statements
' It just strips out nulls, escapes any single-quotes inside the string,
' and wraps strings with single-quotes. Any variable passed to SQL is
' run through this function.
function escapeSQL(strInput)
      strOutput = strInput
      If isNull(strOutput) then strOutput = ""
      strOutput = replace(strOutput,"'","''")
      if Not isNumeric(strOutput) then strOutput = "'" & strOutput & "'"
      if isNumeric(strOutput) AND instr(strOutput,"-") > 0 then strOutput = "'" & strOutput & "'"
      If strInput = "Null" then strOutput = "Null"
      escapeSQL = strOutput
end function

Im not sure why this line "If isNull(strOutput) then strOutput = """ is there, but I tried removing it and updated the form with some empty fields, it still didin't give me a NULL value.

golfDoctor

Try to this:

function escapeSQL(strInput)
strOutput = strInput
If isNull(strOutput) then
strOutput = NULL
exit function
end if
strOutput = replace(strOutput,"'","''")
if Not isNumeric(strOutput) then strOutput = "'" & strOutput & "'"
if isNumeric(strOutput) AND instr(strOutput,"-") > 0 then strOutput = "'" & strOutput & "'"
If strInput = "Null" then strOutput = "Null"
escapeSQL = strOutput
end function

ASKER CERTIFIED SOLUTION

golfDoctor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

llputney

ASKER

golfDoctor: your function didn't work :-(

golfDoctor

still need help? if not, please close this question.

the function works