Solved

connection is busy with results from another hstmt

Posted on 2001-07-15
3
517 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

809 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