5 Replies - 3625 Views - Last Post: 18 March 2013 - 12:14 PM Rate Topic: ***** 1 Votes

#1 nilesh7136  Icon User is offline

  • D.I.C Head

Reputation: 10
  • View blog
  • Posts: 120
  • Joined: 03-January 13

Add Multiple Value in DB From List Box

Posted 18 March 2013 - 11:36 AM

Hello,

can i add multiple value in database from the ListBox in one click??
i have one list box and some text box, i want to insert all the item of listbox to database one by one, so how to write that code???

Thank You in adv,,
@Nilesh
Is This A Good Question/Topic? 0
  • +

Replies To: Add Multiple Value in DB From List Box

#2 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Add Multiple Value in DB From List Box

Posted 18 March 2013 - 11:47 AM

One option would be to set up your connection to the database and create an INSERT statement (with a parameter would be preferable)
Create a loop to get the items(index) of the list and use that to fill your parameter. then inside the loop, execute the statement with ExecuteNonQuery.

define connection
create INSERT Statement
Build a Command
Open Database
For x as integer = 0 to listbox.items.count -1
  yourcommand.Parameters.AddWithValue("@parm", listbox.items(x).ToString)
  yourcommand.executenonquery
Next
Close Database


Was This Post Helpful? 1
  • +
  • -

#3 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3371
  • View blog
  • Posts: 11,420
  • Joined: 12-December 12

Re: Add Multiple Value in DB From List Box

Posted 18 March 2013 - 11:50 AM

You need to clear the parameters each time within the loop:

yourcommand.Parameters.Clear()

Was This Post Helpful? 2
  • +
  • -

#4 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Add Multiple Value in DB From List Box

Posted 18 March 2013 - 11:52 AM

good catch andrewsw
Was This Post Helpful? 1
  • +
  • -

#5 nilesh7136  Icon User is offline

  • D.I.C Head

Reputation: 10
  • View blog
  • Posts: 120
  • Joined: 03-January 13

Re: Add Multiple Value in DB From List Box

Posted 18 March 2013 - 12:05 PM

View PostCharlieMay, on 18 March 2013 - 11:47 AM, said:

One option would be to set up your connection to the database and create an INSERT statement (with a parameter would be preferable)
Create a loop to get the items(index) of the list and use that to fill your parameter. then inside the loop, execute the statement with ExecuteNonQuery.

define connection
create INSERT Statement
Build a Command
Open Database
For x as integer = 0 to listbox.items.count -1
  yourcommand.Parameters.AddWithValue("@parm", listbox.items(x).ToString)
  yourcommand.executenonquery
Next
Close Database



thanks its done,,, :)
 Dim j As Integer
            For j = 0 To ListBoxSizeOfPriceListing.Items.Count - 1
                Dim cmd As New SqlCommand("INSERT INTO tblHosePriceList VALUES('" & txtidofPrice.Text & "','" & txtBrandOfPrice.Text & "','" & ListBoxSizeOfPriceListing.Items(j) & "','" & ListBoxPriceOfPriceListing.Items(j) & "')", conn)
                conn.Open()
                cmd.ExecuteNonQuery()
                conn.Close()
                txtidofPrice.Text = txtidofPrice.Text + 1
                RefreshPriceList()
            Next


Thank u so mch,,
@nilesh
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3371
  • View blog
  • Posts: 11,420
  • Joined: 12-December 12

Re: Add Multiple Value in DB From List Box

Posted 18 March 2013 - 12:14 PM

You don't need to keep opening and closing the connection within the loop; refer back to CharlieMay 's code-outline.

You should also store txtBrandOfPrice.Text as a variable - once - as it doesn't change, rather than reading it again each time.

You are also using txtBrandOfPrice.Text as though it were a variable. Store its value once, and update it once when the loop has finished.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1