Solved

ADO recordset with StoredProc on SQL Server

Posted on 2000-02-23
10
313 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 4

Expert Comment

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

Accepted Solution

by:
theBastard earned 100 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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 …
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…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now