Trouble with creating DataRelations - Need Help

  • Thread starter Thread starter Prasun
  • Start date Start date
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
 
Hi,

Sorry for the late reply for the previous posting. Check answer there

--
Val Mazur
Microsoft MVP

http://xport.mvps.org



Prasun said:
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
Click to expand...
 
Back
Top