Solved

connection is busy with results from another hstmt

Posted on 2001-07-15
3
513 Views
Last Modified: 2012-06-22
i'm getting this error. the vb program use to work before.

Dim FSAElecRecSet As ADODB.Recordset
Set FSAElecRecSet = New ADODB.Recordset
Dim FSAClaimRecSet As ADODB.Recordset
Set FSAClaimRecSet = New ADODB.Recordset

SelStmnt = "select curr_year_last_proc_date, prev_year_last_proc_date, min_reimburse_amt, " & _
        "fe.cust_no, fe.company_no, fe.soc_sec_no, fe.plan_year_code, customer_type, " & _
        "fe.fsa_type, type, eff_date, term_date, customer_contact, company_contact, " & _
        "annual_contrib, lvl1_download_reimbur_cnt, lvl1_download_reimbur_fee, lvl2_download_reimbur_fee, " & _
        "reimburse_deduc_code from fsa_elections as fe " & _
    "join proc3_cust_and_comp_nos as toproc on fe.cust_no = toproc.cust_no " & _
        "and fe.company_no = toproc.company_no " & _
    "join fsa_type_tbl as ftt on fe.cust_no = ftt.cust_no " & _
        "and fe.company_no = toproc.company_no " & _
        "and fe.plan_year_code = ftt.plan_year_code " & _
        "and fe.fsa_type = ftt.code " & _
    "where ( (fe.plan_year_code = curr_year_plan_code) " & _
        "or (fe.plan_year_code = prev_year_plan_code) ) and reimburse_deduc_code <> '' " & _
    "order by  fe.cust_no, fe.company_no, soc_sec_no, fe.plan_year_code, fe.fsa_type "

FSAElecRecSet.Open SelStmnt, HROnline_DataBase, adOpenForwardOnly, adLockReadOnly

If FSAElecRecSet.EOF Then
    Exit Sub
End If
FSAElecRecSet.MoveFirst

SelStmnt = "select claim.cust_no, " & _
        "claim.company_no, soc_sec_no, plan_year_code, fsa_type, claim_no, " & _
        "claim_amt, prev_year_plan_code " & _
        "from fsa_claims as claim " & _
    "join proc3_cust_and_comp_nos as toproc on claim.cust_no = toproc.cust_no " & _
        "and claim.company_no = toproc.company_no " & _
    "where claim.status_code = 'CA' " & _
        "and ( (plan_year_code = curr_year_plan_code and status_date " & _
            "<= curr_year_grace_end_date and curr_year_last_proc_date >= '" & _
            ProcRunDate & "') " & _
        "or (plan_year_code = prev_year_plan_code and status_date <= " & _
            "prev_year_grace_end_date and prev_year_last_proc_date >= '" & _
            ProcRunDate & "') ) " & _
    "order by  claim.cust_no, claim.company_no, soc_sec_no, plan_year_code, " & _
        "fsa_type, claim_date "

*************************************
i get the error on the open? ideas?

FSAClaimRecSet.Open SelStmnt, HROnline_DataBase, adOpenForwardOnly, adLockReadOnly
0
Comment
Question by:tomhoran
3 Comments
 
LVL 18

Accepted Solution

by:
nigelrivett earned 100 total points
ID: 6284290
It is because you are trying to open a new recordset on a connection which still has data to retrieve.
Easiest way round it is to deal with client side disconnected recordsets.

I work with command objects (in a db interface object) but if you make this a client side cursor and set the recordset current connect to null that might do it.

0
 
LVL 1

Expert Comment

by:barx
ID: 6284352
please post the exact error message
0
 

Author Comment

by:tomhoran
ID: 6286693
this did it .. thanks

FSAAcctRecSet.CursorLocation = adUseClient
FSAElecRecSet.CursorLocation = adUseClient
FSAClaimRecSet.CursorLocation = adUseClient
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Webservices in T-SQL 3 31
SQL Server 2012 r2 - Sum totals 2 25
what are the unique tables in SQL master database 5 61
Increment column based of a FK 8 22
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

777 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