Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ADO recordset with StoredProc on SQL Server

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

580 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