locked
DGV IndexOutOfRangeException RRS feed

  • Question

  • I started using BindingNavigators in some of my user controls. I am having a pesky issue when trying to delete the last row in a datatable. I understand what's going on to cause the exception, but don't know how to handle it when removing the row using a binding navigator. Is there anyway to see the code that runs when clicking "BindingNavigatorDeleteItem" Or is there some trick to using this navigator? Seems broken to me. 

    Using Conn As New SqlConnection(My.Settings.SQLConn)
                Using SHiftsDA As New SqlDataAdapter("SELECT * FROM shifts", Conn)
                    With SHiftsDA
                        .MissingSchemaAction = MissingSchemaAction.AddWithKey
                        .FillSchema(ShiftsTable, SchemaType.Source)
                        .Fill(ShiftsTable)
                        With ShiftsTable
                            .Columns("StartTime").DefaultValue = New DateTime(Now.Year, Now.Month, Now.Day, 0, 0, 0, 0)
                            .Columns("EndTime").DefaultValue = New DateTime(Now.Year, Now.Month, Now.Day, 0, 0, 0, 0)
                            .Columns("EndTimeNextDay").DefaultValue = False
                        End With
                        ShiftsBind.DataSource = ShiftsTable
                        bindnav_shifts.BindingSource = ShiftsBind


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Wednesday, June 28, 2017 11:31 AM

