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?

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

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.

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

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.

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