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

[CODE=vbnet]
For Each lstItem As ListItem In Me.myListBox.It ems
If lstItem .Selected = True Then
//Write your code to insert here
End If
Next

[/CODE]

[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.Odb c

[Frinavale]

Imports System.Data
Imports System.Data.Odb c

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.W ebControls.List Item In Me.myListBox.It ems

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.W ebControls.List Item In Me.myListBox.It ems

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.Da taRowView' to type 'System.Web.UI. WebControls.Lis tItem'.

[Shashi Sadasivan]

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

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.InvalidC astException was unhandled
Message="Unable to cast object of type 'System.Data.Da taRowView' to type 'System.Web.UI. WebControls.Lis tItem'."
Source="MediaCe nter"
StackTrace:
at MediaCenter.frm MediaCenter.btn AddProduction_C lick(Object sender, EventArgs e) in C:\Documents and Settings\aradam s\Desktop\Media Center\MediaCen ter\MediaCenter .vb:line 297
at System.Windows. Forms.Control.O nClick(EventArg s e)
at System.Windows. Forms.Button.On Click(EventArgs e)
at System.Windows. Forms.Button.On MouseUp(MouseEv entArgs mevent)
at System.Windows. Forms.Control.W mMouseUp(Messag e& m, MouseButtons button, Int32 clicks)
at System.Windows. Forms.Control.W ndProc(Message& m)
at System.Windows. Forms.ButtonBas e.WndProc(Messa ge& m)
at System.Windows. Forms.Button.Wn dProc(Message& m)
at System.Windows. Forms.Control.C ontrolNativeWin dow.OnMessage(M essage& m)
at System.Windows. Forms.Control.C ontrolNativeWin dow.WndProc(Mes sage& m)
at System.Windows. Forms.NativeWin dow.DebuggableC allback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows. Forms.UnsafeNat iveMethods.Disp atchMessageW(MS G& msg)
at System.Windows. Forms.Applicati on.ComponentMan ager.System.Win dows.Forms.Unsa feNativeMethods .IMsoComponentM anager.FPushMes sageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows. Forms.Applicati on.ThreadContex t.RunMessageLoo pInner(Int32 reason, ApplicationCont ext context)
at System.Windows. Forms.Applicati on.ThreadContex t.RunMessageLoo p(Int32 reason, ApplicationCont ext context)
at System.Windows. Forms.Applicati on.Run(Applicat ionContext context)
at Microsoft.Visua lBasic.Applicat ionServices.Win dowsFormsApplic ationBase.OnRun ()
at Microsoft.Visua lBasic.Applicat ionServices.Win dowsFormsApplic ationBase.DoApp licationModel()
at Microsoft.Visua lBasic.Applicat ionServices.Win dowsFormsApplic ationBase.Run(S tring[] commandLine)
at MediaCenter.My. MyApplication.M ain(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.v b:line 81
at System.AppDomai n.nExecuteAssem bly(Assembly assembly, String[] args)
at System.AppDomai n.ExecuteAssemb ly(String assemblyFile, Evidence assemblySecurit y, String[] args)
at Microsoft.Visua lStudio.Hosting Process.HostPro c.RunUsersAssem bly()
at System.Threadin g.ThreadHelper. ThreadStart_Con text(Object state)
at System.Threadin g.ExecutionCont ext.Run(Executi onContext executionContex t, ContextCallback callback, Object state)
at System.Threadin g.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.W ebControls.List Item 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

[CODE=vbnet]objcommand.Comm andText = "INSERT INTO Production_Spea ker (Speaker_ID, Production_ID) SELECT Speaker.Speaker _ID, Production.Prod uction_ID FROM Production, Speaker WHERE Speaker.Speaker _FullName = '" & MediaCenter.frm MediaCenter.lst Speakers.Text & "' AND Production.Titl e = '" & MediaCenter.frm MediaCenter.txt Title.Text & "'"[/CODE]

notice the "MediaCenter.fr mMediaCenter.ls tSpeakers.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

[CODE=vbnet]objcommand.Comm andText = "INSERT INTO Production_Spea ker (Speaker_ID, Production_ID) SELECT Speaker.Speaker _ID, Production.Prod uction_ID FROM Production, Speaker WHERE Speaker.Speaker _FullName = '" & MediaCenter.frm MediaCenter.lst Speakers.Text & "' AND Production.Titl e = '" & MediaCenter.frm MediaCenter.txt Title.Text & "'"[/CODE]

notice the "MediaCenter.fr mMediaCenter.ls tSpeakers.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.