CODEWITHSUNDEEP
c#sqlasp.netsql-server
Ahmed Mujtaba
Ahmed Mujtaba

Reputation: 2248

Query returning NULL value

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

Upvotes: 2

Views: 3206

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460048

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?;

Upvotes: 3

Related Questions