Passing NULL Parameters and/or empty strings

Johann Anhofer

unread,

Apr 3, 2003, 9:06:32 AM4/3/03

to

How do I pass empty (NULL) and empty String Parameters to a stored procedure
with ADO?
If I use enclosed code, I get the "Parameter object is improperly defined.
Inconsistent or incomplete information was provided" error message.
The error occurs at the line with the Append statement.
How can I solve this ?

e.g.

Dim cmd As New ADODB.Command
Dim par As ADODB.Parameter

On Error GoTo fail

Set par = cmd.CreateParameter("", adVarChar, adParamInput, 0, "")
cmd.Parameters.Append par
Set par = cmd.CreateParameter("", adEmpty, adParamInput)
cmd.Parameters.Append par

cmd.CommandText = "EXEC MyProcedure ?,?"
cmd.Execute
GoTo noerror
fail:
MsgBox Err.Description, vbOK, "Error"
Err.Clear
noerror:

SQL Procedure:

create procedure MyProcedure
@Data as int,
@Data2 as varchar(10)
as

/* Do some processing */

Val Mazur

unread,

Apr 3, 2003, 9:37:13 AM4/3/03

to

Johan,

NULL is not an empty string "" is an empty string and it is a value, but
NULL is not. To pass NULL using ADO parameter you need to assign it to the
parameter using next kind of code in VB

Set par = cmd.CreateParameter("", adVarChar, adParamInput, 0, NULL)
cmd.Parameters.Append par

But what is adEmpty parameter for? I do not think it is supported by SQL
Server stored procedures

--
Val Mazur
Microsoft MVP

"Johann Anhofer" <anh...@andromeda-software.at> wrote in message
news:eFNyIoe#CHA....@TK2MSFTNGP12.phx.gbl...

Johann Anhofer

unread,

Apr 3, 2003, 9:54:34 AM4/3/03

to

Hi !

> NULL is not an empty string "" is an empty string and it is a value, but
> NULL is not. To pass NULL using ADO parameter you need to assign it to the
> parameter using next kind of code in VB
>
> Set par = cmd.CreateParameter("", adVarChar, adParamInput, 0, NULL)
> cmd.Parameters.Append par
>

Fine, but this is still not working, the same error occurs!
Btw. I know the difference between NULL and "", I wrote in my Posting about
to different problems.
1. How to pass an empty string as parameter ("")
2. How to pass a parameter having a NULL-Value

> But what is adEmpty parameter for? I do not think it is supported by SQL
> Server stored procedures

adEmpty is defined in the ADO DataTypeEnum, it's not part of the SQL-Server
Datatypes,
but I hoped, that it will replaced with NULL by ADO.

Val Mazur

unread,

Apr 3, 2003, 10:33:15 AM4/3/03

to

Hi,

No, you should specify particular datatype, like adVarChar. Actually you
need to specify size of that parameter as well (I forgot to mention it in
previous posting). Next code should work fine, assuming that your parameter
was declared as VARCHAR(10) in your SP

Set par = cmd.CreateParameter("MyParam1", adVarChar, adParamInput, 10, Null)
cmd.Parameters.Append par

--
Val Mazur
Microsoft MVP

"Johann Anhofer" <anh...@andromeda-software.at> wrote in message

news:OOXS#Cf#CHA...@TK2MSFTNGP12.phx.gbl...

Francesco Adinolfi

unread,

Apr 4, 2003, 7:50:45 PM4/4/03

to

I do this from C++ by passing an empty variant( adEmpty in your case ?).
But note that you also have to definine the parameter in the SP as having a
default value of NULL ( =NULL).
I use this myself particularly for foreign keys which I want to remain null.

Francesco

"Johann Anhofer" <anh...@andromeda-software.at> a écrit dans le message de
news: eFNyIoe#CHA....@TK2MSFTNGP12.phx.gbl...

Johann Anhofer

unread,

Apr 7, 2003, 6:57:17 AM4/7/03

to

Hi !

Yes ! Now it works. The point is the length of the parameter !
I must not be 0. The String may be NULL or "", but the length have to be
greater 0.

thanks, Johann