How to pass NULL date parameters to a stored procedure using ADO Sybase.

How to pass NULL date parameters to a stored procedure using ADO Sybase.	Applies To
	OS: VB:	NT, 9x, 2000 5, 6

Update. We intend to create a resource center for all things Sybase. It is a pretty good database, however, for people who have gotten used to MS type of customer support, Sybase support and resources may not seem as thorough. Soon, you'll see here various stuff regarding making Sybase databases work with ADO/OLEDB. For now, read this article.

OLEDB provider and ODBC driver for Sybase seem to have this nasty bug, where it is impossible to pass NULL to a Date parameter in a stored procedure. For instance, consider the following piece of code:


cmd.Parameters.Append cmd.CreateParameter("@mat_date", adDBTimeStamp, adParamInput, 0)
'This causes various errors when .Execute method is run
cmd.Parameters("@mat_date").Value = NULL	
'This statement just basically passes 1, which the database translates to Jan 1, 1899 (???)            
cmd.Parameters("@mat_date").Value = vbNull

The error messages vary from provider to provider (and 
one even locks up the program), but the gist of it is that the provider is 
expecting a data type with a certain Precision and 
NumericScale.  The error is somewhat misleading, since it is just convering up a bug.

So what should you do to fix this problem? Nothing. Yep, absolutely nothing. Not populating the date parameter will actually pass NULL to your stored proc. In reality, what happens is by not setting the date parameter, the value stored in the parameter will be Empty (yes, there is such a value in VB). That, for some strange reason, will force VB/ADO/ODBC to pass NULL to the stored procedure.


Private Sub Populate_SP_DateParameter(dt as Date, prm as ADODB.Parameter)
    If Not IsNull(dt) then
        prm.Value = dt
    End If
End Sub

Or alternatively you can simply set the stored proc parameter to Empty.


cmd.Parameters("@mat_date").Value = Empty