Loop through ListBox items and save them to database?

[savedlema]

Hi all!

I have a form with a combobox, a listbox and 2 command buttons.(see photo and neglect the blank listview)

I select items from the combobox and add items to my listbox after clicking the button.(no problem with this)

Then, I need to save each item of the ListView to my database. There can be more than one item in the Listbox,depending on how many were added by the user. I was thinking of a way to loop through the items and add each one, and stop adding when all have been saved to a database.

I tried this,

For x = 0 To ListBox1.Items.Count - 1
            'For Each item As String In ListBox1.Items

            cmd.Parameters.Clear()
            sqlSaveAdd = "INSERT INTO lab_req ( @req_test)"        
            cmd.Parameters.AddWithValue("@req_test", ListBox1.Items)

            cmd.CommandText = sqlSaveAdd
            cmd.ExecuteNonQuery()
        Next

Upon running this, it will say data is added but when I check the db, this is what I find "System.Windows.Forms.ListBox+ObjectCollection" saved there.

I also had triend looping with

For Each item As String In ListBox1.Items

                'For Each item As String In ListBox1.Items

                cmd.Parameters.Clear()
                sqlSaveAdd = "INSERT INTO lab_req ( @req_test)"        
                cmd.Parameters.AddWithValue("@req_test", ListBox1.Items)

                cmd.CommandText = sqlSaveAdd
                cmd.ExecuteNonQuery()
            Next

With the same result; "System.Windows.Forms.ListBox+ObjectCollection" is posted to the db instead of the items.

*I have ommited some codes (like connection settings, no problem with it, also some other items which are saved together with listiview items in other columns)

I will appreciate any help please.
Thanks and peace to you.

[Reverend Jim]

The syntax for INSERT is

INSERT INTO tablename (fld1, fld2, ...)
       VALUES(val1, val2, ...)

where you specify the field names by fld1, fld2, etc. and the values by val1, val2, etc.

For a complete example of how to use parameterized queries please see Code Snippets.

[djjeavons]

Hi

With the same result; "System.Windows.Forms.ListBox+ObjectCollection"

Along with the above suggestion (although it may be valid if you only have one column in your table, but you still need the VALUES keyword), you are also using ListBox1.Items which is an ObjectCollection hence the reason for getting this string added to your database. What you actually want to do is read the item variable that you have declared in your For loop which will return the text of each individual item in the list box.

HTH

[savedlema]

Thanks Reverend and djjeavons
I'm sorry, when I was posting the question I had to strip some code off so as to be straight to the questions and unfortunately removed the "VALUES" keyword. My sql statement is like this:

sqlSaveAdd = "INSERT INTO lab_req (req_test) VALUES (@req_test)" 
                cmd.Parameters.AddWithValue("@req_test", ListBox1.Items)

As I said, no problem with this part, the problem I think is with reading the items and adding them to the database. I still haven't found out. Can someone help please.

Thanks.

[djjeavons]

It is the line:

cmd.Parameters.AddWithValue("@req_test", ListBox1.Items)

You are using ListBox1.Items which is an ObjectCollection and as this inherits from System.Object, it's ToString method is being called which results in the string "System.Windows.Forms.ListBox+ObjectCollection".

Instead, use

cmd.Parameters.AddWithValue("@req_test", Item)

Assuming you are still using the For Each Item As String In ListBox1.Items loop.

[savedlema]

Thank you, I was able to solve this by considering djjeavons comment and changing "ListBox1.Items" to "item"

Question solved.