如何在SQL表中插入多个选定的ListBox值？ [英] How do I insert into SQL table multiple selected ListBox values?

问题描述

我是VB.Net的新程序员并且遇到了一些麻烦 - 希望大家可以提供帮助。



我有一堆列表框选项。我想要选择任何选项，抓住登录的员工编号，然后按顺序将它们放在我的桌子上。



到目前为止，它看起来好像是计算选择的项目是好的，并且它还更新了我的SQL表中的数量，但它似乎没有循环到下一个检查的顺序项。我不确定如何让它循环，因为它只捕获当前检查的项目的一个唯一值。



这是我的下面的代码到目前为止：

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:

' 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

推荐答案

1。您应该删除未首先选择的项目。

2.您的插入语句应检查以确保该值尚不存在。

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.

在您之前在SQL中进行任何更改，如果要遍历列表框中所选项的所有项目，则需要使用 SelectedItems 。并且因为你有一个带有Multiselect选项的扩展ListBox，你需要使用Index。所以 SelectedItems（i）。休息很简单。

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.

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

我想出来了。



再次，我有一个由SQL中的表填充的checklistbox。我正在使用此表单插入一个新用户（在文本框中绑定到NewUserXID（也由SQL填充，自动生成）和他或她有权访问的终端（TermXID）。

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).

' 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

来自PIEBALDconsult ：

不要继续创建和删除参数;参数的主要目的是多次使用它们。

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

' 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

这篇关于如何在SQL表中插入多个选定的ListBox值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！