Proper use of AddWithValue in C# to Db method

Question

Trying to do the following:

 public static int GetJobStatusByNumber(int jobNumber)
    {
        SqlConnection connection = null;
        try
        {
            connection = new SqlConnection(connString);
            connection.Open();

            SqlCommand cmd = connection.CreateCommand();
            cmd.CommandText = @"select STATUS  from JOB
                                where JOB_NUMBER = @jobNumber";

            cmd.Parameters.AddWithValue("@jobNumber", jobNumber);

            int result = ((int)cmd.ExecuteScalar());
            return result;
        }

        finally
        {
            if (connection != null)
                connection.Close();
        }
    }

Thouht I can add the varible 'jobNumber' to the query by using 'AddWithValue' but I'm getting a cast error

Message: System.InvalidCastException : Specified cast is not valid.

What's wrong here?

Thanks.

is STATUS an integer? ExecuteScalar returns the first column value from the first row, regardless of type, so if it gets back, say, a string, then the cast to int will fail. — D Stanley
– D Stanley, Commented May 3, 2019 at 0:57

Oded Dahari · Accepted Answer · 2019-05-03 01:24:05Z

2

ExecuteScalar() returns the first column of the first row in the result set returned by the query.

2 scenarios are possible:

the first column is not a int.
the query return 0 rows - in that case ExecuteScalar() will return null.

for point 1 - make sure that the first column is an int.
for point 2 - make sure you have rows. possible solution will be:

var result = 0;
var tempResult = cmd.ExecuteScalar();

if (tempResult != null) {
    result = (int)tempResult;
}

edited May 3, 2019 at 1:24

answered May 3, 2019 at 1:08

Oded Dahari

924 bronze badges

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

Collectives™ on Stack Overflow

Proper use of AddWithValue in C# to Db method

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related