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

Focker513
Focker513 used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Reza RadConsultant, Trainer

Commented:
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

Author

Commented:
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?
Reza RadConsultant, Trainer

Commented:
could you paste part of you .net code that fetch count(*) here?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Sorry thought I had attached this in my original post.
Code-Sample.txt

Commented:
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

Author

Commented:
Value still comes through as 0.
Consultant, Trainer
Commented:
could you remove the "SET NOCOUNT ON; " from sql command and try again?
also let me know how do you read data from this command? did you used DataAdapter or ExecuteReader ...?

Author

Commented:
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

Author

Commented:
Also tried adding : sqlCommand.CommandType = CommandType.Text;

Author

Commented:
I guess this is not an easy solution. Sorry I have been doing this like crazy in Oracle.

Author

Commented:
I also tried to do a conversion from int to varchar in the sql statement convert(varchar(8),COUNT(*))

Author

Commented:
received a value from wcf service that injected a \ in place of an empty query.
Reza RadConsultant, Trainer

Commented:
Glad to see it solved

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial