Counting Records... 1

For an excellent discussion on the topic of returning a record count, e.g., in a grid, see the following:

http://www.dotnet247.com/247reference/msgs/19/99835.aspx

...good review on general considerations

 

[Isadore]

After reviewing the literature it was obvious there were several approaches, as expected. Using a DataReader looping through the records and incrementing a counter was not recommended. The old ASP technique referencing a textbox didn't seem to work. I had no luck with the "Execute.Scalar" approach, which was recommended. There were various arguments for and against the use of SELECT COUNT(*), which again I did not have luck implimenting the first few times looking at it.

At any rate, I wanted to return a record count while binding a datagrid to a specified SQL query, without having to re-loop again through the records. The following approach worked perfectly:

Sub GetSites (sortField As String)
'open database...
Dim cmdSelect As OLEDbCommand
Dim dbconn As OleDbConnection = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & Server.MapPath(".\fpdb\Sites.mdb;&quot)
cmdSelect = New OLEDbCommand("SELECT AwwSiteCode, Active, County, ... FROM tblSites WHERE " & "County ='" & ddCty.SelectedItem.Value & "'" & " ORDER BY " & sortField, dbconn)
Try
dbconn.Open()
dgName.DataSource = cmdSelect.ExecuteReader()
dgName.DataBind()
lblNo.Text = "No. of Records " & dgName.Items.Count.ToString()
Catch err As Exception
lblError.Text = "Error reading Sites. "
lblResults.Text &= err.Message
Finally
If (Not dbconn Is Nothing) Then
dbconn.Close()
End If
End Try

...FYI