storing selected items from listbox for sql where statement

Question

I wanted to get all the selected items from the listbox for sql where statement in a button I have a code

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

and I have a method

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

We really need to see how you set your Listbox up in order to answer this properly. Hopefully you used databinding to a datatable that had the display text and the student id in it, something like this:

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

This means your .SelectedItems is a collection of DataRowView, from which we can retrieve the 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

Which will pass the ID downloaded when setting up the listbox, to the UpdateLve method

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

And a final note:

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)

Please, don't post the same question over and over - we're a free help service and that's the virtual equivalent of marching up to our desk when we're busy with something else, and punching us repeatedly in the face saying "answer me", "answer me", "answer me". If you want a service to be at your beck and call you'll need to pay for it on a freelancer hiring site

Answer 2

well, I may need to see more of the code and a better explanation in order to help BUT. the first thing I can tell you is that your variable is declared as a STRING and you are trying to set it equal to something that is not a string. now you could use a for each to get a comma separated list of values as a string.

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

Now that will give you a variable students which have stored a comma separated list of the selected items as one large string.

Hope this helps, If don't please post some of your code and explain what you want to do, maybe we can do it some other way.

---

your sql statement is looking for one record with the value "a,b,c,d,..." that's why won't work... there's no such record, you need to loop through the list of selected items executing your code once for each item.

here is an idea, not a clean way to do it but it worked for me.

once you have your string "students" (in the first part of the answer)

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

that should do the trick... maybe not the most elegant way of doing it but it should make it.