CreateParameter - Stored Procs on SQL7

[Toot]

Hi all. I'm trying to append a varchar parameter like this:

Dim cm As New ADODB.Command

With cm
.ActiveConnection = cn_INOUT
.CommandType = adCmdStoredProc
.CommandText = "AddAttribute"
.Parameters.Append .CreateParameter("FormatID", adInteger, adParamInput, 4, iFormatID_IN)
.Parameters.Append .CreateParameter("AttributeName", adVarChar, adParamInput, Len(sAttributeName_IN), sAttributeName_IN)
.Parameters.Append .CreateParameter("Data", adVarChar, adParamInput, Len(sData_IN), sData_IN)
.Execute
End With

Set cm = Nothing


But I'm running into a bit of a problem when sData_IN is a zero-length string - VB errors with "The application has improperly defined a Parameter object."

It works when sData_IN is anything over zero-length so I assume I've somehow got to tell it that ... but how? My field definition allows nulls.

TIA if u can help!

Toot

[Ianpbaker]

Hi Toot
as you said if it allows nulls give this a go, it might work


Dim cm As New ADODB.Command

With cm
.ActiveConnection = cn_INOUT
.CommandType = adCmdStoredProc
.CommandText = "AddAttribute"
.Parameters.Append .CreateParameter("FormatID", adInteger, adParamInput, 4, iFormatID_IN)
.Parameters.Append .CreateParameter("AttributeName", adVarChar, adParamInput, Len(sAttributeName_IN), sAttributeName_IN)
.Parameters.Append .CreateParameter("Data", adVarChar, adParamInput, Len(sData_IN), iif(Len(sData_IN) < 1,Null,sData_IN))
.Execute
End With

Set cm = Nothing


[Edited by Ianpbaker on 07-05-2000 at 10:53 AM]

[Toot]

Right what I've done, because I'm attempting to keep the code as simple as possible, is say that the length of the argument is Len(sData_IN) + 1 - MSDN says that the size argument of the CreateParameter function is
A Long value that specifies the maximum length for the parameter value in characters or bytes
Can anyone see if this is going to cause me a problem???

Cheers,
Toot