Passing a User-Defined Type as a parameter to a Stored Procedure

[ToeJamNEarl]

Hey guys,

I have created a user define type in VB that is defined as follows:

Type ErrorInfo

    Code As Integer
    Message As String
    Location As String

End Type

Now in a Function I have written I wanna create a variable that uses the defined type as a parameter to the stored procedure as follows:

Public prmError As ErrorInfo

datCmd1.ActiveConnection = DBConnection
datCmd1.CommandType = adCmdStoredProc
datCmd1.CommandText = "Do_It"

Set prmError = datCmd1.CreateParameter("p_ErrorInfo", adUserDefined, adParamInputOutput)

datCmd1.Parameters.Append prmError
prmError.Code = intCode
prmError.Location = strLocation
prmError.Message = strMessage

Any ideas if I am doing something wrong here? I am getting errors.

Much appreciated. Also what is the proper way to pass in arrays to a stored procedure?

Thanks

-Diran

 

[SemperFiDownUnda]

your datatypes send to SQL server have to match SQL datatypes

better to use 3 different params.

bigint adBigInt
binary adBinary
bit adBoolean
char adChar
datetime adDBTimeStamp
decimal adNumeric
float adDouble
image adVarbinary
int adInteger
money adCurrency
nchar adWChar
ntext adWChar
numeric adNumeric
nvarchar adWChar
real adSingle
smalldatetime adTimeStamp
smallint adSmallInt
smallmoney adCurrency
sql_variant adVariant
sysname adWChar
text adChar
timestamp adBinary
tinyint adVarbinary
uniqueidentifier adGUID
varbinary adVarbinary
varchar adChar


Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[chiph]

SemperFiDownUnda is correct. If you define a private datatype in your program, how do you expect SQL Server to know about it, and how to parse it?

You could do something like concatenate your datatype into one large string, then take it apart again in your stored proc, but that is a pretty nasty way to do it. Won't be especially fast, either.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first