none
Index out of Range on Insert. RRS feed

  • Question

  • I have the following two Tables.

    I'm trying to Insert the PolicyId from dbo.Policy into employee_Policy from a checkedlistbox

    Public Class Form1
    
        Public Class constantshelpers
        End Class
    
        Dim stringCon As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
        Dim Con As New SqlConnection(stringCon)
        Dim cmd As New SqlCommand
        Dim ds As New DataSet()
        Dim dAdapter As New SqlDataAdapter()
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            CheckBox()
        End Sub
    
        Private Sub CheckBox()
            Dim ds As New DataSet()
    
            cmd.Connection = Con
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "Select PolicyId From Policy"
            dAdapter.SelectCommand = cmd
            Con.Open()
            dAdapter.Fill(ds)
            Con.Close()
                CheckedListBox1.DataSource = ds.Tables(0)
                CheckedListBox1.ValueMember = "PolicyId"
                'CheckedListBox1.DisplayMember = "Policy"
        End Sub
    
        Private Sub InsertPolicyValues()
            If CheckedListBox1.SelectedIndices.Count > 0 Then
                Dim con As New SqlConnection(stringCon)
                Dim cmd As New SqlCommand()
                For i As Integer = 0 To CheckedListBox1.SelectedIndices.Count
                    Try
                        con.Open()
                        cmd.Connection = con
                        cmd.CommandText = "Insert Into employee_Policy Values (" & CheckedListBox1.SelectedIndices(i) & ")"
                        cmd.ExecuteScalar()
                        con.Close()
                    Catch ex As Exception
                        MsgBox("" & ex.Message)
                    End Try
                Next
            End If
        End Sub

    On insert I require the 3 Ids to create 3 separate records.

    There are two issues the first is the Index is out of Range how can I control this?

    The second

    We can it has create a single record and numerically calculates to 2 if I checked all 3. How can create 3 separate records from one insert? If anyone could it would be highly appreciated ?

    Thanks R.


    Thursday, January 9, 2014 6:10 PM

Answers

  • Ok I'm a junior? Where I'm I doing this please Andrew?

    Like this:

    Private Sub InsertPolicyValues()
      If CheckedListBox1.CheckedIndices.Count > 0 Then
        Using con As New SqlConnection(stringCon)
          Dim cmd As New SqlCommand()
          cmd.Connection = con
          For i As Integer = 0 To CheckedListBox1.CheckedIndices.Count - 1
            Dim polNo As String = (CheckedListBox1.CheckedIndices(i) + 1).ToString()
            Try
                con.Open()
                cmd.CommandText = "Insert Into employee_Policy Values (" & polNo & ")"
                cmd.ExecuteNonQuery()
                con.Close()
            Catch ex As Exception
                MsgBox("" & ex.Message)
            End Try
          Next
        End Using
      End If
    End Sub

    The Using... End Using makes sure that con is disposed of when you have finished using it.

    HTH,

    Andrew


    Thursday, January 9, 2014 9:01 PM

All replies

  • Since SelectedIndices is zero based you would want Count - 1:

                For i As Integer = 0 To CheckedListBox1.SelectedIndices.Count - 1
    

    Also, you should use ExecuteNonQuery instead of ExecuteScalar. This will insert one row at a time, which should be fine in your example.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, January 9, 2014 6:34 PM
  • Yes the ExecuteNonQuery has removed the Index Out of Range but its not creating the three records only one.

    Data

    And why is it one record returning the Value of two? All help regarded


    Thursday, January 9, 2014 7:06 PM
  • That looks like a CheckedListBox, in which case you should be using CheckedItems:

    http://msdn.microsoft.com/en-us/library/system.windows.forms.checkedlistbox.checkeditems(v=vs.110).aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, January 9, 2014 7:33 PM
  • Yes Thank you for the response it has created 3 records but not but not based on the id it as created the policy Id of 0,1,2 where as I need it on the Id of the policy e.g 1,2,3. Is there something I have missed in the method.

    Private Sub InsertPolicyValues()
            If CheckedListBox1.CheckedIndices.Count > 0 Then
                Dim con As New SqlConnection(stringCon)
                Dim cmd As New SqlCommand()
                For i As Integer = 0 To CheckedListBox1.CheckedIndices.Count - 1
                    Try
                        con.Open()
                        cmd.Connection = con
                        cmd.CommandText = "Insert Into employee_Policy Values (" & CheckedListBox1.CheckedIndices(i) & ")"
                        cmd.ExecuteNonQuery()
                        con.Close()
                    Catch ex As Exception
                        MsgBox("" & ex.Message)
                    End Try
                Next
            End If
        End Sub

    Thanks, Richard


    Thursday, January 9, 2014 7:43 PM
  • Yes Thank you for the response it has created 3 records but not but not based on the id it as created the policy Id of 0,1,2 where as I need it on the Id of the policy e.g 1,2,3. Is there something I have missed in the method.

    All you need to do is add one to the relevant number.

    --
    Andrew

    Thursday, January 9, 2014 8:35 PM
  • Ok I'm a junior? Where I'm I doing this please Andrew?
    Thursday, January 9, 2014 8:37 PM
  • Ok I'm a junior? Where I'm I doing this please Andrew?

    Like this:

    Private Sub InsertPolicyValues()
      If CheckedListBox1.CheckedIndices.Count > 0 Then
        Using con As New SqlConnection(stringCon)
          Dim cmd As New SqlCommand()
          cmd.Connection = con
          For i As Integer = 0 To CheckedListBox1.CheckedIndices.Count - 1
            Dim polNo As String = (CheckedListBox1.CheckedIndices(i) + 1).ToString()
            Try
                con.Open()
                cmd.CommandText = "Insert Into employee_Policy Values (" & polNo & ")"
                cmd.ExecuteNonQuery()
                con.Close()
            Catch ex As Exception
                MsgBox("" & ex.Message)
            End Try
          Next
        End Using
      End If
    End Sub

    The Using... End Using makes sure that con is disposed of when you have finished using it.

    HTH,

    Andrew


    Thursday, January 9, 2014 9:01 PM
  • Sweet As, Thank you Andrew.
    Thursday, January 9, 2014 9:12 PM
  • Sweet As, Thank you Andrew.

    You're welcome :)

    You can mark other replies as answers too if you want: I suggest that Paul's replies contributed significantly.

    --
    Andrew

    Thursday, January 9, 2014 9:18 PM