How to pass NULL or empty strings to stored procedure input parameter with ADO and VB?

Question

I have a stored procedure in Sql Server 2005 with a varchar input parameter defined as:

@Value varchar(24) = NULL

in my VB6 application I try to set the parameter with an ADO function:

Set prmParamVal = cmdChkParam.CreateParameter(, adVarChar, adParamInput, Len(paramValue), paramValue)

If the value I try to pass is an empty (zero length) string, then I get the following error:

ADODB.Connection error '800a0e7c' Parameter object is improperly defined. Inconsistent or incomplete information was provided.

I tried to pass a NULL value instead of the empty string, but this lead to different errors.

How can I pass empty strings or NULL values to the stored procedure?

I already read a lot of articles and searched forums (even found my question several times) but without the right answer.

The workaround so far for the empty strings is to set the length = 1 or set the string = " " (a blank space). But thats not really nice and I would favour to send NULL. I also experimented with setting paramValue to vbNull, Null, vbNullString or to set prmParamVal.value = Empty without any luck!

Answer 1

A quick test here shows that's NULL ought to do the job. Sample code I used to test (onto a simple form with one button and one textbox):

Private Sub Command1_Click()
    Dim dbConn As ADODB.Connection
    Dim dbComm As ADODB.Command
    Dim dbRS As ADODB.Recordset

    Set dbConn = New ADODB.Connection
    With dbConn
        .ConnectionString = "...REPLACE THIS ACCORDINGLY..."
        .ConnectionTimeout = 10
        .Open
    End With
    Set dbComm = New ADODB.Command
    With dbComm
        .ActiveConnection = dbConn
        .CommandType = adCmdStoredProc
        .CommandText = "usp_Bob"
        .Parameters.Append .CreateParameter("b", adVarChar, adParamInput, 10, Null)
        Set dbRS = .Execute
    End With
    Text1.Text = dbRS.Fields.Item(0).Value

    dbRS.Close
    dbConn.Close
End Sub

And it called this stored proc:

ALTER PROCEDURE usp_Bob
 @b VARCHAR(10)
AS
 IF @b IS NULL
  SELECT 'NULL' AS '1'
 ELSE
  IF @b = ''
   SELECT 'EMPTY' AS '1'
  ELSE
   SELECT 'NOT NULL AND NOT EMPTY' AS '1'

usp_Bob returned 'NULL' for using the VB value Null (as per the sample above), and 'NOT NULL' for vbNull. If Null doesn't work for you, then I can't comment on what might be wrong...!

Similarly, empty strings should be passed just as that -- an empty string, i.e. str = "" -- which makes usp_Bob return 'EMPTY'. Anything else has it return 'NOT NULL AND NOT EMPTY' (as expected).

If you can't get NULL passed through, then another option is to cast an empty string to NULL in the sproc -- i.e.,

IF @param = ''
    SET @param = NULL

Note that the length you pass through shouldn't matter too much. It's a reflection of the maximum length of the parameter as defined in SQL Server rather than the length of the data you're passing through.

Answer 2

if you want to pass a blank string to a parameter us adBSTR instead of adVarChar. It works for strings longer than zero chars too

eg (where oCmd is the command object in question):

oCmd.Parameters.Append oCmd.CreateParameter("@Parm", adBSTR, adParamInput, len(sParm), sParm)