Member Avatar for sameera

hi,

i m novice to asp.i m using one stored procedure in sql server2000 which is using cursor.this is doing the following things

1) select the peticlur records from one table
2) insert the record in another table relavant to retireved ID
3) select the second table values

i m binding this code into asp page.

but it always gives a recordset error"ADODB.Recordset (0x800A0E78):Operation is not allowed when the object is closed." becos it is contianing multiple tables(select statements).i have already chekde that recordset is just not closed and it is assiging the value.

my code is as follows:

set cmd = Server.CreateObject("ADODB.Command") cmd.ActiveConnection = MM_conn_STRING cmd.CommandType=adCmdStoredProc cmd.CommandText = "usp_IPA_Get_Top_Five_Picks" cmd.Parameters.Append cmd.CreateParameter("cat_id",adInteger,adParamInput,4,argcat_id) cmd.Parameters.Append cmd.CreateParameter("cat_type",adChar,adParamInput,1,argcat_type) cmd.Parameters.Append cmd.CreateParameter("event_id",adChar,adParamInput,2,argevent_id)
set rs = cmd.Execute

Same things i can do if i do upto first two steps by executing procedure and then directly select the table from view as another recordset.

tell me as i have multiple data tables then how to get perticular table from recordset(for example in asp.net i can take dataset.datatables(0) similarly how to do here?)

thnx.

  • 2 Contributors
  • 1 Reply
  • 250 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by campkev

Dani AI

Generated

— the error means the ADO Recordset you're trying to use is not open when you attempt to read from it. When a stored procedure does inserts/updates and then SELECTs, SQL Server will send intermediate "n rows affected" and/or informational results that ADO can treat as separate (often empty) recordsets. Two reliable fixes: suppress those messages in the stored procedure and explicitly walk the resultsets from ADO until you reach the SELECT you want.

As pointed out, use NextRecordset to advance. A common pattern:

Set rs = cmd.Execute

Do While Not rs Is Nothing
  If rs.State = 1 And Not (rs.BOF And rs.EOF) Then
    Exit Do   ' rs now points to the first usable resultset
  End If
  Set rs = rs.NextRecordset()
Loop

If Not rs Is Nothing Then
  ' process rs safely here
End If

Also add SET NOCOUNT ON; at the top of the stored procedure so "rows affected" messages are not sent. Example (conceptual):

ALTER PROCEDURE usp_IPA_Get_Top_Five_Picks
AS
BEGIN
  SET NOCOUNT ON;
  -- do cursor/insert work
  -- final SELECT that should be returned
END

Troubleshooting tips: temporarily put a small distinct SELECT marker in the SP (SELECT 'Result2' AS Marker) so you can see which resultset you land on while debugging. Check rs.State (adStateOpen = 1) and guard against rs Is Nothing before reading fields. If you only need the final SELECT, prefer returning just that resultset (or move inserts into a separate proc) — simpler and less error-prone.

Member Avatar for campkev

rs.NextRecordset look here

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.