|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Excel and ADO.NETI 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 doing 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. 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,
Check if ParentCol or Child1Col is not set to Nothing before creating relation. If it is then you need to check why it was not set to the proper column Show quote "Prasun" <prasu***@csufresno.edu> wrote in message news:%23C8WfpLNFHA.2704@TK2MSFTNGP15.phx.gbl... > Hello: > > 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 doing 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. > > 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 > > > Hello:
Yes, I put some breakpoint in my my program and ParentCol, ChildCol1 and ChildCol2 are set to 'Nothing' through the whole program. I will have to find out why. Thank you for helping me figure put what the problem might be. This is my first real project as a beginner. Now to figure out why they have no value Thank You Prasun Show quote "Val Mazur (MVP)" <group***@hotmail.com> wrote in message news:%23ecWSnMNFHA.568@TK2MSFTNGP09.phx.gbl... > Hi, > > Check if ParentCol or Child1Col is not set to Nothing before creating > relation. If it is then you need to check why it was not set to the proper > column > > -- > Val Mazur > Microsoft MVP > > http://xport.mvps.org > > > > "Prasun" <prasu***@csufresno.edu> wrote in message > news:%23C8WfpLNFHA.2704@TK2MSFTNGP15.phx.gbl... >> Hello: >> >> 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 doing 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. >> >> 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 >> >> >> > > Hello:
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. Show quote "Val Mazur (MVP)" <group***@hotmail.com> wrote in message news:%23ecWSnMNFHA.568@TK2MSFTNGP09.phx.gbl... > Hi, > > Check if ParentCol or Child1Col is not set to Nothing before creating > relation. If it is then you need to check why it was not set to the proper > column > > -- > Val Mazur > Microsoft MVP > > http://xport.mvps.org > > > > "Prasun" <prasu***@csufresno.edu> wrote in message > news:%23C8WfpLNFHA.2704@TK2MSFTNGP15.phx.gbl... >> Hello: >> >> 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 doing 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. >> >> 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,
Yes, when you call Fill method, then DataAdapter fills in columns and other schema information automatically. I have checked your code again and it looks like you do not fill the tables properly and, I think, this is the cause of the problem. Try to do it next way 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(TableMain) Dim TableTC As DataTable = XLFileMainDS.Tables.Add("TableWithModTC") XLFileCmd1.CommandText = "SELECT * FROM [Sheet1$] WHERE Modify = 'TC'" XLFileDA.Fill(TableTC) Dim Table26 As DataTable = XLFileMainDS.Tables.Add("TableWithMod26") XLFileCmd1.CommandText = "SELECT * FROM [Sheet1$] WHERE Modify = '26'" XLFileDA.Fill(Table26) Dim ParentCol As DataColumn Dim Child1Col As DataColumn Dim Child2Col As DataColumn ParentCol = TableMain .Columns("Proc Code") Child1Col = TableTC .Columns("Proc Code") Child2Col = Table26.Columns("Proc Code") Rest of the code should be the same Show quote "Prasun" <prasu***@csufresno.edu> wrote in message news:%23Azv3aYNFHA.3156@TK2MSFTNGP15.phx.gbl... > Hello: > > 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. > > > "Val Mazur (MVP)" <group***@hotmail.com> wrote in message > news:%23ecWSnMNFHA.568@TK2MSFTNGP09.phx.gbl... >> Hi, >> >> Check if ParentCol or Child1Col is not set to Nothing before creating >> relation. If it is then you need to check why it was not set to the >> proper column >> >> -- >> Val Mazur >> Microsoft MVP >> >> http://xport.mvps.org >> >> >> >> "Prasun" <prasu***@csufresno.edu> wrote in message >> news:%23C8WfpLNFHA.2704@TK2MSFTNGP15.phx.gbl... >>> Hello: >>> >>> 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 doing 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. >>> >>> 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 >>> >>> >>> >> >> > > Hello:
I found the error in my code Dim TableMain As DataTable = XLFileMainDS.Tables.Add("TableWithMain") XLFileCmd1.CommandText = "SELECT * FROM [Sheet1$] WHERE trim (Modify) <> 'TC' OR '26'" XLFileDA.SelectCommand = XLFileCmd1 XLFileDA.Fill(XLFileMainDS, "Sheet1") Dim TableMain As DataTable = XLFileMainDS.Tables.Add("TableWithMain") XLFileCmd1.CommandText = "SELECT * FROM [Sheet1$] WHERE trim (Modify) <> 'TC' OR '26'" XLFileDA.SelectCommand = XLFileCmd1 XLFileDA.Fill(XLFileMainDS, "TableWithMain") I had put Sheet1 instead of the name of the table. It now reads the columns. The issue I have now is with my command statement. I am using a coulmn that has been set to text in excel and contains both numbers and text. Unfortunately the code can not see the numeric values i.e. 26. I have tried put the 26 in quotes like '26' and without quotes but it doesn't seem to work. Thank You for the help Prasun Show quote "Val Mazur (MVP)" <group***@hotmail.com> wrote in message news:OrUeRplNFHA.164@TK2MSFTNGP12.phx.gbl... > Hi, > > Yes, when you call Fill method, then DataAdapter fills in columns and > other schema information automatically. I have checked your code again and > it looks like you do not fill the tables properly and, I think, this is > the cause of the problem. Try to do it next way > > 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(TableMain) > > Dim TableTC As DataTable = XLFileMainDS.Tables.Add("TableWithModTC") > XLFileCmd1.CommandText = "SELECT * FROM [Sheet1$] WHERE Modify = 'TC'" > XLFileDA.Fill(TableTC) > > Dim Table26 As DataTable = XLFileMainDS.Tables.Add("TableWithMod26") > XLFileCmd1.CommandText = "SELECT * FROM [Sheet1$] WHERE Modify = '26'" > XLFileDA.Fill(Table26) > > Dim ParentCol As DataColumn > Dim Child1Col As DataColumn > Dim Child2Col As DataColumn > ParentCol = TableMain .Columns("Proc Code") > Child1Col = TableTC .Columns("Proc Code") > Child2Col = Table26.Columns("Proc Code") > > > Rest of the code should be the same > > -- > Val Mazur > Microsoft MVP > > http://xport.mvps.org > > > > "Prasun" <prasu***@csufresno.edu> wrote in message > news:%23Azv3aYNFHA.3156@TK2MSFTNGP15.phx.gbl... >> Hello: >> >> 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. >> >> >> "Val Mazur (MVP)" <group***@hotmail.com> wrote in message >> news:%23ecWSnMNFHA.568@TK2MSFTNGP09.phx.gbl... >>> Hi, >>> >>> Check if ParentCol or Child1Col is not set to Nothing before creating >>> relation. If it is then you need to check why it was not set to the >>> proper column >>> >>> -- >>> Val Mazur >>> Microsoft MVP >>> >>> http://xport.mvps.org >>> >>> >>> >>> "Prasun" <prasu***@csufresno.edu> wrote in message >>> news:%23C8WfpLNFHA.2704@TK2MSFTNGP15.phx.gbl... >>>> Hello: >>>> >>>> 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 doing 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. >>>> >>>> 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 >>>> >>>> >>>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics