Cast error in C#

Question

I have a function in a class "Checkout" that runs a sql statement to insert a record into a table, then select the scope_identity, giving me the ID of that record. It returns the ID as an int, and I assign it to a variable in the webpage:

int OrderID = Checkout.WriteOrder_tblOrders(CustID);

Here's the function:

public static int WriteOrder_tblOrders(int CustID)
{
    // OrderID is initially 0, then changed when scope_identity is called in the sql statement.
    int OrderID = 0;
    DateTime OrderDate = DateTime.Now;
    string sql = "INSERT INTO tblOrders (OrderDate, CustID) VALUES (@OrderDate, @CustID); SELECT scope_identity();";

    using (SqlConnection myConnection = new SqlConnection(connectString))
    {
        using (SqlCommand cmd = new SqlCommand(sql, myConnection))
        {
            cmd.Parameters.AddWithValue("@CustID", CustID);
            cmd.Parameters.AddWithValue("@OrderDate", OrderDate);
            myConnection.Open();
            OrderID = (int)cmd.ExecuteScalar();
            myConnection.Close();
        }
    }
    return OrderID;
}

The debugger shows that there's nothing wrong with the function, and new records are showing up correctly in that table, but I'm getting an

"Invalid Cast"

error on the first line, where I assign OrderID. I've tried this:

int OrderID = (int)Checkout.WriteOrder_tblOrders(CustID);

With both int and Int32, and also tried using Convert.ToInt32, which didn't work either. What am I doing wrong here?

possible duplicate of [What data type does the SQLCommand method ExecuteScalar() return?](http://stackoverflow.com/questions/3964305/what-data-type-does-the-sqlcommand-method-executescalar-return) — Conrad Frix, Mar 19 '13 at 06:33

शेखर · Accepted Answer · 2013-03-19T06:42:26.990

The reason may be you are getting null values.
So you ca create an extension method as follows

public static T ExecuteNullableScalar<T>(this SqlCommand cmd) where T : struct
{
    var result = cmd.ExecuteScalar();
    if (result == DBNull.Value) return default(T);
    return (T)result;
}

Usage becomes:

int value = cmd.ExecuteNullableScalar<int>();
//new approach
ulong value=cmd.ExecuteNullableScalar<ulong>();

Other-Wise

 myConnection.Open();
 var o = cmd.ExecuteScalar();
 OrderID = (o== DBNull.Value ? 0 :  Convert.ToUInt64(o));
 myConnection.Close();

changed

OrderID = (o== DBNull.Value ? 0 :  Convert.ToUInt64(o));

so it should be cast in `ToInt64` or `ulong`? – शेखर Mar 19 '13 at 06:38 — शेखर, Mar 19 '13 at 06:38

score 1 · Answer 2 · answered Mar 19 '13 at 06:53

1

Thanks for your help guys, didn't realize that scope_identity returns a decimal. I altered the code like this:

OrderID = Convert.ToInt32((decimal)cmd.ExecuteScalar());

It now works fine. Thanks!

answered Mar 19 '13 at 06:53

chazbot7

598
3
12
34

1

Here's an article explaining it a bit more FYI: http://stackoverflow.com/questions/2601620/why-does-select-scope-identity-return-a-decimal-instead-of-an-integer – jordanhill123 Mar 19 '13 at 06:58

Cast error in C#

2 Answers2

Other-Wise

changed