Datagrid Leaving Bad Row Behind - How to Prevent this Behavior

The following code builds a dataset and dataview for a datagrid on a Windows form.  It is executed everytime the user clicks on the btnSearch button after typing some text into tbText.  FilteredMattersList takes a string argument and calls a stored procedure which returns rows that match the text in tbText.  Because there are over 300k records in the table, I want the user to always specify a string to match on as opposed to returning all 300k records..  When the user leaves the field empty, I produce an error message.  All of this works fine.

However, if the user specifies a string for which zero records are returned (i.e. "zzzzzzzzzz"), one row in the datagrid is left on the form just dangling in the grid at row position 1.  It is not active and shouldn't really be there, but it is visible as well as annoying.

Example:

First dataview based upon tbText.Text = "ack" contains rows "bracket", "jacket" in that order.
Second dataview based upon tbText.Text = "zzzzzzzzzzzzz" contains no rows but the data grid shows a blank for row 0 and "bracket" in row 1 where "jacket" used to be even though it is a residual row and it doesn't really exist.

When I have a dataview that has no rows, how can I get the datagrid to show no residue from the previous dataview's contents?

      Private Sub GetGridData()
            If tbText.Text.Trim <> Nothing Then
                  cOriginalText = tbText.Text.Trim
                  dsMatters = oDataServices.FilteredMattersList(tbText.Text.Trim)
                  dvMatters = dsMatters.Tables("Matter").DefaultView
                  dvMatters.Sort = "Description"
                  dgMatters.DataSource = dvMatters
                  dgMatters.Focus()
            Else
                  tbText.Text = cOriginalText
                  MsgBox("The Text box cannot be left empty." + vbCrLf + "Please enter text to filter on.", _
                   MsgBoxStyle.OKOnly + MsgBoxStyle.Information, "Data Entry Required")
                  tbText.Focus()
            End If
      End Sub
LVL 1
Edward StevensIT ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
Is the DataGrid in read-only mode?

Bob
0
Edward StevensIT ConsultantAuthor Commented:
Yes - the datagrid is in read-only mode so that users will not try to update the data from there.  I am using the grid as a selection tool.  The user will eventually be presented with a full editing form to make their changes to the data.

Ed
0
Bob LearnedCommented:
The DataView has an AllowNew property:

 dsMatters.Tables("Matter").DefaultView.AllowNew = False.

Bob
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Edward StevensIT ConsultantAuthor Commented:
I applied your suggestion.  It did not help.  However, I paid a bit more attention to the issue this time and have some info to add.

Where I had mentioned that the original datagrid row 0 was remaining on the grid as row 1...   ...that's not true.  The residual row is remaining at the row position from which it was originally painted.  It is the row that was selected when the datasource was changed to a dataview without any rows.

Example:  If row 5 was selected when the empty dataview is assigned as the data source, then row 5 is the residual row and it is still painted in row 5's position.

Ed
0
Bob LearnedCommented:
What happens if you dgMatters.Refresh?

Bob
0
Edward StevensIT ConsultantAuthor Commented:
Bob:

Sorry it took me a few days to get back to you.  I did the dgMatters.Refresh but no dice.  The problem persists.  This appears to be a real stumper.

