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

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Jobe479
    New Member
    • Nov 2007
    • 16

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

    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).

    Code:
    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.

    Code:
    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:
    Code:
    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
    Recognized Expert Contributor
    • Aug 2006
    • 648

    #2
    Originally posted by 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).

    Code:
    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.

    Code:
    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:
    Code:
    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]

    Comment

    • Jobe479
      New Member
      • Nov 2007
      • 16

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

      Comment

      • Frinavale
        Recognized Expert Expert
        • Oct 2006
        • 9749

        #4
        Originally posted by Jobe479
        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?

        Comment

        • Jobe479
          New Member
          • Nov 2007
          • 16

          #5
          Originally posted by Frinavale
          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

          Comment

          • Frinavale
            Recognized Expert Expert
            • Oct 2006
            • 9749

            #6
            Originally posted by Jobe479
            Imports System.Data
            Imports System.Data.Odb c
            This shouldn't be necessary but try adding
            Imports System.Object

            Comment

            • Jobe479
              New Member
              • Nov 2007
              • 16

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

              Comment

              • Shashi Sadasivan
                Recognized Expert Top Contributor
                • Aug 2007
                • 1435

                #8
                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)

                Comment

                • Jobe479
                  New Member
                  • Nov 2007
                  • 16

                  #9
                  Originally posted by 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)
                  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'.

                  Comment

                  • Shashi Sadasivan
                    Recognized Expert Top Contributor
                    • Aug 2007
                    • 1435

                    #10
                    Originally posted by Jobe479
                    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! ?

                    Comment

                    • Jobe479
                      New Member
                      • Nov 2007
                      • 16

                      #11
                      Originally posted by Shashi Sadasivan
                      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()

                      Comment

                      • Shashi Sadasivan
                        Recognized Expert Top Contributor
                        • Aug 2007
                        • 1435

                        #12
                        Very detailed Stack sent by you there....

                        whats at MediaCenter.vb: line 297

                        Comment

                        • Jobe479
                          New Member
                          • Nov 2007
                          • 16

                          #13
                          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.

                          Comment

                          • Shashi Sadasivan
                            Recognized Expert Top Contributor
                            • Aug 2007
                            • 1435

                            #14
                            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

                            Comment

                            • Jobe479
                              New Member
                              • Nov 2007
                              • 16

                              #15
                              Originally posted by 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
                              Ok, I've simplified the SQL query a bit:

                              Code:
                              "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:

                              Code:
                              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.

                              Comment

                              Working...