PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
Run-time error '3709' The connection cannot be.....-VBForums
Results 1 to 11 of 11

Thread: Run-time error '3709' The connection cannot be.....

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Nov 2010
    Posts
    71

    Question Run-time error '3709' The connection cannot be.....

    Hey people! Watsup! i need your help am getting a Run-time error '3709': (The connection cannot be used to perform this operation. Its either closed or invalid in this context) when this code tries to execute. Can anyone see the problem i have tried re writing the sql to no avail. please help

    Code:
    Private Sub lvButtons_H1_Click()
    Dim MyCon As New ADODB.Connection
    Dim MyRs As New ADODB.Recordset
    
    
    3709 Error Code:
    1. MyRs.Open "SELECT plans.Number, plans.plan, patients.fname, patients.lname , plans.phone number, plans.card number, plans.vcode, plans.expiry FROM plans LEFT JOIN patients ON plans.payerid = patients.number WHERE plans.plan='" & Me.Combo1.Text & "'"
    With DataReport9.Sections("Section1").Controls 'section1 mean that section you create in datareport .Item("rptpayid").DataField = MyRs("payerid").Name .Item("rptplan").DataField = MyRs("plan").Name .Item("rptname").DataField = MyRs("fname").Name .Item("rptlname").DataField = MyRs("lname").Name .Item("rptphone").DataField = MyRs("phone number").Name .Item("rptcard").DataField = MyRs("card number").Name .Item("rptvcode").DataField = MyRs("vcode").Name .Item("rptexp").DataField = MyRs("expiry").Name End With

  2. #2
    PowerPoster RhinoBull's Avatar
    Join Date
    Mar 2004
    Location
    New Amsterdam
    Posts
    24,135

    Re: Run-time error '3709' The connection cannot be.....

    You need to set connection/recordset object and also open connection before opening recordset.
    Try this instead but KIM that sample code below was not tested:
    Code:
    Private Sub Command1_Click()
    Dim strSQL As String
    Dim MyCon As ADODB.Connection
    Dim MyRs As ADODB.Recordset
    
        Set MyCon = New ADOB.Connection
        MyCon.Open "connection string goes here"
    
        Set MyRs = New ADODB.Recordset
        
        strSQL = "SELECT" & vbNewLine
        strSQL = strSQL & "    plans.Number," & vbNewLine    '<<< this field name may not be allowed as it could be reserved word
        strSQL = strSQL & "    plans.plan," & vbNewLine
        strSQL = strSQL & "    patients.fname," & vbNewLine
        strSQL = strSQL & "    patients.lname," & vbNewLine
        strSQL = strSQL & "    plans.[phone number]," & vbNewLine   '<<< bad naming convention - I would rename it to "phone_number"
        strSQL = strSQL & "    plans.[card number]," & vbNewLine    '<<< bad naming convention - I would rename it to "card_number"
        strSQL = strSQL & "    plans.vcode," & vbNewLine
        strSQL = strSQL & "    plans.expiry" & vbNewLine
        strSQL = strSQL & "FROM plans LEFT JOIN patients ON plans.payerid = patients.number" & vbNewLine
        strSQL = strSQL & "WHERE plans.PLAN = '" & Me.Combo1.Text & "'"
        
        MyRs.Open strSQL, MyCon
        
        If Not MyRs.EOF Then
            With DataReport9.Sections("Section1").Controls 'section1 mean that section you create in datareport
                .Item("rptpayid").DataField = MyRs("payerid").Name
                .Item("rptplan").DataField = MyRs("plan").Name
                .Item("rptname").DataField = MyRs("fname").Name
                .Item("rptlname").DataField = MyRs("lname").Name
                .Item("rptphone").DataField = MyRs("phone number").Name
                .Item("rptcard").DataField = MyRs("card number").Name
                .Item("rptvcode").DataField = MyRs("vcode").Name
                .Item("rptexp").DataField = MyRs("expiry").Name
            End With
        End If
        
        Set MyRs = Nothing
        Set MyCon = Nothing
    
    End Sub

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,001

    Re: Run-time error '3709' The connection cannot be.....

    Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)


    There is general advice on various common errors (including this one) in the article What does this error mean, and how do I fix it? from the top of the "General" section of our Database Development FAQs/Tutorials (at the top of this forum)


    In this case there are two reasons for the problem, and neither of them are anything to do with SQL (which may have its own problems).

    The first is that you have not specified the connection for the recordset to use, which needs to be done because you can have multiple connection objects, and you can't expect VB to know which one you intended. To do that you should either specify it as a parameter on the .Open line, or set it separately before that line using the .ActiveConnection property.

    The second reason is that the connection needs to be Open in order to be used, and you have not set it up at all.


    For examples of how to solve both of those (and more explanation of how to use ADO objects), I recommend looking at the ADO Tutorial from the "Classic VB:ADO" section our DB FAQs.


    edit: how long did I take? I should have refreshed the page after being briefly away!

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Nov 2010
    Posts
    71

    Re: Run-time error '3709' The connection cannot be.....

    Thanx Rhino Bull, I tried the changes you suggested but am getting an ODBC error. Data Source not found and no default driver selected.

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Nov 2010
    Posts
    71

    Re: Run-time error '3709' The connection cannot be.....

    RhinoBull i resolved the error but it brings me back to the same 3709 run time error.

    si_the_geek, Thanx for the advice. I had another sql query like this on another form and that works well. i don't know where i messed up.

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,001

    Re: Run-time error '3709' The connection cannot be.....

    Thanx Rhino Bull, I tried the changes you suggested but am getting an ODBC error. Data Source not found and no default driver selected.
    I guess you hadn't put in a valid connection string at that stage.
    Quote Originally Posted by Patoh View Post
    RhinoBull i resolved the error but it brings me back to the same 3709 run time error.
    That should not be happening, because the code follows all of the correct steps - so it should not be possible for that error to occur in the same place (only potentially in the DataReport section, or a different error prior to that), unless you are using something different to what RhinoBull posted.

    si_the_geek, Thanx for the advice. I had another sql query like this on another form and that works well. i don't know where i messed up.
    This error is always due to one of the issues I mentioned (connection not specified, or connection not open), albeit with variations in the specific details.

    I would guess that in the other form you have set up and opened the connection elsewhere, and specified it as the second parameter to recordset.Open

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Nov 2010
    Posts
    71

    Re: Run-time error '3709' The connection cannot be.....

    haha!! Yea the connection string string wasn't there. That was Dumb! Anways let me post both codes of the two diffrent forms
    the working
    Code:
    If Combo1.Text = "" Then
    MsgBox "Please Select an Insurance Company", vbInformation
    Else
    If opt1.Value = True Then
    Dim MyCon As New ADODB.Connection
    Dim MyRs As New ADODB.Recordset
    
    MyRs.Open "SELECT invoice.Number, invoice.inno, invoice.fileid, invoice.lname,invoice.total, invoice.dob, payment.cnumber, payment.cname, invoice.method FROM payment LEFT JOIN invoice ON payment.invoice = invoice.Number WHERE payment.cname='" & Me.Combo1.Text & "'", conn, 1, 3
    With DataReport25.Sections("Section1").Controls 'section1 mean that section you create in datareport
        .Item("rptdate").DataField = MyRs("dob").Name
        .Item("rptinno").DataField = MyRs("Number").Name
        .Item("rptname").DataField = MyRs("lname").Name
        .Item("rptin").DataField = MyRs("inno").Name
        .Item("rptinsure").DataField = MyRs("cname").Name
        .Item("rptcardno").DataField = MyRs("cnumber").Name
        .Item("rptamount").DataField = MyRs("total").Name
    End With
    '////////////////////////////////////////////////////////////////////////////////
    
    '////////////////////////////////////////////////////////////////////////////////
    Set DataReport25.DataSource = MyRs
    and the Not working
    Which is the one Above!!

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,001

    Re: Run-time error '3709' The connection cannot be.....

    The "working" code declares the MyCon variable, but never uses it (so you should remove the declaration).

    Also in the "working" code the second parameter to MyRs.Open is conn, which is presumably another variable declared as ADODB.Connection, and opened elsewhere.

  9. #9
    PowerPoster RhinoBull's Avatar
    Join Date
    Mar 2004
    Location
    New Amsterdam
    Posts
    24,135

    Re: Run-time error '3709' The connection cannot be.....

    @Patoh:

    All I can say is that you need to pay attention to details - so far you failed doing that...
    Try looking at the sample code I posted more carefully as well as reading ADO Tutorials suggested by SI - that will help you to identify your own mistakes.
    Unless you do that you will never learn.

    Regards and good luck.

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Nov 2010
    Posts
    71

    Re: Run-time error '3709' The connection cannot be.....

    Thank you guys Il most definately do that Rhinobull, si_the_geek, conn is public and was declared in a module elsewhere. Am Re readint the ADO Tutorials. What i was curios about was why that error appeared was it because of my sql query ? because the select query works or is it in the coding??

  11. #11
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,001

    Re: Run-time error '3709' The connection cannot be.....

    As I mentioned earlier, the SQL statement is totally irrelevant to this error:
    Quote Originally Posted by post #3
    In this case there are two reasons for the problem, and neither of them are anything to do with SQL (which may have its own problems).
    Quote Originally Posted by post #6
    This error is always due to one of the issues I mentioned (connection not specified, or connection not open), albeit with variations in the specific details.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width