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?
 
Pratima PharandeCommented:
try this - add quotes

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

Open in new window

0
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.