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!
Best Answer
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):
And it called this stored proc:
usp_Bob returned 'NULL' for using the VB value
Null
(as per the sample above), and 'NOT NULL' forvbNull
. IfNull
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.,
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.