CODEWITHSUNDEEP
c#asp.net
عثمان غني
عثمان غني

Reputation: 2708

Invalid Column Name Error in SqlDataReader

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:         {

Upvotes: 1

Views: 7570

Answers (6)

snnpro
snnpro

Reputation: 307

Check that the column name has been correctly spelt.

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460288

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

"Select * from TB_User where UserID='" + Label1.Text +"'";
  1. But, you are open for SQL-Injection. Don't concatenate strings to build your query. Instead use Parameters.
  2. 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())
            {
                // ...
            }
        }
    }
}

Upvotes: 3

Oscar
Oscar

Reputation: 13990

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.

Upvotes: 1

codingbiz
codingbiz

Reputation: 26396

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";

Upvotes: 1

Kamil Krasinski
Kamil Krasinski

Reputation: 529

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'

Upvotes: 2

Emanuele Greco
Emanuele Greco

Reputation: 12731

UserID must be encapsulate with ' in the query.

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

Upvotes: 2

Related Questions