Bug #4965 Is there an issue with the MySqlDataReader.HasRow Attribute?
Submitted: 9 Aug 2004 18:50 Modified: 26 Aug 2004 18:18
Reporter: Peter Snizek Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:MySql 4.0.16-nt, ByteFX 0.76, .net 1.1 OS:Microsoft Windows (Windows XP (Whistler))
Assigned to: Reggie Burnett CPU Architecture:Any

[9 Aug 2004 18:50] Peter Snizek
Description:
I have the issue that the HasRow property of myReader often returns false, although
there is 1 record in the respective table. The strange thing is, that it sometimes works correctly
and sometimes not. 

Please see the method:

public void FindFirstRec()
  {
	/* this Method finds the first entry in the header table and returns
	 * the record ID because I want the queue to work among the FIFO principles */

	string SQLQuery="SELECT __headerID, AUFNR FROM header ORDER BY __headerID ASC";
	mycmd = new MySqlCommand(SQLQuery, mycon);
	// ExecuteReader returns a dataset
	MySqlDataReader myReader = mycmd.ExecuteReader();
	MessageBox.Show("Waiting for myReader"); // debug code
	if (myReader.HasRows)
		{
			myReader.Read();
			RecID=myReader.GetInt32(0); // gets the first field which is a INT type
			OrderNumber=myReader.GetString(1);
			Console.WriteLine (Convert.ToString(RecID)+"    "+OrderNumber); // debug
information to console
		}
			myReader.Close(); // closes the Reader
  }

1)
The statement: if (myReader.HasRows) often, not always, returns false, although
there is 1 record in the respective table.

2) 
If I would not check with "HasRows" the statement
"OrderNumber=myReader.GetString(1)" would through following error: "Object
reference not set to an instance of an object"

3) As I mentioned, this happens mostly, but sometimes it works. If it works than
only after the program got restarted.

4) The method works reliably if there are at least 2 records in the table. (if
only 1 record, then it is often ignored as already mentioned).

5) If there are at least 2 records, then it identifies record 2 as the 1st
record while the other record is still ignored.

6) I mentioned that it sometimes works. If it worked, and I repeat the method,
   then the 2nd time (and each following try) it doesn't work. 

I would appreciate very much your help on this. Otherwise I wouldn't be able to
finish my work. Please just let me know if I did a mistake or if it needs a
bugfix on your end.

Here's the complete class (just for your reference):

######################################################
######################################################

using System;
using System.Threading;
using System.Data;
using System.Windows.Forms;
using System.Data.Odbc;
using ByteFX.Data.MySqlClient;

namespace ASReporter
{

	/* This class is designed to move jobs from header to header_spooler tables.
	 * It happens record by record. Once a record is "computed" the next record
	 * can be taken from the header table. Reason is to make sure that there is
	 * alway only 1 print job stored in the spooler tables (header_spooler 
	 * and detail_spooler). */

	public class computeJob
	{
		string DataSource;
		string Database;
		string UserID;
		string Password;
		string MyConString;
		int RecID;
		string OrderNumber;
		MySqlConnection mycon;
		MySqlCommand mycmd;

		public void ConnectDatabase()
		{
			// set connection and open mysql database
			DataSource	= "localhost";
			Database	= "ASReporter";
			UserID		= "";
			Password	= "";
			
			// create connection string
			MyConString = "Data Source=" + DataSource + 
				";Database=" + Database +
				";User ID=" + UserID +
				";Password=" + Password;
			mycon = new MySqlConnection(MyConString);
			mycon.Open(); // open database based on defined connection
			
			
		}

