how can i add parameter to insert command in vb.net code

[ebrahim]

hi all

I have two tables and the form contains a text box so I need to add rows of Table 1 to Table 2 and taking the value in the text box for each row is added to Table 2.

i have insert syntax but it is need to modification:

INSERT INTO table2(column3) SELECT column1 @parameter FROM table1 WHERE column2=true

table1: column1 column2

table2: column3

Form1: it have textbox1

the full code :

  Dim con As New OleDbConnection
    Dim cmd As New OleDbCommand
    Try
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\DellXPS\Desktop\mDB.accdb"

        con.Open()
        cmd.Connection = con
        cmd.CommandText = "INSERT INTO table2(column3) SELECT column1 @parameter FROM table1 WHERE column2=true"
        
        cmd.Parameters.Add("@parameter",  OleDbType.VarChar).Value = TextBox1.Text
        cmd.ExecuteNonQuery()
    Catch ex As Exception
        MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
    Finally
        con.Close()
    End Try

i think the wrong in insert syntax ,, hop to corrected

[jmcilhinney]

You're adding the parameter OK, although sub-optimally, but your SQL code doesn't make sense. You are getting two values using your SELECT statement yet you are only using one value in your INSERT statement. There have to be the same number of target columns as there are source values.

Also, you have to use commas to separate your column/value list in your SELECT statement.

[ebrahim]

yes sir jmcilhinney i forget 1 column when i write my question ,, see it Again plz Because the problem is still put I am optimistic because you are a professional

INSERT INTO table2(column3,column4) SELECT column1 + @parameter FROM table1 WHERE column2=true

table1: column1 column2

table2: column3 column4

Form1: it have textbox1

the full code :

  Dim con As New OleDbConnection
    Dim cmd As New OleDbCommand
    Try
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\DellXPS\Desktop\mDB.accdb"

        con.Open()
        cmd.Connection = con
        cmd.CommandText = "INSERT INTO table2(column3,column4) SELECT column1 + @parameter FROM table1 WHERE column2=true"
        
        cmd.Parameters.Add("@parameter",  OleDbType.VarChar).Value = TextBox1.Text
        cmd.ExecuteNonQuery()
    Catch ex As Exception
        MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
    Finally
        con.Close()
    End Try

i think the wrong in insert syntax ,, hop to corrected

[jmcilhinney]

Now you've done the complete opposite. First you had two source values and one target column and now you have one source value and two target columns. I said:

Also, you have to use commas to separate your column/value list in your SELECT statement.

Is that what you did?

[ebrahim]

sir jmcilhinney i have tow source value in my code it is :
column1 and @parameter
The first value are retrieved from table1
The second value are retrieved from TextBox on Form and i reference it by @parameter
if i write code wrong plz corrected

[jmcilhinney]

I already told you what to do so how about you read what I posted and correct it yourself? I said this:

Also, you have to use commas to separate your column/value list in your SELECT statement.

You did this:

"INSERT INTO table2(column3,column4) SELECT column1 + @parameter FROM table1 WHERE column2=true"

Is that a comma?

[ebrahim]

sorry my Language is Arabic and I Do not speak English well ,, so u mean

"INSERT INTO table2(column3,column4) SELECT column1, @parameter FROM table1 WHERE column2=true"

is it right now ?

be patience

[ebrahim]

yes Mr jmcilhinney i Succeeded thank you very much man

[jmcilhinney]

I second the motion.

[ebrahim]

....