none
An exception occurred: ExecuteReader: CommandText property has not been initialized RRS feed

  • Question

  • Hello all,

    I am getting this error when I run this C# code in my production environment (Windows Server 2003).  It works in my Visual Studio test environment (my PC) just fine.  The same drivers are installed on both machines.

    Can anyone tell me why I would get this error on the production environment?  I looked at many of the forum posts and couldn't find an answer.  Thank you in advance!!

    using

    System;

    using

    System.IO;

    using

    System.Text;

    using

    System.Data;

    using

    System.Data.SqlClient;

    using

    System.Configuration;

    using

    System.Web;

    using

    System.Data.Odbc;

    using

    System.Collections;

    using

    System.Diagnostics;

    using

    System.Threading;

    using

    System.Runtime.CompilerServices;

    namespace

    AndonTPDataWrite

    {

     

    class AndonTPDataWrite

     

    {

     

     

    static void Main(string[] args)

    {

     

     

    DataSet oracleResultsDS = new DataSet("andonreport");

     

    StringBuilder timeClock = new StringBuilder();

     

    OdbcCommand mySqlCommand;

     

    OdbcCommandBuilder mBuild;

     

    OdbcDataAdapter mySqlAdapter;

     

    OdbcDataAdapter adapter;

     

    String todayString = null;

     

    DateTime today;

     

    String oracleConnectionString = "Driver={Microsoft ODBC for Oracle};Server=qsys566;UID=QSYSRW;PWD=QSYSRW;",

    mySqlConnectionString =

    "Driver={MySQL ODBC 5.1 Driver};Server=s566a001.dcmi.daimlerchrysler.com;Port=3306;Database=qmart;Uid=root;Pwd=qmart;";

     

    OdbcConnection odbcConnOracle;

     

    OdbcConnection odbcConnMySQL;

    odbcConnMySQL =

    new OdbcConnection(mySqlConnectionString);

    odbcConnOracle =

    new OdbcConnection(oracleConnectionString);

     

    String tpQueryString = null;

    System.

    TimeSpan dateCompare;

     

    OdbcDataReader rdr;

     

    try
     

    {

     

    //Gets DateTime as of Now
     

    today =

    DateTime.Now;

    today = today.AddSeconds(-today.Second);

    // Rounds down to nearest minute
     

    todayString = today.ToString(

    "u").TrimEnd('Z');

     

     

    odbcConnMySQL.Open();

    mySqlCommand =

    new OdbcCommand("select distinct tp from qmart.andonreport", odbcConnMySQL);

    mySqlCommand.ExecuteNonQuery();

    rdr = mySqlCommand.ExecuteReader();

     

    Boolean rowCount = rdr.HasRows; ; //Checks to make sure andonreport has rows. If not, then use 'Else' query.
     

    rdr.Dispose();

    mySqlCommand =

    new OdbcCommand("select dateChecker from qmart.andondatecheck ar", odbcConnMySQL);

     

    // mySqlCommand = new OdbcCommand("select max(STR_TO_DATE(ar.dtime, '%m/%d/%Y %H:%i' )) as dtime,max(STR_TO_DATE(adc.dateChecker, '%Y-%m-%d %H:%i' )) from qmart.andonreport ar,qmart.andondatecheck adc", odbcConnMySQL);
     

    rdr = mySqlCommand.ExecuteReader();

     

    // QSYS data pull from Oracle Query String Builder
     

     

    while (rdr.Read())

    {

     

    if (rowCount==true && DateTime.Parse(todayString).DayOfYear == DateTime.Parse(rdr[0].ToString()).DayOfYear)

    {

    dateCompare =

    DateTime.Parse(todayString).Subtract(DateTime.Parse(rdr[0].ToString()));

     

    if (dateCompare.Minutes <= 2)

    {

     

    // if (today.DayOfYear > DateTime.Parse(rdr[0].ToString()).DayOfYear)

     

    // {

    tpQueryString =

    "SELECT bt.nlfdkbnr AS SHORTVIN, zpt.szpktnr AS TP,min(zpk.dtmeldezeit) AS DTIME FROM bt_info bt,zpkt_hist zpk, zaehlpunkt zpt WHERE bt.nlfdkbnr = zpk.nlfdkbnr AND zpk.nlfdzpkt = zpt.nlfdzpkt AND to_date('" + DateTime.Parse(rdr[0].ToString()).ToString("u").TrimEnd('Z') + "','YYYY-MM-DD HH24:MI:SS') <= zpk.dtmeldezeit AND to_date('" + todayString + "','YYYY-MM-DD HH24:MI:SS') >= zpk.dtmeldezeit AND zpt.szpktnr IN ('921','937','945','988') group by bt.spnr8,zpt.szpktnr,bt.nlfdkbnr order by TP,DTIME";

     

     

    // }

    }

    }

     

    else

    {

    tpQueryString =

    "SELECT bt.nlfdkbnr AS SHORTVIN, zpt.szpktnr AS TP,min(zpk.dtmeldezeit) AS DTIME FROM bt_info bt,zpkt_hist zpk, zaehlpunkt zpt WHERE bt.nlfdkbnr = zpk.nlfdkbnr AND zpk.nlfdzpkt = zpt.nlfdzpkt AND to_date('" + today.Year + "-" + today.Month + "-" + today.Day + " 06:00:00','YYYY-MM-DD HH24:MI:SS') <= zpk.dtmeldezeit AND to_date('" + today.Year + "-" + today.Month + "-" + today.Day + " 15:00:00','YYYY-MM-DD HH24:MI:SS') >= zpk.dtmeldezeit AND zpt.szpktnr IN ('921','937','945','988') group by bt.spnr8,zpt.szpktnr,bt.nlfdkbnr order by TP,DTIME";

     

    // tpQueryString = "SELECT bt.nlfdkbnr AS SHORTVIN, zpt.szpktnr AS TP,min(zpk.dtmeldezeit) AS DTIME FROM bt_info bt,zpkt_hist zpk, zaehlpunkt zpt WHERE bt.nlfdkbnr = zpk.nlfdkbnr AND zpk.nlfdzpkt = zpt.nlfdzpkt AND to_date('" + today.Year + "-" + today.Month + "-" + today.Day + " 06:00:00','YYYY-MM-DD HH24:MI:SS') <= zpk.dtmeldezeit AND to_date('2011-06-30 15:00:00','YYYY-MM-DD HH24:MI:SS') >= zpk.dtmeldezeit AND zpt.szpktnr IN ('921','937','945','988') group by bt.spnr8,zpt.szpktnr,bt.nlfdkbnr order by TP";

    }

    mySqlCommand.Dispose();

    mySqlCommand =

    new OdbcCommand("update andondatecheck set dateChecker = '" + todayString + "'", odbcConnMySQL);

    mySqlCommand.ExecuteNonQuery();

    mySqlCommand.Dispose();

     

    //Console.WriteLine(rdr[0]);

    }

    rdr.Dispose();

     

    mySqlAdapter =

    new OdbcDataAdapter("select * from andonreport", odbcConnMySQL);

    mBuild =

    new OdbcCommandBuilder(mySqlAdapter);

    mySqlAdapter.Fill(oracleResultsDS,

    "andonreport");

    mySqlAdapter.Dispose();

    odbcConnOracle.Open();

    adapter =

    new OdbcDataAdapter(tpQueryString, odbcConnOracle);

    adapter.Fill(oracleResultsDS.Tables[

    "andonreport"]);

    adapter.Dispose();

    odbcConnOracle.Close();

     

    if (oracleResultsDS.Tables[0].Rows.Count > 0)

    {

     

    foreach (DataRow dr1 in oracleResultsDS.Tables[0].Rows)

    {

     

    DataRow myDataRow = oracleResultsDS.Tables["andonreport"].NewRow();

    mySqlCommand =

    new OdbcCommand("insert into qmart.andonreport (shortvin,tp,dtime) values('" + dr1["shortvin"].ToString() + "','" + dr1["tp"].ToString() + "','" + dr1["dtime"].ToString() + "')", odbcConnMySQL);

    mySqlCommand.ExecuteNonQuery();

    mySqlCommand.Dispose();

    }

    }

    }

     

    catch (OdbcException oe)

    {

     

    Console.WriteLine("An ODBC exception occurred: " + oe.Message.ToString());

     

    Debug.WriteLine("An ODBC exception occurred: " + oe.Message.ToString());

    }

     

    catch (Exception c)

    {

     

    Console.WriteLine("An exception occurred: " + c.Message.ToString());

     

    Debug.WriteLine("An exception occurred: " + c.Message.ToString());

    }

     

    finally

    {

     

    if (odbcConnOracle.State.Equals("Open"))

    odbcConnOracle.Close();

    odbcConnMySQL.Close();

    }

    }

     

    }

    }

    Tuesday, July 12, 2011 9:08 PM

