Geek asks and answers

Trying to use ExecuteScalar and get the error "The specified cast is invalid"

I am trying to get the price of a product using the product name. Below is the function I am using.

public int GetProductPrice(string ProductName)
{
    cnn.Open();
    SqlCommand cmd = new SqlCommand("SELECT ProductPrice FROM Products WHERE ProductName ='" + ProductName + "'", cnn);
    int price = (int)cmd.ExecuteScalar();
    return price;
}

Now I keep getting this error Specified cast is not valid, and I don't know why. Can anybody help me?

+5
source share
1 answer

First, you should use parameterized SQL instead of putting this parameter directly in SQL. In addition, you must use the operator usingto close the command - and the connection - when you are done. Oh, and create a new one SqlConnectionfor each operation. So something like:

public int GetProductPrice(string productName)
{
    // Quite possibly extract the connection creation into a separate method
    // to call here.
    using (var conn = new SqlConnection(...))
    {
        conn.Open();
        using (var command = new SqlCommand(
            "SELECT ProductPrice FROM Products WHERE ProductName = @ProductName",
            conn))
        {
            command.AddParameter("@ProductName", SqlDbType.VarChar)
                   .Value = productName;
            object price = command.ExecuteScalar();
            // And you'd do the casting here
        }
    }
}

ProductPrice. , long , , decimal. :

object tmp = cmd.ExecuteScalar();

... . - , . SqlDbType .

+10

More articles:


All Articles