How do I insert into SQL table multiple selected ListBox values?

Question

1.00/5 (1 vote)

See more:

I'm a new programmer to VB.Net and having a bit of trouble - hoping you folks could help.

I have a listbox with a bunch of options. I am wanting to take whatever options are selected, grab the employee number logged in, and then sequentially place them in my table.

So far, it appears as if it's counting the items selected which is good and also it updates my SQL table for that amount, however it does not appear to be looping through to the next sequential item checked. I am unsure on how to get it to loop through as it is only capturing one unique value for the items that are checked at the moment.

Here is my below code so far:

VB

' INSERTS TERMINAL SELECTION INTO tblUserTerminals
        Dim QueryUserTerm As String

        QueryUserTerm = "INSERT INTO tblUserTerminals (UserXID, TerminalXID) VALUES ((@NewUserXID), (@TermXID))"

        For i As Integer = 0 To TerminalListBox.CheckedItems.Count - 1
            Dim commandsql = New SqlCommand(QueryUserTerm, con)
            commandsql.Parameters.AddWithValue("@TermXID", TerminalListBox.SelectedValue.ToString)
            commandsql.Parameters.AddWithValue("NewUserXID", NewUserXID.Text.ToString)
            commandsql.ExecuteNonQuery()
            commandsql.Parameters.Clear()
        Next

Posted 22-Apr-15 9:57am

kaniption

Updated 24-Apr-15 14:50pm

PIEBALDconsult

v6

Add a Solution

Comments

Maciej Los 22-Apr-15 16:19pm

Your code is sql injection vulnerable....
What's error message? Which line?

kaniption 23-Apr-15 13:34pm

I'm no longer getting an error message. Please see my updated code.

3 solutions

Solution 1

1. You should delete items that are not selected first.
2. Your insert statement should check to make sure that the value doesn't already exist.

Posted 22-Apr-15 12:03pm

mattHauser

Solution 3

Before you make any changes in SQL, If you want to loop through all your items in list box for selected ones, You need to use SelectedItems. and because you have an extended ListBox with Multiselect option, you need to Use Index for that. So SelectedItems(i). Rest is simple.

VB

commandsql.Parameters.AddWithValue("@TermXID", TerminalListBox.SelectedItems(i).ToString)

Posted 23-Apr-15 8:06am

Vamshi Krishna Naidu

Updated 23-Apr-15 8:07am

v2

Comments

kaniption 23-Apr-15 14:20pm

Vamshi,

Thank you for the assistance. When changing it to SelectedItems, it now only documents one item checked, whereas I had 3 items checked, and also no longer puts in a value. Instead I get System.Data.DataRowView

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

kaniption · Accepted Answer · 2015-04-23T11:04:00

I figured this out.

Again, I have a checklistbox being populated by a table in SQL. I am using this form to insert a new user (tied to NewUserXID within a textbox (also being populated by SQL, auto generated) and the terminal(s) he or she has access to (TermXID).

VB

' INSERTS TERMINAL SELECTION INTO tblUserTerminals
Dim QueryUserTerm As String

QueryUserTerm = "INSERT INTO tblUserTerminals (UserXID, TerminalXID) VALUES ((@NewUserXID), (@TermXID))"

For i As Integer = 0 To TerminalListBox.CheckedItems.Count - 1
    Dim XDRV As DataRowView = CType(TerminalListBox.CheckedItems(i), DataRowView)
    Dim XDR As DataRow = XDRV.Row
    Dim XValueMember As String = XDR(TerminalListBox.ValueMember).ToString()
    Dim commandsql = New SqlCommand(QueryUserTerm, con)
    commandsql.Parameters.AddWithValue("@TermXID", XValueMember)
    commandsql.Parameters.AddWithValue("@NewUserXID", NewUserXID.Text.ToString)
    commandsql.ExecuteNonQuery()
    commandsql.Parameters.Clear()
Next

From PIEBALDconsult:
Don't keep creating and removing the parameters; the primary purpose of parameters is to use them many times.

VB

' INSERTS TERMINAL SELECTION INTO tblUserTerminals
Dim QueryUserTerm As String

QueryUserTerm = "INSERT INTO tblUserTerminals (UserXID, TerminalXID) VALUES ((@NewUserXID), (@TermXID))"

Dim commandsql = New SqlCommand(QueryUserTerm, con)
commandsql.Parameters.AddWithValue("@TermXID", "")
commandsql.Parameters.AddWithValue("@NewUserXID", "")

For i As Integer = 0 To TerminalListBox.CheckedItems.Count - 1
    Dim XDRV As DataRowView = CType(TerminalListBox.CheckedItems(i), DataRowView)
    Dim XDR As DataRow = XDRV.Row
    Dim XValueMember As String = XDR(TerminalListBox.ValueMember).ToString()
    commandsql.Parameters("@TermXID").Value= XValueMember
    commandsql.Parameters("@NewUserXID").Value= NewUserXID.Text.ToString
    commandsql.ExecuteNonQuery()
Next