C＃ExecuteScalar不返回null [英] C# ExecuteScalar not returning null

问题描述

这里有一个奇怪的问题，我正在尝试使用ExecuteScalar来查看是否存在任何值并且取决于它是否要执行一定的行动。似乎正在发生的事情是，即使没有值的BLANK表，它也没有被注册为null，这里有一段代码供您查看：

  private   void  check_services（）
 {
  try  
 {
 dbConnect.services_initialise（）; 
 dbConnect.services_Open_Connection（）; 
  string  check_query =   SELECT MIN（services_remaining ）FROM服务WHERE SRID = @ SRID AND services_left = @ SL; 
 MySqlCommand cs =  new  MySqlCommand（check_query，dbConnect.services_connection）; 
 
 cs.Parameters.AddWithValue（  @ SRID，txtServiceRecordID.Text ）; 
 cs.Parameters.AddWithValue（  @ SL，  TRUE）; 
 
  if （cs.ExecuteScalar（）==  null ）
 {
 create_new_service（）; 
} 
 其他 
 {
  //  < span class =code-comment>如果DB中当前有记录，请执行此操作 
 使用（MySqlDataReader read = cs.ExecuteReader （））
 {
  while （read.Read（））
 {
 services_remaining = read.GetString（read .GetOrdinal（  services_remaining））; 
 rowID = read.GetString（read.GetOrdinal（  ID））; 
} 
} 
 update_previous_record（）; 
} 
 
} 
  catch （MySqlException ex）
 {
 MessageBox.Show（  错误检查服务！ + ex， 错误！，MessageBoxButtons.OK，MessageBoxIcon.Error）; 
} 
} 
 

什么是最奇怪的，不会被try / catch捕获是运行时的例外情况，如下所示：



MySql.Data.dll中出现未处理的'System.IndexOutOfRangeException'类型异常



附加信息：在结果中找不到指定的列：services_remaining



我已检查过并仔细检查列名称，它是正确的，拼写正确。我真的很难过这个，两种情况都发生了错误。



案例1：空白表中没有值 - >不会调用create_new_service（）而是跳转到else并尝试拉出值并显示上述错误。



案例2：表中的值 - - >它跳到其他地方，试图拉出值并呈现上面提到的错误。



不确定它是什么导致它，希望有人可以提供帮助。



感谢您的所有帮助和反馈！

解决方案

使用reader.HasRows检查查询是否返回结果与否。

接下来，您无法向读者询问ID列的位置，因为您的查询不包含具有该名称的列。使用GetInt32（0）来获取由SQL查询重新调整的值。

将代码更改为：



< pre lang =C＃> object scalarVal = cs.ExecuteScalar（）;

if （scalarVal == null ）
{
// ...

然后在调试时，在if行上休息一下。看看scalarVal的价值是什么。我猜是因为你使用的是聚合物，你在那里有价值。



为什么你得到例外？您只捕获MySqlException，您获得的异常是IndexOutOfRangeException，因此执行将停止抛出异常的位置。

Hi,

Got a bit of a bizarre issue here, I am trying to use ExecuteScalar to see if any values are present and depending if it is to perform a certain action. What seems to be happening is that even with a BLANK table with no values, it isn't being registered null, here is a snippet of the code for you to see:

private void check_services()
       {
           try
           {
               dbConnect.services_initialise();
               dbConnect.services_Open_Connection();
               string check_query = "SELECT MIN(services_remaining) FROM services WHERE SRID=@SRID AND services_left=@SL";
               MySqlCommand cs = new MySqlCommand(check_query, dbConnect.services_connection);

                   cs.Parameters.AddWithValue("@SRID", txtServiceRecordID.Text);
                   cs.Parameters.AddWithValue("@SL", "TRUE");

                   if (cs.ExecuteScalar() == null)
                   {
                       create_new_service();
                   }
                   else
                   {
                       //If there is a record currently in the DB do this
                       using (MySqlDataReader read = cs.ExecuteReader())
                       {
                           while (read.Read())
                           {
                               services_remaining = read.GetString(read.GetOrdinal("services_remaining"));
                               rowID = read.GetString(read.GetOrdinal("ID"));
                           }
                       }
                       update_previous_record();
                   }

           }
           catch (MySqlException ex)
           {
               MessageBox.Show("Error checking services! " + ex, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
           }
       }

What is most strange and not being caught in the try/catch is a exception when run that say's the following:

"An unhandled exception of type 'System.IndexOutOfRangeException' occurred in MySql.Data.dll

Additional information: Could not find specified column in results: services_remaining"

I've checked and double checked the column name and it is correct and spelt correctly. I'm really stumped on this one, the errors happening in both cases.

CASE 1: Blank table with no values in it --> doesn't call "create_new_service()" instead jumps to else and tries to pull the values and presents the error mentioned above.

CASE 2: Values in the table --> it jumps to the else, tries to pull the values and presents the error mentioned above.

Not sure what it causing it, hopefully someone might be able to help.

Appreciate all your help and feedback!

解决方案

Use reader.HasRows to check if the query returns a result or not.
Next, you cannot ask the reader for the position of the "ID" column, since your query does not contain a column with that name. Use GetInt32(0) instead to get the value retuned by your SQL query.

Change your code to this:

object scalarVal = cs.ExecuteScalar();

if (scalarVal == null)
{
  //...

Then when you debug, put a break on the if line. See what the value of scalarVal is. I'm guessing because you are using an aggregate you have a value there.

Why are you getting the exception? You are only catching the MySqlException, the exception you are getting is an IndexOutOfRangeException, so execution will stop where the exception is thrown.

这篇关于C＃ExecuteScalar不返回null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！