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

[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 LoadAddProducti on_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 LoadAddProducti on_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

[CODE=cpp]
Dim SelectedSpeaker ID As String
'Following is the value being sent to the SQL Insert Function
SelectedSpeaker ID = lstSpeakers.Sel ectedValue
'the foreach loop starts here for some reason
For Each item As DataRowView In lstSpeakers.Sel ectedItems
'SelectedSpeake rID will remain the same, isint it
Production.AddP roduction_Speak er2(SelectedSpe akerI D)
Next item[/CODE]

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.Collect ions.IEnumerabl e'.

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 SelectedSpeaker ID , 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 SelectedSpeaker ID , 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 SelectedSpeaker ID with Item

[Jobe479]

You still dont get it do you?

replace SelectedSpeaker ID 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.Da taRowView'.

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.Dat aRowView 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
[code=vbnet]
Dim item As Integer
For Each item As DataRowView In Me.lstSpeakers. Items
item = Ctype(lstSpeake rs.SelectedValu e,Integer)
'Or you can use: item = Integer.Parse(l stSpeakers.Sele ctedValue)
Production.AddP roduction_Speak er2(item)
Next item
[/code]

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...lik e 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.