SqlDataReader中的列名错误无效(Invalid Column Name Error in SqlDataReader)

SqlDataReader中的列名错误无效(Invalid Column Name Error in SqlDataReader)

我正在使用SQL Datareader来填充.aspx页面中的文本框。 我的下面

#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

在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); }

我有表TB_User与列UserID和密码。 它分别具有值test1和test1。 但它给出了下面的错误。

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

请帮忙

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

Please Help

最满意答案

您忘记在参数周围添加单引号。

"Select * from TB_User where UserID='" + Label1.Text +"'"; 但是 ，您对SQL-Injection持开放态度 。 不要连接字符串来构建查询。 而是使用参数。 使用using-statement进行连接（以及实现IDisposable所有其他内容）。 Dispose也会关闭连接，甚至在出错时using 。

这是一个例子：

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()) { // ... } } } }

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()) { // ... } } } }