Select 2 or more Excel Worksheets to show in Datagrid

[jared.geli]

Hi guys I need your help on selecting data from 4 different worksheet. Here's my code

 Dim MyConnection As New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & path & "'; Extended Properties=Excel 12.0;")
        Dim cmd As OleDbCommand = New OleDbCommand("Select [MAM$].[Seller Name], [MAM$].[Final Computation], [MAM$].Designantion From [MAM$] Left Join [DSS$] on [MAM$].[Seller Name] = [DSS$].[Seller Name] Where [MAM$].Designation is not Null", MyConnection)
        MyConnection.Open()
        adpt = New OleDbDataAdapter(cmd)
        'Here one CommandBuilder object is required.
        'It will automatically generate DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object
        Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(adpt)
        DtSet = New DataSet()
        adpt.Fill(DtSet)
        DataGridView1.DataSource = Nothing
        DataGridView1.DataSource = DtSet.Tables(0).DefaultView

        MyConnection.Close()

It says "No value given for one or more required parameters". I don't know if I can use Join in Excel.

[jared.geli]

I have this Pseudo-cde I want to try but I don't know how I can pull it off

'Select data from table1

'Select data from table2

'Select data from table3

'Select data from table4

'Put all selection in one dataset or datatable

All 4 tables has 5 same columns

[jared.geli]

I did a Dataset Merge and now it says "No value given for one or more required parameters."

 Dim first As String = "Select [Seller Name], Designation, [Final Computation], Agecny From [MAM$]"
    Dim second As String = "Select [Seller Name], Designation, [Final Computation], Agecny From [DSS$]"
MyConnection.Open()

        adpt = New OleDbDataAdapter(first, MyConnection)
        DtSet = New DataSet
        adpt.Fill(DtSet, "MAM")
        adpt.SelectCommand.CommandText = second
        DtSet2 = New DataSet
        adpt.Fill(DtSet2, "DSS")
        adpt.Dispose()

        MyConnection.Close()

        DtSet.Tables(0).Merge(DtSet2.Tables(0))
        dt = DtSet.Tables(0)

        For i = 0 To dt.Rows.Count - 1
            MsgBox(dt.Rows(i).Item(0) & "  --  " & dt.Rows(i).Item(1))
        Next

[jared.geli]

Nevermind guys problem solved! I can now merge 4 worksheets into 1 datatable

Dim first As String = "Select [Seller Name], Designation, [Final Computation] From [MAM$] Where [Seller Name] Is Not Null"
        Dim second As String = "Select [Seller Name], Designation, [Final Computation] From [DSS$] Where [Seller Name] Is Not Null"
        Dim third As String = "Select [Seller Name], Designation, [Final Computation] From [PSG$] Where [Seller Name] Is Not Null"
        Dim fourth As String = "Select [Seller Name], Designation, [Final Computation] From [OLD DSP$] Where [Seller Name] Is Not Null"
        Dim fifth As String = "Select [Seller Name], Designation, [Final Computation] From [New DSP$] Where [Seller Name] Is Not Null"

        MyConnection.Open()
        Dim cmd As OleDbCommand = New OleDbCommand(first, MyConnection)
        adpt = New OleDbDataAdapter(cmd)
        DtSet = New DataSet()
        adpt.Fill(DtSet)
        adpt.SelectCommand.CommandText = second
        DtSet2 = New DataSet()
        adpt.Fill(DtSet2)
        adpt.SelectCommand.CommandText = third
        DtSet3 = New DataSet()
        adpt.Fill(DtSet3)
        adpt.SelectCommand.CommandText = fourth
        DtSet4 = New DataSet()
        adpt.Fill(DtSet4)
        adpt.SelectCommand.CommandText = fifth
        DtSet5 = New DataSet()
        adpt.Fill(DtSet5)


        MyConnection.Close()

        DtSet.Tables(0).Merge(DtSet2.Tables(0))
        DtSet.Tables(0).Merge(DtSet3.Tables(0))
        DtSet.Tables(0).Merge(DtSet4.Tables(0))
        DtSet.Tables(0).Merge(DtSet5.Tables(0))
        dt = DtSet.Tables(0)

        DataGridView1.DataSource = dt