Solved

connection is busy with results from another hstmt

Posted on 2001-07-15
3
504 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
Comment Utility
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
Comment Utility
please post the exact error message
0
 

Author Comment

by:tomhoran
Comment Utility
this did it .. thanks

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

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

762 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

6 Experts available now in Live!

Get 1:1 Help Now