[thelist] Calling a sproc from VB

Ken Schaefer ken at adOpenStatic.com
Mon Feb 2 20:30:50 CST 2004

After calling cmd.Execute you need to update the value of your spResultStr
variable to reflect the value that the output parameter currently holds:

spResultStr = cmd.Parameters("@ResultStr").Value

Also, you don't need spResultStr in:
.Parameters.Append cmd.CreateParameter("@ResultStr", adVarChar,
adParamOutput, 5000, spResultStr)

There, you are setting the value of the output parameter to the current
value of spResultStr (which is ""). However, when you execute the command,
the parameter changes value, and you need to get the current value.


----- Original Message ----- 
From: "Tab Alleman" <Tab.Alleman at MetroGuide.com>
To: <thelist at lists.evolt.org>
Sent: Tuesday, February 03, 2004 5:05 AM
Subject: [thelist] Calling a sproc from VB

I'm calling a SQL2k Procedure from a VB app, and I'm not getting the
expected change in my Output parameter.  Here are some relevant code

The VB app:
Set x = CreateObject("ADODB.Connection")

Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = x
spResultStr = ""
With cmd
    .Parameters.Append cmd.CreateParameter("@ResultStr", adVarChar,
adParamOutput, 5000, spResultStr)
    .CommandText = "tsp_CURT_ImportTextFiles_dev"
    .CommandType = adCmdStoredProc
End With
iSprocReturn = cmd.Execute

And in the sproc:
CREATE PROCEDURE dbo.tsp_CURT_ImportTextFiles_dev
@ResultStr varchar(5000) OUTPUT


SELECT @ResultStr = 'Beginning procedure'


But when I run the app, the cmd.execute runs without error, but the
spResultStr variable is empty afterwards.  It should contain the text,
"Beginning procedure".

What am I doing wrong?



More information about the thelist mailing list