CODEWITHSUNDEEP
c#
odip
odip

Reputation: 39

How do i fix invalid column name in sql

I'm a beginner in SQL and c#. I'm trying to create a system that will lead the user to eligibility form if they have not done it before, but an error that says invalid column name keeps popping. 

string query = "select * from Eligibility where Name = " + textBox1.Text;
sql.Open();
SqlCommand cmd = new SqlCommand(query, sql);
SqlDataReader dr = cmd.ExecuteReader();

if (dr.Read())
{
     label6.Text = (dr["name"].ToString());
}

sql.Dispose();

if (label6.Text == textBox1.Text)
{
     this.Hide();
     UserHomeView uhv = new UserHomeView();
     uhv.Show();
}
else
{
     this.Hide();
     Eligibility eli = new Eligibility();
     eli.Show();
}

Upvotes: 0

Views: 357

Answers (3)

Athanasios Kataras
Athanasios Kataras

Reputation: 26450

You missed the single quotation

string query = "select * from Eligibility where Name = '" + textBox1.Text + "'";

Even so, there is some serious problem with the above code. This can cause a serious sql injection problem for you Check wikipedia entry on this

It's better to use the add parameters function which will sanitize the input and make it safe for you to execute the query.

The best solution would be something like this

    string query = "select * from Eligibility where Name = @Name";
    sql.Open();
    SqlCommand cmd = new SqlCommand(query, sql);
    cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = textBox1.Text;

This way, your query will be injection safe.

Upvotes: 4

GingerNeil
GingerNeil

Reputation: 106

Just to build on what others have said:

Once you're comfy with doing things this way check out Stored Procedures.

Stored Procedures lets you save the query in the database and all you do on the c# side is call the Stored Procedure and add the required parameters.

These tend to be a better way of doing this as you can then learn about how to restrict access to your database for only certain users and also it means the Query itself is in an environment that will check for mistakes as well.

This is a good article as an introduction to them:

http://www.sqlservertutorial.net/sql-server-stored-procedures/

Upvotes: 2

Abdellah Azizi
Abdellah Azizi

Reputation: 370

You can use Parameters of SqlCommand, like this:

string query = "select * from Eligibility where Name = @Name";
            sql.Open();
        SqlCommand cmd = new SqlCommand(query, sql);
        cmd.Parameters.Add("@Name", SqlDbType.Text);
        cmd.Parameters["@Name"].Value = textBox1.Text;
            SqlDataReader dr = cmd.ExecuteReader();
        if (dr.Read())
        {
            label6.Text = (dr["name"].ToString());
        }
            sql.Dispose();
            if (label6.Text == textBox1.Text)
                {
                    this.Hide();
                    UserHomeView uhv = new UserHomeView();
                    uhv.Show();
                }
                else
                {
                    this.Hide();
                    Eligibility eli = new Eligibility();
                    eli.Show();
                }

Upvotes: 0

Related Questions