ado.net and tabControl(pls help !!)

  • 11 years ago

    I need to dynamically create tabpages on a tabcontrol & display the tabPage.text to be a worksheet name from excel.

    I can connect to the excel sheet but canot create dynamic tabcontrol to display all the worksheet names.I am two days behind time with this project. Please help me.......

    I got it to be displayed in a datagrid only but need to be on a tabcontrol

    Code is below

    Dim DS As New System.Data.DataSet("DS")

    Dim DT As System.Data.DataTable

    Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

    Dim MyConnection As System.Data.OleDb.OleDbConnection

    Private Sub frmImports_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Dim OpenFileDialog1 As New OpenFileDialog

    OpenFileDialog1.InitialDirectory =

    My.Computer.FileSystem.SpecialDirectories.MyDocuments

    OpenFileDialog1.FileName = frmSupplierImport.cmbSupplierName.Text

    OpenFileDialog1.Filter =

    "Excel Files frmSupplierImport.cmbSupplierName.Text(*.xls)|*.xls|(*.txt)|*.txt|All Files (*.*)|*.*"

    If (OpenFileDialog1.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then

    End If

    Try

    'ChooseSupplier.OpenFileDialog1.FileName

    MyConnection =

    New System.Data.OleDb.OleDbConnection( _

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

    OpenFileDialog1.FileName &

    ";Extended Properties=""Excel 8.0;HDR=YES;""")

    Catch ex As Exception

    MessageBox.Show(ex.Message)

    Exit Sub

    End Try

    'Select the data from Sheet1 of the workbook.

    MyCommand =

    New System.Data.OleDb.OleDbDataAdapter( _

    "select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10 from [Main Price List$]", MyConnection)

     

    DS =

    New System.Data.DataSet()

    DT = DS.Tables.Add(

    "DataTable")

    MyCommand.Fill(DT)

    Try

    dtgImport.SetDataBinding(DT,

    "")

    Call FormatTablesGrid(DT)

    Catch ex As Exception

    'Handling the Exceptions

    MessageBox.Show(ex.Message)

     

    End Try

    MyConnection.Close()

    Private Sub FormatTablesGrid(ByVal dt2format As DataTable)

    Dim gs As DataGridTableStyle = New DataGridTableStyle()

    gs.MappingName = dt2format.TableName

    Dim cs As DataGridColumnStyle = New DataGridTextBoxColumn()

    With cs

    .MappingName =

    "TABLE_NAME"

    .HeaderText =

    "WorkSheet Name"

    .Width = 200

    End With

    gs.GridColumnStyles.Add(cs)

    DataGrid1.TableStyles.Add(gs)

    End Sub

  • 11 years ago

    I would suggest that you add a TabControl to your form at design time, then just add the TabPages at run time.  The smartest thing to do would be to create your own class that inherits TabPage.  You would put all the logic in it to display whatever controls you want, then give it a constructor with a DataTable argument.  The class itself would set its Text property based on the name of this DataTable and it would display the data.  Then all you have to do is create an instance of this class by passing it a DataTable and add that to your TabControl:

    myTabControl.TabPages.Add(New ExtendedTabPage(myDataTable))



  • 11 years ago

    Thanks,

    Im not sure how to do this. Do you have sample code to help me..........?

  • 11 years ago

    Friend Class ExtendedTabPage
        Inherits TabPage

        Public Sub New(ByVal table As DataTable)
            Me.Text = table.TableName

            'Add controls here, such as a grid.
        End Sub
    End Class









    Plus whatever else you need.

  • 11 years ago

    Thanks for the help........

    I really appreciate it. Thank God for this website.

  • 11 years ago

    This does not want to work.Am I doing something wrong please help..........

    This is the full code.I have a DataGrid (DataGrid1) and TabControl (myTabControl) on the form.

    Imports

    System

    Imports

    System.Data

    Imports

    System.Data.OleDb

    Public

    Class Form1

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Dim schemaTable As New DataTable

    Dim workAdapter As New OleDbDataAdapter

    Dim workSet As New DataSet

    Dim conn As New OleDbConnection

    Dim i As Integer

    Dim x As Integer

    Dim charArray As Char() = {",", " "}

    Dim charArray2 As Char() = {"$"}

    Dim cmdString As String

    Dim cmdString2 As String

    Dim cmd As New OleDbCommand

    Dim tableName As String

    workSet.DataSetName =

    "excelData"

     

    Dim OpenFileDialog1 As New OpenFileDialog

    OpenFileDialog1.InitialDirectory =

    My.Computer.FileSystem.SpecialDirectories.MyDocuments

    'OpenFileDialog1.FileName = frmSupplierImport.cmbSupplierName.Text

    OpenFileDialog1.Filter =

    "Excel Files (*.xls)|*.xls|(*.txt)|*.txt|All Files (*.*)|*.*"

    If (OpenFileDialog1.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then

    End If

    Try

    ' Set the connection string.

    Dim connString As String = _

    "Data Source=" & OpenFileDialog1.FileName & _

    ";Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Extended Properties=Excel 8.0"

    ' Open the connection.

    conn.ConnectionString = connString

    conn.Open()

    ' Populate the DataTable with schema

    ' information on the data source tables.

    schemaTable = _

    conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _

    New Object() {Nothing, Nothing, Nothing, "TABLE"})

     

    ' Populate an array with the table names.

    i = schemaTable.Rows.Count - 1

    Dim tablesArray(i) As String

    For i = 0 To schemaTable.Rows.Count - 1

    tablesArray(i) = schemaTable.Rows(i).Item(

    "Table_Name")

    Next

    DataGrid1.DataSource = schemaTable

    Call FormatTablesGrid(schemaTable)

    myTabControl.TabPages.Add(

    New ExtendedTabPage(schemaTable))

    ' Clear the DataTable

    'schemaTable.Clear()

    ' Use the table names and the column schema

    ' information to construct SELECT statements

    ' and return data for each table in the data source.

    For i = 0 To tablesArray.GetLength(0) - 1

    ' Populate the DataTable with schema

    ' information on the data source columns.

    schemaTable = _

    conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _

    New Object() {Nothing, Nothing, _

    tablesArray(i).ToString(),

    Nothing})

    ' Step through the column names and append

    ' them into a SELECT statement

    cmdString =

    "SELECT "

    For x = 0 To schemaTable.Rows.Count - 1

    cmdString = cmdString & _

    schemaTable.Rows(x).Item(

    "Column_Name") & _

    ", "

    Next

    cmdString2 = cmdString.TrimEnd(charArray)

    ' Note that you don't need to append "$" to

    ' the table name - it is already included.

    cmdString2 = cmdString2 &

    " FROM [" & _

    tablesArray(i).ToString() &

    "]"

    ' Use the SELECT command and the

    ' OleDbDataAdapter to fill the DataSet.

    cmd.CommandText = cmdString2

    workAdapter.SelectCommand = cmd

    workAdapter.SelectCommand.Connection = conn

    tableName = _

    tablesArray(i).ToString().TrimEnd(charArray2)

    workAdapter.Fill(workSet, tableName)

    DataGrid1.DataSource = workSet.Tables

    schemaTable.Clear()

    Next

    Catch ex As Exception

    'Error handling

    Finally

    conn.Close()

    End Try

     

    End Sub 'GetExcelSchema

    Private Sub FormatTablesGrid(ByVal dt2format As DataTable)

    Dim gs As DataGridTableStyle = New DataGridTableStyle()

    gs.MappingName = dt2format.TableName

    Dim cs As DataGridColumnStyle = New DataGridTextBoxColumn()

    With cs

    .MappingName =

    "TABLE_NAME"

    .HeaderText =

    "WorkSheet Name"

    .Width = 200

    End With

    gs.GridColumnStyles.Add(cs)

    DataGrid1.TableStyles.Add(gs)

     

    End Sub

    & this in a class named ExtenededTabPage

    Public

    Class ExtendedTabPage

    Inherits TabPage

    Public Sub New(ByVal dt2format As DataTable)

    Me.Text = dt2format.TableName

    Dim DataGrid1 = New DataGrid

    Dim gs As DataGridTableStyle = New DataGridTableStyle()

    gs.MappingName = dt2format.TableName

    Dim cs As DataGridColumnStyle = New DataGridTextBoxColumn()

    With cs

    .MappingName =

    "TABLE_NAME"

    .HeaderText =

    "WorkSheet Name"

    .Width = 200

    End With

    gs.GridColumnStyles.Add(cs)

    DataGrid1.TableStyles.Add(gs)

    End Sub

    End

    Class

    Thanks for all your assistance. It means a great deal to me..............!!

  • 11 years ago
    I'm afraid that you're going to have to be a bit more specific than "this doesn't work".

  • 11 years ago

    Sorry for the late reply.......

    It only creates a new tabPage on the TabControl named Table1.

  • 11 years ago

    So what exactly is the issue?  Are you saying it should add more TabPages and doesn't or that it should put some controls on the TabPage and doesn't.  You need to be specific because we don't know what you're doing or what you're thinking.  If your issue is that you can't see the DataGrid that's because you never Add it to the Controls collection of the TabPage.

  • 11 years ago

    Yip, It should add TabPages based on the sheets in an excel doc. But it adds a TabPage names Table 1

  • 11 years ago

    Well there's only one line in your code that adds a TabPage and it's not in a loop so how could it add more than one page?  Also, you're passing the schema table from the connection, which is a DataTable that contains data about the tables in the database, not the data in those tables.

  • 11 years ago

    I understand what you saying. What table must I call in this

    myTabControl.TabPages.Add(

    New ExtendedTabPage(schemaTable))

    The loop should be based on the columns of that table? am I correct ?

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Programs must be written for people to read, and only incidentally for machines to execute.”