2

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. enter image description here

enter image description here

Ahmed Mujtaba
  • 2,110
  • 5
  • 36
  • 67
  • Do you want it to return 0 instead? http://stackoverflow.com/questions/17048343/my-select-sum-query-returns-null-it-should-return-0 – robaudas Nov 06 '15 at 15:07
  • @robaudas It should sum the values in the column and return the result. The sum of the values isn't 0 but it's giving me NULL – Ahmed Mujtaba Nov 06 '15 at 15:09
  • does the username column in videos table allow nulls? – Adish Nov 06 '15 at 15:12
  • 4
    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 Nov 06 '15 at 15:13
  • 3
    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 Nov 06 '15 at 15:14
  • 3
    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 Nov 06 '15 at 15:15
  • I have checked with the debugger and the username is in the database. I used the same username string value in SQL management studio in the same query and I get the right result. – Ahmed Mujtaba Nov 06 '15 at 15:19
  • 1
    @AhmedMujtaba use SQL Server Profiler. – Han Nov 06 '15 at 15:22
  • try this then for your query - `SELECT Sum(views) FROM videos WHERE ltrim(rtrim(upper(username))) = ltrim(rtrim(upper(@username)))` - I suspect what's happening is you have an nchar value on the database end and/or there's some padding going on when the value is pulled from a text box within the application. – user2366842 Nov 06 '15 at 15:24
  • @AhmedMujtaba: even if the user does exists it does not mean that you can't get `NULL`. Is `views` nullable? – Tim Schmelter Nov 06 '15 at 15:29
  • 2
    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 Nov 06 '15 at 15:44
  • @Wolfie: you're right that there's currently no benefit in using it. But it's also not so terrible as you're presenting it. The `StringBuilder`'s portion of the costs of this method is something about 1/1000. – Tim Schmelter Nov 06 '15 at 15:56
  • 1
    @Tim Schmelter and you are also right, in this case the cost is minimal. However, how many more needless uses in the project are there? And inside loops? They add up at the cost of app performance.. If you get in the habit of using standard strings which are stored and retrieved directly from the application MetaData pool and optimized with folding you see performance increase. The StringBuilder use here pulls it from the MetaData and stores it again in the memory heap for absolutely no gain. I was pointing it out as as a matter of practice than direct benefit for this single situation. – Wolfie Nov 06 '15 at 16:35

1 Answers1

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?;
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • 1
    But the result is not 0. Why is it giving me NULL? – Ahmed Mujtaba Nov 06 '15 at 15:10
  • 1
    @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. – Tim Schmelter Nov 06 '15 at 15:13
  • Please have a look at the edit. I have included the screen shots – Ahmed Mujtaba Nov 06 '15 at 15:31
  • @AhmedMujtaba: just execute `SELECT * FROM videos WHERE username = 'Administrator'` in SSMS. – Tim Schmelter Nov 06 '15 at 15:38
  • I get 3 rows with username = 'Administrator'. When I do SUM(views) I get 13 back as a result – Ahmed Mujtaba Nov 06 '15 at 15:41
  • @AhmedMujtaba: i have no clue, you have even checked the connection string. Sometimes we confuse development database with production database. – Tim Schmelter Nov 06 '15 at 15:44
  • @AhmedMujtaba I think you check what SQL statement and parameter values are being passed to the SQL Server. In that case, you need SQL Profiler to capture it. It's in SQL Server Management Studio. Click Tools then choose Profiler. – Han Nov 06 '15 at 15:50