how to insert multiple value from multiple textbox into a single field

[pri.chakraborty]

hello sir, i am new in vb.net.. i am making a project on railway reservation.. in train booking page, there are multiple textbox in my project to insert multiple passenger name for reservation.. i want to book train in one click... so, i have to insert all the passenger name in a sinle field of a table, but in multiple rows of the field.. i don't know what will be the code to insert multiple record in a single fild at a time.. can anyone pls help me??
thanks in advance...

[ddanbe]

Do you mean string concatenation?

[Reverend Jim]

A typical statement to insert multiple rows at a time looks like

INSERT INTO table (fldname1, fldname2, fldname3)
       VALUES(val1, val2, val3),
             (val4, val5, val6),
             (val7, val8, val9)

Just modify for your particular database columns.

[pri.chakraborty]

i have just one field named "name" in the table named "table1" and three textboxes in my form...

i wrote this code

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Rose\My Documents\dd.mdb")
        cn.Open()
        str = "INSERT INTO table1 (name)VALUES('" & TextBox1.Text & "'),('" & TextBox2.Text & "'),('" & TextBox3.Text & "');"
        cmd = New OleDbCommand(str, cn)
        icount = cmd.ExecuteNonQuery
    End Sub

but it is showing "Missing semicolon (;) at end of SQL statement." in this line

icount = cmd.ExecuteNonQuery

pls help

[Reverend Jim]

I can't imagine why it is complaining about a missing semi-colon, especially since it isn't necessary to have one in a single line statement. Try omitting it completely. As a tip, try formatting the statement as

str = "INSERT INTO table1 (name)" &
      " VALUES('" & TextBox1.Text & "')," &
             "('" & TextBox2.Text & "')," &
             "('" & TextBox3.Text & "')"

I find that it makes it easier to read and debug. And for even better (safer and cleaner) code you should use Parameterized Queries.

[ryanjayson]

Maybe the problem is, you have 1 Column in your Insert Statement and 3 Values to insert on our table.

  str = "INSERT INTO table1 (name)VALUES('" & TextBox1.Text & "'),('" & TextBox2.Text & "'),('" & TextBox3.Text & "');"

try to change to:

str = "INSERT INTO table1 (name) VALUES('" & TextBox1.Text & "," & TextBox2.Text & "," & TextBox3.Text & "');"

the generated Insert statement will be:
INSERT INTO table1 (name) VALUES ('name1,name2,name3');

Is that what your tying to do? or insert the names to table rows? then use looping to do so:

   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Rose\My Documents\dd.mdb")
            cn.Open()

            Dim arrayName() As String = {TextBox1.Text, TextBox2.Text, TextBox3.Text}

            For Each name As String In arrayName
                str = "INSERT INTO table1 (name) VALUES('" & name & "');"
                cmd = New OleDbCommand(str, cn)
                icount = cmd.ExecuteNonQuery
            Next
            cn.close()
        End Sub

[Reverend Jim]

He said that the table contains only one column so

str = "INSERT INTO table1 (name) VALUES('" & TextBox1.Text & "," & TextBox2.Text & "," & TextBox3.Text & "');"

would not work because you have to have the same number of values as you have fields in "(name)".

[pri.chakraborty]

thanks sir.. it works... :D.. thanks a lott...

[Danilo_4]

good noon sir/mam
i hava a problem in saving data using textbox can you help me about it? please i really need it for my project

[Reverend Jim]

Please start a new thread for your question. Please provide more information than "Code broke. Please help" if you want a meaningful answer.