Trouble with null value in SQL statement

[The_Grudge]

I have the following code:

strWriteData = "INSERT INTO PROD.CSU_Clinic_Info(ID,Institution,Occurs,Clinic_Date,Location_Num,SID_DOC_NUM," & _
                         "Clinic_Num,Clinic_Code,Doc_Index,Rooms_Used,Time_Of_Day,Cancelled," & _
                         "Cancelled_Reason,Special,Hold,Reassignment,Day_Of_Week,Reinstate,Start_Date,End_Date,User_Name,Special_Booked_Date) " & _
                         "VALUES ('" & mUniqueID & _
                         "','" & mInstitution & _
                         "','" & mOccurs & _
                         "'," & "To_Date ('" & Month(mClinic_Date) & "/" & Day(mClinic_Date) & "/" & Year(mClinic_Date) & "','MM/DD/YYYY')" & _
                         ",'" & mLocation & _
                         "','" & mSID_DOC_NUM & _
                         "','" & mClinicNum & _
                         "','" & mClinic_Code & _
                         "','" & mDoc_Index & _
                         "','" & mRooms & _
                         "','" & mTime & _
                         "','" & mCancelled & _
                         "','" & mCancelledReason & _
                         "','" & mSpecial & _
                         "','" & mHold & _
                         "','" & mReassignment & _
                         "','" & mDayOfWeek & _
                         "','" & mReinstatement & _
                         "'," & "To_Date ('" & Month(mStart_Date) & "/" & Day(mStart_Date) & "/" & Year(mStart_Date) & "','MM/DD/YYYY')" & _
                         "," & "To_Date ('" & Month(mEnd_Date) & "/" & Day(mEnd_Date) & "/" & Year(mEnd_Date) & "','MM/DD/YYYY')" & _
                         ",'" & mUserName & _
                         "'," & "To_Date ('" & Month(mSpecialBookedDate) & "/" & Day(mSpecialBookedDate) & "/" & Year(mSpecialBookedDate) & "','MM/DD/YYYY')"

The problem is the last line highlighted in bold. If the SpecialBookedDate is null, which it often is, I get a type mismatch error, understandably so since it's receiving a null value.

At present, the only solution I can think of is an If statement above the code that says "If Len(mSpecialBookedDate) = 0 Then" and I have the SQL string without the SpecialBookedDate line included, else, the code you see above. This seems like a lot of extra code when there is a simpler solution though. Any other ideas I'm missing here?

[Arnoutdv]

Build the query without the To_Date but with a special placeholder
Then replace it with the To_Date values if mSpecialBookedDate is <> null and otherwise replace it with an empty string.

'//// UNTESTED !!!
strWriteData = "INSERT INTO PROD.CSU_Clinic_Info(ID,Institution,Occurs,Clinic_Date,Location_Num,SID_DOC_NUM," & _
                         "Clinic_Num,Clinic_Code,Doc_Index,Rooms_Used,Time_Of_Day,Cancelled," & _
                         "Cancelled_Reason,Special,Hold,Reassignment,Day_Of_Week,Reinstate,Start_Date,End_Date,User_Name$1$) " & _
                         "VALUES ('" & mUniqueID & _
                         "','" & mInstitution & _
                         "','" & mOccurs & _
                         "'," & "To_Date ('" & Month(mClinic_Date) & "/" & Day(mClinic_Date) & "/" & Year(mClinic_Date) & "','MM/DD/YYYY')" & _
                         ",'" & mLocation & _
                         "','" & mSID_DOC_NUM & _
                         "','" & mClinicNum & _
                         "','" & mClinic_Code & _
                         "','" & mDoc_Index & _
                         "','" & mRooms & _
                         "','" & mTime & _
                         "','" & mCancelled & _
                         "','" & mCancelledReason & _
                         "','" & mSpecial & _
                         "','" & mHold & _
                         "','" & mReassignment & _
                         "','" & mDayOfWeek & _
                         "','" & mReinstatement & _
                         "'," & "To_Date ('" & Month(mStart_Date) & "/" & Day(mStart_Date) & "/" & Year(mStart_Date) & "','MM/DD/YYYY')" & _
                         "," & "To_Date ('" & Month(mEnd_Date) & "/" & Day(mEnd_Date) & "/" & Year(mEnd_Date) & "','MM/DD/YYYY')" & _
                         ",'" & mUserName "'"

