CODEWITHSUNDEEP
vb.netdatagridview
Vector
Vector

Reputation: 3235

DataGridView moves rows to bottom when cell is updated with Unbound data

I have a DataGridView that is styled with code and is using data from a SQLite Database
The Database is NOT bound to the DataGridView. A number of events are triggered when I click on a row.
First the Database is updated with today's date.
And the cell that contain's that date reflects the change.
I then call a sort on the column based on the cells value. With this code

 dgvLinks.Sort(dgvLinks.Columns(3), ListSortDirection.Ascending)

The process works as expected with no issues IF I omit these lines of code from the Sub Routine ViewSearches() that populates the DataGridView.

  If rowCount <= 25 Then
     maxRowCount = 25 - rowCount
     For iA = 1 To maxRowCount
        dgvLinks.Rows.Add(" ")
     Next
  End If

I can use these empty rows if I make a call to repopulate the DataGridView with ViewSearches()
I am trying to avoid this design as it seems like a over use of resource.
The ERROR that is happening is the 4 rows that contain data are moved to the bottom of the DataGridView and above these 4 rows with data are the empty rows. I will post the relevant code below.
My question How can I keep the empty rows and populate DataGridView so the rows with data are at the top of the DataGridView?
 Here is a Screen Shot after I selected LID 2. It is updated and bubbled to the bottom of the DGV.
DataGridView

    Private Sub ViewSearches()

    Dim intID As Integer
    Dim strChannelName As String
    Dim strLinkAddress As String
    Dim strLastVisit As String
    Dim strLinkType As String
    Dim rowCount As Integer
    Dim maxRowCount As Integer
    'Dim emptyStr As String = "  "

    Using conn As New SQLiteConnection($"Data Source = '{gv_dbName}';Version=3;")
        conn.Open()

        Using cmd As New SQLiteCommand("", conn)

            'cmd.CommandText = "SELECT * FROM LinkTable"
            ' Line of CODE Above works with If statement in  While rdr
            '==========================================================
            'cmd.CommandText = "SELECT * FROM LinkTable WHERE ytSiteType = 'News'"

            cmd.CommandText = "SELECT * FROM LinkTable WHERE ytSiteType = @site"
            cmd.Parameters.Add("@site", DbType.String).Value = gvSLT

            Using rdr As SQLite.SQLiteDataReader = cmd.ExecuteReader
                'dgvLinks.DataSource = rdr
                'Statement Above use when DB is bound to dgvLinks
                '=================================================

                While rdr.Read()
                    intID = CInt((rdr("LID")))
                    strChannelName = rdr("ytChannelName").ToString
                    strLinkAddress = rdr("ytLinkAddress").ToString
                    strLastVisit = rdr("ytLastVisit").ToString
                    strLinkType = rdr("ytSiteType").ToString
                    'If strLinkType = gvSLT Then
                    dgvLinks.Rows.Add(intID, strChannelName, strLinkAddress, strLastVisit)
                    rowCount = rowCount + 1
                    'End If
                End While

                dgvLinks.Sort(dgvLinks.Columns(3), ListSortDirection.Ascending)

            End Using

            If rowCount <= 25 Then
                maxRowCount = 25 - rowCount
                For iA = 1 To maxRowCount
                    dgvLinks.Rows.Add(" ")
                Next
            End If

        End Using
    End Using
    'FindEmpty()
End Sub

Click Event with Update to Database

    Private Sub dgvLinks_CellClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvLinks.CellClick

    selRow = e.RowIndex

    If e.RowIndex = -1 Then
        gvalertType = "4"
        frmAlert.ShowDialog()
        Exit Sub
    End If

    'Dim col As DataGridViewColumn = Me.dgvLinks.Columns(e.ColumnIndex)
    Dim row As DataGridViewRow = Me.dgvLinks.Rows(e.RowIndex)
    If row.Cells(2).Value Is Nothing Then
        gvalertType = "5"
        frmAlert.ShowDialog()
        Return
        Exit Sub
    ElseIf gvTxType = "View" Then
        webPAGE = row.Cells(2).Value.ToString()
        siteID = CInt(row.Cells(0).Value.ToString())

        UpdateSiteData()

        ''MsgBox("Stop " & selRow)
        'dgvLinks.ClearSelection()
        'dgvLinks.Refresh()
        'dgvLinks.RefreshEdit()

        Process.Start(webPAGE)

        'dgvLinks.Columns.Clear()

        ''dgvLinks.Rows.Clear()
        ''ViewSearches()


    ElseIf gvTxType = "Delete" Or gvTxType = "Update" Then
        gvID = CInt(row.Cells(0).Value)
        gvSiteName = row.Cells(1).Value.ToString
        gvSiteURL = row.Cells(2).Value.ToString
        frmADE.Show()
        Close()
    End If

