CODEWITHSUNDEEP
sqlvb.net
redned
redned

Reputation: 301

How to insert selected items from a listview into a database

I have a number of textboxes and 4 listviews. Each listview contains a number of options. The user enters in some data to the textboxes and selects any number of options from the listviews. All of this is then inserted into a row in a database. At the moment I am gathering all selected options from each listview and joining them together in a String, one string for each list and inserting the concatenated string.

    'retrieve reference type 1s selected
    For i = 0 To listRef3.CheckedItems.Count - 1
        selectedRef3.Add(listRef3.CheckedItems(i).Text)
    Next
    'join array contents into one delimited string to be added to the database
    refType3 = String.Join(",", selectedRef3.ToArray())

However this makes retrieval of individual selections from the listview tricky as I would like to be able to filter a different database based on the selections at a later date.

Is it possible to create an insert statement that will add each listview selection to a particular column in the database dynamically as the user may select any number of items from the list?

Upvotes: 1

Views: 1352

Answers (1)

dan1111
dan1111

Reputation: 6566

You should be able to construct the needed statement by building on your existing join statement just a little bit (I assume the text you are getting from your checked items is like "ref1", etc. Have I understood you correctly?).

Dim cols, vals, insertStatement As String

cols = String.Join(", ", selectedRef3.ToArray())
vals = "@" & String.Join(", @", selectedRef3.ToArray())

insertStatement = "insert into ListTable (" & cols & ") VALUES (" & vals & ")"

Apologies if there are any mistakes; my VB is a bit rusty.

Upvotes: 1

Related Questions