Invalid Column Name Error in SqlDataReader

Question

I am using the SQL Datareader to fill the text boxes in .aspx page. My below

#region "[--------Function To Fill Up All TextBox---------]>"
public void FillTextBox(string Sqlstring)
{


    SqlConnection Conn =  new SqlConnection(ConfigurationManager.ConnectionStrings["SQL Connection String"].ConnectionString);
    SqlDataReader MyDataReader = null;
    SqlCommand MyOleDbCommand = new SqlCommand();
    MyOleDbCommand.Connection = (Conn);
    Conn.Open();
    MyOleDbCommand.CommandText = Sqlstring;
    MyDataReader = MyOleDbCommand.ExecuteReader();
    try
    {

        while (MyDataReader.Read())
        {

            txtuniv.Text = (MyDataReader[0].ToString());
            txtcollrno.Text = (MyDataReader[1].ToString());
            /*txtLastName.Text = (MyDataReader[2].ToString());
            txtClass.Text = (MyDataReader[3].ToString());
            txtSession.Text = (MyDataReader[4].ToString());
            txtobt.Text = (MyDataReader[5].ToString());
            txttot.Text = (MyDataReader[6].ToString());

            */
        }
    }
    catch (System.Exception err)
    {
        MyDataReader.Close();
        Conn.Close();
        Conn.Dispose();
    }

}
#endregion

In PageLoad() Eveent

    protected void Page_Load(object sender, EventArgs e)
{
    Label1.Text = User.Identity.Name.ToString();
 
    string SqlStr = null;
    SqlStr = "Select * from TB_User where UserID=" + Label1.Text;
    FillTextBox(SqlStr);
}

I have table TB_User with columns UserID & Password. It has value test1 & test1 respectively. But it gives the following error:

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Invalid column name 'test1'.

Source Error:


Line 40:         Conn.Open();
Line 41:         MyOleDbCommand.CommandText = Sqlstring;
Line 42:         MyDataReader = MyOleDbCommand.ExecuteReader();
Line 43:         try
Line 44:         {

Is UserID a varchar or int column?

Tim Schmelter
– Tim Schmelter

2012-10-15 08:41:59 +00:00
Commented Oct 15, 2012 at 8:41 — Tim Schmelter
– Tim Schmelter, Commented Oct 15, 2012 at 8:41

Tim Schmelter · Accepted Answer · 2012-10-15 08:50:05Z

3

You have forgotten to add single quotation marks around the parameter.

"Select * from TB_User where UserID='" + Label1.Text +"'";

But, you are open for SQL-Injection. Don't concatenate strings to build your query. Instead use Parameters.
Use using-statement for you connection (and everything else implementing IDisposable). Dispose will also close the connection, with using even on error.

Here's an example:

using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL Connection String"].ConnectionString)) {
    var sql = "Select * from TB_User where UserID=@UserID";
    using (var cmd = new SqlCommand(sql, con)) {
        cmd.Parameters.AddWithValue("@UserID", Label1.Text);
        con.Open();
        using(var reader = cmd.ExecuteReader())
        {
            while(reader.Read())
            {
                // ...
            }
        }
    }
}

edited Oct 15, 2012 at 8:50

answered Oct 15, 2012 at 8:44

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

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

Comments

Emanuele Greco · Accepted Answer · 2012-10-15 08:43:51Z

2

UserID must be encapsulate with ' in the query.

SqlStr = "Select * from TB_User where UserID='" + Label1.Text + "'";

answered Oct 15, 2012 at 8:43

Emanuele Greco

12.8k7 gold badges55 silver badges72 bronze badges

Comments

Kamil Krasinski · Accepted Answer · 2012-10-15 08:44:02Z

2

Try changing following:

SqlStr = "Select * from TB_User where UserID=" + Label1.Text;

to:

SqlStr = string.Format("Select * from TB_User where UserID='{0}'",Label1.Text);

In original version database thinks you are looking for a column Test1 and not comparing to value 'Test1'

answered Oct 15, 2012 at 8:44

Kamil Krasinski

5294 silver badges10 bronze badges

Comments

codingbiz · Accepted Answer · 2012-10-15 08:44:49Z

1

Try this, quotes around userID

SqlStr = "Select * from TB_User where UserID='" + Label1.Text + "'";

Consider using Parameterized query in your code - Avoid SQL Injection attack and also save you from accidental characters that could mar your SQL syntax

SqlStr = "Select * from TB_User where UserID=@UserID";

answered Oct 15, 2012 at 8:44

codingbiz

26.4k9 gold badges62 silver badges99 bronze badges

Comments

Oscar · Accepted Answer · 2012-10-15 08:45:47Z

1

Your code is bad from a security perspective. This line is asking for an Sql Injection:

SqlStr = "Select * from TB_User where UserID=" + Label1.Text;

Also, you should dispose your objects in a finally block, not in catch, because this way you only release resources in case something goes wrong.

And third and last, specify the column names in your query and tell us how it went.

answered Oct 15, 2012 at 8:45

Oscar

14.1k9 gold badges53 silver badges84 bronze badges

Comments

snnpro · Accepted Answer · 2020-11-06 10:06:25Z

0

Check that the column name has been correctly spelt.

answered Nov 6, 2020 at 10:06

snnpro

3071 silver badge2 bronze badges

Collectives™ on Stack Overflow

Invalid Column Name Error in SqlDataReader

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related