如何修复错误ExecuteReader

Question

错误在System.Data.dll中出现类型为'System.Data.SqlClient.SqlException‘的异常，但未在用户代码中进行处理。如何修复它？图片来源：http://i.stack.imgur.com/7Sibc.png

    private void Form1_Load(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection(@"Data Source=QEAG1YU4664IBKF\HUYNHBAO;Initial Catalog=TonghopDB;User ID=sa;Password=koolkool7");
        conn.Open();
        SqlCommand sc = new SqlCommand("select Title from TongHopDB", conn);
        SqlDataReader reader;

        reader = sc.ExecuteReader();
        DataTable dt = new DataTable();

        dt.Columns.Add("Title", typeof(string));
        dt.Load(reader);

        cboxDB.ValueMember = "Title";
        cboxDB.DisplayMember = "Title";
        cboxDB.DataSource = dt;

        conn.Close();
    }

    private void cboxDB_SelectedIndexChanged(object sender, EventArgs e)
    {
        string sql = "Select Title, Post from TongHopDB where Title = " + cboxDB.SelectedValue.ToString(); // câu query có thể khác với kiểu dữ liệu trong database của bạn
        SqlConnection conn = new SqlConnection(@"Data Source=QEAG1YU4664IBKF\HUYNHBAO;Initial Catalog=TonghopDB;User ID=sa;Password=koolkool7");
        conn.Open();
        SqlCommand cmd = new SqlCommand(sql, conn);
        SqlDataReader sdr = cmd.ExecuteReader();
        textBox1.Text = sdr.GetValue(0).ToString();
        textBox2.Text = sdr.GetValue(1).ToString();
        sdr.Close();
        sdr.Dispose();
        conn.Close();
        conn.Dispose();
    }

Answer 1

 string sql = "Select Title, Post from TongHopDB where Title = '" + cboxDB.SelectedValue.ToString()+"'";

但是，我强烈建议使用参数：

 string sql = "Select Title, Post from TongHopDB where Title = @Title";
cmd.Paramaters.Add( "@Title",cboxDB.SelectedValue.ToString());

Answer 2

我强烈怀疑你的Title是字符类型的，这就是为什么它需要用单引号作为；

where Title = '" + cboxDB.SelectedValue.ToString() + "'";

但不要用这种方式。

您应该始终使用parameterized queries。这种字符串连接对于SQL Injection攻击是开放的。

还可以使用using statement自动释放SqlConnection、SqlCommand和SqlDataReader对象，而不是手动调用Close或Dispose方法。

using(var conn = new SqlConnection(@"Data Source=QEAG1YU4664IBKF\HUYNHBAO;Initial Catalog=TonghopDB;User ID=sa;Password=koolkool7"))
using(var cmd = conn.CreateCommand())
{
   cmd.CommandText = "Select Title, Post from TongHopDB where Title = @title";
   cmd.Parameters.Add("@title", SqlDbType.NVarChar).Value = cboxDB.SelectedValue.ToString();
   // I assumed your column type is nvarchar.
   conn.Open();
   using(SqlDataReader sdr = cmd.ExecuteReader())
   {
      if(dr.Read())
      {
         textBox1.Text = sdr.GetValue(0).ToString();
         textBox2.Text = sdr.GetValue(1).ToString();
      }
   }
}

Answer 3

根据屏幕截图中显示的错误，cboxDB.SelectedValue是苹果。您的SQL语句简单地说就是：

从TongHopDB(表)中选择标题(列)和帖子(列)，其中标题(列)等于苹果(列)

Apple不是有效列！

虽然简单地在cboxDB的值两边添加单引号是可行的，但是您应该使用参数，而不是连接字符串。http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/