• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 617
  • Last Modified:

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!
0
sarah_siu
Asked:
sarah_siu
  • 4
  • 4
  • 2
1 Solution
 
HainKurtSr. System AnalystCommented:
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()
0
 
sarah_siuAuthor Commented:
Hi HainKurt,

Thanks for the quick reply!

Sorry, but i'm not sure what you mean by loop all phones?  
0
 
HainKurtSr. System AnalystCommented:
where is your phone numbers stored?
how do you add multiple phone numbers? do you have multiple text boxes with different id/name?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
ItpotterCommented:
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.
0
 
sarah_siuAuthor Commented:
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.
0
 
ItpotterCommented:
you can create rows as you want
0
 
HainKurtSr. System AnalystCommented:
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?
0
 
sarah_siuAuthor Commented:
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.  
0
 
HainKurtSr. System AnalystCommented:
then repeat it 5 times :


Connection.Open()
  sql = "insert into table (...) VALUES ('" & tbagentid.text & "','" & tbcn1.Text & "', '" & tbcp1.Text & "');" 
  Adapter.InsertCommand = New SqlCommand(sql, Connection)
  Adapter.InsertCommand.ExecuteNonQuery()

  if tbcp2.Text<>"" then
    sql = "insert into table (...) VALUES ('" & tbagentid.text & "','" & tbcn2.Text & "', '" & tbcp2.Text & "');" 
    Adapter.InsertCommand = New SqlCommand(sql, Connection)
    Adapter.InsertCommand.ExecuteNonQuery()
  end if

...

  if tbcp5.Text<>"" then
    sql = "insert into table (...) VALUES ('" & tbagentid.text & "','" & tbcn5.Text & "', '" & tbcp5.Text & "');" 
    Adapter.InsertCommand = New SqlCommand(sql, Connection)
    Adapter.InsertCommand.ExecuteNonQuery()
  end if

Connection.Close()

Open in new window

0
 
sarah_siuAuthor Commented:
Exactly what i was looking for =)  thanks!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now