none
Can I use one form and switch from two tables in the same database? RRS feed

  • Question

  • Hello,

    I am using Vs2005 (Visual Basic) on an XPSp2 machine.  I have a form that connects to an Access DB (created with the wizard).  The database has 2 tables in it with the same schema.  I'd like to have the form display the info from either table depending on user input.  Is this possible?  Also, I am a total novice at coding, so please answer in simple terms.

     

    Cheers!

    Monday, January 28, 2008 3:01 PM

Answers

  • Hi Dan

     

    Yes, it is possible for you to adapt the wizard generated code to allow you to switch between the tables. In order to have the dataGrid display contents for a particular table all you need to do is to change the DataSource property on the dataGrid to point to the DataTable object corresponding to the table you want to display.

     

    The following code illustrates how to do this. The code assumes that you are using buttons to allow users to specify the selection. I am adding listerner for the Button.Click event and then in the body of the method I am setting the DataSource property for the dataGrid object to the appropriate DataTable object. If you are using BindingSource, you can use the BindingSource object instead of the DataTable.

     

    Private Sub Contacts_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Me.DataGridView2.DataSource = Me.ContactsDataTable

    End Sub

     

    Private Sub Employee_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

    Me.DataGridView2.DataSource = Me.EmployeesDataTable

    End Sub

     

     

    Note that you can create the DataTable objects on the fly as well using the code below

     

    connection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=e:\deleteme\MultipleTables\MultipleTables\DB.mdb; User Id=admin; Password=")

    connection.Open()

    command = New OleDb.OleDbCommand()

    command.Connection = connection

     

    command.CommandText = "select * from " + tableName

    dataReader = command.ExecuteReader()

    dataTable = New DataTable()

    dataTable.Load(dataReader)

    Wednesday, January 30, 2008 1:27 AM

