Query returning NULL value

Question

I'm trying to execute a query in C# which sums the view count of a user. I get returned a NULL value. Using the same statement in Server Management Studio gives me the correct result.

here's my code:

    public static int Count_views(string username)
{
    int views = 0;
    StringBuilder query = new StringBuilder();
    query.Append("SELECT Sum(views) FROM videos WHERE username = @username");

    using (SqlConnection con = new SqlConnection(Config.ConnectionString))
    {
        con.Open();

        using (SqlCommand cmd = new SqlCommand(query.ToString(), con))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@username", username));

            views = Convert.ToInt32(cmd.ExecuteScalar());
        }
    }

    return views;
}

I have debugged the code and the parameters are correct. I get this error :

System.InvalidCastException: Object cannot be cast from DBNull to other types.

which means I'm getting a Null value in return.

The ConnectionString is alright. Every other function works fine except for this one. can anyone tell me what might me the issue here?

Edit:

Below are the screen shots of what I'm encountering. The first screenshot shows the value "Administrator" is being passed inside the function. the second screenshot shows this value is also in the database.

might want to check if the value being passed as username has padding around it (either left or right)? This is just a shot in the dark, but something worth checking out. To fix this in the query that you're passing, simply change username to ltrim(rtrim(username)) in your where clause. — user2366842
– user2366842, Commented Nov 6, 2015 at 15:13
Then you aren't matching on username. Try comparing TRIM(UPPPER(username)) = TRIM(UPPER(@username)). Grab the query that is actually running against the database using sql profiler and you'll see why you aren't matching on username correctly. — robaudas
– robaudas, Commented Nov 6, 2015 at 15:14
I suspect that the username is not found. Use Profiler to see the parameter's value when you execute that code. Then the SQL statement in profiler and execute it in Management Studio. See if the result is null. — Han
– Han, Commented Nov 6, 2015 at 15:15
I know this does not address the question but...why on earth are you using an overhead laden StringBuilder for absolutely no reason or benefit? Its wasted overhead, wasted heap, wasted CPU cycles, and wasted memory. Simply assign a string SomeString = "your query; or even better, since this is a simple query, use an immediate string in the SqlCommand() and be done with it. StringBuilder has its uses, especially where a string is heavily manipulated but a simple assignment and immediate use are not its strong suit. StringBuilder is mutable, but you aren't changing it. Zero gain. — Wolfie
– Wolfie, Commented Nov 6, 2015 at 15:44

Tim Schmelter · Accepted Answer · 2015-11-06 15:11:30Z

3

You can change the SUM query to return 0 instead of NULL:

query.Append("SELECT COALESCE(Sum(views),0) FROM videos WHERE username = @username");

You could also use the as operator to cast it to the desired nullable type:

int? views = cmd.ExecuteScalar() as int?;

edited Nov 6, 2015 at 15:11

answered Nov 6, 2015 at 15:09

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ahmed Mujtaba Over a year ago

But the result is not 0. Why is it giving me NULL?

Tim Schmelter Over a year ago

@AhmedMujtaba: you have porovided a username which does not exist- Then you get NULL as result with SUM (as opposed to SELECT COUNT(views)...). Use the debugger, inspect the value passed to the parameter. Copy it into ssms and look what you get.

Ahmed Mujtaba Over a year ago

Please have a look at the edit. I have included the screen shots

Tim Schmelter Over a year ago

@AhmedMujtaba: just execute SELECT * FROM videos WHERE username = 'Administrator' in SSMS.

Ahmed Mujtaba Over a year ago

I get 3 rows with username = 'Administrator'. When I do SUM(views) I get 13 back as a result

|

Collectives™ on Stack Overflow

Query returning NULL value

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related