All replies

  • Here I used a TableAdapter approach meaning Visual Studio created strong typed classes to represent data where the core data comes from a database table, into DataTable with a class layered on top.

    So we have

    BindingNavigator.BindingSource = CustomerBindingSource

    where CustomerBindingSource is populated from an SQL query. I remove the default behavior of the delete button under the properties for the BindingNavigator e.g.

    Next double click the delete button in the BindingNavigator and place the following code into it. I first determine if there is a row to work with then get data (for demoing) into a string and display it to the IDE output window. You could simple focus on one field e.g. get primary key

    CType(CustomerBindingSource.Current, DataRowView).Row.Field(Of integer)("id")

    But I slapped it all in the click event and delete the row (in a real app you would ask permission to remove the row first)

    Private Sub BindingNavigatorDeleteItem_Click(sender As Object, e As EventArgs) Handles BindingNavigatorDeleteItem.Click
        If CustomerBindingSource.Current IsNot Nothing Then
            Dim FieldData = String.Join(",", CType(CustomerBindingSource.Current, DataRowView).Row.ItemArray)
            Console.WriteLine(FieldData)
            CustomerBindingSource.RemoveCurrent()
        End If
    End Sub

    Note, when setting a DataTable as the DataSource of a BindingSource when casting a row we start with DataRowView then access the Row property then from the row property access fields e.g. Row.Field(Of String)("FirstName"), Row.Field(Of DateTime)("JoinedDate")

    To reiterate, I'm using a strong type class to represent data but note you don't see this as in this sample I go with the DataTable object. You are not using strong typed classes so how I did the sample will work for you via the SqlConnection, SqlDataAdapter as this produces a DataTable populated with data from your database table.

    Does that make sense?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Wednesday, June 28, 2017 12:39 PM
  • What you're saying does make sense and answers a few questions. I would have never in a million years thought of this solution. This can't be how this (bindingnavigator) was intended to work by design. Maybe I can just unbind the dgv before the row is removed, either way it is safe to say the built in "DeleteItem" function on the navigator is flawed, or I am not using it as it was intended.

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Wednesday, June 28, 2017 1:00 PM
  • Hi Gary :)

    Is the error occurring when you click the delete button on the form?  Or are you executing some code which causes the error?

    The delete button should work as expected when clicked by a user - if you are experiencing the error when clicking the delete button with the mouse then something else is at play that we aren't seeing (perhaps some event handlers on the button control or data components).

    If you are programmatically "clicking" the button then it might be related to when/where you execute that code.

    The short answer is that it should work and isn't broken... something else is going on here.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Wednesday, June 28, 2017 2:58 PM
  • Hey Reed, good to see you're still with us here!

    I have zero events handles on the DGV, nor am I attaching any code to the delete button. Just have a simple bindnav, dgv, and some textbox bindings. Here is How I am setting up the DGV/Bindings

            Using Conn As New SqlConnection(My.Settings.SQLConn)
                Using UsersDA As New SqlDataAdapter("SELECT * FROM users", Conn)
                    With UsersDA
                        .MissingSchemaAction = MissingSchemaAction.AddWithKey
                        .FillSchema(UsersTable, SchemaType.Source)
                        .Fill(UsersTable)
                        With UsersTable
                            .Columns("ShiftStart").DefaultValue = New DateTime(Now.Year, Now.Month, Now.Day, 0, 0, 0, 0)
                            .Columns("ShiftEnd").DefaultValue = New DateTime(Now.Year, Now.Month, Now.Day, 0, 0, 0, 0)
                            .Columns("ShiftSpan2Days").DefaultValue = False
                            .Columns("IsNew").DefaultValue = False
                            .Columns("IsOnline").DefaultValue = False
                            .Columns("ModBy").DefaultValue = Environment.UserName
                            .Columns("ModDate").DefaultValue = Now
                            .Columns("ModFrom").DefaultValue = Environment.MachineName
                        End With
                        UsersBind.DataSource = UsersTable
                        bindnav_users.BindingSource = UsersBind
                        With dgv_users
                            Dim SumColWidth As Integer = 0
                            .DataSource = UsersBind
                            For Each col As DataGridViewColumn In dgv_users.Columns
                                col.Visible = False
                            Next
                            .Columns("UserName").Visible = True
                            .Columns("ShiftName").Visible = True
                            .Columns("LastLogon").DefaultCellStyle.Format = "hh:mm tt"
    
                            For Each col As DataGridViewColumn In dgv_users.Columns
                                If col.Visible = True Then
                                    col.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
                                    SumColWidth += col.Width + SystemInformation.BorderSize.Width
                                End If
                            Next
    No other events are in play on bindingsource, bindingnav, or dgv

    Maybe setup a quick test, dont apply code to the delete apparatus in the binding nav. then keep hammering it and see if it pukes on the last row to be removed.


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Wednesday, June 28, 2017 8:26 PM
  • I do have the bindingsource declared with events however, but I am not calling upon any of the events currently. Do you suppose this would cause strange behavior?

    Public Class UC_Settings
    
        Dim ShiftsTable As New DataTable
        WithEvents ShiftsBind As New BindingSource

    I have production data in the table now and can't test this at this juncture. maybe tonight Ill have more time.

    Again, nice to see you, my friend.


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Wednesday, June 28, 2017 8:31 PM
  • I just took 20 minutes, put together a database and a rush job on the project. Using a data generator for data.

    Using a button

    Left this stock

    Class for data operations

    Imports System.Data.SqlClient
    Public Class Operations
        ''' <summary>
        ''' Replace with your SQL Server name
        ''' </summary>
        Private Server As String = "KARENS-PC"
        ''' <summary>
        ''' Database in which data resides, see SQL_Script.sql
        ''' </summary>
        Private Catalog As String = "ShiftDatabase"
        ''' <summary>
        ''' Connection string for connecting to the database
        ''' </summary>
        Private ConnectionString As String = ""
        ''' <summary>
        ''' Setup the connection string
        ''' </summary>
        Public Sub New()
            ConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True"
        End Sub
    
        Public Function Load() As DataTable
            Dim dt = New DataTable()
            Using cn As SqlConnection = New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As SqlCommand = New SqlCommand With {.Connection = cn}
                    cmd.CommandText =
                        <SQL>
                            SELECT id
                                  ,ShiftStart
                                  ,ShiftEnd
                                  ,ShiftSpan2Days
                                  ,IsNew
                                  ,IsOnline
                                  ,ModBy
                                  ,ModDate
                                  ,ModFrom
                              FROM Shifts
                        </SQL>.Value
    
                    cn.Open()
                    dt.Load(cmd.ExecuteReader())
    
                    dt.Columns("id").ColumnMapping = MappingType.Hidden
                    dt.Columns("ShiftStart").DefaultValue = New DateTime(Now.Year, Now.Month, Now.Day, 0, 0, 0, 0)
                    dt.Columns("ShiftEnd").DefaultValue = New DateTime(Now.Year, Now.Month, Now.Day, 0, 0, 0, 0)
                    dt.Columns("ShiftSpan2Days").DefaultValue = False
                    dt.Columns("IsNew").ColumnMapping = MappingType.Hidden
                    dt.Columns("IsNew").DefaultValue = False
                    dt.Columns("IsOnline").ColumnMapping = MappingType.Hidden
                    dt.Columns("ModBy").ColumnMapping = MappingType.Hidden
                    dt.Columns("ModBy").DefaultValue = Environment.UserName
                    dt.Columns("ModDate").ColumnMapping = MappingType.Hidden
                    dt.Columns("ModDate").DefaultValue = Now
                    dt.Columns("ModFrom").ColumnMapping = MappingType.Hidden
                    dt.Columns("ModFrom").DefaultValue = Environment.MachineName
    
                End Using
            End Using
    
            Return dt
    
        End Function
        Public Sub AddRow(ByVal pRow As DataRow)
            Throw New NotImplementedException
        End Sub
        Public Sub ModifyRow(ByVal pRow As DataRow)
            Throw New NotImplementedException
        End Sub
        Public Sub RemoveRow(ByVal pId As Integer)
            Throw New NotImplementedException
        End Sub
    End Class
    

    Form code

    Public Class Form1
        Private ops As New Operations
        WithEvents bsShifts As New BindingSource
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            bsShifts.DataSource = ops.Load
            BindingNavigator1.BindingSource = bsShifts
            DataGridView1.DataSource = bsShifts
            For Each col As DataGridViewColumn In DataGridView1.Columns
                col.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            Next
            DataGridView1.Columns("ShiftEnd").HeaderText = "Shift end"
            DataGridView1.Columns("ShiftSpan2Days").HeaderText = "Span 2 days"
        End Sub
        Private Sub cmdRemoveCurrent_Click(sender As Object, e As EventArgs) Handles cmdRemoveCurrent.Click
            If My.Dialogs.Question($"Remove this row {bsShifts.CurrentRow.Flatten}") Then
                bsShifts.RemoveCurrent()
            End If
        End Sub
    End Class
    

    For question function (pre-done)

    Namespace My
        <ComponentModel.EditorBrowsable(ComponentModel.EditorBrowsableState.Never)>
        Partial Friend Class _Dialogs
            Public Function Question(ByVal Text As String) As Boolean
                Return (MessageBox.Show(Text, Application.Info.Title, MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) = MsgBoxResult.Yes)
            End Function
        End Class
        <HideModuleName()>
        Friend Module KP_Dialogs
            Private instance As New ThreadSafeObjectProvider(Of _Dialogs)
            ReadOnly Property Dialogs() As _Dialogs
                Get
                    Return instance.GetInstance()
                End Get
            End Property
        End Module
    End Namespace

    BindingSource extensions (pre-done)

    Module BindingSourceExtensions
        <DebuggerStepThrough()>
        <Runtime.CompilerServices.Extension()>
        Public Function CurrentRow(ByVal sender As BindingSource) As DataRow
            Return (CType(sender.Current, DataRowView)).Row
        End Function
        <DebuggerStepThrough()>
        <Runtime.CompilerServices.Extension()>
        Public Function Flatten(ByVal sender As DataRow) As String
            Return String.Join(",", sender.ItemArray)
        End Function
    End Module
    

    Tested this, zero issues with removal of any records in any order

    This is the database and table

    USE [master]
    GO
    /****** Object:  Database [ShiftDatabase]    Script Date: 6/28/2017 4:14:01 PM ******/
    CREATE DATABASE [ShiftDatabase]
     CONTAINMENT = NONE
     ON  PRIMARY 
    ( NAME = N'ShiftDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ShiftDatabase.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
     LOG ON 
    ( NAME = N'ShiftDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ShiftDatabase_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    GO
    ALTER DATABASE [ShiftDatabase] SET COMPATIBILITY_LEVEL = 110
    GO
    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [ShiftDatabase].[dbo].[sp_fulltext_database] @action = 'enable'
    end
    GO
    ALTER DATABASE [ShiftDatabase] SET ANSI_NULL_DEFAULT OFF 
    GO
    ALTER DATABASE [ShiftDatabase] SET ANSI_NULLS OFF 
    GO
    ALTER DATABASE [ShiftDatabase] SET ANSI_PADDING OFF 
    GO
    ALTER DATABASE [ShiftDatabase] SET ANSI_WARNINGS OFF 
    GO
    ALTER DATABASE [ShiftDatabase] SET ARITHABORT OFF 
    GO
    ALTER DATABASE [ShiftDatabase] SET AUTO_CLOSE OFF 
    GO
    ALTER DATABASE [ShiftDatabase] SET AUTO_CREATE_STATISTICS ON 
    GO
    ALTER DATABASE [ShiftDatabase] SET AUTO_SHRINK OFF 
    GO
    ALTER DATABASE [ShiftDatabase] SET AUTO_UPDATE_STATISTICS ON 
    GO
    ALTER DATABASE [ShiftDatabase] SET CURSOR_CLOSE_ON_COMMIT OFF 
    GO
    ALTER DATABASE [ShiftDatabase] SET CURSOR_DEFAULT  GLOBAL 
    GO
    ALTER DATABASE [ShiftDatabase] SET CONCAT_NULL_YIELDS_NULL OFF 
    GO
    ALTER DATABASE [ShiftDatabase] SET NUMERIC_ROUNDABORT OFF 
    GO
    ALTER DATABASE [ShiftDatabase] SET QUOTED_IDENTIFIER OFF 
    GO
    ALTER DATABASE [ShiftDatabase] SET RECURSIVE_TRIGGERS OFF 
    GO
    ALTER DATABASE [ShiftDatabase] SET  DISABLE_BROKER 
    GO
    ALTER DATABASE [ShiftDatabase] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
    GO
    ALTER DATABASE [ShiftDatabase] SET DATE_CORRELATION_OPTIMIZATION OFF 
    GO
    ALTER DATABASE [ShiftDatabase] SET TRUSTWORTHY OFF 
    GO
    ALTER DATABASE [ShiftDatabase] SET ALLOW_SNAPSHOT_ISOLATION OFF 
    GO
    ALTER DATABASE [ShiftDatabase] SET PARAMETERIZATION SIMPLE 
    GO
    ALTER DATABASE [ShiftDatabase] SET READ_COMMITTED_SNAPSHOT OFF 
    GO
    ALTER DATABASE [ShiftDatabase] SET HONOR_BROKER_PRIORITY OFF 
    GO
    ALTER DATABASE [ShiftDatabase] SET RECOVERY FULL 
    GO
    ALTER DATABASE [ShiftDatabase] SET  MULTI_USER 
    GO
    ALTER DATABASE [ShiftDatabase] SET PAGE_VERIFY CHECKSUM  
    GO
    ALTER DATABASE [ShiftDatabase] SET DB_CHAINING OFF 
    GO
    ALTER DATABASE [ShiftDatabase] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
    GO
    ALTER DATABASE [ShiftDatabase] SET TARGET_RECOVERY_TIME = 0 SECONDS 
    GO
    USE [ShiftDatabase]
    GO
    /****** Object:  Table [dbo].[Shifts]    Script Date: 6/28/2017 4:14:01 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Shifts](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[ShiftStart] [datetime2](7) NULL,
    	[ShiftEnd] [datetime2](7) NULL,
    	[ShiftSpan2Days] [bit] NULL,
    	[IsNew] [bit] NULL,
    	[IsOnline] [bit] NULL,
    	[ModBy] [nvarchar](max) NULL,
    	[ModDate] [datetime2](7) NULL,
    	[ModFrom] [nvarchar](max) NULL,
     CONSTRAINT [PK_Shift] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO
    SET IDENTITY_INSERT [dbo].[Shifts] ON 
    
    GO
    INSERT [dbo].[Shifts] ([id], [ShiftStart], [ShiftEnd], [ShiftSpan2Days], [IsNew], [IsOnline], [ModBy], [ModDate], [ModFrom]) VALUES (1, CAST(N'2017-05-15 00:00:00.0000000' AS DateTime2), CAST(N'2017-06-15 11:52:08.2562097' AS DateTime2), 1, 0, 1, N'Ralph Kirby', CAST(N'2017-05-23 14:38:24.6642623' AS DateTime2), N'Chris Boyd')
    GO
    INSERT [dbo].[Shifts] ([id], [ShiftStart], [ShiftEnd], [ShiftSpan2Days], [IsNew], [IsOnline], [ModBy], [ModDate], [ModFrom]) VALUES (2, CAST(N'2017-05-07 00:00:00.0000000' AS DateTime2), CAST(N'2017-05-24 20:17:39.2431051' AS DateTime2), 0, 0, 1, N'Xavier Avery', CAST(N'2017-06-21 09:41:52.9682406' AS DateTime2), N'Rafael Walsh')
    GO
    INSERT [dbo].[Shifts] ([id], [ShiftStart], [ShiftEnd], [ShiftSpan2Days], [IsNew], [IsOnline], [ModBy], [ModDate], [ModFrom]) VALUES (3, CAST(N'2017-05-29 00:00:00.0000000' AS DateTime2), CAST(N'2017-05-10 19:03:14.8219753' AS DateTime2), 0, 0, 1, N'Greg Zuniga', CAST(N'2017-06-08 23:53:20.7232679' AS DateTime2), N'Jack Calderon')
    GO
    INSERT [dbo].[Shifts] ([id], [ShiftStart], [ShiftEnd], [ShiftSpan2Days], [IsNew], [IsOnline], [ModBy], [ModDate], [ModFrom]) VALUES (4, CAST(N'2017-06-16 00:00:00.0000000' AS DateTime2), CAST(N'2017-06-28 02:49:35.8553717' AS DateTime2), 1, 1, 0, N'Marlon Parsons', CAST(N'2017-05-04 02:00:09.2701334' AS DateTime2), N'Deborah Strickland')
    GO
    INSERT [dbo].[Shifts] ([id], [ShiftStart], [ShiftEnd], [ShiftSpan2Days], [IsNew], [IsOnline], [ModBy], [ModDate], [ModFrom]) VALUES (5, CAST(N'2017-06-09 00:00:00.0000000' AS DateTime2), CAST(N'2017-05-07 10:21:02.0199943' AS DateTime2), 0, 1, 1, N'Teresa Pope', CAST(N'2017-05-22 14:52:10.5551361' AS DateTime2), N'Shelley Wheeler')
    GO
    INSERT [dbo].[Shifts] ([id], [ShiftStart], [ShiftEnd], [ShiftSpan2Days], [IsNew], [IsOnline], [ModBy], [ModDate], [ModFrom]) VALUES (6, CAST(N'2017-05-26 00:00:00.0000000' AS DateTime2), CAST(N'2017-05-19 02:15:34.0477370' AS DateTime2), 1, 1, 0, N'Angelica Gordon', CAST(N'2017-06-09 21:27:21.3444626' AS DateTime2), N'Annette Barrera')
    GO
    INSERT [dbo].[Shifts] ([id], [ShiftStart], [ShiftEnd], [ShiftSpan2Days], [IsNew], [IsOnline], [ModBy], [ModDate], [ModFrom]) VALUES (7, CAST(N'2017-05-22 00:00:00.0000000' AS DateTime2), CAST(N'2017-06-17 07:49:51.2149561' AS DateTime2), 1, 0, 1, N'Lea Sweeney', CAST(N'2017-05-03 12:04:20.3230902' AS DateTime2), N'Benjamin Dillon')
    GO
    INSERT [dbo].[Shifts] ([id], [ShiftStart], [ShiftEnd], [ShiftSpan2Days], [IsNew], [IsOnline], [ModBy], [ModDate], [ModFrom]) VALUES (8, CAST(N'2017-06-26 00:00:00.0000000' AS DateTime2), CAST(N'2017-05-27 06:53:38.7505674' AS DateTime2), 0, 1, 0, N'Kelli Ho', CAST(N'2017-06-26 06:49:10.8095141' AS DateTime2), N'Terri Robinson')
    GO
    INSERT [dbo].[Shifts] ([id], [ShiftStart], [ShiftEnd], [ShiftSpan2Days], [IsNew], [IsOnline], [ModBy], [ModDate], [ModFrom]) VALUES (9, CAST(N'2017-05-07 00:00:00.0000000' AS DateTime2), CAST(N'2017-05-14 06:34:34.1256749' AS DateTime2), 1, 1, 0, N'Dawn Orr', CAST(N'2017-06-19 18:29:25.3858287' AS DateTime2), N'Shane Hatfield')
    GO
    INSERT [dbo].[Shifts] ([id], [ShiftStart], [ShiftEnd], [ShiftSpan2Days], [IsNew], [IsOnline], [ModBy], [ModDate], [ModFrom]) VALUES (10, CAST(N'2017-06-08 00:00:00.0000000' AS DateTime2), CAST(N'2017-05-01 15:52:07.0342019' AS DateTime2), 1, 0, 1, N'Ramiro Berg', CAST(N'2017-06-20 10:37:35.0109073' AS DateTime2), N'Alfonso Potter')
    GO
    SET IDENTITY_INSERT [dbo].[Shifts] OFF
    GO
    USE [master]
    GO
    ALTER DATABASE [ShiftDatabase] SET  READ_WRITE 
    GO
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, June 28, 2017 11:14 PM
  • That is quite remarkable Karen

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Thursday, June 29, 2017 2:24 AM