asked on

Why is my column name coming up as Invalid?

Hi, I created a method in c# that is supposed to sort images from my database by their color and group them into a section on my web page:

http://auroriella.com/showcase.aspx

 for (int i = 0; i < 7; i++)
            {
                string[] imgColor = { "blue", "green", "red", "pink", "purple" };

                // get the DataReader
                SqlCommand cmd = new SqlCommand("SELECT * FROM Images WHERE color=" + imgColor[i], con);
                SqlDataReader sd = cmd.ExecuteReader();

Open in new window

but I keep getting an error message that the column name 'blue' is invalid. But I can see it stored in my Images table, so how can it be invalid?
database-table.png
showcase.aspx.cs

ASKER CERTIFIED SOLUTION

Pratima

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

FairyBusiness

ASKER

hey it worked! well, at least the error is gone, my script still needs more work lol

kaufmed

Yes, you need to quote string literals that occur in your queries. However, I'd like to mention the concept of parameterized queries. In addition to not having to worry about quoting your string literals, you help negate SQL injection attacks on your queries. Here is a quick example of a parameterized query:

 for (int i = 0; i < 7; i++)
{
    string[] imgColor = { "blue", "green", "red", "pink", "purple" };

    // get the DataReader
    SqlCommand cmd = new SqlCommand("SELECT * FROM Images WHERE color=@color", con);

    cmd.Parameters.Add(new SqlParameter("@color", imgColor[i]));

    SqlDataReader sd = cmd.ExecuteReader();

Open in new window

Notice how there is now a placeholder in the query (@color) and the parameter uses that placeholder name to associate the value that should be substituted. There is no quoting--the DB will take care of that when it substitutes the values for the placeholders.

kaufmed

P.S.

For your code above, parameterized queries probably won't give you much benefit, but it is a good practice (and mindset) to get into. You don't want to mistakenly pass unvalidated user data to your database. If you fall into that anti-pattern, then you may come in one day and find your DB missing ; )

FairyBusiness

ASKER

@kaufmed I just tried it out and it works great!

it still groups my images by color:

http://auroriella.com/showcase.aspx

I wouldn't have known to do that! Thanks for the extra protection :)