Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ADO recordset with StoredProc on SQL Server

Posted on 2000-02-23
10
Medium Priority
?
347 Views
Last Modified: 2010-05-02
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

0
Comment
Question by:sruel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 2552288
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
 

Author Comment

by:sruel
ID: 2552652
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
 

Expert Comment

by:lmcneal
ID: 2552731
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 44

Expert Comment

by:bruintje
ID: 2552746
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
 
LVL 1

Expert Comment

by:SergioL
ID: 2552849
HI,
Do you want to read the @SiteName value?
0
 
LVL 4

Expert Comment

by:TigerZhao
ID: 2553601
ADOcmd.Execute
MsgBox ADOcmd("SiteName")
0
 

Accepted Solution

by:
theBastard earned 300 total points
ID: 2554217
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
 

Author Comment

by:sruel
ID: 2554218
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
 

Author Comment

by:sruel
ID: 2554241
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
 
LVL 1

Expert Comment

by:SergioL
ID: 2556229
I had the answer, but I can't post them.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question