VB.NET App: How to loop through listbox items to perform a SQL insert?

[Jobe479]

Hiya, I am needing to add a Speaker ID and Production ID to an associative table based on a production that was previously created, based on the title of the production and every selected Speaker in a listbox.

The following code functions, but only selects the first occurance and then adds multiple duplicate entries to the table (based on how many items were selected).

For Each objDataRowView As DataRowView In lstSpeakers.SelectedItems
            LoadAddProduction_Speaker()
        Next

I have also tried this code, but recieve the "Public member 'Selected' on type 'DataRowView' not found" exception.

For i As Integer = 0 To lstSpeakers.Items.Count - 1
            If Me.lstSpeakers.Items(i).Selected Then
                LoadAddProduction_Speaker()
            End If
        Next i

This is the code that the LoadAddProduction_Speaker() function is calling:

Public Shared Function AddProduction_Speaker() As OdbcCommand
        Dim con As New OdbcConnection
        Dim objcommand As OdbcCommand = New OdbcCommand()
        con = New OdbcConnection(dc)
        objcommand.Connection = con
        objcommand.CommandText = "INSERT INTO Production_Speaker (Speaker_ID, Production_ID) SELECT Speaker.Speaker_ID, Production.Production_ID FROM Production, Speaker WHERE Speaker.Speaker_FullName = '" & MediaCenter.frmMediaCenter.lstSpeakers.Text & "' AND Production.Title = '" & MediaCenter.frmMediaCenter.txtTitle.Text & "'"
        objcommand.CommandType = CommandType.Text
        con.Open()
 
        Try
            objcommand.ExecuteNonQuery()
        Catch err As Exception
            MessageBox.Show(err.Message)
        Finally
            If Not con Is Nothing Then
                con.Close()
            End If
        End Try
    End Function

Any ideas? Thanks

[dip_developer]

Hiya, I am needing to add a Speaker ID and Production ID to an associative table based on a production that was previously created, based on the title of the production and every selected Speaker in a listbox.

The following code functions, but only selects the first occurance and then adds multiple duplicate entries to the table (based on how many items were selected).

For Each objDataRowView As DataRowView In lstSpeakers.SelectedItems
LoadAddProduction_Speaker()
Next

I have also tried this code, but recieve the "Public member 'Selected' on type 'DataRowView' not found" exception.

For i As Integer = 0 To lstSpeakers.Items.Count - 1
If Me.lstSpeakers.Items(i).Selected Then
LoadAddProduction_Speaker()
End If
Next i

This is the code that the LoadAddProduction_Speaker() function is calling:

Public Shared Function AddProduction_Speaker() As OdbcCommand
Dim con As New OdbcConnection
Dim objcommand As OdbcCommand = New OdbcCommand()
con = New OdbcConnection(dc)
objcommand.Connection = con
objcommand.CommandText = "INSERT INTO Production_Speaker (Speaker_ID, Production_ID) SELECT Speaker.Speaker_ID, Production.Production_ID FROM Production, Speaker WHERE Speaker.Speaker_FullName = '" & MediaCenter.frmMediaCenter.lstSpeakers.Text & "' AND Production.Title = '" & MediaCenter.frmMediaCenter.txtTitle.Text & "'"
objcommand.CommandType = CommandType.Text
con.Open()
 
Try
objcommand.ExecuteNonQuery()
Catch err As Exception
MessageBox.Show(err.Message)
Finally
If Not con Is Nothing Then
con.Close()
End If
End Try
End Function

Any ideas? Thanks

 
For Each lstItem As ListItem In Me.myListBox.Items
If lstItem .Selected = True Then
//Write your code to insert here
End If
Next
 
 

[Jobe479]

Hmm, I'm getting a "type 'listitem' is not defined" exception.

[Frinavale]

Hmm, I'm getting a "type 'listitem' is not defined" exception.

That is rather strange...
ListItem should be included in the default libraries for applications.
Could you please post your include/import statements?

[Jobe479]

That is rather strange...
ListItem should be included in the default libraries for applications.
Could you please post your include/import statements?

Imports System.Data
Imports System.Data.Odbc

[Frinavale]

Imports System.Data
Imports System.Data.Odbc

This shouldn't be necessary but try adding
Imports System.Object

[Jobe479]

