M
MRM256
Guest
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
Continue reading...
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
Continue reading...