[thelist] Calling a sproc from VB

[Ken Schaefer]

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.

Cheers
Ken

----- 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
snips:

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

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
AS

SET NOCOUNT OFF

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?

TIA,
Tab

--