"Prasun" <prasunp_@csufresno.edu> wrote in message
news:upxqjahNFHA.576@TK2MSFTNGP15.phx.gbl...
>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
>
>