End Sub

Update Routine

    Public Sub UpdateSiteData()

    Dim dateToday = Date.Today
    dateToday = CDate(CDate(Date.Today).ToString("M-d-yyyy"))

    Using conn As New SQLiteConnection($"Data Source = '{gv_dbName}';Version=3;"),
        cmd As New SQLiteCommand("UPDATE LinkTable SET ytLastVisit = @ytLastVisit WHERE LID =" & siteID, conn)
        conn.Open()

        cmd.Parameters.Add("@ytLastVisit", DbType.String).Value = dateToday.ToString("M-d-yyyy")

        cmd.ExecuteNonQuery()

        dgvLinks.Rows(selRow).Cells(3).Value = dateToday.ToString("M-d-yyyy")

        'Line of code above INSERTS value in Last Visit Column at the correct ROW
        'NOT needed if you reload data from the database
        '=========================================================================
        'dgvLinks.Refresh()
        'dgvLinks.RefreshEdit()

        dgvLinks.Sort(dgvLinks.Columns(3), ListSortDirection.Ascending)

    End Using

End Sub

You will see a number of things I have tried commented out. As I said I can FIX the issue if I make a call to the ViewSearches() Sub Routine.

    Private Sub StyleDGV()

    'Sets Design of the DataGridView
    '===============================
    dgvLinks.DefaultCellStyle.Font = New Font("Times New Roman", 13.0F, FontStyle.Bold)
    dgvLinks.ColumnCount = 4
    dgvLinks.Columns(0).Width = 60   'ID
    dgvLinks.Columns(1).Width = 325  'Site Name 325
    dgvLinks.Columns(2).Width = 860  'Site Url 860
    dgvLinks.Columns(3).Width = 154  'LastVisit 140
    'Option with no blank rows increase col count to 5
    'OR increase width of col(3) WHY? because the scroll bar is not showing 
    ' TOTAL Width 1450 Height 488
    '=============================
    'To Set Col Header Size Mode = Enabled
    'To Set Col Header Default Cell Styles DO in Properties
    'dgvLinks.Columns(6).DefaultCellStyle.Format = "c"

    dgvLinks.ColumnHeadersHeight = 10 'Sans Serif 'Tahoma
    dgvLinks.ColumnHeadersDefaultCellStyle.Font = New Font("Sans Serif", 12.0F, FontStyle.Bold)

    dgvLinks.ColumnHeadersDefaultCellStyle.ForeColor = Color.Blue
    dgvLinks.DefaultCellStyle.BackColor = Color.LightGoldenrodYellow

    'DGV Header Names
    dgvLinks.Columns(0).Name = "LID"
    dgvLinks.Columns(1).Name = "Site Name"
    dgvLinks.Columns(2).Name = "Site URL"
    dgvLinks.Columns(3).Name = "Last Visit"

    dgvLinks.Columns(0).SortMode = DataGridViewColumnSortMode.NotSortable
    dgvLinks.Columns(1).SortMode = DataGridViewColumnSortMode.NotSortable
    dgvLinks.Columns(2).SortMode = DataGridViewColumnSortMode.NotSortable
    dgvLinks.Columns(3).SortMode = DataGridViewColumnSortMode.NotSortable

End Sub

Any one following this question the FIX that permitted keeping the empty rows was to just omit the sort command in the Update to Database Sub Routine

Upvotes: 1

Views: 98

Answers (1)

JohnG
JohnG

Reputation: 9469

As far as getting the data from the SQLite DB into the grid… you almost have it in the ViewSearches method. The command text looks good; however you are using an SQLiteDataReader. This is resorting to reading the data line by line.

I suggest you use the SQLiteDataAdapter instead. With it you can get the DataTable from the DB in one shot. First create and initialize a DataSet, then create a new SQLiteDataAdapter then add your command to the data adapter something like…

DataSet ds = new DataSet();

using (SQLiteDataAdapter sqlDA = new SQLiteDataAdapter()) {
  conn.Open();
  sqlDA.SelectCommand = cmd.CommanText;
  sqlDA.Fill(ds, “tableName”);
  if (ds.Tables.Count > 0) {
    //return ds.Tables[“tableName”];
    dgvLinks.DataSource = ds.Tables[“tableName”];
  }
}

This will add a DataTable to the DataSet ds called “tableName” if the query succeeded.

Then simply use that DataTable as a DataSource to the grid… something like…

dgvLinks.DataSource = ds.Tables[“tableName”];

Upvotes: 1

Related Questions