asked on

Insert multiple rows of data into an sql database using VB.NET

Hi Everyone,

I currently have a web form which allows a user to input multiple data entries by selecting a "+" button and adding an additional textbox for each additional entry. I would like to insert these entries as individual rows into the database table.

So for instance. user enters 2 entries

customer name: bobby
phone number: 123-456-7890 (presses "+" button to insert additional phone number)
phone number 2: 032-165-4987

I would like this record to show up in the database like this:

1. Bobby / 123-456-7890
2. Bobby / 032-165-4987

Here is my current code to insert data into the database (this only inserts the first record)

Dim ConnectionString As String
        Dim Connection As SqlConnection
        Dim Adapter As New SqlDataAdapter
        Dim sql As String

        ConnectionString = "Data Source=00.0.000.00;Initial Catalog=Database;User ID=user;pwd=pwd
        Connection = New SqlConnection(ConnectionString)

        sql = "insert into table(field1, field2, field3, field4, field5) VALUES ('" & tbpbrid.Text & "', '" & tbconvo.Text & "', '" & tbcn1.Text & "', '" & tbcp1.Text & "', '" & tbcif1.Text & "');"

        Try
            Connection.Open()
            Adapter.InsertCommand = New SqlCommand(sql, Connection)
            Adapter.InsertCommand.ExecuteNonQuery()
            Response.Write("<span style='color: green'>Data Submitted</span>")


        Catch ex As Exception
            Response.Write("<span style='color: red>Data Not Submitted</span>")
        End Try

Open in new window

Can someone please help me with the code? Thanks!

HainKurt

loop all phones and run it multiple times, something like this:

Connection.Open()
for each phone as ListItem in dbPhones.Items
sql = "insert into ...." & phone.text & "...."
Adapter.InsertCommand = New SqlCommand(sql, Connection)
Adapter.InsertCommand.ExecuteNonQuery()
next
Connection.Close()

sarah_siu

ASKER

Hi HainKurt,

Thanks for the quick reply!

Sorry, but i'm not sure what you mean by loop all phones?

HainKurt

where is your phone numbers stored?
how do you add multiple phone numbers? do you have multiple text boxes with different id/name?

Itpotter

You can use table values parameter
check this link for detail
http://www.codeproject.com/KB/cs/CSharpAndTableValueParams.aspx

also make sure the order of colomn defined in user defined table type in sql server is same when u create table in code.

sarah_siu

ASKER

My phone numbers will be stored in a MS SQL Database under a column named phone numbers. Each additional phone number is a different textbox id/name.

Itpotter

you can create rows as you want

HainKurt

you have a web form and a button "+" which adds new text boxes and user puts additional phone numbers...
is it dynamic? can you add say 10 phone numbers? or is it limited to 2 or 3, and + button just shows some hidden text boxes (not creating dynamic text boxes on the fly on client browser)?
either case, what are the names / id's of these text boxes?

in your query

sql = "insert into table(field1, field2, field3, field4, field5) VALUES ('" & tbpbrid.Text & "', '" & tbconvo.Text & "', '" & tbcn1.Text & "', '" & tbcp1.Text & "', '" & tbcif1.Text & "');"

which one is phone field? which text box store phone text user enters?

sarah_siu

ASKER

Hi HainKurt,

The phone number is just an example to illustrate what i was looking for. In reality, I am capturing customer name and phone number (these will appear together in one line when the + sign is pushed)

My actual code looks like this:

sql = "insert into table (Agent_Id, lead_name, lead_phone) VALUES ('" & tbagentid.text & "','" & tbcn1.Text & "', '" & tbcp1.Text & "');"

is what it really looks like right now - this inserts only one row of data into my table.

The + sign just shows HIDDEN textboxes (they are not being created on the fly) and i've only allowed 5 lines maximum (each line will have lead_name and lead_phone.

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sarah_siu

ASKER

Exactly what i was looking for =) thanks!