Get number of Rows populated in SQL Server from within Vb.net

[lmalave]

Hi,

I am trying to get the the number of rows that are populated in a SQL Server table. I am using the code below but it only counts one row everytime when I know in fact that there are 47 row in a particular table. Could someone please point out what I am doing wrong?

Regards,

        Dim connetionString As String
        Dim connection As SqlConnection
        Dim command As SqlCommand
        Dim adapter As New SqlDataAdapter
        Dim ds As New DataSet
        Dim sql As String

        connetionString = ("SERVER=x.x.x.x;database=ITDatabase;trusted_connection=yes;")
        sql = ("SELECT COUNT(*)FROM ASSETINFO;")
        connection = New SqlConnection(connetionString)

        Try
            connection.Open()
            command = New SqlCommand(sql, connection)
            adapter.SelectCommand = command
            adapter.Fill(ds, "SQLTempTable")

            lbltotalAssetsinDatabase.Text = ds.Tables(0).Rows.Count
            adapter.Dispose()
            command.Dispose()
            connection.Close()

        Catch ex As Exception
            MsgBox("Can not open connection ! ")
        End Try

[hericles]

lbltotalAssetsinDatabase.Text = ds.Tables(0).Rows.Count
That is giving you the number of rows in the dataTable. There is only one row but it holds the value 47.
You want:
lbltotalAssetsinDatabase.Text = ds.Tables(0).Rows[0][0].ToString()
The [0][0] accesses the first cell of the first row of the dataTable. Alternatively you could name the column in your SQL query and refer to it more specifically:
sql = ("SELECT COUNT(*) AS count FROM ASSETINFO;")
lbltotalAssetsinDatabase.Text = ds.Tables(0).Rows[0]['count'].ToString()

[lmalave]

I got it working after changing the SELECT part of the code to:

sql = ("SELECT * FROM ASSETINFO;")

Now it returns the correct number of rows (47).

Thank you for taking time to assit.
Regards,