CODEWITHSUNDEEP
c#t-sql
Widunder
Widunder

Reputation: 295

count from ExecuteScalar() returns 0

I've done some research and everything I'm doing seems to be correct with what other people are suggesting to do. So haven't found yet why this isn't working and when I debug (int)command.ExecuteScalar() shows 0. Even though if I run the query manually - the result being returned in SQL Management Studio is 1. This is the code snippet I'm trying to get working:

using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                var command = new SqlCommand(@"
select count (distinct ProcessCalled) 
from ETL.ProcessMonitor 
where ProcessCalled = '@package' and ProcessStatusDescription in ('Running','RanToCompletion')
and ProcessStartDateTime > convert(datetime, @processEventDateTime, 120)", connection);
                command.Parameters.AddWithValue("processEventDateTime", _lastPackageExecution);
                command.Parameters.AddWithValue("package", package);
                return (int)command.ExecuteScalar() > 0;
            }

So what is wrong with it? Why (int)command.ExecuteScalar() returns 0 all the time? Cheers.

I've also tried to convert both the datetimes to just dates like so:

and cast(ProcessStartDateTime as date) = convert(date, @processEventDateTime, 120)

But its still not working in c#, still works in SQL Management

Have also just tried count(*), still not working in c# only

Upvotes: 0

Views: 1247

Answers (1)

OJay
OJay

Reputation: 4921

Try remove the quotes from around the '@package' in the query, you shouldn't need to do that, the command.Parameters.AddWithValue will take care of that for you. I do believe quoting it will treat it as a value, not as a parameter, i.e. in essence you are executing the query with ProcessCalled = '@package', literally the string @package.

Also, you should add the @ symbol to your AddWithValues i.e. command.Parameters.AddWithValue("@package", package);

Upvotes: 3

Related Questions