Link to home
Start Free TrialLog in
Avatar of Focker513
Focker513Flag for United States of America

asked on

Need to execute sql query to get count and other values as union into a DataTable

I have a sql query that works when directly connected to the database but is returning 0 on the count(*) as Value operations when executed through c#

I am loading the query into a datatable from a dataadapter and subsequently parsing the datatable into json. Do I need to specify count as a scalar in order to make this work through the data adapter?

Thanks!
Avatar of Reza Rad
Reza Rad
Flag of New Zealand image

first: are you sure that you pointing same database in C#?
second: how did you read count(*) in C#? did you used ExecuteScalar? if no, try ExecuteScalar to get count(*) and let us know if you have any problem
Avatar of Focker513

ASKER

Yes, I am sure I am connected to the same DB. Using that union statement and the data adapter how would I incorporate the executescalar?
could you paste part of you .net code that fetch count(*) here?
Sorry thought I had attached this in my original post.
Code-Sample.txt
Try adding "SET NOCOUNT ON;" before "SELECT 'Year > ..."

sqlCommand.CommandText="SET NOCOUNT ON; SELECT 'Year  > 2010' AS YEAR_BUILT, COUNT(*) AS VALUE " +
                                                   " FROM STRUCTURE AS STRUCTURE " + 
                                                   " WHERE (YR > 2010) " +   
                                                   " UNION ALL " + 
                                                   " SELECT 'Year Between 2000 - 2010' AS YEAR_BUILT, COUNT(*) AS VALUE " +  
                                                   " FROM STRUCTURE AS STRUCTURE " +
                                                   " WHERE (YR BETWEEN 2000 AND 2010) " ;

Open in new window

Value still comes through as 0.
ASKER CERTIFIED SOLUTION
Avatar of Reza Rad
Reza Rad
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am using the data adapter per the attachment.
I removed the "SET NOCOUNT ON" but still get 0 as a returned Value for count
Also tried adding : sqlCommand.CommandType = CommandType.Text;
I guess this is not an easy solution. Sorry I have been doing this like crazy in Oracle.
I also tried to do a conversion from int to varchar in the sql statement convert(varchar(8),COUNT(*))
received a value from wcf service that injected a \ in place of an empty query.
Glad to see it solved