Answers

  • Something odd in your code I noticed. You have ExecuteNonQuery right before ExecuteReader in your first query to the database. Is there a reason for this?


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by mb_dude30 Saturday, July 16, 2011 11:55 PM
    Wednesday, July 13, 2011 1:28 PM
  • Hello,

    Regarding this error information, it looks that your question is related to CommandText. From reflector, you will find the following code snippet for OdbcCommand.

    public OdbcCommand(string cmdText, OdbcConnection connection) : this()
    {
      this.CommandText = cmdText;
      this.Connection = connection;
    }
    

    I suggest you first check if your passed SQL statement returned non-null value. Please check this document and test your code first. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtext.aspx

    If you have any finding or question, please feel free to let us know.

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by mb_dude30 Saturday, July 16, 2011 11:55 PM
    Thursday, July 14, 2011 5:09 AM

All replies

  • Something odd in your code I noticed. You have ExecuteNonQuery right before ExecuteReader in your first query to the database. Is there a reason for this?


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by mb_dude30 Saturday, July 16, 2011 11:55 PM
    Wednesday, July 13, 2011 1:28 PM
  • Hello,

    Regarding this error information, it looks that your question is related to CommandText. From reflector, you will find the following code snippet for OdbcCommand.

    public OdbcCommand(string cmdText, OdbcConnection connection) : this()
    {
      this.CommandText = cmdText;
      this.Connection = connection;
    }
    

    I suggest you first check if your passed SQL statement returned non-null value. Please check this document and test your code first. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtext.aspx

    If you have any finding or question, please feel free to let us know.

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by mb_dude30 Saturday, July 16, 2011 11:55 PM
    Thursday, July 14, 2011 5:09 AM
  • Thank you both!  I found out what it was.  Based on the logic, the query would sometimes return no results, which is what bombed it.  I started using datareaders exclusively, and put boolean logic around it based on HasRows == true.  I also had an ExecuteNonQuery() being used on a Select statement, which I corrected.

     

    It works great now!  Thanks again.

    Saturday, July 16, 2011 11:56 PM
  • Glad to hear it helps. If you have any question later, welcome to come back.

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Sunday, July 17, 2011 3:27 AM