C# SQL 数据读取器：不存在数据时读取尝试无效

SQL Data Reader: Invalid attempt to read when no data is present

提问by user1836162

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

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

Invalid attempt to read when no data is present.

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

Here is my code.

这是我的代码。

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

回答by Steve

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.

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

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

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

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

如果您只有一条记录，则可以通过results这种方式使用该变量

MessageBox.Show(results);

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.

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

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:

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

 results = "No data found";

EDIT: 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();
        }
    }
}
.....

回答by vapcguy

I ran into a similar issue trying to get a GUID I knew was there - I could run the same SQL directly in SQL Management Studio and get my result. So instead of trying to bring it back as a GUID (it was saved in a char(35) field, even though it really was a GUID!), I brought it back as a string, instead:

我在尝试获取我知道的 GUID 时遇到了类似的问题 - 我可以直接在 SQL Management Studio 中运行相同的 SQL 并获得我的结果。因此，与其尝试将其作为 GUID 带回来（它保存在 char(35) 字段中，即使它确实是一个 GUID！），我还是将其作为字符串带回来：

        SqlConnection sqlConn = null;
        string projId = String.Empty;
        string queryString = "SELECT * FROM project WHERE project_name='My Project'";

        try
        {
            sqlConn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(queryString, sqlConn);
            sqlConn.Open();

            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    projId = reader.GetSqlValue(0).ToString();   // <-- safest way I found to get the first column's parameter -- increment the index if it is another column in your result
                }                    
            }
            reader.Close();
            sqlConn.Close();
            return projId;                
        }
        catch (SqlException ex)
        {                
            // handle error
            return projId;
        }
        catch (Exception ex)
        {
            // handle error
            return projId;
        }
        finally
        {
            sqlConn.Close();
        }