ado.net and tabControl(pls help !!)

• • Reply

  15 years ago

  by u2envy1

  Krivahn Doss

  , South Africa

  Joined 15 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

  Report abuse
• • Reply

  15 years ago

  by jmcilhinney

  John McIlhinney

  Sydney, Australia

  Joined 15 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))

  
  
  
  

  Report abuse
• • Reply

  15 years ago

  by u2envy1

  Krivahn Doss

  , South Africa

  Joined 15 years ago

  Thanks,

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

  Report abuse
• • Reply

  15 years ago

  by jmcilhinney

  John McIlhinney

  Sydney, Australia

  Joined 15 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.

  Report abuse
• • Reply

  15 years ago

  by u2envy1

  Krivahn Doss

  , South Africa

  Joined 15 years ago

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

  I really appreciate it. Thank God for this website.

  Report abuse
• • Reply

  15 years ago

  by u2envy1

  Krivahn Doss

  , South Africa

  Joined 15 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..............!!

  Report abuse
• • Reply

  15 years ago

  by jmcilhinney

  John McIlhinney

  Sydney, Australia

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

  15 years ago

  by u2envy1

  Krivahn Doss

  , South Africa

  Joined 15 years ago

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

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

  Report abuse
• • Reply

  15 years ago

  by jmcilhinney

  John McIlhinney

  Sydney, Australia

  Joined 15 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.
  
  

  Report abuse
• • Reply

  15 years ago

  by u2envy1

  Krivahn Doss

  , South Africa

  Joined 15 years ago

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

  Report abuse
• • Reply

  15 years ago

  by jmcilhinney

  John McIlhinney

  Sydney, Australia

  Joined 15 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.
  
  

  Report abuse
• • Reply

  15 years ago

  by u2envy1

  Krivahn Doss

  , South Africa

  Joined 15 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 ?

  Report abuse