InvalidCastException: Unable to cast object of type 'System.Decimal' to type 'System.String'

Question

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

Solution 1

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.

Solution 2

CustomerId is declared as a numeric type in the database, but you are trying to read it as a string. If you must have your result as a string, you can either:

• read it as a numeric type (say, a decimal) and convert to string in C#, or
• change your SQL to cast it to varchar on the RDBMS side

On a side note, you should not bake parameter values into your queries to avoid Bobby Tables; you need to use parameterized queries instead.