none
mbindingcollection.datasource problem RRS feed

  • Question

  • I am in the process of resolving issues from the upgrade wizard in upgrading an enterprise project from VB6 to VB.NET using VS2008.  I'm very new to .NET I should add.   I am currently stuck on a binding collection datasource issue as follows.

    In my Form1A class definition I have

    Private bndColDetail As New VB6.MBindingCollection

    Public WithEvents rstCustomer As New ADODB.Recordset

    Public rsDetail As New ADODB.Recordset

    Private Sub RebindControls()

    If rsDetail.State = ADODB.ObjectStateEnum.adStateOpen Then rsDetail.Close()
    rsDetail.open {new criteria here - rsDetail opens fine}

    bndColDetail.datasource = rsDetail 'This works fine the first time

    End Sub

    Upon navigating to a new rstCustomer recordset, I call RebindControls (from rstCustomer.MoveComplete event) to get accompanying detail information.  This runs fine repeatedly within the current form Form1A.  However, I then open a second form Form1B that closes Form1A.rstCustomer and reopens it with a new account.   The rstCustomer.Movecomplete event fires which calls RebindControls.   rsDetail is closed and reopened successfully.  

    When I get to the statement that sets bndColDetail.datasource, hmmmm, went to capture the exact error and  I am now getting a different error than I was getting before, so not sure if in closing and reopening the project I have inadvertently changed the error logging settings.   But initially I was getting the Null Exception error, or Object not set to an instance of an object, which led me to believe I had an issue with memory management/garbage collection - that I had closed the COM object but not its wrapper or vice versa.   Now I am just getting Error#5 - an exception has been thrown by the target of an invocation.

    In the initial Null Exception error, it appeared that the problem was related to closing the datasource, rsDetail, which affected bndcolDetail.   But even trying to set bndcolDetail.Datasource to Nothing prior to closing rsDetail causes the Object not set to an instance of an object.

    I know there is probably some glaring error here - obvious to you folks but lost on me.   Do I need to call the Dispose method on something, or redefine something As New again?   Sorry for being dense.   Guess I just don't have the basic workings down quite yet.   

    Wednesday, May 24, 2017 8:20 PM

