[RESOLVED] Get Record Count

[SparrowHawk7]

Back with another simple question that I cannot seem to get past. Everywhere I look on the net people are using readers but it seems to me a nonquery is indicated for this ... Unfortunately my syntax is apparently off.

Using VB 2012:

Imports System.Data.SqlClient

Public Class clsProduct

    Public Sub New()

    End Sub

    Property ProductID As Integer
    Property ProductName As String
    Property Cost As String
    Property Shipping As String
    Property QtyOnHand As Integer
    Property ReorderLevel As Integer
    Property Remarks As String
    Property VendorID As Integer
    Property StatusID As Integer

    Public Shared Function GetProductCount(ID As Integer) As clsProduct

        Dim connection As SqlConnection = clsConnection.GetConnection

        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.Text
        cmd.CommandText =  "SELECT COUNT(*) FROM PRODUCTS WHERE VendorID = " & Chr(39) & ID & Chr(39)
        cmd.Connection = connection

        connection.Open()
        Dim CNT As Integer = cmd.ExecuteNonQuery()
        connection.Close()

        Return CNT

    End Function

End Class

VB2012 complains that value type integer (CNT) cannot be converted to cls.Product. Seems like it should be a syntax error somehow. All I want is the record count of products with a certain VendorID. What am I missing here?

TIA
Ken

[Shaggy Hiker]

It isn't a non-query you want, because you have a SELECT statement. What you want is cmd.ExecuteScalar, which will return the first value from the first row:

Dim CNT As Integer = CInt(cmd.ExecuteScalar)

Of course, this query only returns a single row and only returns a single value, so the fact that execute scalar only gets the first value from the first row is kind of convenient. This is also the fastest type of query.

One thing to note that if this returned NULL, the CInt would fail, but since you are returning a COUNT, it can't return NULL. If no records are found, it will return 0.

Another thing is that you might get ever so slightly better performance if you use COUNT(ID) rather than COUNT(*). They do the same thing, but depending on how the query engine handles things, the * may be inefficient relative to the count of a single field.

[SparrowHawk7]

Thanks for the reply! I made the changes you suggested which make perfect sense to me .. but I still get the complaint:

Value of type 'Integer' cannot be converted to 'Rentals.clsProduct'.

On the Return CNT line.

[Shaggy Hiker]

Well, yeah, of course. You have a function called GetProductCount, but the function is returning type clsProduct, which is a class. A count is almost always an integer, so why are you returning a class from a function that gets a product count?

[SparrowHawk7]

Of course! Silly mistake .. thanks for pointing it out. Duh! Thank you!