Avatar of Focker513
Focker513
Flag 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!
.NET ProgrammingMicrosoft SQL Server

Avatar of undefined
Last Comment
Reza Rad

8/22/2022 - Mon
Reza Rad

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
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?
Reza Rad

could you paste part of you .net code that fetch count(*) here?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Focker513

ASKER
Sorry thought I had attached this in my original post.
Code-Sample.txt
yawkey13

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

Focker513

ASKER
Value still comes through as 0.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Reza Rad

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Focker513

ASKER
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
Focker513

ASKER
Also tried adding : sqlCommand.CommandType = CommandType.Text;
Focker513

ASKER
I guess this is not an easy solution. Sorry I have been doing this like crazy in Oracle.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Focker513

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

ASKER
received a value from wcf service that injected a \ in place of an empty query.
Reza Rad

Glad to see it solved
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.