Results 1 to 2 of 2

Thread: datagrid error

  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Unanswered: datagrid error

    hi
    getting an error with this code

    Code:
    Dim rs As New ADODB.Recordset
    Dim MyConn As ADODB.Connection
    'Set oCnn = New ADODB.Connection
    
    Set MyConn = New ADODB.Connection
    
    MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\lida946\Desktop\users.mdb"
    MyConn.Open
    
    Set datagrid1 = New ADODB.Recordset
    
    datagrid1.Open ("SELECT * FROM tblUsers ")
    Set datagrid.DataSource = datagrid1
    error is run-time error 3709

    the connection cannot be used to peform this operation. its either closed or invalid in thsi context

    anyone able to help thanks

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    First, add a line at the very beginning of the module

    Code:
    OPTION EXPLICIT
    This will stop you from declaring a new variable implicitly. Which is always a good idea.
    In your case, you've instantiated datagrid1 as a recordset, NOT as a datagrid object!

    Next, you need to open the recordset.

    rs.open "SELECT * FROM tblUsers", MyConn

    And, you should explicitly reference the form name in association with objects.

    i.e.
    Code:
    form1.datagrid1 
    or
    me.datagrid1
    Finally, change the statement

    Set datagrid1 = New ADODB.Recordset

    to
    Set Me.datagrid1.Datasource = rs


    Now, your datagrid is bound to the recordset, which has been opened, using the conenction MyConn.

    One thing to keep in mind though, is the scope of these objects.

    If you place everything inside the click event of a button, for instance, the objects will be declared within the event, referenced, opened, and displayed....

    Then, the event ends, goes out of scope, and the best result you can achieve is that the data immediately goes out of scope and is no longer visible!

    (However, ADO objects in VB don't automatically release when they go out of scope unless they were implicitly instantiated (Dim Obj as New Object.) They remain in memory, using memory and connection resources. So, you'll have a memory leak.

    My recommendation: Change
    Code:
    Dim rs As New ADODB.Recordset
    Dim MyConn As ADODB.Connection
    'Set oCnn = New ADODB.Connection
    
    Set MyConn = New ADODB.Connection
    
    MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\lida946\Desktop\users.mdb"
    MyConn.Open
    
    Set datagrid1 = New ADODB.Recordset
    
    datagrid1.Open ("SELECT * FROM tblUsers ")
    Set datagrid.DataSource = datagrid1
    to
    Code:
    Option Explicit
    
    ' I assume that you want to SCOPE these objects at the form level, 
    ' since you'll want to probably want to actually view the data on the grid...
    ' ...
    ' Since you're not looking for high performance with an access database... 
    ' don't worry about possible minor performance hits when using implicit instantiation.  
    Dim rs As New ADODB.Recordset   
    
    ' Ditto with the connection.
    Dim MyConn As New ADODB.Connection
    ' ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    ' THE ABOVE IS FORM-LEVEL CODE.  IT MUST NOT BE LOCATED WITHIN ANY SUB OR FUNCTION.
    ' THIS DEFINES THE SCOPE OF THESE OBJECTS AS FORM LEVEL OBJECTS.  THIS MEANS THAT THEY
    ' MAY BE ACCESSED BY ANY CODE WITHIN THE FORM, BUT NOT BY CODE EXTERNAL TO THE FORM.
    '
    
    Private Sub Form1_Load
      ' connect and retrieve data in the load event.
      MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\lida946\Desktop\users.mdb"
      MyConn.Open
    
      ' Set datagrid1 = New ADODB.Recordset  ' WRONG.  Datagrid is a form  object.  It is NOT a recordset.  It MUST BE BOUND to a recordset.
      
      ' rs is already (implicitly) instantiated. do not re-instantiate it.  Just open it.
      rs.open "SELECT * FROM tblUsers", MyConn
      
      ' Finally BIND the datagrid to the recordset 
      Set datagrid.DataSource = rs
    
      'AND, that's it.  Just be sure to clean up when the form is unloaded...
    end sub
    
    '...
    
    ' be sure to unbind the recordset prior to releasing the form.  
    ' It's also a good practice to close the ado objects when they'll no longer be needed.
    Private Sub Form1_unload()
      ' FIRST, unbind the grid from it's recordset.  Else, you'll get an error.
      Set me.Datagrid1.Datasource = Nothing
    
      ' THEN, close the recordset
      rs.Close
    
      ' and the connection
      MyConn.Close
      ' That's it.  Since you implicitly declared the ado objects, their memory will be released when the form is unloaded from memory.
    End Sub

    Note that this code assumes 1 form. So, the objects are declared at the form level.

    If you have child forms, you can pass the ado objects to that form as needed, and not add global objects. Variables/objects should be scoped at the highest level needed, and no higher.

    NOTE: the above is 'air' code. It should work as typed, but there are no guarantees - I may have made a typo!
    Last edited by loquin; 02-27-12 at 19:54.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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