存储sql where语句列表框中的选定项

Question

我想从sql where语句的列表框中获取所有选中的项，在按钮中我有一个代码

Imports System.Data.OleDb
Public Class Form1

    Dim conString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\jp\Documents\Visual Studio 2010\Projects\WindowsApplication1\WindowsApplication1\TestDb.mdb"
    Dim con As OleDbConnection = New OleDbConnection(conString)
    Dim cmd As OleDbCommand
    Dim adapter As OleDbDataAdapter
    Dim dt As DataTable = New DataTable()
    Private selectedIndices As New List(Of Integer)

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
      
        Dim tempo = ListBox6.GetItemText(ListBox6.SelectedItems())
        Dim Studente As String = ""
        For Each selected In tempo
            Studente = selected.ToString & "," & Studente
        Next
        UpdateLVe(Studente)

    End Sub

我有一个方法

Private Sub UpdateLVe(ByVal Studente As String)

    Dim sql As String = "UPDATE peace SET Votes = Votes + 1 WHERE Student='" & Studente & "'"

    cmd = New OleDbCommand(sql, con)

    Try
        con.Open()
        adapter = New OleDbDataAdapter(cmd)

        adapter.UpdateCommand = con.CreateCommand()
        adapter.UpdateCommand.CommandText = sql

        If cmd.ExecuteNonQuery() > 0 Then
            MsgBox("Successfully Voted")
        End If

        con.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
        con.Close()

    End Try

End Sub

Answer 1

好吧，我可能需要查看更多的代码和更好的解释才能帮助BUT。我可以告诉你的第一件事是，您的变量被声明为STRING，并且您正在尝试将其设置为等于不是字符串的东西。现在您可以使用a for each将逗号分隔的值列表作为字符串。

dim tempo = listbox1.SelectedItems
dim students as string = ""
for each selected in tempo
students = selected.ToString & "," & students
next

现在，这将为您提供一个变量students，它将选定项的逗号分隔列表存储为一个大字符串。

希望这能有所帮助，如果你不想发布一些代码并解释你想做什么，也许我们可以用其他方式。

您的sql语句正在查找一条值为“a，b，c，d，…”的记录这就是为什么不起作用。。。没有这样的记录，您需要在所选项目的列表中循环，为每个项目html" target="_blank">执行一次代码。

这里有一个想法，不是一个干净的方法，但对我来说很有效。

一旦你有了字符串“学生”（在答案的第一部分）

Dim myarray1() As String = students.Split(",")
Dim liststudent As List(Of String) = myarray1.ToList

        Dim i As Integer = liststudent.Count - 1
        Do While i >= 0
            Dim stri As String = listastudent.Item(i)

              'here your update code using the variable stri for the where'

            i -= 1
        Loop

这样就可以了。。。也许这不是最优雅的方式，但它应该做到。

Answer 2

我们真的需要看看你如何设置你的列表框，以便正确回答这个问题。希望您使用数据绑定到一个包含显示文本和学生id的datatable，如下所示：

Dim da as New DataAdapter("SELECT name, id FROM Student", conStr)
Dim dt as New DataTable
da.Fill(dt)

'please, name your controls something better than ListBox6!!
ListBox6.DisplayMember = "name"
ListBox6.DataSource = dt

这意味着你的。SelectedItems是DataRowView的集合，我们可以从中检索id：

'please, name your controls something better than Button1!!
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click


    For Each selected In ListBox6.SelectedItems.Cast(Of DataRowView)
        UpdateLVe(selected("id").ToString())
    Next

End Sub

它会将设置listbox时下载的ID传递给Updateve方法

Private Sub UpdateLVe(ByVal studente As String)

    'please, learn to use SQL Parameters and prevent SQL injection hacking!!
    Dim sql As String = "UPDATE peace SET Votes = Votes + 1 WHERE studentid = ?"

    Dim cmd = New OleDbCommand(sql, con)
    cmd.Parameters.AddWithValue("param1", studente)

    Try
        con.Open()
        'you don't create a dataadapter for INSERT/UPDATE/DELETE queries
        'ONLY make a dataadapter when you need to read/write datatable from/to a db

        'this line of code is useless, you never use the adapter
        'adapter = New OleDbDataAdapter(cmd)

        'this line of code is useless, you never use the adapter
        'adapter.UpdateCommand = con.CreateCommand()

        'this line of code is useless, you never use the adapter
        'adapter.UpdateCommand.CommandText = sql

        'execute the command you made, 5 lines above
        If cmd.ExecuteNonQuery() > 0 Then
            MsgBox("Successfully Voted")
        End If

        con.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
        con.Close()

    End Try

End Sub

最后一点是：

Imports System.Data.OleDb 

Public Class Form1

  Dim conString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\jp\Documents\Visual Studio 2010\Projects\WindowsApplication1\WindowsApplication1\TestDb.mdb"

  'consider not caching this at form level; make a new one when you need
  Dim con As OleDbConnection = New OleDbConnection(conString)

  'definitely don't cache this at form level; make a new one when you need
  'Dim cmd As OleDbCommand

  'definitely don't cache this at form level; make a new one when you need
  'Dim adapter As OleDbDataAdapter

  'definitely don't cache this at form level; make a new one when you need
  'Dim dt As DataTable = New DataTable()

  'you don't need to have a form level variable for this; you already have a form level ListBox6 that has this
  'Private selectedIndices As New List(Of Integer)

请不要一遍又一遍地发布同样的问题——我们是一个免费的帮助服务机构，这实际上相当于当我们忙于其他事情时，走到我们的办公桌前，反复打我们的脸，说“回答我”、“回答我”、“回答我”。如果你想让一项服务随叫随到，你需要在自由职业者招聘网站上付费