Ed
0
Bob LearnedCommented:
Well, Ed, since I am not the DataGrid expert (I don't really use the stupid thing), I am not sure if I can find a way to help with this one.  I was hoping to go for the easy one, but it doesn't look that I was going to get that lucky :(

Bob
0
Edward StevensIT ConsultantAuthor Commented:
Thanks Bob.  I appreciate all of your attempts.

I'll leave the question out here a little longer to see if anyone else wants to take a crack at it.

Ed
0
Bob LearnedCommented:
Ok, but I really doubt that others will see it now.  They generally don't want to waste their time.  I would ask for a refund, and reask this question, and then I won't respond.

Bob
0
Edward StevensIT ConsultantAuthor Commented:
Bob:

I have an annual subscription so I don't think a refund is a concern to me.  However, I do have a question based upon your statement.  I have never gone into Expert Mode so I am unfamiliar with how that works.  What did you mean others won't see it?

Ed
0
Bob LearnedCommented:
These are my observation about how E-E works:

There are many factors with E-E for experts IMHO:

(1) Interest
(2) Timing of question
(3) Level of expertise
(4) Disrespect from the askers
(5) Language barriers
(6) Misunderstandings

The general rule is:  If you don't get a question answered to your liking, then there are a couple of options:

(1) Post a 20-point question pointing to this one, so that it gets moved back to the top of the list (Most experts don't take the time to scroll, since it is generally assumed that someone is already on it, and it isn't worth their time).

(2) Post a free question in Community Support as ask for a refund:
http://www.experts-exchange.com/Community_Support/

The contributing experts will be able to respond as to whether they think that it should be deleted or not, so always remember that respect above anything is paramount.


Bob

0
Edward StevensIT ConsultantAuthor Commented:
Thanks for all of your help Bob.

I have re-posted the question.
0
Bob LearnedCommented:
Would you like this question deleted?

Bob
0
amyhxuCommented:
This is a known issue with datagird readonly cells. The following approach at http://www.syncfusion.com/FAQ/WinForms   DataGrid section Q5.41  will solve your problem by always selecting whole row while user click on one cell.
The website seems not available right now. I'll copy the comment and code below:


5.41 How can I make my grid never have an active edit cell and always select whole rows (as in a browser-type grid)?    

For a single row select datagrid, you can get both these behaviors by using a custom column style and overriding its Edit method. In your override, handle unselecting and selecting the current row, and DO NOT call the base class. Not calling the base class keeps the cell from becoming active.
 
Option Strict Off
Option Explicit On

Imports Microsoft.VisualBasic
Imports System
Imports System.ComponentModel
Imports System.Data
Imports System.Data.Common
Imports System.Data.OleDb
Imports System.Drawing
Imports System.Windows.Forms

Namespace DataGridBrowser
    Public Class Form1
        Inherits Form
        Private WithEvents dataGrid1 As DataGrid
        Private components As Container
        'Fields
        'Constructors
        'Events
        'Methods
        Public Sub New()
            MyBase.New()
            '
            ' Required for Windows Form Designer support
            '
            InitializeComponent()

        End Sub
        Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)

            If disposing Then
                If (Not (components) Is Nothing) Then
                    components.Dispose()
                End If
            End If
            MyBase.Dispose(disposing)

        End Sub
        Private Sub InitializeComponent()

            Me.dataGrid1 = New System.Windows.Forms.DataGrid()
            CType(Me.dataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
            Me.SuspendLayout()
            '
            ' dataGrid1
            '
            Me.dataGrid1.Anchor = (((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _
                        Or System.Windows.Forms.AnchorStyles.Left) _
                        Or System.Windows.Forms.AnchorStyles.Right)
            Me.dataGrid1.DataMember = ""
            Me.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
            Me.dataGrid1.Location = New System.Drawing.Point(24, 24)
            Me.dataGrid1.Name = "dataGrid1"
            Me.dataGrid1.Size = New System.Drawing.Size(448, 280)
            Me.dataGrid1.TabIndex = 0
            '
            ' Form1
            '
            Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
            Me.ClientSize = New System.Drawing.Size(504, 341)
            Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.dataGrid1})
            Me.Name = "Form1"
            Me.Text = "Form1"
            AddHandler Me.Load, New System.EventHandler(AddressOf Form1_Load)
            CType(Me.dataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
            Me.ResumeLayout(False)

        End Sub
        <STAThread()> _
        Public Shared Sub Main()

            Application.Run(New Form1())

        End Sub
        Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)

            ' Set the connection and sql strings
            ' assumes your mdb file is in your root
            Dim connString As String
            connString = "Provider=Microsoft.JET.OLEDB.4.0;data source=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
            Dim sqlString As String
            sqlString = "SELECT * FROM customers"
            Dim dataAdapter As OleDbDataAdapter
            dataAdapter = Nothing
            Dim _dataSet As DataSet
            _dataSet = Nothing
            ' Create a table style that will hold the new column style
            ' that we set and also tie it to our customer's table from our DB
            Try
                ' Connection object
                Dim connection As OleDbConnection
                connection = New OleDbConnection(connString)
                ' Create data adapter object
                dataAdapter = New OleDbDataAdapter(sqlString, connection)
                ' Create a dataset object and fill with data using data adapter's Fill method
                _dataSet = New DataSet()
                dataAdapter.Fill(_dataSet, "customers")
                connection.Close()
            Catch ex As Exception
                MessageBox.Show(((((("Problem with DB access-" & Microsoft.VisualBasic.ChrW(10) & Microsoft.VisualBasic.ChrW(10) & "   connection: " + connString) _
                                + "" & Microsoft.VisualBasic.ChrW(13) & Microsoft.VisualBasic.ChrW(10) & Microsoft.VisualBasic.ChrW(13) & Microsoft.VisualBasic.ChrW(10) & "            query: ") _
                                + sqlString) _
                                + "" & Microsoft.VisualBasic.ChrW(13) & Microsoft.VisualBasic.ChrW(10) & Microsoft.VisualBasic.ChrW(13) & Microsoft.VisualBasic.ChrW(10) & Microsoft.VisualBasic.ChrW(13) & Microsoft.VisualBasic.ChrW(10)) _
                                + ex.ToString))
                Me.Close()
                Return
            End Try
            Dim tableStyle As DataGridTableStyle
            tableStyle = New DataGridTableStyle()
            tableStyle.MappingName = "customers"
            ' since the dataset has things like field name and number of columns,
            ' we will use those to create new columnstyles for the columns in our DB table
            Dim numCols As Integer
            numCols = _dataSet.Tables("customers").Columns.Count
            Dim aColumnTextColumn As DataGridNoActiveCellColumn
            Dim i As Integer
            i = 0

            Do While (i < numCols)
                aColumnTextColumn = New DataGridNoActiveCellColumn()
                aColumnTextColumn.HeaderText = _dataSet.Tables("customers").Columns(i).ColumnName
                aColumnTextColumn.MappingName = _dataSet.Tables("customers").Columns(i).ColumnName
                tableStyle.GridColumnStyles.Add(aColumnTextColumn)
                i = (i + 1)

            Loop
            ' make the dataGrid use our new tablestyle and bind it to our table
            dataGrid1.TableStyles.Clear()
            dataGrid1.TableStyles.Add(tableStyle)
            dataGrid1.DataSource = _dataSet.Tables("customers")

        End Sub
    End Class
End Namespace

--------------------------------------------------------------------------------------------

Option Strict Off
Option Explicit On

Imports Microsoft.VisualBasic
Imports System
Imports System.Drawing
Imports System.Windows.Forms

Namespace DataGridBrowser
    Public Class DataGridNoActiveCellColumn
        Inherits DataGridTextBoxColumn
        Private SelectedRow As Integer
        'Fields
        'Constructors
        'Events
        'Methods
        Public Sub New()
            'Warning: Implementation not found
        End Sub
        Protected Overloads Overrides Sub Edit(ByVal source As CurrencyManager, ByVal rowNum As Integer, ByVal bounds As Rectangle, ByVal [readOnly] As Boolean, ByVal instantText As String, ByVal cellIsVisible As Boolean)
            'make sure selectedrow is valid
            If (SelectedRow > -1) And (SelectedRow < source.List.Count + 1) Then
                Me.DataGridTableStyle.DataGrid.UnSelect(SelectedRow)
            End If
            SelectedRow = rowNum
            Me.DataGridTableStyle.DataGrid.Select(SelectedRow)

        End Sub
    End Class
End Namespace
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
amyhxuCommented:
I wrote my own code to simulate the same behavior above, seems it's working. But I am not sure if it will always work under any condition:

   dgMatters.ReadOnly = True

   Private Sub dgMatters_MouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgMatters.MouseUp
        dgMatters.Select(dgMatters.CurrentRowIndex)
    End Sub
0
Edward StevensIT ConsultantAuthor Commented:
amyhxu:

I have been and still am out of the office.  Sorry for not getting back to you sooner.  I will try this suggestion on Monday when I return to the office.  Thanks
0
Edward StevensIT ConsultantAuthor Commented:
amyhxu,

I will be attempting your suggestion in the next hour or so.  I will post my results at that time.  Thanks
0
Edward StevensIT ConsultantAuthor Commented:
amyhxu,

Is your example making an assumption that the data view has at least one row?  From the code that I see, it looks like there needs to be a row to select.  My issue is that the data view is generated by a SQL stored procedure, which during this condition, is returning no rows.  Keep in mind that the data view has no physical rows and there is no filter on the data view.  In circumstances where my data view has at least one row and the "filter" does not include any of those rows, the data grid works fine as is and shows a blank area..

Ed
0
Edward StevensIT ConsultantAuthor Commented:
amyhxu,

I tried a few variations of your code and got it to work.  Thanks much for the concepts!  I can finally move on to more important things.  Yippy!   :)

Ed
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.