• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

assigning results of a user stored procedure (select) to a recordset

I am defining my SQL statments as user stored procedures (usp's)  in SQLServer. I normally pass parameters, eg @table_id, to and from the usp. Now, however, I want to store the results of my query in a recordset, using the following code:
var cn = new ActiveXObject("ADODB.Connection");
var rs1 = new ActiveXObject("ADODB.Recordset");
var cm = new ActiveXObject("ADODB.Command");
cm.CommandText = "dbo.usp_selectstatus";
cm.CommandType = 4;
cm.Prepared = true;
cm.Parameters.Refresh();
rs1.Open(cm.Execute(), cn);

I know the problem is with the final line of code shown here, and tried to write it like
rs1 = cm.Execute();,
but the error I get is :
The application requested an operation on an object with a reference to a closed or invalid Connection object.

Can you tell me what the correct code would be to establish the connection, etc.?

thanks
Clo
0
clodaghm
Asked:
clodaghm
1 Solution
 
MarineCommented:
dim Cn as adodb.connection
dim rs as adodb.recordset
dim cmd as adodb.command

cn.connectionstring = "DRIVER=SQL SERVER";Server="specifyserver";UID="UID";PWD="password";Database=database"
cn.open

cm.activeconnection = cn
cm.CommandText="dbo.usp_selectstatus";
cm.CommandType = 4;
cm.Prepared = true;
rs.open cm.execute(),cn
0
 
clodaghmAuthor Commented:
rs.open cm.execute(),cn
Would you know the syntax for this in Jscript as opposed to VBscript?
0
 
robbertCommented:
clodaghm,

1. You did not open the connection (cn.Open "DSN=myDSN;UID=myUID;PWD=myPWD")
2. What does (cm.Execute(), cn) return, as a string?
3. rs.open cm.execute(),cn
is
rs.Open(cm.Execute(),cn)
in JavaScript.
0
 
Yury_DelendikCommented:
Try

var cn = new ActiveXObject("ADODB.Connection");
var rs1 = new ActiveXObject("ADODB.Recordset");
var cm = new ActiveXObject("ADODB.Command");
cm.CommandText = "dbo.usp_selectstatus";
cm.CommandType = 4;
cm.Prepared = true;
cm.Parameters.Refresh();

rs1.Open(cm, cn)

or

cm.ActiveConnection = cn;
rs1 = cm.Execute();

Don't forget about cn.Open(".....");
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now