Tuesday, March 20, 2012

Can't execute sp in vb.net

Hellou
I'm trying to execute simple sp with 5 input parmeters from vb.net. This sp works great when it is run from SQL Server management studio, but when I want to execute it from vb.net, I allways get this error:
"Syntax error, permission violation, or other nonspecific error"
Here's the code:

Cnn.Open("...")

Comm.ActiveConnection = Cnn

Comm.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc

Comm.CommandText = "spSklVnos-Prenos" 'that's thename of the sp

RetParam.Direction = ADODB.ParameterDirectionEnum.adParamOutput

RetParam.Type = ADODB.DataTypeEnum.adInteger

Comm.Parameters.Append(RetParam)

P1.Direction = ADODB.ParameterDirectionEnum.adParamInput

P1.Type = ADODB.DataTypeEnum.adVarChar

P1.Size = 1

P1.Value = LocParA

Comm.Parameters.Append(P1)

P2.Direction = ADODB.ParameterDirectionEnum.adParamInput

P2.Type = ADODB.DataTypeEnum.adInteger

P2.Value = LocPar1

Comm.Parameters.Append(P2)

P3.Direction = ADODB.ParameterDirectionEnum.adParamInput

P3.Type = ADODB.DataTypeEnum.adInteger

P3.Value = LocPar2

Comm.Parameters.Append(P3)

P4.Direction = ADODB.ParameterDirectionEnum.adParamInput

P4.Type = ADODB.DataTypeEnum.adInteger

P4.Value = LocPar3

Comm.Parameters.Append(P4)

P5.Direction = ADODB.ParameterDirectionEnum.adParamInput

P5.Type = ADODB.DataTypeEnum.adInteger

P5.Value = PrevzemIDx

Comm.Parameters.Append(P5)

Comm.Execute()

Am I missing something?
Thanks for any suggestion.
Dido

Your syntax looks fine to me, but I'm not a VB.NET expert. Make sure your datatypes for the parameters match the stored procedure exactly. You might try asking this question on the VB forums, since it doesn't seem to be related to SSIS.

No comments:

Post a Comment