none
Can't read DataSet from inside a Class RRS feed

  • Question

  • Hi Everyone,

    I have a problem that I can't seem to solve.

    For some reason when I try to read a data set from inside a Private Class I get the following error: Object reference not set to an instance of an object.

    The idea is to read an entire Access Database into a DataSet. Here is the code for the Class:

    Imports System.Data.OleDb
    Imports System.Data.OleDb.OleDbConnection
    Imports System.Drawing
    Imports System.IO
    Imports System.Text
    
    Partial Public Class MRM_Debug_Tools
        Private _ds As DataSet - Backing Store 
    
    #Region "Class Properties"
        Public Property MyDataset As DataSet
            'Purpose:       Creates an in memory database.
            'Parameters:    Class wide - Private _ds As DataSet
            'Returns:       A useable DataSet
            Get
                Return _ds
            End Get
            Set(value As DataSet)
                _ds = value
                Call ds_Viewer_1(_ds)
            End Set
        End Property
    #End Region
    
        Private Function GetTableNames(ByVal MyDataSet As DataSet) _
                                       As List(Of String)
            'Purpose:       Get the table names from the DataSet
            'Parameters:    MyDataSet As DataSet
            'Returns:       A list of DataSet Tables
            Dim listTables As List(Of String) = New List(Of String)
            Try
                For Each table As DataTable In MyDataSet.Tables
                    'Console.WriteLine(table.TableName)
                    listTables.Add(table.TableName)
                Next
            Catch ex As Exception
                'Log error 
                Dim el As New Log.ErrorLogger
                el.WriteToErrorLog(ex.Message, _
                                   ex.StackTrace, _
                                   "Error - GetTableNames")
            End Try
            Return listTables
        End Function
    
    #Region "Fill the DataSet with an entire OLEDB(MS-Access) Database"
        Public Function getOleDBDataSet(ByVal cnnAccess As String) _
                                          As DataSet
            Dim myDataSet As New DataSet
            Dim myCommand As New OleDb.OleDbCommand
            Dim myAdapter As New OleDb.OleDbDataAdapter
    
            Using cnn As New OleDbConnection(cnnAccess)
                Try
                    If cnn.State = ConnectionState.Closed Then
                        cnn.Open()
                        myCommand.Connection = cnn
                        'Get Database Tables
                        Dim tables As DataTable = cnn.GetOleDbSchemaTable( _
                            System.Data.OleDb.OleDbSchemaGuid.Tables, _
                            New Object() {Nothing, Nothing, Nothing, "TABLE"})
                        'iterate through all tables
                        Dim table As DataRow
                        For Each table In tables.Rows
                            'get current table's name
                            Dim tableName As String = table("TABLE_NAME")
    
                            Dim strSQL = "SELECT * FROM " & "[" & tableName & "]"
    
                            Dim adapter1 As New OleDb.OleDbDataAdapter(New OleDb.OleDbCommand(strSQL, cnn))
                            adapter1.FillSchema(myDataSet, SchemaType.Source, tableName)
    
                            'Fill the table in the dataset
                            myCommand.CommandText = strSQL
                            myAdapter.SelectCommand = myCommand
                            myAdapter.Fill(myDataSet, tableName)
                        Next
                    Else
                        cnn.Close()
                        Return Nothing
                    End If
    
                Catch ex As Exception
                    'Log error 
                    Dim el As New Log.ErrorLogger
                    el.WriteToErrorLog(ex.Message, ex.StackTrace, "Error")
                    Return Nothing
                End Try
                cnn.Close()
            End Using
            Return myDataSet
        End Function
    #End Region
    
    #Region "Controls used on form"
        Private Function Insert_cboCtrl(ByVal Ctrl As ComboBox, _
                                        ByVal strCtrlName As String, _
                                        ByVal X As Integer, _
                                        ByVal Y As Integer, _
                                        ByVal W As Integer, _
                                        ByVal H As Integer) As Control
            'Purpose:       Creates a ComboBox for the Form
            'Parameters:    strCtrlName - Name of the control
            '               X, Y - Where we are placing it on the Form.
            '               W, H - Width and Height of the control.
            '               
            'Returns:       A fully functional ComboBox
            With Ctrl
                '.Anchor = AnchorStyles.Left
                '.Anchor = AnchorStyles.None
                .BackColor = Color.FromKnownColor(KnownColor.White)
                .ForeColor = Color.Black
                .Name = strCtrlName
                .Location = New Point(X, Y)
                .Size = New Size(W, H)
                'AddHandler routines neccessary to add 
                'functionality to the combo boxes
                'AddHandler Ctrl.Paint, Function(sender, e) dt
                .DataSource = GetTableNames(_ds) _ds should be available, but isn't. Why?
                Call ds_Viewer_1(_ds)
                .ValueMember = "Table"
                .DisplayMember = "Table"
                .Refresh()
            End With
            Return Ctrl
        End Function
    
        Private Function Insert_dgvCtrl(ByVal Ctrl As DataGridView, _
                                        ByVal strCtrlName As String, _
                                        ByVal X As Integer, _
                                        ByVal Y As Integer, _
                                        ByVal W As Integer, _
                                        ByVal H As Integer) As Control
            With Ctrl
                .BackColor = Color.FromKnownColor(KnownColor.White)
                .ForeColor = Color.Black
                .Name = strCtrlName
                .Location = New Point(X, Y)
                .Size = New Size(W, H)
            End With
            Return Ctrl
        End Function
    
    #End Region
    
    #Region "Dynamically created frmViewer Form"
        Public Function Create_frmViewer(ByVal strName As String, _
                                         ByVal ctrlCombo As ComboBox, _
                                         ByVal ctrlDGV As DataGridView, _
                                         ByVal W As Long, _
                                         ByVal H As Long) As Form
            'Purpose:       Dynamically creates the DataSet Viewer form
            'Parameters:    strName As String - Name of the Form
            '               ctrlCombo As ComboBox 
            '               ctrlDGV As DataGridView
            '               W As Long - Width of the form
            '               H As Long - Height of the form
            'Returns:       The DataSet Viewer form with controls
            Dim frmViewer As New Form
            Dim ctrlCB As New ComboBox
            Dim ctrlDV As New DataGridView
            'Build Viewer Form
            With frmViewer
                .Text = strName
                .SuspendLayout()
                .Controls.Clear()
                .Size = New Size(W, H)
                .AutoSize = True
                .AutoSizeMode = AutoSizeMode.GrowAndShrink
                'New System.Drawing.Point(125, 63)
                .Location = New Point(125, 63)
                'Adding ComboBox to form
                ctrlCB = New ComboBox
                ctrlCB = Insert_cboCtrl(ctrlCombo, "cboTbls", 10, 5, 163, 26)
                .Controls.Add(ctrlCB)
                ctrlDV = Insert_dgvCtrl(ctrlDGV, "Table", 10, 35, 500, 500)
                .Controls.Add(ctrlDV)
    
                .ResumeLayout()
            End With
            Return frmViewer
        End Function
    #End Region
    
    #Region "Debug Tool - DataTable Viewer Subroutine"
        Public Sub dt_Read(ByVal dt As DataTable)
            'Purpose:       Lets the programmer see the data
            '               contained in the DataTable
            'Parameters:    dt As DataTable
            'Returns:       Nothing - Just a Debug routine
    
            Dim myRow As DataRow
            Dim myCol As DataColumn
            'For each field in the table...
            For Each myRow In dt.Rows
                'For each property of the field...
                For Each myCol In dt.Columns
                    'Display the field name and value.
                    Debug.Print(myCol.ColumnName & vbTab & _
                                myRow(myCol).ToString())
                Next
                Debug.Print(vbCrLf)
            Next
        End Sub
    #End Region
    
        Public Sub Log_DataSet(ByVal ds As DataSet)
            'Purpose:       Writes the entire DataSet into
            '               a text file
            'Parameters:    ds As DataSet
            'Returns:       Nothing - Creates a text file
    
            'check and make the directory if necessary; 
            'this is set to look in the application folder, 
            'you may wish to place the DataSet log in another 
            'location depending upon the user's role and
            'write access to different areas of the file system
    
            If Not System.IO.Directory.Exists(Application.StartupPath & "\DataSet\") Then
                System.IO.Directory.CreateDirectory(Application.StartupPath & "\DataSet\")
            End If
    
            'check the file
            Dim fs As FileStream = New FileStream(Application.StartupPath & _
                                                  "\DataSet\DataSetLog.txt", _
                                                  FileMode.OpenOrCreate, _
                                                  FileAccess.ReadWrite)
            Dim s As StreamWriter = New StreamWriter(fs)
            s.Close()
    
            'log it
            Dim fs1 As FileStream = New FileStream(Application.StartupPath & _
                                                   "\DataSet\DataSetLog.txt", _
                                                   FileMode.Append, _
                                                   FileAccess.Write)
            Dim s1 As StreamWriter = New StreamWriter(fs1)
            Dim strTbl As String = ""
            Dim rowData As String = ""
            Dim myRow As DataRow
            Dim myCol As DataColumn
    
            For Each table As DataTable In ds.Tables
                strTbl = ds.Tables(table.TableName).ToString & vbCrLf
                's1.Write("Table: " & strTbl & vbCrLf)
                Debug.Print(strTbl)
                'For Each myRow As DataRow In table.Rows
                's1.Write(vbTab & row.Item(.ToString)
                'For Each Mycol As DataColumn In table.Columns
                'rowData = rowData & column.ColumnName & "=" & row(column) & " "
                'rowData = myCol.ColumnName & "=" & myRow(myCol)
                'Debug.Print(row(column).ToString)
            Next
            'rowData = vbTab & rowData & vbCrLf
            's1.Write(rowData)
            'Next
            'Next
            s1.Close()
        End Sub
    
    #Region "OleDB DataTable Viewer"
        Public Sub dt_Viewer(ByVal dt As DataTable)
            'Purpose:       Lets the Developer view DataTable contents
            'Parameters:    dt As DataTable
            'Returns:       A Log file
            Dim rowData As String = ""
            Dim el As New Log.ErrorLogger
            'dt in the name of the data table
            For Each row As DataRow In dt.Rows
                For Each column As DataColumn In dt.Columns
                    rowData = rowData & column.ColumnName & "=" & row(column) & " "
                Next
                rowData = rowData & vbCrLf & vbCrLf
            Next
            el.WriteToErrorLog(rowData, " ", "DataTable Viewer")
            'MessageBox.Show(rowData)
        End Sub
    #End Region
    
    #Region "Debug Tool - DataSet Viewer Subroutine"
        Public Sub ds_Viewer_1(ByRef MyDataSet As DataSet)
            'Purpose:       The developer can view DataSets
            'Parameters:    MyDataSet as DataSet - DataSet we want to see
            'Returns:       Nothing
            Dim I As Long
            For Each table As DataTable In MyDataSet.Tables
                Debug.Print(table.TableName.ToString)
                For Each row As DataRow In table.Rows
                    For Each column As DataColumn In table.Columns
                        Debug.Print(column.ColumnName & vbTab & _
                                row(column).ToString())
                    Next
                    'Debug.Print(vbCrLf)
                Next
                'For Each column As DataColumn In table.Columns
                '    Debug.Print(vbTab & column.ColumnName & vbTab & RowNotInTableException(column).tostring))
                'For Each row As DataRow In table.Rows
                ' Debug.Print(vbTab & vbTab & row(column).ToString)
                'Next
                'Next
            Next
        End Sub
    #End Region
    End Class
    

    After I read the database into memory I use another function to place all the table names into a list for use in the combo box created in the Create_frmViewer function.

    Private Function GetTableNames(ByVal MyDataSet As DataSet) _
                                       As List(Of String)
            'Purpose:       Get the table names from the DataSet
            'Parameters:    dataSet As DataSet
            'Returns:       A list of DataSet Tables
            Dim listTables As List(Of String) = New List(Of String)
            Try
                For Each table As DataTable In MyDataSet.Tables
                    'Console.WriteLine(table.TableName)
                    listTables.Add(table.TableName)
                Next
            Catch ex As Exception
                'Log error 
                Dim el As New Log.ErrorLogger
                el.WriteToErrorLog(ex.Message, _
                                   ex.StackTrace, _
                                   "Error - GetTableNames")
            End Try
            Return listTables
    End Function

    I thought the variable _ds was accessible to any function inside the Class. Why do I get the error?

    Thanks,


    MRM256

    Friday, September 21, 2018 10:02 PM

All replies

  • Because it's not initialized, use this.

    Private _ds As New DataSet - Backing Store 


    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

    Saturday, September 22, 2018 12:59 AM
    Moderator
  • Hi Karen,

    I added the 'New' into the variable definition. It no longer throws the exception, but for some reason when the _ds is passed into the GetTableNames function. No table names are available in the data set. I assume because we tacked New into the definition it clears anything stored therein.

    What now?


    MRM256

    Saturday, September 22, 2018 2:31 PM
  • New creates an instance of the DataSet when first declared but if you use New after loading data, yes it clears the DataSet but if you only use New in the declare statement as shown below it does not clear the tables.

     Private _ds As New DataSet 

    To better understand what's happening place breakpoints on lines after the DataSet is populated, step through the code to where it goes to any method and re-check the DataSet DataTable contents for data. At some point this will point you to why data is dumped or that you thought it had data and does not have data. In short follow the path from population thru usage of the tables.


    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

    Saturday, September 22, 2018 3:11 PM
    Moderator
  • The idea is to read an entire Access Database into a DataSet. 

    This idea is very very very wrong idea.

    DataSet is "data in memory" storage. Think - what will happen if amount of data is bigger than available memory?

    > I use another function to place all the table names into a list 

    You post 303 lines of code and seas:

    - "Do I have here too many lines of code to find where I did something incorrectly?"

    But why you put all code in one class? Why you simply didn't put code responsible operation with DataSet in the class derived from DataSet? This will reduce your 303 lines to 250. After that look again and ask self - what I can move into separate class? - And move it!!! - As far as I can see there is few candidates for such relocation.

    After you relocate all code for a tasks unrelated with current - you will have 30-40 lines of code and comments where you will be able find/see a problem in the code.

    And look how simple can be code if it is in correct place (it's C#):

    using System;
    using System.Data;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    
    namespace ConsoleApplication2
    {
        class MyDataSet : DataSet
        {
            public MyDataSet()
            {
                //Tables.Add("A1");
                //Tables.Add("A2");
                //Tables.Add("A3");
            }
    
            public List<string> GetTableNames()
            {
                return Tables.Cast<DataTable>()
                             .Select(x => x.TableName)
                             .ToList<string>();
            }
        }
    }
    

    And... if somebody give such mess on workplace - I would aks to reassign him... street cleaner position may be will suit...


    Sincerely, Highly skilled coding monkey.

    Saturday, September 22, 2018 4:08 PM
  • Hi Andrey,

    Yes, I agree loading the entire database into memory is a (very)^3 bad idea. 

    I ran some experiments, and as one monkey to another I find that playing around with the code can reveal some interesting things.

    I don't know how much the internal workings have changed, but if a variable was defined as Private. It means that variable can be accessed from inside that scope. Therefore, if I define _ds as Private to the class. Anything inside the class should be able to access said variable.

    This does not seem to be the case.

    I followed Karen Payne's suggestion and added 'New' to the declaration of my variable.

    Private _ds As New DataSet

    To test a theory I changed the declaration of the GetTableNames function from Private to Public. After I load the DataSet from the Private Sub btnAccessCnn_Click event. I use the following event to test my theory.

    Private Sub DataSetViewerToolStripMenuItem_Click(sender As Object, _
                                                      e As EventArgs) _
                                                      Handles DataSetViewerToolStripMenuItem.Click
            Dim objDebug As New MRM_Debug_Tools
            Dim frmViewer As New Form
            Dim ctrlCboBox As New ComboBox
            Dim ctrlDGV As New DataGridView
            Dim NewColor As Color = Color.FromKnownColor(KnownColor.Control)
            'frmViewer = objDebug.Create_frmViewer("DataSet Tables", ctrlCboBox, ctrlDGV, 500, 500)
            'frmViewer.Show()
            Call objDebug.ds_Viewer_1(ds.MyDataset) 'Shows the entire database.
            Call objDebug.GetTableNames(ds.MyDataset) 'Shows the table names only.
    End Sub

    The theory I was testing is: Is the Backing Store only accessible from the Property MyDataSet?

    From my little test. This seems to be the case.

    Thanks,


    MRM256

    Saturday, September 22, 2018 5:01 PM
  • You have at least three thinks in your code.

    1. variable declaration

    2. property to access to variable

    3. some code to fill your variable

    The mess which you create didn't allow you to 

    4. miss to run a 3.

    5. use 2 to get empty variable

    If you move code responsible for 1.2.3. into separate class you will have an option to see which one of 4. or 5. (or both) happen.

    The theory I was testing is

    Your variable are accessible from any non-static method in the class where it declared.

    Your property are accessible from outside against instance of the class and from any non-static methods inside class.

    To test this you need to wrote just 10 lines of code.

    P.S. Please, do not expect that I (or somebody) will analyze 303 lines of your code to find where is a first problem in it. Reduce amount of code to 5-10 lines with reproduction of the problem and most likely somebody will provide exact answer for a problem.


    Sincerely, Highly skilled coding monkey.

    Saturday, September 22, 2018 5:30 PM
  • Andrey,

    Can you share your 10 line test?

    Thanks,


    MRM256

    Saturday, September 22, 2018 6:25 PM
  • using System;
    
    namespace ConsoleApplication2
    {
        class A {
            private int _count;
            private bool wasSet = false;
            public int Count { get { return _count; } set { _count = value; wasSet = true; } }
            public void MyFunc() {
                Count = 22; // set
                _count = 33; wasSet = false; // overvrite
            }
        }
    
        class B {
            public B() {
                A a = new A();
                a.Count = 44;
                a._count = 55; // incompilable
            }
        }
    }


    Sincerely, Highly skilled coding monkey.

    Saturday, September 22, 2018 7:04 PM