		public void FindFirstRec()
		{
			/* this Method finds the first entry in the header table and returns
			 * the record ID because I want the queue to work among the FIFO principles */

			string SQLQuery="SELECT __headerID, AUFNR FROM header ORDER BY __headerID ASC";
			mycmd = new MySqlCommand(SQLQuery, mycon);
			// ExecuteReader returns a dataset
			MySqlDataReader myReader = mycmd.ExecuteReader();
			MessageBox.Show("Waiting for myReader");
			if (myReader.HasRows)
			{
				myReader.Read();
				RecID=myReader.GetInt32(0); // gets the first field which is a INT type
				OrderNumber=myReader.GetString(1);
				Console.WriteLine (Convert.ToString(RecID)+"    "+OrderNumber); // debug information to console
			}
			myReader.Close(); // closes the Reader
		}
		
		
		public void MoveHeader()
		{
			string CopyHeaderRec="INSERT INTO header_spooler SELECT * FROM header WHERE __headerID="+this.RecID; 
			// update command object with Query String
			mycmd = new MySqlCommand(CopyHeaderRec, mycon); 
			mycmd.ExecuteNonQuery(); // execute Query
			mycmd.Dispose();
			string DeleteHeaderRec="DELETE FROM header WHERE __headerID="+this.RecID; 
			mycmd = new MySqlCommand(DeleteHeaderRec, mycon);
			mycmd.ExecuteNonQuery();
			mycmd.Dispose();
		}

		public void MoveDetail()
		{
			string CopyDetailRec="INSERT INTO detail_spooler SELECT * FROM detail WHERE _headerID="+this.RecID; 
			// update command object with Query String
			mycmd = new MySqlCommand(CopyDetailRec, mycon); 
			mycmd.ExecuteNonQuery(); // execute Query
			mycmd.Dispose();
			string DeleteDetailRec="DELETE FROM detail WHERE _headerID="+this.RecID; 
			mycmd = new MySqlCommand(DeleteDetailRec, mycon);
			mycmd.ExecuteNonQuery();
			mycmd.Dispose();

		}

		public void SendRecord()
		{
			MessageBox.Show("Gedruckt wird Auftragsnr: " 
				+ this.OrderNumber + " Job-ID: " + Convert.ToString(this.RecID));
		}

		public  void CloseDBConnection()
		{
			mycmd.Dispose(); // close connection and destroy command object
			mycon.Close();
			mycon.Dispose();
			mycmd=null;
			mycon=null;

		}

		public void ClearSpooler()
		{
			string ClrSpooler="DELETE FROM header_spooler;" +
							   "DELETE FROM detail_spooler;";
			mycmd = new MySqlCommand(ClrSpooler, mycon);
			mycmd.ExecuteNonQuery();
			mycmd.Dispose();
		}

		public computeJob()
		{

			this.ConnectDatabase();	// opens database
			this.FindFirstRec();	// finds first record in header (FIFO principle)
			this.MoveHeader();		// moves found header record to header_spooler table
			this.MoveDetail();		// moves corresponding detail records to detail_spooler table
			this.SendRecord();		// performs action (i.e. printing a report based on records
									// in header_spooler and detail_spooler
			this.ClearSpooler();    // After job is processed it needs to be removed from Spooler;
			this.CloseDBConnection(); // closes db connection
		}
	}
}

How to repeat:
Repeat myReader.HasRow as described in my method above. Repetition is not 100% reliable. It sometimes works, especially if the program is restarted. But even then, it can cappen that the HasRow attribute returns FALSE instead of TRUE (if there is only 1 record). The respective table has 45 columns in total. Hope that is not a problem.
[13 Aug 2004 11:58] Peter Snizek
Issue closed. Problem caused by bad thread construction.
[26 Aug 2004 17:46] Reggie Burnett
Sorry, but the bug system is not the appropriate forum for asking
support questions. Your problem is not the result of a bug.
For a list of more appropriate places to ask for help using MySQL
products, please visit http://www.mysql.com/support/

Thank you for your interest in MySQL.

Additional info:

Closed at user's request
[26 Aug 2004 18:18] Reggie Burnett
I apologize to Peter for how that last comments sounded.  That message is automatically appended based on the status change of the bug.  Peter certainly did use the bug system correctly and I apologize to him publicly for how it sounded.   I'm sorry Peter!

-reggie