ADO recordset with StoredProc on SQL Server

Hi,

I have a problem. I want to execute a stored procedure on a SQL Server from VB6 and put the result in a ADO recordset.

Here is my stored proc:

Create Procedure sp_test @SiteId  int,
                         @SiteName  char(30) OUTPUT
AS
select @SiteName = Name from tblSites where Sitesid = @SiteId

Here is my VB6 code:

Dim ADOcmd As New ADODB.Command
Dim ADOrs As New ADODB.Recordset
With ADOcmd
  .ActiveConnection = gApp.DB
  .CommandText = "sp_test"
  .CommandType = adCmdStoredProc
  .Parameters.Append .CreateParameter("SiteId", adInteger, adParamInput)
  .Parameters.Append .CreateParameter("SiteName", adChar, adParamOutput, 30)
End With
       
ADOcmd("SiteId") = 2
Set ADOrs = ADOcmd.Execute
MsgBox ADOrs.Recordcount
...

When i want to display the number of records in the recordset "ADOrs", i have this error message:
"The operation requested by the application is not allowed if the object is closed."

Can anyone help me please ?

Thanks to all

Sylvain

sruelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
bruintjeCommented:
Hi Sylvain,

does the

Set ADOrs = ADOcmd.Execute

return anything in the watch window for the ADOrs, while debugging?, could be an empty recordset

:O)Bruintje

0
 
sruelAuthor Commented:
No, the recordset should have one record and I can't get this record. The only thing i get is the error msg "The operation requested by the application is not allowed if the object is closed.". It seems that the recordset is not opened but it doesn't have to be !
0
 
lmcnealCommented:
I admit I'm fairly new to ADO and stored procedures, but I thought you had to do a .Parameters.Refresh.

I could be completely wrong though.

Lyle
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bruintjeCommented:
well it has to be open, at least the resultset, had exactly the same problem a week ago, with setting a param in VB from the resultset of a stored procedure in MSQL, the param could only be set when the rs was open, after setting the rs could be closed....
0
 
SergioLCommented:
HI,
Do you want to read the @SiteName value?
0
 
TigerZhaoCommented:
ADOcmd.Execute
MsgBox ADOcmd("SiteName")
0
 
theBastardCommented:
You are not returning a recordset in the stored procedure. (Run in Query analyzer)

So the recordset returned is set to noting and will generate an error (because it is not open)

the output parameter value is being returned and you will get that by using
ADOcmd("@auname") like TigerZhao specified.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
sruelAuthor Commented:
I want to read the @SiteName value and i want it on a recordset.

bruintje: you said that the param could only be set when the rs is opened. It's exactly what i want to know !

Thanks
0
 
sruelAuthor Commented:
I want to read the @SiteName value and i want it on a recordset.

bruintje: you said that the param could only be set when the rs is opened. It's exactly what i want to know !

Thanks
0
 
SergioLCommented:
I had the answer, but I can't post them.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.