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
            
llputneyAsked:
Who is Participating?
 
golfDoctorCommented:
Sorry, this instead:

function escapeSQL(strInput)
      strOutput = strInput
      If isNull(strOutput) then
          escapeSQL = 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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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...



0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
golfDoctorCommented:
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
0
 
DiscoNovaCommented:
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.
0
 
llputneyAuthor Commented:
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.
0
 
golfDoctorCommented:
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
0
 
llputneyAuthor Commented:
golfDoctor: your function didn't work :-(
0
 
golfDoctorCommented:
still need help? if not, please close this question.

the function works
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.