If Len(mSpecialBookedDate) = 0 Then
  strWriteData = Replace(strWriteData, "$1$", ",Special_Booked_Date")
  strWriteData = strWriteData & "To_Date ('" & Month(mSpecialBookedDate) & "/" & Day(mSpecialBookedDate) & "/" & Year(mSpecialBookedDate) & "','MM/DD/YYYY')"
Else
  strWriteData = Replace(strWriteData, "$1$", "")
End If
' Close the VALUES()
strWriteData = strWriteData & ")"

[DEXWERX]

What I do is break out that expression into a custom DateToString function which returns NULL or your quoted value.

edit:

Private Function DateToString(ByVal DateExpr As Variant) As String
    If IsNull(DateExpr) Then
        DateToString = "NULL"
    Else
        DateToString = Replace("TO_DATE('{0}','MM/DD/YYYY')", "{0}", Format(DateExpr, "mm/dd/yyyy"))
    End If
End Function

strWriteData = "INSERT INTO PROD.CSU_Clinic_Info(ID,Institution,Occurs,Clinic_Date,Location_Num,SID_DOC_NUM," & _
                         "Clinic_Num,Clinic_Code,Doc_Index,Rooms_Used,Time_Of_Day,Cancelled," & _
                         "Cancelled_Reason,Special,Hold,Reassignment,Day_Of_Week,Reinstate,Start_Date,End_Date,User_Name,Special_Booked_Date) " & _
                         "VALUES ('" & mUniqueID & _
                         "','" & mInstitution & _
                         "','" & mOccurs & _
                         "'," & DateToString(mClinic_Date) & _
                         ",'" & mLocation & _
                         "','" & mSID_DOC_NUM & _
                         "','" & mClinicNum & _
                         "','" & mClinic_Code & _
                         "','" & mDoc_Index & _
                         "','" & mRooms & _
                         "','" & mtime & _
                         "','" & mCancelled & _
                         "','" & mCancelledReason & _
                         "','" & mSpecial & _
                         "','" & mHold & _
                         "','" & mReassignment & _
                         "','" & mDayOfWeek & _
                         "','" & mReinstatement & _
                         "'," & DateToString(mStart_Date) & _
                         "," & DateToString(mEnd_Date) & _
                         ",'" & mUserName & _
                         "'," & DateToString(mSpecialBookedDate)

conversion from date to string back to date seems very very wrong...

[The_Grudge]

Thanks to both of you for getting my brain out of the rut it was in when I wrote the post.

Sometimes I get writer's block, but for programmers.

[Elroy]

Say Grudge,

I think of Dex's solution as a "wrapper function". I literally have 100s of generic wrapper functions I use for all sorts of things in my primary project. Some of them are as simple as a SafeKill(SomeFileSpec), and others can be rather complex. Many of them use the IsNull() function to avoid the kinds of problems you had.

IDK, for me, it's just always important to remember that we can "wrap" some variable or function in another "wrapper function" to solve certain problems.

All The Best,
Elroy

[dilettante]

You can also use a prepared query (ADO Command object) or stored procedure, then invoke it passing parameters. This has lots of advantages over cobbling together dynamic SQL, not the least of which being that you can pass Null values when you have them.

For something like an INSERT these can be treated as extended methods of your ADO Connection object.


Command Object (ADO)

To execute a Command, simply call it by its Name property on the associated Connection object. The Command must have its ActiveConnection property set to the Connection object. If the Command has parameters, pass their values as arguments to the method.

I'm not sure why this was moved under the "Office" heading at MSDN but you can also find it in your MSDN Library documentation installed from CDs:

MSDN Library - October 2001
    Platform SDK Documentation
        Data Services
            Microsoft Data Access Components (MDAC) SDK
                Microsoft ActiveX Data Objects (ADO)
                    ADO API Reference
                        ADO Objects
                            Command Object

The October 2001 issue of the docs was the most updated issue that still had the VB6 Help within it.