CODEWITHSUNDEEP
c#sqlwinforms
ShmuelCohen
ShmuelCohen

Reputation: 472

Trying to use ExecuteScalar , and get " Specified cast is not valid " error

I'm trying to get product price by using 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 someone help me ?

Upvotes: 9

Views: 14074

Answers (2)

Tinashe Bandama
Tinashe Bandama

Reputation: 1

The cast of the ExecuteScalar works if your SQL query is retrieving a count of rows and not the column data. The returned count from SQL is then being cast to an int in C#.

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1500615

Firstly, you should use parameterized SQL instead of putting the parameter directly into the SQL. Also, you should use a using statement to close the command - and connection - when you're done. Oh, and create a new SqlConnection for 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
        }
    }
}

Next, we don't know the type of the ProductPrice field. It could be that you're getting a long returned, or perhaps it's decimal. The simplest way to find out is just to use:

object tmp = cmd.ExecuteScalar();

... and then look in the debugger. Also look at the type of the field in the database - that should really tell you what to expect. Have a look at the SqlDbType enumeration for mappings between the two.

Upvotes: 12

Related Questions