Help returning data back to VB from a stored procedure

[digitallyskilled]

I need help returning data back to VB from a stored procedure. Everytime I run the below code I get a catastrophic error. I have an field that I want to return from the stored procedure. If it was an Int I could get it using the return value but since it needs to be a string I cant use adParamReturnValue

' Open Connection to Database
Dim Conn1 As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim ErrorCode As ADODB.Error
Dim ErrorMessage As String
Dim TransError As String
Dim objRS As ADODB.Recordset

Set Conn1 = New ADODB.Connection
Set cmd = New ADODB.Command
Set objRS = New ADODB.Recordset


ConnAdPronto.ConnectionString = CONNECTION_STRING
Conn1.Open
Conn1.BeginTrans

' Call Stored Procedure
cmd.ActiveConnection = Conn1
cmd.CommandText = "__AcctDataLoadTest"
cmd.CommandType = adCmdStoredProc

cmd.Parameters.Append _
cmd.CreateParameter("OrderId", adInteger, _
adParamInput, 4, 800020)

'cmd.Parameters.Append _
'cmd.CreateParameter("@TransError", adVarChar, _
' adParamOutput, 500)

On Error GoTo SystemErrorHandler
Set objRS = cmd.Execute

 

[fredericofonseca]

1- what is the error you get?
2- what line gives you the error
3- Show us our stored procedure.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[bjd4jc]

I notice that you are using adParamOutput in a commented line. Try changing this to be adParamInputOutput instead of adParamOutput and see if that fixes your problem

 

[rmnastor]

try to change the SP returned value
use CAST or CONVERT

..or..

do some casting on your code


be creative man

 

[BobRodes]

Your @TransError is interesting. What version of SQL Server are you using? If you're using 6.5, I believe that varchar is limited in size to 256.