ASP.NET SQL COUNT

Use
int count = reader1.GetInt32(0);
to get the results of of your query.

E.g.
Label4.Text = Convert.ToString(reader1.GetInt32(0));

still get nada..

i changed my sql string just in case to this
string cmdStringSingle = "SELECT  COUNT ([DB1].[ID]) FROM [DB1]";

Hi There,

Try this :
int count = ConvertTo.Int32(SingleC.ExecuteScalar());

Regards,
Chinmay

Try this:

OleDbCommand SingleC = new OleDbCommand(cmdStringSingle, conn);
SingleC.Connection.Open();
OleDbDataReader reader1 = SingleC.ExecuteReader();    
if(reader1.Read())
{
  Label4.Text = reader1[0].ToString();
}
SingleC.Connection.Close();

You definitely must call reader1.Read() method first, having done that then the following (as suggested earlier) will work:

Use
int count = reader1.GetInt32(0);
to get the results of of your query.

E.g.
Label4.Text = Convert.ToString(reader1.GetInt32(0));

Agree with above the absolute best way to return this kind of value is to use the,

ExecuteScalar() method of a command.

It was made for this very purpose and is absolute fastest way to return a count().

Hi Dev,

Thanks a lot for putting in this comment, on later stage I would have to say the same thing.

Regardsm
Chinmay

dstanley that did the job problem is when I apply it to my original SQL statement, it does not work
I already use the same statement to populate a gridview and that works.

whats wrong?

In what way does it not work?  Have you verified the SQL statement in Query Analyzer?  

Sorry.  Just read that ytou said you use the same statement in a GridView.  In what way does it not work for the label?

ok just wrote it out to a exception handler

"System.Data.OleDb.OleDbException: Wrong number of arguments used with function in query expression 'COUNT "

That looks like a SQL error.  Have you verified that the SQL statement works with the same parameters in Query Analuzer?

we are dealing with access not SQL db

mmm, perhaps I should do a SUM instead?

Can you create a Query with that statement and verify that it works with the same parameters?

I just looked in Access's help, and COUNT in Access does not support a column list like SQL does.  If you want unique records based on some set of columns, you have to combine them, for example converting to strings and concatenating.  If you want to count all rows, not just unique combinations, use count(*) instead.

how would i apply that to my statement (see first post)

System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression '(*)'.

can i get acount from my dataset instead

i started

 foreach (DataRow dataRows in ds)
{
 if ((dataRows["Hname"] as string) == "text")
 { count++; }
}

i dont know how to implement IEnumerable tho..

opps plus

int count=0;

Yes, you could do it that way.  You could also do it this way:

int count = (ds.Tables["tablename"].Select("Hname='text'")).Length

That example was assuming you're loading into a DataSet.

But COUNT(*) should work.  Can you paste in the entire query as you have it now?

Here's the Help docs on COUNT

Count(expr)

The expr placeholder represents a string expression identifying the field that contains the data you want to count or an expression that performs a calculation using the data in the field. Operands in expr can include the name of a table field or function (which can be either intrinsic or user-defined but not other SQL aggregate functions ). You can count any kind of data, including text.

Remarks
You can use Count to count the number of records in an underlying query. For example, you could use Count to count the number of orders shipped to a particular country.

Although expr can perform a calculation on a field, Count simply tallies the number of records. It does not matter what values are stored in the records.

The Count function does not count records that have Null fields unless expr is the asterisk (*) wildcard character . If you use an asterisk, Count calculates the total number of records, including those that contain Null fields. Count(*) is considerably faster than Count([Column Name]). Do not enclose the asterisk in quotation marks (' '). The following example calculates the number of records in the Orders table:

SELECT Count(*)

AS TotalOrders FROM Orders;

If expr identifies multiple fields, the Count function counts a record only if at least one of the fields is not Null. If all of the specified fields are Null, the record is not counted. Separate the field names with an ampersand (&). The following example shows how you can limit the count to records in which either ShippedDate or Freight is not Null:

SELECT

Count('ShippedDate & Freight')

AS [Not Null] FROM Orders;

You can use Count in a query expression. You can also use this expression in the SQL property of a QueryDef object or when creating a Recordset object based on an SQL query.

ORG:

string cmdStringSingle = "SELECT  COUNT ([DB1].[ID], Db2.[Client no], Db2.Title, Db2.Initials, Db2.Surname, [Db3].[No nights], [Db3].[Hotel coments],[Db3].[hotelnotes], db4.Status) FROM ((((([Db3] INNER JOIN Db2 ON [Db3].[Client no] = Db2.[Client no]) INNER JOIN db5 ON [Db3].Hid = db5.Hid) INNER JOIN [DB1] ON Db2.[ID] = [DB1].[ID]) INNER JOIN Db6 ON db5.HotelID = Db6.[Hotel Ref]) INNER JOIN db4 ON [Db3].StatusID = db4.StatusID)WHERE ( [DB1].active=false AND Db6.[Hname] = '" + strHname + "' AND [Db3].[Arrival Date]= " + startdate + " AND db4.Status=’active’) ORDER BY db4.StatusID, [DB1].[ID] ";

Count

string cmdStringSingle = "SELECT  COUNT (*) FROM ((((([Db3] INNER JOIN Db2 ON [Db3].[Client no] = Db2.[Client no]) INNER JOIN db5 ON [Db3].Hid = db5.Hid) INNER JOIN [DB1] ON Db2.[ID] = [DB1].[ID]) INNER JOIN Db6 ON db5.HotelID = Db6.[Hotel Ref]) INNER JOIN db4 ON [Db3].StatusID = db4.StatusID)WHERE ( [DB1].active=false AND Db6.[Hname] = '" + strHname + "' AND [Db3].[Arrival Date]= " + startdate + " AND db4.Status=’active’) ORDER BY db4.StatusID, [DB1].[ID] ";

then your part

OleDbCommand SingleC = new OleDbCommand(cmdStringSingle, conn);
            SingleC.Connection.Open();
            OleDbDataReader reader1 = SingleC.ExecuteReader();    
                if(reader1.Read())
                {
                    Label4.Text = reader1[0].ToString();
                }
            SingleC.Connection.Close();

Try removing the spaces Between count and (*) (Count(*) not Count  (*))

string cmdStringSingle = "SELECT COUNT(*) FROM (((((

still the same problem

Yes its sorted!

Try removing the ORDER BY clause since it's useless if you just want a count of all rows. Also, I cleaned up the SQL by removing all the parentheses arout the join clauses - it should make it easier to read:

string cmdStringSingle =
"SELECT  COUNT (*)  " +
" FROM  " +
" [Db3] " +
" INNER JOIN Db2 ON [Db3].[Client no] = Db2.[Client no] " +
" INNER JOIN db5 ON [Db3].Hid = db5.Hid " +
" INNER JOIN [DB1] ON Db2.[ID] = [DB1].[ID] " +
" INNER JOIN Db6 ON db5.HotelID = Db6.[Hotel Ref]  " +
" INNER JOIN db4 ON [Db3].StatusID = db4.StatusID " +
" WHERE ( [DB1].active=false AND Db6.[Hname] = '" + strHname + "'" +
"  AND [Db3].[Arrival Date]= " + startdate +
"  AND db4.Status=’active’) " ;

dstanley9 your a good man, thanks for spending along time helping me fix it!

Thanks, I appreciate it :)