Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Update with null instead of empty string

Posted on 2007-10-16
9
Medium Priority
?
1,696 Views
Last Modified: 2008-01-30
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
            
0
Comment
Question by:llputney
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20088506
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
 
LVL 16

Expert Comment

by:golfDoctor
ID: 20088530
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:DiscoNova
ID: 20088549
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
 

Author Comment

by:llputney
ID: 20088584
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
 
LVL 16

Expert Comment

by:golfDoctor
ID: 20088609
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
 
LVL 16

Accepted Solution

by:
golfDoctor earned 2000 total points
ID: 20088612
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
 

Author Comment

by:llputney
ID: 20088651
golfDoctor: your function didn't work :-(
0
 
LVL 16

Expert Comment

by:golfDoctor
ID: 20207030
still need help? if not, please close this question.

the function works
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Screencast - Getting to Know the Pipeline
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question