Null/Nothing value to text/adLongVarChar??

[gan.gary]

I have a stored procedure and my VB6 apps need to pass the parameter to it.
I need to pass Null value to a text(in T-sql), if I use:

Set prm5 = cmd.CreateParameter("@xmlStation", adLongVarChar, adParamInput, 0, null)

or

Set prm5 = cmd.CreateParameter("@xmlStation", adLongVarChar, adParamInput,  , null)

I get:

Parameter object is improperly defined. Inconsistent or incomplete information was provided.

Anyone knows?

[si_the_geek]

The size parameter is basically for the length of the field (technically the maximum length of the parameter), not the length of the value you are passing.

A size of 0 is not apt for a text/char based parameter, even if the value will only ever be Null.

[gan.gary]

The size parameter is basically for the length of the field (technically the maximum length of the parameter), not the length of the value you are passing.

A size of 0 is not apt for a text/char based parameter, even if the value will only ever be Null.

Sorry, I do not get you. SO what's the value I should pass for the 4th parameter for size, and 5th parameter for value, since even this one also fail:

Set prm5 = cmd.CreateParameter("@xmlStation", adLongVarChar, adParamInput,  , null)

[si_the_geek]

The size parameter should be the size of the field (or whatever it is) that this parameter refers to - so if the field is VarChar(400) it should be 400

The value parameter should Null (or perhaps vbNull).

[gan.gary]

but datatype "text" is equvalent to varchar(max) in SQL2005, means runtime allocated size, or size depends on memory. It make no sense to put a maximum values, furthermore null not work(let alone vbNull) as i mentioned on previous post

[Ellis Dee]

The value parameter should Null (or perhaps vbNull).

vbNull is the worst idea ever. (ETA: Not your idea; the VB6 developers including it in the language was a bad idea.) It is not a Null, and in fact it has nothing to do with Null anymore than vbString has to do with strings. Both vbNull and vbString are longs; they are constants used to compare against the return value of VarType().

Using vbNull for anything other than comparing against VarType() is like using vbString to initialize an empty string. No joy.

Just for completeness:

vbEmpty = 0 (Uninitialized)
vbNull = 1
vbInteger = 2
vbLong = 3
vbSingle = 4
vbDouble = 5
vbCurrency = 6
vbDate = 7
vbString = 8
vbObject = 9
vbError = 10
vbBoolean = 11
vbVariant = 12

They would have been fine if they'd left it at that, but then they went ahead and added the constant vbNullString. Endless confusion ensued. (If vbNullString is a null string, vbNull is a Null, right? Doh!)

[gan.gary]

hi Ellis Dee, thanks for your info. So, what's the answer for my question?

[Ellis Dee]

si_the_geek gave you the answer. I was merely responding to his parenthetical aside.

[gan.gary]

really? this?

The size parameter should be the size of the field (or whatever it is) that this parameter refers to - so if the field is VarChar(400) it should be 400

but datatype "text" is equvalent to varchar(max) in SQL2005, means runtime allocated size, or size depends on memory. It make no sense to put a maximum values

[Ellis Dee]

In that case, I wish I could help you, but I have no idea.

[si_the_geek]

Well you don't need to specify the exact length of the field, just the maximum you could pass via the parameter. Even if you are passing Null, set the size to something reasonable (eg: 50).

vbNull is the worst idea ever. ...

I knew there was a reason it didn't seem right!

[gan.gary]

Well you don't need to specify the exact length of the field, just the maximum you could pass via the parameter. Even if you are passing Null, set the size to something reasonable (eg: 50).
I knew there was a reason it didn't seem right!

Haha, I passed "0" is not working, but "1" it works. Thanks.