Counting rows in a DB, VB.NET

September 15, 2005 at 10:45:14
Specs: xp, xp

Hello,

I have a web application that is linked up to an Access database. The table is called "Messages" and I want to display how many messages are unread on my webform. I need to count the number of rows in my database where: colRead = "No." Any suggestions?


See More: Counting rows in a DB, VB.NET

Report •


#1
September 15, 2005 at 11:33:35

Use the following SQL statement:

SELECT COUNT(*) AS [RecordCount] FROM Messages WHERE colRead = No

The variable RecordCount will contain the number of matching records.

Stuart


Report •

#2
September 15, 2005 at 12:08:51

I have the SQL statement that I need I just don't know what I need to use to access the database in my vb code. This is what I have but

Dim SQLResult As Object
Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\Organizer1.0\DB\organizer.mdb"
Dim dbConnection As OleDbConnection = New OleDbConnection(connStr)
Dim SQL As New OleDbCommand
dbConnection.Open()
SQL.Connection = dbConnection
SQL.CommandText = "SELECT COUNT * FROM Messages WHERE Read = No"
SQLResult = SQL.ExecuteNonQuery
Summary = SQLResult
lblMessages.Text = "You have " & Summary & " unread messages"

This is generally what I use (either ExecuteNonQuery or ExecuteScalar) to update and query my databases but it does not work with a COUNT statement.


Report •

#3
September 16, 2005 at 08:07:16

Looks like you did Read = No instead of colRead = No. Is that on purpose?

Also, you should use ExecuteScalar, not ExecuteNonQuery. ExecuteScalar indicates you want just one value back (in this case, a count), whereas execute nonquery implies you're not expecting anything back.

Good luck,
-SN


Report •

Related Solutions

#4
September 16, 2005 at 09:27:04

I thought ExecuteScalar returned the value of field in the first row returned, this is why you can't use a COUNT statement with .ExecuteScalar.

Report •

#5
September 16, 2005 at 10:28:13

That's exactly why you need to do execute scalar...count will return a 1x1 table with the only value being the count you asked for. ExecuteScalar will return that value.

Good luck,
-SN


Report •


Ask Question