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? — Conrad Frix
– Conrad Frix, Commented Mar 19, 2013 at 6:33

शेखर · Accepted Answer · 2013-03-19 06:42:26Z

3

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

edited Mar 19, 2013 at 6:42

answered Mar 19, 2013 at 6:26

शेखर

17.6k14 gold badges64 silver badges123 bronze badges

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

1 Comment

शेखर Over a year ago

so it should be cast in ToInt64 or ulong?

chazbot7 · Accepted Answer · 2013-03-19 06:53:18Z

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, 2013 at 6:53

chazbot7

5963 gold badges13 silver badges35 bronze badges

1 Comment

jordanhill123 Over a year ago

Here's an article explaining it a bit more FYI: stackoverflow.com/questions/2601620/…

Collectives™ on Stack Overflow

Cast error in C#

2 Answers 2

Other-Wise

changed

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Other-Wise

changed

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related