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.
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.