Solved

connection is busy with results from another hstmt

Posted on 2001-07-15
3
522 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

729 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