C# – InvalidCastException: Unable to cast object of type ‘System.Decimal’ to type ‘System.String’

asp.netcdata-binding

I am attempting to get the sale price from a table and put it into a text box. On my table the sale price is a Decimal variable, and of course the text box is string.. When I run this, there is an exception that is stopping it in my Data Access Layer.

Here is some code:

textSellPrice.Text = DAL.Util.getSellPrice(listItemsPricing.SelectedValue.ToString());


public static String getSellPrice(string item)
{
    string sql = "SELECT Price FROM Item it INNER JOIN Customers cu 
        ON it.SalesRep = Cu.SalesRep WHERE CustomerID='" 
        + HttpContext.Current.Session["SelectedCustomer"] +
        "' AND ProductID='" + item + "'";
    string dt = AdoUtil.GetDataColumn(sql);
    return dt;
}



    public static string GetDataColumn(string sqlQuery)
    {
        string result = String.Empty;
        try
        {
            SqlCommand cmd = new SqlCommand(sqlQuery, GetACESConn());

            if (cmd.Connection.State != ConnectionState.Open)
                cmd.Connection.Open();

            SqlDataReader reader = cmd.ExecuteReader();

            if (reader.HasRows)
                while (reader.Read())
                {
                    result = reader.GetString(0);
                }


            if (cmd.Connection.State != ConnectionState.Closed)
                cmd.Connection.Close();

            return result;
        }
        catch (Exception ex)
        {

            return result;
        }
    }

So is there something completely obvious that I am missing?
Thanks for any helpful insight to this, and if any other code could be of use, I can provide it. Thank you

Best Answer

You're selecting a price, which is presumably a decimal. So don't call reader.GetString(0) - call reader.GetDecimal(0) and store the result in a decimal variable. If you really want to convert everything into a string, just call GetValue(0).ToString().

While you're there, please fix this:

string sql = "SELECT Price FROM Item it INNER JOIN Customers cu ON it.SalesRep = Cu.SalesRep WHERE CustomerID='" + HttpContext.Current.Session["SelectedCustomer"] +
    "' AND ProductID='" + item + "'";

This is just begging for a SQL Injection Attack. Don't put values directly into SQL like this. Instead, use parameterized SQL and specify the values for those parameters. See SqlCommand.Parameters for an example.

Next up, don't catch Exception, and don't return a value when an exception is thrown as if nothing had happened... you'll be masking errors for no reason.

Related Topic