[RESOLVED] dataset, datatable, add records/tables at the end

[GBeats]

Hi

im having a stupid problem and i dont know why

what i want is just to put a list of datatables into 1 table

Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim DtSet As System.Data.DataTable
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

            MyConnection = New System.Data.OleDb.OleDbConnection(ConnectionString)
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [test$]", MyConnection)
            MyCommand.TableMappings.Add("Table", "TestTable")
            DtSet = New System.Data.DataTable
            MyCommand.Fill(DtSet)
            testtables.Add(DtSet)
            MyConnection.Close()

            MyConnection = New System.Data.OleDb.OleDbConnection(ConnectionString)
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [test$]", MyConnection)
            MyCommand.TableMappings.Add("Table", "TestTable")
            DtSet = New System.Data.DataTable
            MyCommand.Fill(DtSet)
            testtables.Add(DtSet)
            MyConnection.Close()

            DtSet = New System.Data.DataTable("Test")

            For Each item As System.Data.DataTable In testtables
                For Each item2 As DataRow In item.Rows
                    DtSet.ImportRow(item2)
                Next
            Next
            DataGridView1.DataSource = DtSet

sometimes i get an error saying the column f1 is already used by the othertable - if so how do i get around that
sometimes i get no error but an empty gridview

could i not just insert the tables straight into a dttable when i get the data from the connection?

please help

[dunfiddlin]

How can you put a list of datatables into a datatable? It's nonsense to start with! Would you propose putting a list of integers into an integer? A list of buttons into a button? The collection type for datatables is DataSet. I suggest you look it up (not least because you insist on creating DtSet as a DataTable which is just plain confusing at best.

As for the code you've got, what is the purpose of filling the exact same table with the exact same values twice (well actually only once, because you close the database connection in the middle and never re-open it)?

[jmcilhinney]

Your code doesn't really make much sense. You're executing a query and populating a DataTable, then executing the same query and populating another DataTable, then trying to copy the data from the first two DataTables into a third. I'm guessing that this is just for testing and that's not actually what you intend to do but it would help if we knew what you actually intended to do.

Firstly, I believe that ImportRow requires that the source and destination DataTables have the same schema. I haven't tested that but it would make sense. You aren't adding any columns at all to your third DataTable. Secondly, if you want the results of two queries in the same DataTable then simply call Fill twice and pass the same DataTable both times. The second result set will be appended to the first.

[GBeats]

Hi,

Sorry, i always seem to P*ss you guys of with my questions,

please bare with me im learning alsorts of stuff at once and sometimes my techniques of learning or questions will be.. well questionable.


this is just test code, i was initially trying a faster way of working with excel, and i found that a ole db connection was many times faster.
the connection itself is to an excel file and im sorry if the code just doesnt make sense i just put it that way because i was to lazy the point the connection to another file, so i just copy pasted the code again but changed the datatable.

i was just trying to basically put 2 tables 1 after the other, or a more precise explanation, regardless of the columns in each table one table must imediately follow the previous table.

i was initially working with dataset and adding tables to that but in the examples i found online everyone was using datatable, not dataset so i just used datatable instead since i didnt really need anything but the table part anyway.

i did notice on some examples that they created columns before adding data, i didnt know it was necessary if i was going to add rows manually. so i guess thats why that method didnt work

as for Mr dunfiddlin's response, obviously im not trying to join the datatypes, but rather the data inside them, cmon give me some slack

[GBeats]

Oh, thanks Mr jm, ill try the adapter fill idea. i didnt try that one yet

[dunfiddlin]

obviously im not trying to join the datatypes, but rather the data inside them

In what way is that obvious? Nothing in your code or your description suggests that at all. You seem(ed) to be unaware that a list in VB.Net is not a list in the sense which we normally use it. The whole object is contained within the list, not just a reference to it. If you want to use a property such as Name then you must explicitly refer to it as a property of the item not merely refer to the item.

[jmcilhinney]

There are two main reasons that you will piss people off with questions:

1. You don't bother to look for an answer for yourself first.
2. You don't provide an adequate description of the problem.

