P
Prasun
I am building an App that takes an Excel file and populates 3 DataTable's in
a Dataset. I then was to create a relationship between these 3 DataTables.
After dong so I want to run some code to process / modify the data in the
tables and then return the result back into an Excel file. I am a beginner
at VB and i chose this as a starter project. I get the following exception
when i try to create the DataRelation:
An unhandled exception of type 'System.ArgumentNullException' occurred in
system.data.dll
Additional information: 'column' argument cannot be null.
Am i coding this correctly? When i run the fill command, will the table
automatically pick the column header and other schema info? Once this is
done I would like to create some DataRelationships between the three tables.
Will there be an error because the table schema was not detected? I am
curious about the concepts themselves rather than my code. I have already
tried to read up on all the info online, but nothing addresses my situation.
Listed below is the code I have. All help would be appreciated
Thank You
Private Sub cbConvert_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cbConvert.Click 'Convert button click event
Dim filename As String
filename = tbInputFileBrowser.Text()
Dim XLFileConn As New OleDbConnection
XLFileConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & filename & _
";Extended Properties=""Excel 8.0;HDR=YES"""
Try
XLFileConn.Open()
Dim XLFileCmd1 As New OleDbCommand
XLFileCmd1.Connection = XLFileConn
Dim XLFileDA As OleDbDataAdapter = New OleDbDataAdapter
XLFileDA.SelectCommand = XLFileCmd1
Dim XLFileMainDS = New DataSet("MainDataSet")
Dim TableMain As DataTable = XLFileMainDS.Tables.Add("TableWithMain")
XLFileCmd1.CommandText = "SELECT * FROM [Sheet1$] WHERE Modify <> 'TC' OR
'26'"
XLFileDA.Fill(XLFileMainDS, "Sheet1")
Dim TableTC As DataTable = XLFileMainDS.Tables.Add("TableWithModTC")
XLFileCmd1.CommandText = "SELECT * FROM [Sheet1$] WHERE Modify = 'TC'"
XLFileDA.Fill(XLFileMainDS, "Sheet1")
Dim Table26 As DataTable = XLFileMainDS.Tables.Add("TableWithMod26")
XLFileCmd1.CommandText = "SELECT * FROM [Sheet1$] WHERE Modify = '26'"
XLFileDA.Fill(XLFileMainDS, "Sheet1")
'XLFileDA.FillSchema(XLFileMainDS, SchemaType.Source, "Sheet1$")
Dim ParentCol As DataColumn
Dim Child1Col As DataColumn
Dim Child2Col As DataColumn
ParentCol = XLFileMainDS.Tables("TableWithMain").Columns("Proc Code")
Child1Col = XLFileMainDS.Tables("TableWithModTC").Columns("Proc Code")
Child2Col = XLFileMainDS.Tables("TableWithMod26").Columns("Proc Code")
Dim RelProcCode As DataRelation
Dim RelProcCode1 As DataRelation
RelProcCode = New DataRelation("ProcCodeTC", ParentCol, Child1Col) 'Get an
Exception Here
RelProcCode1 = New DataRelation("ProcCode26", ParentCol, Child2Col)
'Add the relation to the DataSet.
XLFileMainDS.Relations.Add(RelProcCode)
XLFileMainDS.Relations.Add(RelProcCode1)
Finally
XLFileConn.Close()
End Try
a Dataset. I then was to create a relationship between these 3 DataTables.
After dong so I want to run some code to process / modify the data in the
tables and then return the result back into an Excel file. I am a beginner
at VB and i chose this as a starter project. I get the following exception
when i try to create the DataRelation:
An unhandled exception of type 'System.ArgumentNullException' occurred in
system.data.dll
Additional information: 'column' argument cannot be null.
Am i coding this correctly? When i run the fill command, will the table
automatically pick the column header and other schema info? Once this is
done I would like to create some DataRelationships between the three tables.
Will there be an error because the table schema was not detected? I am
curious about the concepts themselves rather than my code. I have already
tried to read up on all the info online, but nothing addresses my situation.
Listed below is the code I have. All help would be appreciated
Thank You
Private Sub cbConvert_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cbConvert.Click 'Convert button click event
Dim filename As String
filename = tbInputFileBrowser.Text()
Dim XLFileConn As New OleDbConnection
XLFileConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & filename & _
";Extended Properties=""Excel 8.0;HDR=YES"""
Try
XLFileConn.Open()
Dim XLFileCmd1 As New OleDbCommand
XLFileCmd1.Connection = XLFileConn
Dim XLFileDA As OleDbDataAdapter = New OleDbDataAdapter
XLFileDA.SelectCommand = XLFileCmd1
Dim XLFileMainDS = New DataSet("MainDataSet")
Dim TableMain As DataTable = XLFileMainDS.Tables.Add("TableWithMain")
XLFileCmd1.CommandText = "SELECT * FROM [Sheet1$] WHERE Modify <> 'TC' OR
'26'"
XLFileDA.Fill(XLFileMainDS, "Sheet1")
Dim TableTC As DataTable = XLFileMainDS.Tables.Add("TableWithModTC")
XLFileCmd1.CommandText = "SELECT * FROM [Sheet1$] WHERE Modify = 'TC'"
XLFileDA.Fill(XLFileMainDS, "Sheet1")
Dim Table26 As DataTable = XLFileMainDS.Tables.Add("TableWithMod26")
XLFileCmd1.CommandText = "SELECT * FROM [Sheet1$] WHERE Modify = '26'"
XLFileDA.Fill(XLFileMainDS, "Sheet1")
'XLFileDA.FillSchema(XLFileMainDS, SchemaType.Source, "Sheet1$")
Dim ParentCol As DataColumn
Dim Child1Col As DataColumn
Dim Child2Col As DataColumn
ParentCol = XLFileMainDS.Tables("TableWithMain").Columns("Proc Code")
Child1Col = XLFileMainDS.Tables("TableWithModTC").Columns("Proc Code")
Child2Col = XLFileMainDS.Tables("TableWithMod26").Columns("Proc Code")
Dim RelProcCode As DataRelation
Dim RelProcCode1 As DataRelation
RelProcCode = New DataRelation("ProcCodeTC", ParentCol, Child1Col) 'Get an
Exception Here
RelProcCode1 = New DataRelation("ProcCode26", ParentCol, Child2Col)
'Add the relation to the DataSet.
XLFileMainDS.Relations.Add(RelProcCode)
XLFileMainDS.Relations.Add(RelProcCode1)
Finally
XLFileConn.Close()
End Try