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)

Answer 3

I always pass DBNULL.Value as the parameter value when inserting null into a column

友情链接