Solved

ASP.NET SQL COUNT

Posted on 2006-07-05
32
435 Views
Last Modified: 2008-01-09

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?
0
Comment
Question by:ziwez0
  • 14
  • 13
  • 2
  • +2
32 Comments
 
LVL 7

Expert Comment

by:mjmarlow
ID: 17043995
Use
int count = reader1.GetInt32(0);
to get the results of of your query.

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

0
 
LVL 2

Author Comment

by:ziwez0
ID: 17044083
still get nada..

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

Expert Comment

by:Chinmay Patel
ID: 17044398
Hi There,

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

Regards,
Chinmay
0
 
LVL 25

Expert Comment

by:dstanley9
ID: 17044472
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
 
LVL 7

Expert Comment

by:mjmarlow
ID: 17044554
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
 
LVL 13

Expert Comment

by:devsolns
ID: 17044878
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
 
LVL 27

Expert Comment

by:Chinmay Patel
ID: 17045193
Hi Dev,

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

Regardsm
Chinmay
0
 
LVL 2

Author Comment

by:ziwez0
ID: 17049056
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
 
LVL 25

Expert Comment

by:dstanley9
ID: 17050438
In what way does it not work?  Have you verified the SQL statement in Query Analyzer?  
0
 
LVL 25

Expert Comment

by:dstanley9
ID: 17050446
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
 
LVL 2

Author Comment

by:ziwez0
ID: 17050505
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
 
LVL 25

Expert Comment

by:dstanley9
ID: 17050516
That looks like a SQL error.  Have you verified that the SQL statement works with the same parameters in Query Analuzer?
0
 
LVL 2

Author Comment

by:ziwez0
ID: 17050531
we are dealing with access not SQL db
0
 
LVL 2

Author Comment

by:ziwez0
ID: 17050555
mmm, perhaps I should do a SUM instead?
0
 
LVL 25

Expert Comment

by:dstanley9
ID: 17050569
Can you create a Query with that statement and verify that it works with the same parameters?
0
 
LVL 25

Expert Comment

by:dstanley9
ID: 17050583
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 2

Author Comment

by:ziwez0
ID: 17051132
how would i apply that to my statement (see first post)
0
 
LVL 25

Accepted Solution

by:
dstanley9 earned 500 total points
ID: 17051218
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
 
LVL 2

Author Comment

by:ziwez0
ID: 17051269
System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression '(*)'.
0
 
LVL 2

Author Comment

by:ziwez0
ID: 17051304

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
 
LVL 2

Author Comment

by:ziwez0
ID: 17051315
opps plus

int count=0;
0
 
LVL 25

Expert Comment

by:dstanley9
ID: 17051437
Yes, you could do it that way.  You could also do it this way:

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

Expert Comment

by:dstanley9
ID: 17051444
That example was assuming you're loading into a DataSet.
0
 
LVL 25

Expert Comment

by:dstanley9
ID: 17051456
But COUNT(*) should work.  Can you paste in the entire query as you have it now?
0
 
LVL 25

Expert Comment

by:dstanley9
ID: 17051504
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
 
LVL 2

Author Comment

by:ziwez0
ID: 17051696
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
 
LVL 25

Expert Comment

by:dstanley9
ID: 17051795
Try removing the spaces Between count and (*) (Count(*) not Count  (*))
0
 
LVL 2

Author Comment

by:ziwez0
ID: 17051818
string cmdStringSingle = "SELECT COUNT(*) FROM (((((

still the same problem
0
 
LVL 2

Author Comment

by:ziwez0
ID: 17052120
Yes its sorted!
0
 
LVL 25

Expert Comment

by:dstanley9
ID: 17052123
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
 
LVL 2

Author Comment

by:ziwez0
ID: 17052137
dstanley9 your a good man, thanks for spending along time helping me fix it!
0
 
LVL 25

Expert Comment

by:dstanley9
ID: 17052203
Thanks, I appreciate it :)
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now