Click here to Skip to main content
15,867,704 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

C#
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!
Posted
Comments
Wombaticus 17-Jan-14 8:49am    
I don't know about C#, but in VB you can't test ExecuteScalar = null like that - you have to use
If cs.ExecuteScalar() Is DBNull.Value Then ...
Ron Beyer 17-Jan-14 10:00am    
Thats not true in this case. ExecuteScalar should return null if there are no results from the query, it will return DBNull.Value if there is a result, but its value is null.
Wombaticus 17-Jan-14 11:25am    
If you say so - but in my VB tests I have to test against DBNull.Value to catch cases where there are no records returned - seems odd that C# and VB should treat this differently.
Ron Beyer 17-Jan-14 11:27am    
MSDN DBCommand.ExecuteScalar take a look at the remarks. This is the class all providers implement for the commands, its the standard of how no results versus a null result in the first row is handled.
Wombaticus 17-Jan-14 11:35am    
I believe you... I believe MIcrosoft... but all I can tell you is that
cs.CommandText = "select min(ID) from some_table_with_no_records"
If objCmd.ExecuteScalar() Is Nothing Then ...
will throw the exception
Conversion from type 'DBNull' to type 'String' is not valid.
and
If objCmd.ExecuteScalar() = Nothing Then ...
throws
Operator '=' is not defined for type 'DBNull' and 'Nothing'.
and I have to use
If objCmd.ExecuteScalar() Is DBNull.Value Then ...
in order to get the right result.
Perhaps the fact I'm testing against MySQL is relevant here?

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.
 
Share this answer
 
Comments
Linux Goblin 17-Jan-14 9:40am    
Hi, I've just tried what you've said and changed the code up a bit, I now get the following error: "An unhandled exception of type 'System.ArgumentException' occurred in MySql.Data.dll

Additional information: You have specified an invalid column ordinal."
Change your code to this:

C#
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.
 
Share this answer
 
Comments
Linux Goblin 17-Jan-14 10:51am    
Hi, yes I was using a MySQLException instead so that is why it was crashing (was using it at the time since there was a lot of interaction with DB's). After using the break points, it gets to the IF statement then jumps straight to the else. I have a feeling it is partially due to the SQL query but then again if nothing is in the table then scalarVal should be NULL and then do something else
Ron Beyer 17-Jan-14 10:53am    
So what is the value that is actually being returned from ExecuteScalar? The value of scalarVal in my suggestion? This will help narrow it down... Have you also tried your SQL in a simple query window for MySql? To see what is really returned?
Linux Goblin 17-Jan-14 11:26am    
Checked what you mentioned, if there is a value in the table it is successfully pulling the value (found what is causing the program to crash) HOWEVER when there is no value in the table scalarVar does return blank but it still registers as a value so it is jumping to the ELSE statement (maybe because it isn't DBNull?)
Ron Beyer 17-Jan-14 11:28am    
If you put a watch (or mouse hover over scalarVal) what is its type? If its not null it should be something like an integer, there really isn't a "blank" type...
Linux Goblin 17-Jan-14 11:37am    
With no values in the table it is returning as "Object"

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900