ASP.NET SQL COUNT


Just simply trying to return a count into a label

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] ";

OleDbCommand SingleC = new OleDbCommand(cmdStringSingle, conn);
SingleC.Connection.Open();
OleDbDataReader reader1 = SingleC.ExecuteReader();    
Label4.Text = reader1.ToString() ;
SingleC.Connection.Close();

get no errors, and no result i know...Label4.Text = reader1.ToString() ; is wrong
what have i missed?
LVL 2
ziwez0Asked:
Who is Participating?
 
dstanley9Connect With a Mentor Commented:
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] ";

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

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

0
 
ziwez0Author Commented:
still get nada..

i changed my sql string just in case to this
string cmdStringSingle = "SELECT  COUNT ([DB1].[ID]) FROM [DB1]";
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Chinmay PatelEnterprise ArchitectCommented:
Hi There,

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

Regards,
Chinmay
0
 
dstanley9Commented:
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();
0
 
mjmarlowCommented:
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));
0
 
devsolnsCommented:
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().
0
 
Chinmay PatelEnterprise ArchitectCommented:
Hi Dev,

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

Regardsm
Chinmay
0
 
ziwez0Author Commented:
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?
0
 
dstanley9Commented:
In what way does it not work?  Have you verified the SQL statement in Query Analyzer?  
0
 
dstanley9Commented:
Sorry.  Just read that ytou said you use the same statement in a GridView.  In what way does it not work for the label?
0
 
ziwez0Author Commented:
ok just wrote it out to a exception handler

"System.Data.OleDb.OleDbException: Wrong number of arguments used with function in query expression 'COUNT "
0
 
dstanley9Commented:
That looks like a SQL error.  Have you verified that the SQL statement works with the same parameters in Query Analuzer?
0
 
ziwez0Author Commented:
we are dealing with access not SQL db
0
 
ziwez0Author Commented:
mmm, perhaps I should do a SUM instead?
0
 
dstanley9Commented:
Can you create a Query with that statement and verify that it works with the same parameters?
0
 
dstanley9Commented:
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.
0
 
ziwez0Author Commented:
how would i apply that to my statement (see first post)
0
 
ziwez0Author Commented:
System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression '(*)'.
0
 
ziwez0Author Commented:

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..
0
 
ziwez0Author Commented:
opps plus

int count=0;
0
 
dstanley9Commented:
Yes, you could do it that way.  You could also do it this way:

int count = (ds.Tables["tablename"].Select("Hname='text'")).Length
0
 
dstanley9Commented:
That example was assuming you're loading into a DataSet.
0
 
dstanley9Commented:
But COUNT(*) should work.  Can you paste in the entire query as you have it now?
0
 
dstanley9Commented:
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.
0
 
ziwez0Author Commented:
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();
0
 
dstanley9Commented:
Try removing the spaces Between count and (*) (Count(*) not Count  (*))
0
 
ziwez0Author Commented:
string cmdStringSingle = "SELECT COUNT(*) FROM (((((

still the same problem
0
 
ziwez0Author Commented:
Yes its sorted!
0
 
dstanley9Commented:
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’) " ;
0
 
ziwez0Author Commented:
dstanley9 your a good man, thanks for spending along time helping me fix it!
0
 
dstanley9Commented:
Thanks, I appreciate it :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.