CODEWITHSUNDEEP
c#asp.netsqlcastingint
chazbot7
chazbot7

Reputation: 596

Cast error in C#

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?

Upvotes: 1

Views: 277

Answers (2)

chazbot7
chazbot7

Reputation: 596

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!

Upvotes: 1

शेखर
शेखर

Reputation: 17614

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

Upvotes: 3

Related Questions