With regards to point 2, don't assume that we'll just work out what you're trying to do from code that doesn't actually do it. Examples are excellent but not a substitute for a clear explanation.

As for the question, there's no reason to use three DataTables when you can just use one. As I said, just pass the same DataTable to multiple calls to Fill and the multiple result sets will be combined. If the schemas don't match then you may have to use column aliases so the output columns match up but you'd have to do extra work to combine multiple DataTables with different schemas anyway.

[GBeats]

I apologise
i know you both have helped me out a great deal in the past (i appreciate everything, i really do), but i hope your also aware that i am studying and going through msdn, and s**flow(usually google takes me to those 2 sites).

As the saying saying goes, its easy if you know how, and if im asking questions its because i dont know how, and i just want to know something, i never asked for any code and usually i wouldnt (but admittedly looking at the code does help most of the time).

anyway sorry again.

the schema, when i was reading about datatables it was talking about schema but it didnt explain what it was, im assuming its the format or layout of the table, example certain columns representing certain forms of information and rows representing items and visa versa.... is that right?

so if the tables schemas need to match then i will have to create my own schema or just manually import the data i need from the table? the later sounds easier

[jmcilhinney]

"Schema", in this context, means the columns in the tables; the number, their data types and any constraints on their values. Are you saying that you want to take data from two completely arbitrary Excel worksheets and put it all into a single DataTable? If so then you're going to have to read all the data as text. Is that what you're trying to do?

[GBeats]

Hi,

i did a test just opening a connection to 2 seperate files and using fill with the same datatable

the second file is just a copy of the other, but i added a new column of data,

the file with fewer columns was read first into the datatable and then i read the file with an extra column later(i did it that way so if there was an issue with adding a table with more columns it would arise)

the datatable just added a column and the 2 tables were inserted correctly with no problems.

so its working just as i want it, ill just change it around and put it in an app.

do you see any situations arising from this method, the purpose of the program when i finish is to simply put multiple files of data into 1 file, since theres no headers being used it should be ok right?

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim TestFile As String = My.Computer.FileSystem.SpecialDirectories.Desktop & "test.xlsx"
        Dim testtables As New List(Of System.Data.DataTable)
        Dim ConnectionString As String = " Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Main Admin\\Desktop\\Test.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=No"";"
        Try
            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim DtTable As System.Data.DataTable
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

            'get data from file 1, put into dtTable
            MyConnection = New System.Data.OleDb.OleDbConnection(ConnectionString)
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [test$]", MyConnection)
            MyCommand.TableMappings.Add("Table", "TestTable")
            DtTable = New System.Data.DataTable
            MyCommand.Fill(DtTable)
            MyConnection.Close()

            'get data from file 2(added 1 column), put into dtTable
            ConnectionString = " Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Main Admin\\Desktop\\Test2.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=No"";"
            MyConnection = New System.Data.OleDb.OleDbConnection(ConnectionString)
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [test$]", MyConnection)
            MyCommand.TableMappings.Add("Table", "TestTable")
            MyCommand.Fill(DtTable)
            MyConnection.Close()

            'remove blank rows, going backwards 
            For i As Long = DtTable.Rows.Count - 1 To 0 Step -1
                Dim row As DataRow = DtTable.Rows(i)
                If row.Item(0) Is Nothing Then
                    DtTable.Rows.Remove(row)
                ElseIf row.Item(0).ToString = "" Then
                    DtTable.Rows.Remove(row)
                End If
            Next

            'show results on DGV
            DataGridView1.DataSource = DtTable

            Dim xlApp As New Excel.Application
            Dim xlSheet As Excel.Worksheet
            xlApp.Visible = True
            xlApp.Workbooks.Add()
            xlSheet = xlApp.ActiveSheet

            'Show results in excel
            Dim rowcnt As Long = 1
            For Each item As DataRow In DtTable.Rows
                xlSheet.Range(xlSheet.Cells(rowcnt, 1), xlSheet.Cells(rowcnt, item.ItemArray.Length)).Value = item.ItemArray
                rowcnt += 1
            Next

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

End Class

result in DGV so you can see the added column



thanks for your help