Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ADO recordset with StoredProc on SQL Server

Posted on 2000-02-23
10
Medium Priority
?
352 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
  • 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

971 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