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
FairyBusinessAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pratima PharandeCommented:
try this - add quotes

 
SqlCommand cmd = new SqlCommand("SELECT * FROM Images WHERE color='" + imgColor[i] + "'" , con);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FairyBusinessAuthor Commented:
hey it worked! well, at least the error is gone, my script still needs more work lol
0
käµfm³d 👽Commented:
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.
0
käµfm³d 👽Commented:
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  ; )
0
FairyBusinessAuthor Commented:
@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 :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.