Getting row Count only returns value of 1

I am using Sql Server 2005 and Vb Studios 2010 on a .net Framework of 4.0. I am trying to get the exact number of rows from a database and populate a datatable then have a label show the number of rows.

Dim comm2 = db.selectcommand(db.conn, "*", "Tablename", " columnname = '" &开发者_运维知识库 Session(sessionvariable) & "' AND columnname = 'Unread '")
    Dim sqlda2 As New SqlDataAdapter(comm2)
    Dim dt2 As New DataTable
    sqlda2.Fill(dt2)
    Dim recordcount As Integer = dt2.Rows.Count
    messagecountlbl.Text = recordcount

this will always return the value of 1 and I know for a fact that I have multiple values for the data I am trying to pull. I have atleast 50 and the label should be displaying that amount.

I have also tried the Select Count statement and it does the same thing.

Fix I have added this to the post since there is a 24 hr wait to answer question: I have found a quick and simple fix that I will shorten later in a class file that I have written but this should help alot of people out.

Dim sqlresult As Object
    Dim constring As String = "Connection string goes here"
    Dim dbcon As SqlConnection = New SqlConnection(constring)
    Dim sql As New SqlCommand
    dbcon.Open()
    sql.Connection = dbcon
    sql.CommandText = "SELECT COUNT (*) FROM Tablename WHERE Columnname = 'Unread' AND columnname = '" & Session("sessionvariable") & "'"
    sqlresult = sql.ExecuteScalar
    messagecountlbl.Text = sqlresult

Aren't you missing quotes around Unread?

Also... you're susceptible to SQL Injection. Use Parameters instead

Also... if all you are doing is getting the number of rows, your code is overkill

I just noticed.... you Dimmed comm2 but your adapter uses comm

Your updated question was the route that I would have gone. This was why i said your initial code was overkill (in terms of memory usage...). DataAdapters and DataTables for one value is in no way efficient.

You might want to change your updated code to the following... Again, look up SQL Injection to see why you should never (or at least try not to) build a sql string like that

Dim sqlresult As Object
Dim constring As String = "Connection string goes here"
Dim dbcon As SqlConnection = New SqlConnection(constring)
Dim sql As New SqlCommand
dbcon.Open()
sql.Connection = dbcon
sql.CommandText = "SELECT COUNT (*) FROM Tablename WHERE Columnname = 'Unread' AND columnname = @param"
sql.Parameters.AddWithValue("@param", Session("sessionvariable"))

sqlresult = sql.ExecuteScalar
messagecountlbl.Text = sqlresult

It looks like you're missing a single quote at the beginning of the word "Unread".

...& "' AND columnname = 'Unread'")

I note that your first line declares a variable called comm2 but you don't use it later - instead you're using simply comm.

With that in mind, the fact that you've omitted the quotes around Unread will still be relevant.

Fix I have added this to the post since there is a 24 hr wait to answer question: I have found a quick and simple fix that I will shorten later in a class file that I have written but this should help alot of people out.

Dim sqlresult As Object Dim constring As String = "Connection string goes here" Dim dbcon As SqlConnection = New SqlConnection(constring) Dim sql As New SqlCommand dbcon.Open() sql.Connection = dbcon sql.CommandText = "SELECT COUNT (*) FROM Tablename WHERE Columnname = 'Unread' AND columnname = '" & Session("sessionvariable") & "'" sqlresult = sql.ExecuteScalar messagecountlbl.Text = sqlresult