Click here to Skip to main content
15,886,362 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, I want the List Box values stored in the sql database. So I have code.
VB
Dim DR As SqlDataReader
Dim con As SqlConnection
Dim cmd As SqlCommand
con = New SqlConnection("Data Source=LOCALhost;Initial Catalog=InwardOutwardSystem;Integrated Security=True")
con.Open()
Dim l As String
Dim i As Integer

For i = 0 To ListBox1.Items.Count Step 1
   l = "insert into TransactionCirculation(ForUserId)values('" & ListBox1.Items(i) & "')"
   cmd = New SqlCommand(l, con)
Next

DR = cmd.ExecuteReader(CommandBehavior.CloseConnection)
MsgBox("Data is successfully saved")

This code is in vb.net. In this I'm use the for next loop, but have a error in "for" condition line. Please give me suggestions. Thanks.
Posted
Updated 6-Jul-12 4:55am
v3
Comments
[no name] 6-Jul-12 10:17am    
Out of the thousands of errors that it could possibly be, you expect us to guess what the error is?

The only error in the For statement that I can see is that it does one too many iterations:
0 to ListBox1.Items.Count inclusive.
Try
VB
For i = 0 To ListBox1.Items.Count - 1 Step 1
Instead.
 
Share this answer
 
Comments
Tim Corey 6-Jul-12 11:06am    
Yep. +5
you can iterate though the items using for each loop easily.
use meaning full variable names.

VB
Dim query  As String
For Each item As String In listBox1.Items
     query   = "insert into TransactionCirculation(ForUserId)values('" & item  & "')"
     cmd = New SqlCommand(query  , con)
 
Next


you need to use executenonquery method, not the ExecuteReader, and also for each item you need to run
executenonquery, that means run executenonquery inside the loop. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx[^]
 
Share this answer
 
v3
Comments
Tim Corey 6-Jul-12 11:06am    
You would use ExecuteScalar here? Since this is an INSERT, I went with ExecuteNonQuery. Still, +5 for the good catch.
DamithSL 6-Jul-12 11:11am    
yes, updated answer, thanks for pointing out.
I see a few errors in this statement. First, as OriginalGriff pointed out, your For loop has an "index out of bounds" error. Next, you are creating a new SqlCommand for each item in your For loop, effectively overwriting the previous entries. You need to put the call to the database inside your For loop. Finally, you are using the ExecuteReader command when making an INSERT call. You should instead be using the ExecuteNonQuery[^] method, which is designed for INSERT statements.

Once you get through all of that, I think you need to look at your code design as well. There are a few issues that I think you should address. First, you use letters as variable names. This is not descriptive and it can lead to confusion later on, which can lead to the introduction of bugs into your application. Next, you are doing one insert per row. I'm not sure how big your list is, but that can be anything from inefficient to catastrophic. Instead, look at doing one INSERT statement that puts all of the values in at once. The actual SQL code would end up looking like this:
SQL
INSERT INTO Table(column)
SELECT ListItemOne
UNION ALL
SELECT ListItemTwo
UNION ALL
etc.

You would need to modify your loop to build a SQL statement like this, but the end result is one call to the database instead of multiple.
 
Share this answer
 
Comments
DamithSL 6-Jul-12 11:13am    
5+ for better design

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900