No change =\ Does the same on another computer in a different project as well.

[Shashi Sadasivan]

For Each lstItem As System.Web.UI.WebControls.ListItem In Me.myListBox.Items

does adding the namespace in front of ListItem do anything?

you might need to manually add the reference (if it dosent appear)

[Jobe479]

For Each lstItem As System.Web.UI.WebControls.ListItem In Me.myListBox.Items

does adding the namespace in front of ListItem do anything?

you might need to manually add the reference (if it dosent appear)

That worked, but it throws a similar error as the others:
Unable to cast object of type 'System.Data.DataRowView' to type 'System.Web.UI.WebControls.ListItem'.

[Shashi Sadasivan]

That worked, but it throws a similar error as the others:
Unable to cast object of type 'System.Data.DataRowView' to type 'System.Web.UI.WebControls.ListItem'.

Hi Jobe479,
Could you please paste the exact exception being thrown, and at which line is that pointing to! ?

[Jobe479]

Hi Jobe479,
Could you please paste the exact exception being thrown, and at which line is that pointing to! ?

Sure np,

System.InvalidCastException was unhandled
Message="Unable to cast object of type 'System.Data.DataRowView' to type 'System.Web.UI.WebControls.ListItem'."
Source="MediaCenter"
StackTrace:
at MediaCenter.frmMediaCenter.btnAddProduction_Click( Object sender, EventArgs e) in C:\Documents and Settings\aradams\Desktop\MediaCenter\MediaCenter\M ediaCenter.vb:line 297
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventAr gs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.O nMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.W ndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallba ck(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchM essageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager. System.Windows.Forms.UnsafeNativeMethods.IMsoCompo nentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.Run MessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.Run MessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationCo ntext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.Run(String[] commandLine)
at MediaCenter.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.Run UsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context( Object state)
at System.Threading.ExecutionContext.Run(ExecutionCon text executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

[Shashi Sadasivan]

Very detailed Stack sent by you there....

whats at MediaCenter.vb:line 297

[Jobe479]

297 is the For Each lstItem As System.Web.UI.WebControls.ListItem In Me.lstSpeakers.Items line.

I guess I'm going to try and convert the selecteditems to an arrayList to try and avoid the DataRowView issue from the databound listbox.

[Shashi Sadasivan]

i think this is the line which should be throwing the error

objcommand.CommandText = "INSERT INTO Production_Speaker (Speaker_ID, Production_ID) SELECT Speaker.Speaker_ID, Production.Production_ID FROM Production, Speaker WHERE Speaker.Speaker_FullName = '" & MediaCenter.frmMediaCenter.lstSpeakers.Text & "' AND Production.Title = '" & MediaCenter.frmMediaCenter.txtTitle.Text & "'"

notice the "MediaCenter.frmMediaCenter.lstSpeakers.Text " in the line...
if lstSpeakers is the ListBox, the a .Text should throw an exception (or it shouldnt work as expected)

You will have to create an array or List<string> of the selected items (the selected text) and then call the sql statement.
For each string in that array or List, you would have to keep adding the sql query clauses

[Jobe479]

i think this is the line which should be throwing the error

objcommand.CommandText = "INSERT INTO Production_Speaker (Speaker_ID, Production_ID) SELECT Speaker.Speaker_ID, Production.Production_ID FROM Production, Speaker WHERE Speaker.Speaker_FullName = '" & MediaCenter.frmMediaCenter.lstSpeakers.Text & "' AND Production.Title = '" & MediaCenter.frmMediaCenter.txtTitle.Text & "'"

notice the "MediaCenter.frmMediaCenter.lstSpeakers.Text " in the line...
if lstSpeakers is the ListBox, the a .Text should throw an exception (or it shouldnt work as expected)

You will have to create an array or List<string> of the selected items (the selected text) and then call the sql statement.
For each string in that array or List, you would have to keep adding the sql query clauses

Ok, I've simplified the SQL query a bit:

"INSERT INTO Production_Speaker (Speaker_ID, Production_ID) VALUES(" & MediaCenter.frmMediaCenter.lstSpeakers.SelectedValue & "," & MediaCenter.frmMediaCenter.txtGeneratedProductionID.Text & ")"

and am using the following to call the new function:

For Each objDataRowView As DataRowView In lstSpeakers.SelectedItems
                 LoadAddProduction_Speaker2()
        Next objDataRowView

If I select only one Speaker, it will add to the table correctly, but if I select more than one, it throws a "cannot insert duplicate key in object 'dbo.Production_Speaker'..." SQL error. So, it doesn't seem to be looping through each selecteditem successfully.

[Frinavale]

and am using the following to call the new function:

For Each objDataRowView As DataRowView In lstSpeakers.SelectedItems
                 LoadAddProduction_Speaker2()
        Next objDataRowView

If I select only one Speaker, it will add to the table correctly, but if I select more than one, it throws a "cannot insert duplicate key in object 'dbo.Production_Speaker'..." SQL error. So, it doesn't seem to be looping through each selecteditem successfully.

Aren't you supposed to be passing the LoadAddProduction_Speaker2() function a new variable during the loop????

Otherwise it'll keep using the same data over and over again....

[Jobe479]

Aren't you supposed to be passing the LoadAddProduction_Speaker2() function a new variable during the loop????

Otherwise it'll keep using the same data over and over again....

I see what you're saying. I think you're on to something :)
Is something like this close to what you have in mind?

I've changed:

Public Shared Function AddProduction_Speaker2() As OdbcCommand

to:

Public Shared Function AddProduction_Speaker2(ByVal variable As String) As OdbcCommand

and the SQL string to:

"INSERT INTO Production_Speaker (Speaker_ID, Production_ID) VALUES(" & variable & "," & MediaCenter.frmMediaCenter.txtGeneratedProductionID.Text & ")"

and changed the loop accordingly:

Dim SelectedSpeakerID As String
        SelectedSpeakerID = lstSpeakers.SelectedValue
        For Each item As DataRowView In lstSpeakers.SelectedItems
            Production.AddProduction_Speaker2(SelectedSpeakerID)
        Next item

Upon run it's throwing a "Conversion from type 'DataRowView' to type 'String' is not valid" exception.

[Jobe479]

Oops, nevermind about the "Conversion from type 'DataRowView' to type 'String' is not valid".

I'm back to the same initial problem... it's still trying to insert duplicate rows into the table and isn't looping through the SelectedValue's.

[Shashi Sadasivan]

Hi,

have a look at this piece of code you put in
I have added some comments

Dim SelectedSpeakerID As String
        'Following is the value being sent to the SQL Insert Function
        SelectedSpeakerID = lstSpeakers.SelectedValue
        'the foreach loop starts here for some reason
        For Each item As DataRowView In lstSpeakers.SelectedItems
            'SelectedSpeakerID will remain the same, isint it
            Production.AddProduction_Speaker2(SelectedSpeakerI  D)
        Next item

I dont want to sound harsh here, but why dont you desk check your code now !

[Jobe479]

Ah, that does make sense. Forgive my noobism, I am still very new to this :)

