Parameter returning not in the correct format-CodePudding

I have an async task method that queries a database table for the total amount of Credits in a specific column. I have a string parameter in the method that I use for the query to identify which user is logged in, which is stored in Properties.Settings.Default.Student_Number;. The column for the student number in the database is a varchar and everywhere else in my code its a string, but I'm getting an exception saying Input string was not in correct format. Any idea what I'm doing wrong

private async Task<int> Count_Credits(string student_number)
{
    int count = 0;
    string sql = "SELECT SUM(Module_Credit) AS TOTAL_CREDITS FROM Module_Data WHERE Student_Number=@num";

    using (var conn = new SqlConnection(connString))
    using (var cmd = new SqlCommand(sql, conn))
    {

        cmd.Parameters.Add("@num", SqlDbType.VarChar, 55).Value = student_number;
        await conn.OpenAsync();

        count = int.Parse(cmd.ExecuteScalar().ToString());
    }

    return count;
}

How I am calling the method

Task<int> count_credits = Count_Credits(Student_Number);
module_info.Total_Credits = await count_credits; //Exception Goes to this line

I am using binding on the label for

module_info.Total_Credits

CodePudding user response：

You'll need to check if a result is returned from ExecuteScalar before you can cast the type.

object result = cmd.ExecuteScalar();
if(result != null)
{
  count = (int)result;
}

For a more terse check, use pattern matching (per Olivier's comment):

if(cmd.ExecuteScalar() is int i) 
{ 
  count = i; 
}

Also, you should await the method itself when getting the value:

int count_credits = await Count_Credits(Student_Number);
module_info.Total_Credits = count_credits;

CodePudding user response：

The result returned by ExecuteScalar will either be an int or be null. Also, making a round-trip via string is not necessary. But we must check for null.

Pattern matching was introduced in recent versions of C#, allowing terser code in many situations:

count = cmd.ExecuteScalar() is int i ? i : 0;

If the scalar is a boxed int object, it will be cast to int and assigned to the new variable i and the result of the is expression will be true. We use this result in a ternary expression to determine the final result.

Yet another possibility:

count = (cmd.ExecuteScalar() as int?) ?? 0;

The operator as casts the input to the desired type if the cast is successful and otherwise returns null. Therefore the target type must be nullable. Hence we specify int? (= Nullable<int>) as target type. We use the null coalescing operator ?? to convert null to 0.