All replies

  • Hello

     

    Yes, it is possible to display the info from either tables depending the on the user input. I have created a small smaple which illustrates this and I have pasted the code for the sample below. You will need to create an access database with two tables named contacts and employees in it. Both the tables should have the same schema. Once you have created the access database, modify the connection string to point to your database and run the code. Let me know if you have any questions.

     

    Thanks

    Himanshu Vasishth [MSFT]

     

    Class Form1

    Inherits System.Windows.Forms.Form

    Friend connection As OleDb.OleDbConnection

    Friend command As OleDb.OleDbCommand

    Friend dataReader As OleDb.OleDbDataReader

    Friend dataTable As DataTable

    Private Sub ComboBox1_SelectedValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedValueChanged

    Dim tableName As String

    tableName = ComboBox1.SelectedItem.ToString()

    command.CommandText = "select * from " + tableName

    If Not (dataReader Is Nothing) Then

    dataReader.Close()

    End If

    dataReader = command.ExecuteReader()

    dataTable = New DataTable()

    dataTable.Load(dataReader)

    DataGridView1.DataSource = dataTable

    End Sub

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

    connection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=e:\deleteme\MultipleTables\MultipleTables\DB.mdb; User Id=admin; Password=")

    connection.Open()

    command = New OleDb.OleDbCommand()

    command.Connection = connection

    ComboBox1.SelectedItem = "Contacts"

    End Sub

     

    'Form overrides dispose to clean up the component list.

    <System.Diagnostics.DebuggerNonUserCode()> _

    Protected Overrides Sub Dispose(ByVal disposing As Boolean)

    Try

    If disposing AndAlso components IsNot Nothing Then

    components.Dispose()

    End If

    Finally

    MyBase.Dispose(disposing)

    End Try

    End Sub

    'Required by the Windows Form Designer

    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer

    'It can be modified using the Windows Form Designer.

    'Do not modify it using the code editor.

    <System.Diagnostics.DebuggerStepThrough()> _

    Private Sub InitializeComponent()

    Me.ComboBox1 = New System.Windows.Forms.ComboBox

    Me.Label1 = New System.Windows.Forms.Label

    Me.DataGridView1 = New System.Windows.Forms.DataGridView

    CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).BeginInit()

    Me.SuspendLayout()

    '

    'ComboBox1

    '

    Me.ComboBox1.FormattingEnabled = True

    Me.ComboBox1.Items.AddRange(New Object() {"Contacts", "Employees"})

    Me.ComboBox1.Location = New System.Drawing.Point(188, 23)

    Me.ComboBox1.Name = "ComboBox1"

    Me.ComboBox1.Size = New System.Drawing.Size(121, 21)

    Me.ComboBox1.TabIndex = 0

    '

    'Label1

    '

    Me.Label1.AutoSize = True

    Me.Label1.Location = New System.Drawing.Point(75, 30)

    Me.Label1.Name = "Label1"

    Me.Label1.Size = New System.Drawing.Size(65, 13)

    Me.Label1.TabIndex = 1

    Me.Label1.Text = "Table Name"

    '

    'DataGridView1

    '

    Me.DataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize

    Me.DataGridView1.Location = New System.Drawing.Point(12, 74)

    Me.DataGridView1.Name = "DataGridView1"

    Me.DataGridView1.Size = New System.Drawing.Size(502, 379)

    Me.DataGridView1.TabIndex = 2

    '

    'Form1

    '

    Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)

    Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font

    Me.ClientSize = New System.Drawing.Size(526, 465)

    Me.Controls.Add(Me.DataGridView1)

    Me.Controls.Add(Me.Label1)

    Me.Controls.Add(Me.ComboBox1)

    Me.Name = "Form1"

    Me.Text = "Form1"

    CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).EndInit()

    Me.ResumeLayout(False)

    Me.PerformLayout()

    End Sub

    Friend WithEvents ComboBox1 As System.Windows.Forms.ComboBox

    Friend WithEvents Label1 As System.Windows.Forms.Label

    Friend WithEvents DataGridView1 As System.Windows.Forms.DataGridView

    End Class

    Tuesday, January 29, 2008 8:01 AM
  • Himanshu,

    Thanks for the sample code.  It works well, but I already have my form created using the wizard.  There is a data grid and about 100 text boxes linked to the database.  Is there a way for me to adapt the wizard generated code to allow me to switch between the tables or do I need to recreate my form and manually code in the connections for each control?  Also, I need the functionality of the data navigator toolbar and the ability to add to the database.

     

    Thanks,

    Dan

    Tuesday, January 29, 2008 5:40 PM
  • Hi Dan

     

    Yes, it is possible for you to adapt the wizard generated code to allow you to switch between the tables. In order to have the dataGrid display contents for a particular table all you need to do is to change the DataSource property on the dataGrid to point to the DataTable object corresponding to the table you want to display.

     

    The following code illustrates how to do this. The code assumes that you are using buttons to allow users to specify the selection. I am adding listerner for the Button.Click event and then in the body of the method I am setting the DataSource property for the dataGrid object to the appropriate DataTable object. If you are using BindingSource, you can use the BindingSource object instead of the DataTable.

     

    Private Sub Contacts_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Me.DataGridView2.DataSource = Me.ContactsDataTable

    End Sub

     

    Private Sub Employee_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

    Me.DataGridView2.DataSource = Me.EmployeesDataTable

    End Sub

     

     

    Note that you can create the DataTable objects on the fly as well using the code below

     

    connection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=e:\deleteme\MultipleTables\MultipleTables\DB.mdb; User Id=admin; Password=")

    connection.Open()

    command = New OleDb.OleDbCommand()

    command.Connection = connection

     

    command.CommandText = "select * from " + tableName

    dataReader = command.ExecuteReader()

    dataTable = New DataTable()

    dataTable.Load(dataReader)

    Wednesday, January 30, 2008 1:27 AM
  • Hi Himanshu,

     Himanshu Vasishth wrote:

     

    'Form overrides dispose to clean up the component list.

    <System.Diagnostics.DebuggerNonUserCode()> _

    Protected Overrides Sub Dispose(ByVal disposing As Boolean)

    Try

    If disposing AndAlso components IsNot Nothing Then

    components.Dispose()

    End If

    Finally

    MyBase.Dispose(disposing)

    End Try

    End Sub

    'Required by the Windows Form Designer

    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer

    'It can be modified using the Windows Form Designer.

    'Do not modify it using the code editor.

     

    <System.Diagnostics.DebuggerStepThrough()> _

    Private Sub InitializeComponent()

    CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).BeginInit()

    Me.SuspendLayout()

    'Form1

    '

    CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).EndInit()

    Me.ResumeLayout(False)

    Me.PerformLayout()

    End Sub

    Friend WithEvents ComboBox1 As System.Windows.Forms.ComboBox

    Friend WithEvents Label1 As System.Windows.Forms.Label

    Friend WithEvents DataGridView1 As System.Windows.Forms.DataGridView

    End Class

    I liked the code. Would you please explain the quoted code a little bit. Seems it will teach some lessons to me.

    regards

    rajeev

    Wednesday, January 30, 2008 5:07 PM
  • Himanshu,

    That did the trick!  Thanks so much for your insight.

     

    Dan

    Thursday, January 31, 2008 3:00 PM
  •  

    Hi Rajeev

     

    The code that you are referring to was generated by the designer in Visual Studio. The following lines in the code override the Dispose method of the base class and disposes the components which were initilized in this class, and then calls base.Dispose to let the base class dispose off the resources it had allocated. In general it is a good practice to dispose components that your class had allocated.

     

    <System.Diagnostics.DebuggerNonUserCode()> _

    Protected Overrides Sub Dispose(ByVal disposing As Boolean)

    Try

    If disposing AndAlso components IsNot Nothing Then

    components.Dispose()

    End If

    Finally

    MyBase.Dispose(disposing)

    End Try

    End Sub

     

    In the following lines the dataGridView1 is being initialized. The layout is suspended while it is being initialized until the changes being made to dataGridView are complete.

     

    <System.Diagnostics.DebuggerStepThrough()> _

    Private Sub InitializeComponent()

    CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).BeginInit()

    Me.SuspendLayout()

    'Form1

    '

    CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).EndInit()

    Me.ResumeLayout(False)

    Me.PerformLayout()

    End Sub

     

    And the following lines are simply the declaration for member variables.

    Friend WithEvents ComboBox1 As System.Windows.Forms.ComboBox

    Friend WithEvents Label1 As System.Windows.Forms.Label

    Friend WithEvents DataGridView1 As System.Windows.Forms.DataGridView

     

    I would also recommend you to take a look at the msdn documentation for all of these.

    Friday, February 1, 2008 9:05 PM
  • Hi Himanshu,

    Thanks for the info..

    regards

    rajeev

     

    Saturday, February 2, 2008 4:15 AM