I have changed it to the following, but am receiving a new exception:

Unable to cast object of type 'System.Int32' to type 'System.Collections.IEnumerable'.

Dim SelectedSpeakerID As Integer
        For Each item As DataRowView In lstSpeakers.SelectedValue
            SelectedSpeakerID = lstSpeakers.SelectedValue
            Production.AddProduction_Speaker2(SelectedSpeakerID)
        Next item

[Shashi Sadasivan]

I doubt that you dont use the debugger.

use the debugger,
every the time the loop enters check the value of SelectedSpeakerID , and then get back

[Jobe479]

I doubt that you dont use the debugger.

use the debugger,
every the time the loop enters check the value of SelectedSpeakerID , and then get back

Worth a shot :) I appreciate the help.


Using the following:

Dim SelectedSpeakerID As Integer
        For Each item As DataRowView In Me.lstSpeakers.Items
            SelectedSpeakerID = lstSpeakers.SelectedValue
            Production.AddProduction_Speaker2(SelectedSpeakerID)
        Next item

Stepping into the debugger shows that it's sucessfully pulling the first SelectedValue, populates the SQL variable correctly, and adds it to the database. It then goes back to the loop, but selects the exact same SelectedValue, then pukes as it tries to add a duplicate entry to the database.

[Shashi Sadasivan]

You still dont get it do you?

replace SelectedSpeakerID with Item

[Jobe479]

You still dont get it do you?

