"invalid column name" error when retrieving data from SQL database?

Question

I have a database with 3 columns on it: FIRST_NAME, LAST_NAME, and IMAGE. I always get the error "invalid column name 'the name from the first column'." I am supposed to write the first name and click a button to show the last name and the image. I am using C#, this is my current code:

        private void button_show_Click(object sender, EventArgs e)
    {
        try
        {
            string sql = "select LAST_NAME,IMAGE from Table_1 where FIRST_NAME=" + this.firstname_textbox.Text + "";
            if (conn.State != ConnectionState.Open)
                conn.Open();
            command = new SqlCommand(sql, conn);
            SqlDataReader reader = command.ExecuteReader();
            reader.Read();
            if (reader.HasRows)
            {
                lastname_textbox.Text = reader[0].ToString();
                byte[] img = (byte[])(reader[1]);
                if (img == null)
                pictureBox1.Image = null;
                else
                {
                    MemoryStream ms = new MemoryStream(img);
                    pictureBox1.Image = Image.FromStream(ms);
                }

            }
            else
            {
                MessageBox.Show("This Name Does Not Exist");
            }
            conn.Close();
        }
        catch(Exception ex)
        {
            conn.Close();
            MessageBox.Show(ex.Message);
        }
    }
}

Thanks.

WARNING! Never ever concatenate your query from input field, because you are vulnerable to SQL Injection attack. Use parametrized queries instead — krivtom
– krivtom, Commented Oct 18, 2015 at 18:57

AlVaz · Accepted Answer · 2015-10-18 19:14:29Z

2

You have an unquoted string in your WHERE clause.

string sql = "select LAST_NAME,IMAGE from Table_1 where FIRST_NAME=" + this.firstname_textbox.Text + "";

should be:

string sql = "select LAST_NAME,IMAGE from Table_1 where FIRST_NAME='" + this.firstname_textbox.Text + "'";

You should also know that using string concatenation for SQL query parameters is bad practice as it creates a SQL Injection vulnerability. For example, imagine the result if this.firstname_textbox.Text was:

';DELETE FROM Table_1 WHERE '1' = '1

This would result in the variable "sql" being this:

select LAST_NAME,IMAGE from Table_1 where FIRST_NAME='';DELETE FROM Table_1 WHERE '1' = '1'

To avoid this problem, use parameterized queries (https://msdn.microsoft.com/en-us/library/vstudio/bb738521%28v=vs.100%29.aspx)

edited Oct 18, 2015 at 19:14

answered Oct 18, 2015 at 18:44

AlVaz

7661 gold badge6 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AlVaz Over a year ago

@Tarek-Dev Your welcome, I'm glad my answer was acceptable to you.

Collectives™ on Stack Overflow

"invalid column name" error when retrieving data from SQL database?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related