Insert into two tables using two comboboxes

[david081]

Hi
I was hoping someone could offer me some help please
I have a form with two comboboxes ,one text box and a button .I have multiple tables in ms access database with same structure , col1 and col2 .
I want to insert text box value into column1 in the table selected from combobox1 and into column2 in table selected from combobox2
such as if i select table1 from combobox1 and table2 from combobox2 and click on the button, data should be inserted into both table1 and table2.
when i run the code it , i dont get any error but it does not load the tables in comboboxes . can someone please examine the code
Thanks in advance

Imports System.Data.OleDb

Partial Public Class Form1
    Inherits Form
    Public Sub New()
        InitializeComponent()
    End Sub
    Private conn As OleDbConnection
    Private sda As OleDbDataAdapter
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
        conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\dtb.accdb;Jet OLEDB:Database Password=****")
        sda = New OleDbDataAdapter([String].Empty, conn)
        conn.Open()

        ComboBox2.DataSource = conn.GetSchema("TABLES")
        ComboBox2.ValueMember = "TABLE_NAME"
        ComboBox2.DisplayMember = "TABLE_NAME"

        ComboBox1.DataSource = conn.GetSchema("TABLES")
        ComboBox1.ValueMember = "TABLE_NAME"
        ComboBox1.DisplayMember = "TABLE_NAME"
    End Sub

    Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs)
        If ComboBox1.SelectedItem IsNot Nothing AndAlso ComboBox2.SelectedItem IsNot Nothing AndAlso TextBox1.Text IsNot Nothing Then
            Dim cmd As OleDbCommand = conn.CreateCommand()
            cmd.CommandText = ("insert into " + ComboBox1.Text & "(studName) values('") + TextBox1.Text & "')"
            sda.InsertCommand = cmd
            sda.InsertCommand.ExecuteNonQuery()
            cmd.CommandText = ("insert into " + ComboBox2.Text & "(studName) values('") + TextBox1.Text & "')"
            sda.InsertCommand = cmd
            sda.InsertCommand.ExecuteNonQuery()
            sda.Dispose()
            conn.Close()
            conn.Dispose()
        End If
    End Sub
End Class

[Oxiegen]

Try changing conn.GetSchema("TABLES") into this: conn.GetSchema("Tables", New String() {Nothing, Nothing, "TABLE"} And skip setting the ValueMember and DisplayMember properties.

Solution provided by this thread.

[david081]

Hi
thanks for your response,
I have one more question about insert command, is it possible to write a command text that does not mention table name (studName) so that
the inserted value goes into the table selected from combobox ,not into the mentioned table in command text?

[Oxiegen]

Yes it is.
But you still need to explicitly tell the query which field to use.

cmd.CommandText = "INSERT INTO " & combobox.SelectedItem & " (<field>) VALUES ('" & textbox.Text & "')"

[david081]

hi
I can get it to work now .I am grateful to you
Thank you for your help