replace SelectedSpeakerID with Item

I guess not. :(

Dim item As Integer
        For Each item As DataRowView In Me.lstSpeakers.Items
            item = lstSpeakers.SelectedValue
            Production.AddProduction_Speaker2(item)
        Next item

Produces the following exception:
Variable 'item' hides a variable in an enclosing block.

Dim item As DataRowView
        For Each item In Me.lstSpeakers.Items
            item = lstSpeakers.SelectedValue
            Production.AddProduction_Speaker2(item)
        Next

Throws:
Unable to cast object of type 'System.Int32' to type 'System.Data.DataRowView'.

Dim item As Integer
        For Each item In Me.lstSpeakers.Items
            item = lstSpeakers.SelectedValue
            Production.AddProduction_Speaker2(item)
        Next

Throws:
Conversion from type 'DataRowView' to type 'Integer' is not valid.

[Frinavale]

Jobe479,

Could you please post the ASP code for your list box.
ListBoxes should not be using System.Data.DataRowView types. They are composed of ListItems. I have a feeling that what you are refering to as a ListBox is not what the experts here consider to be a ListBox...

ListBoxes don't have DataRowView objects and since you keep getting errors about casting items to this object type, I'm guessing that you aren't actually using a ListBox but something else entirely...

Take a look at the ListBox Class and How To: Determine the selection in the ListBox control.
From here you will see that there are no DataRowView objects being used...

Just ListItems...

When you process a ListItem's value you should be sure to cast it to the data type that you need to use to perform your logic.

So your code should look something like

             Dim item As Integer
              For Each item As DataRowView In Me.lstSpeakers.Items
                  item = Ctype(lstSpeakers.SelectedValue,Integer)
                  'Or you can use: item = Integer.Parse(lstSpeakers.SelectedValue)
                  Production.AddProduction_Speaker2(item)
              Next item
 

This will Normally get rid of the exception: "Conversion from type 'DataRowView' to type 'Integer' is not valid."

Right now you are letting the compiler figure out what type of data you are taking out of the item. You should never let the compiler do anything for you because you'll end up getting some pretty crazy stuff happening...like this exception...and sometimes worse things

So yeah, take a look at those links and post the ASP code for your ListBox and we'll see if we can get to the bottom of your problem :D

-Frinny

[Jobe479]

Hi Frinny,

No ASP here, just your standard Windows form embedding SQL.

This is how the ListBox is being populated:
(lstSpeakers of type System.Windows.Forms.ListBox for reference)

Private Sub LoadProductionSpeakerList()
        Dim speakerList As DataSet
        speakerList = Speaker.GetSpeaker()
 
        ' Set the data source
        Me.lstSpeakers.DataSource = speakerList.Tables(0).DefaultView
        ' Set the display field
        Me.lstSpeakers.ValueMember = "speaker_id"
        Me.lstSpeakers.DisplayMember = "speaker_fullname"
 
    End Sub

and the Query:

Public Shared Function GetSpeaker() As DataSet
        Dim SpeakerList As New DataSet
        Dim da As OdbcDataAdapter
        Dim con As OdbcConnection
        Dim query As String
 
        Try
            ' Make the connection
            con = New OdbcConnection(dc)
            query = "select Speaker_id, Speaker_fullname FROM Speaker ORDER BY Speaker_fullname"
            da = New OdbcDataAdapter(query, con)
            da.Fill(SpeakerList)
        Catch ex As Exception
            Throw ex
        Finally
            If Not con Is Nothing Then
                con.Close()
            End If
        End Try
        ' return the result
        Return SpeakerList
    End Function

I realize this may not be the best way to go about populating the ListBox, but it is the only way I know at present. So I am very open to other methods that could get around the DataRowView issue :)

[Jobe479]

I keep running across the solution to change the databinding order from

Me.lstSpeakers.DataSource = speakerList.Tables(0).DefaultView
Me.lstSpeakers.ValueMember = "speaker_id"
Me.lstSpeakers.DisplayMember = "speaker_fullname"

to

Me.lstSpeakers.DisplayMember = "speaker_fullname"
Me.lstSpeakers.ValueMember = "speaker_id"
Me.lstSpeakers.DataSource = speakerList.Tables(0).DefaultView

Unfortunately, it hasn't seemed to make a difference.