Answers

  • Hello,

    The best course of action is to discard the old VB6 methods you have an move to VB.NET ways of working with your data. Dependent on the backend database dictates which data provider to use e.g. OleDb or SqlClient are most common.

    Read data using one of the providers into a DataSet, if this is a master-detail relationship then setup a relationship between the master and details tables typically done on the master key to a foreign key into the detail table. This is followed by by setting up a BindingSource for both master and detail tables

    The code below is taken from a code sample (sadly you can not try the code sample out as it requires a later version of Visual Studio but you can view the source code online to get an idea of what it takes) I wrote which shows the basic setup using SQL-Server, the same operation would be done for MS-Access by changing the data provider from SqlClient to OleDb

    Public Sub LoadData()
    
        Dim ds As New DataSet
    
        Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
            Dim da As New SqlDataAdapter("SELECT id,FirstName,LastName,Address,City,State,ZipCode FROM Customer", cn)
            Try
                da.Fill(ds, "Customer")
                Dim dt As DataTable = ds.Tables("Customer")
    
                da = New SqlDataAdapter("SELECT id,CustomerId,OrderDate,Invoice FROM Orders", cn)
                da.Fill(ds, "Orders")
                ds.SetRelation("Customer", "Orders", "Id", "CustomerId")
    
                da = New SqlDataAdapter("SELECT id,OrderId ,ProductName,UnitPrice,Quantity FROM OrderDetails", cn)
                da.Fill(ds, "OrderDetails")
                ds.SetRelation("Orders", "OrderDetails", "Id", "OrderId")
    
                Master.DataSource = ds
                Master.DataMember = ds.Tables(0).TableName
    
                Details.DataSource = Master
                Details.DataMember = ds.Relations(0).RelationName
    
                OrderDetails.DataSource = Details
                OrderDetails.DataMember = ds.Relations(1).RelationName
    
                CustomerTable = ds.Tables("Customer")
                Dim OrderTable As DataTable = ds.Tables("Orders")
    
            Catch ex As Exception
                HasErrors = True
                ExceptionMessage = ex.Message
            End Try
        End Using
    End Sub
    

    Once the above has been done we load DataGridView controls in a form.

    Private Sub MainForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
    
        Dim ops As New Operations
    
        ops.LoadData()
    
        If Not ops.HasErrors Then
    
            StateInformation = ops.StateInformation
    
            bsMaster = ops.Master
            bsDetails = ops.Details
    
            MasterDataGridView.DataSource = bsMaster
            DetailsDataGridView.AutoGenerateColumns = False
    
            DetailsDataGridView.DataSource = bsDetails
            OrderDateColumn.ReadOnly = False
            UpdateButtonColumn.UseColumnTextForButtonValue = True
    
            MasterBindingNavigator.BindingSource = bsMaster
    
            bsOrderDetails = ops.OrderDetails
    
            OrderDetailsDataGridView.DataSource = bsOrderDetails
            OrderDetailsDataGridView.Columns("id").Visible = False
            OrderDetailsDataGridView.Columns("OrderId").Visible = False
    
            DetailBindingNavigator.BindingSource = bsDetails
    
            OrderDetailsDataGridView.Columns("ProductName").HeaderText = "Product"
            OrderDetailsDataGridView.Columns("UnitPrice").HeaderText = "Unit price"
    
        Else
            MessageBox.Show(ops.ExceptionMessage)
        End If
    
    End Sub
    

    If you don't want DataGridView controls you can bind to other controls such as TextBoxes etc.

    What you need to know is there is more work involved with VB.NET (same goes for C#) then what you where use to with classic VB6. The upside is you have much more control over your data operations.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by GreydogMe Friday, May 26, 2017 7:11 AM
    Wednesday, May 24, 2017 10:28 PM
    Moderator

All replies

  • Hello,

    The best course of action is to discard the old VB6 methods you have an move to VB.NET ways of working with your data. Dependent on the backend database dictates which data provider to use e.g. OleDb or SqlClient are most common.

    Read data using one of the providers into a DataSet, if this is a master-detail relationship then setup a relationship between the master and details tables typically done on the master key to a foreign key into the detail table. This is followed by by setting up a BindingSource for both master and detail tables

    The code below is taken from a code sample (sadly you can not try the code sample out as it requires a later version of Visual Studio but you can view the source code online to get an idea of what it takes) I wrote which shows the basic setup using SQL-Server, the same operation would be done for MS-Access by changing the data provider from SqlClient to OleDb

    Public Sub LoadData()
    
        Dim ds As New DataSet
    
        Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
            Dim da As New SqlDataAdapter("SELECT id,FirstName,LastName,Address,City,State,ZipCode FROM Customer", cn)
            Try
                da.Fill(ds, "Customer")
                Dim dt As DataTable = ds.Tables("Customer")
    
                da = New SqlDataAdapter("SELECT id,CustomerId,OrderDate,Invoice FROM Orders", cn)
                da.Fill(ds, "Orders")
                ds.SetRelation("Customer", "Orders", "Id", "CustomerId")
    
                da = New SqlDataAdapter("SELECT id,OrderId ,ProductName,UnitPrice,Quantity FROM OrderDetails", cn)
                da.Fill(ds, "OrderDetails")
                ds.SetRelation("Orders", "OrderDetails", "Id", "OrderId")
    
                Master.DataSource = ds
                Master.DataMember = ds.Tables(0).TableName
    
                Details.DataSource = Master
                Details.DataMember = ds.Relations(0).RelationName
    
                OrderDetails.DataSource = Details
                OrderDetails.DataMember = ds.Relations(1).RelationName
    
                CustomerTable = ds.Tables("Customer")
                Dim OrderTable As DataTable = ds.Tables("Orders")
    
            Catch ex As Exception
                HasErrors = True
                ExceptionMessage = ex.Message
            End Try
        End Using
    End Sub
    

    Once the above has been done we load DataGridView controls in a form.

    Private Sub MainForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
    
        Dim ops As New Operations
    
        ops.LoadData()
    
        If Not ops.HasErrors Then
    
            StateInformation = ops.StateInformation
    
            bsMaster = ops.Master
            bsDetails = ops.Details
    
            MasterDataGridView.DataSource = bsMaster
            DetailsDataGridView.AutoGenerateColumns = False
    
            DetailsDataGridView.DataSource = bsDetails
            OrderDateColumn.ReadOnly = False
            UpdateButtonColumn.UseColumnTextForButtonValue = True
    
            MasterBindingNavigator.BindingSource = bsMaster
    
            bsOrderDetails = ops.OrderDetails
    
            OrderDetailsDataGridView.DataSource = bsOrderDetails
            OrderDetailsDataGridView.Columns("id").Visible = False
            OrderDetailsDataGridView.Columns("OrderId").Visible = False
    
            DetailBindingNavigator.BindingSource = bsDetails
    
            OrderDetailsDataGridView.Columns("ProductName").HeaderText = "Product"
            OrderDetailsDataGridView.Columns("UnitPrice").HeaderText = "Unit price"
    
        Else
            MessageBox.Show(ops.ExceptionMessage)
        End If
    
    End Sub
    

    If you don't want DataGridView controls you can bind to other controls such as TextBoxes etc.

    What you need to know is there is more work involved with VB.NET (same goes for C#) then what you where use to with classic VB6. The upside is you have much more control over your data operations.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by GreydogMe Friday, May 26, 2017 7:11 AM
    Wednesday, May 24, 2017 10:28 PM
    Moderator
  • Thank you Karen.    that's a really nice and concise example that will be easy to follow once I get to that point.   I was really hoping for a solution to the issue at hand since my project is too big to rewrite in .Net at this time, and we were hoping to replace the compatibility components over time.   But, may not have that luxury.   In any case, I have learned more about exception handling and displaying the inner exception and have found that this supposed binding problem was not due to what I was thinking - a disposed object - but rather a datetimepicker issue which was affecting the binding.

    anyway - I know your code will come in handy very soon so thank you for taking the time to share.

    Friday, May 26, 2017 7:11 AM