SQL 数据读取器:不存在数据时读取尝试无效 [英] SQL Data Reader: Invalid attempt to read when no data is present

问题描述

我正在尝试使用 SqlDataReader 运行查询，然后在消息框中显示结果，但我不断收到错误

I am trying to use a SqlDataReader to run a query and then display the results in a messagebox, but I keep getting the error

不存在数据时读取尝试无效.

这是我的代码.

 public void button1_Click(object sender, EventArgs e)
 {
    string results = "";
    using (SqlConnection cs = new SqlConnection(@"Server=100-nurex-x-001.acds.net;Database=Report;User Id=reports;Password=mypassword"))
    {
         cs.Open();
         string query = "select stationipaddress from station where stationname = @name";
         using (SqlCommand cmd = new SqlCommand(query, cs))
         {
              // Add the parameter and set its value -- 
              cmd.Parameters.AddWithValue("@name", textBox1.Text);
              using (SqlDataReader dr = cmd.ExecuteReader())
              {
                   while (dr.Read())
                   {
                        label3.Text = dr.GetSqlValue(0).ToString();
                        results = dr.GetValue(0).ToString();
                        //MessageBox.Show(dr.GetValue(0).ToString());
                        //MessageBox.Show(results);
                    }
                    MessageBox.Show(results);
              }
         }
    } 
}

推荐答案

没错.
当您退出 while 循环时，DataReader 已到达加载数据的末尾，因此无法用于获取不存在的当前记录的值.

That's correct.
When you exit from the while loop the DataReader has reached the end of the loaded data and thus cannot be used to get the value of a non-existant current record.

Read 方法将 SqlDataReader (dr) 推进到下一条记录，如果有更多行，则返回 true，否则返回 false.

The Read method advances the SqlDataReader (dr) to the next record and it returns true if there are more rows, otherwise false.

如果你只有一个记录，你可以这样使用 results 变量

If you have only one record you could use the results variable in this way

MessageBox.Show(results);

现在，这将起作用，因为您的 sql 语句中有一个 TOP 1，但是，如果您有多个记录，它将只显示最后一条记录的值.

Now, this will work because you have a TOP 1 in your sql statement, but, if you have more than one record, it will show only the value of the last record.

同样如 marc_s 在其评论中指出的那样，如果您的表为空，则您的代码不会落入 while 循环内，因此您可能可以使用如下消息初始化结果变量:

Also as noted by marc_s in its comment, if your table is empty, your code doesn't fall inside the while loop, so probably you could initialize the results variable with a message like:

 results = "No data found";

在下面看到您的评论，那么您应该以这种方式更改您的代码

Seeing your comment below then you should change your code in this way

.....
// Use parameters **ALWAYS** -- **NEVER** cancatenate/substitute strings 
string query = "select stationipaddress from station where stationname = @name";
using (SqlCommand cmd = new SqlCommand(query, cs))
{
    // Add the parameter and set its value -- 
    cmd.Parameters.AddWithValue("@name", textBox1.Text);
    using (SqlDataReader dr = cmd.ExecuteReader())
    {

        while (dr.Read())
        {
            label3.Text = dr.GetSqlValue(0).ToString();
            results = dr.GetValue(0).ToString();
        }
    }
}
.....

这篇关于SQL 数据读取器:不存在数据时读取尝试无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！