Link to home
Start Free TrialLog in
Avatar of svasilakos
svasilakos

asked on

Getting a connection not initialized when using SqldataReaer

I'm not familiar with using the DataReader and I am a noice in C#.  I am getting the following exeception error when trying to make a database connection vai asp.net . I'm trying to test a PDF Generator product from a company called cete software. I'm sure I'm missing something big a obvious but I'm not sure what. I have used System.Data.DataTable  successfully many times, this is my first attempt with DataReader. Just looking for ideas on where to look.

Here is the error in the event log:
Exception information:
    Exception type: InvalidOperationException
    Exception message: ExecuteReader: Connection property has not been initialized.

Here is the sections of my code page that deal with the connection. I am obvious closing the System.Web.UI.Page and Pageload sections later on in the code.

public partial class WebApp_Steve_TableReport : System.Web.UI.Page
   {
       
        static string dBConnString =
              System.Configuration.ConfigurationSettings.AppSettings["DFSConnectionString2"];

        protected void Page_Load(object sender, System.EventArgs e)
            {
                  // Establises connection to the database
                  SqlConnection connection = GetOpenDBConn();
                  SqlDataReader data = GetContactListData( connection );


      private SqlConnection GetOpenDBConn()
            {
      // Creates and opens a database connection
            SqlConnection connection = new SqlConnection( dBConnString );
            connection.Open();
          return connection;
           }
   
 private SqlDataReader GetContactListData( SqlConnection connection )
     {
            string from = "09/01/2007";
            string to = "09/24/2007";
            string siteId = "3";
            string transType = "1";
                       
SqlCommand storedProcCommand = new  SqlCommand   "sp_REPORT_CheckDailySumBySettleDate_DFSOPS");
            storedProcCommand.CommandType = CommandType.StoredProcedure;
            storedProcCommand.Parameters.Add("@sinceWhen", from);
            storedProcCommand.Parameters.Add("@untilWhen", to);
            storedProcCommand.Parameters.Add("@siteid", siteId);
            storedProcCommand.Parameters.Add("@transtype", transType);

     SqlDataReader dataReader = storedProcCommand.ExecuteReader();
     return dataReader;
    }







Avatar of JimBrandley
JimBrandley
Flag of United States of America image

You forgot to set the Connection property on the command. So:
storedProcCommand.Connection = connection;

Also, I would not pass the connection in. You want to open them as late as possible, and close them as soon as possible. Best practice here is to open and close in the same method.

Jim
you are not associating the command to the connection
try
SqlCommand storedProcCommand = new  SqlCommand   "sp_REPORT_CheckDailySumBySettleDate_DFSOPS", connection);
Avatar of svasilakos
svasilakos

ASKER

Thanks this got rid of my error. This may be a stupid question but regarding Jim's comment, do you mean I should do something like this

private SqlConnection GetOpenDBConn()
            {
                  // Creates and opens a database connection
            SqlConnection connection = new SqlConnection( dBConnString );
           
                  connection.Open();
                  return connection;
                                                                connnection.Close();
            }
No - That one wouldn't do you much good. I meant open and close in the same method that makes use co the connection. In this case, GetContactListData.

There are two reasons for this:
1. Minimize the time it's held open, as mentioned above, and
2. Reduces the likelyhood of connection leaks because someone forgot to close it.

Jim
OK. Understood. One last question on sqldatareader and than I'll start a new thread if I have trouble.

How do I convert an Int to a string in sqldatareader? or should I do it out site of datareader. I'm getting this error in the event log and I believe it's with the section below. Some

 Exception type: InvalidCastException
 Exception message: Unable to cast object of type 'System.Int32' to type 'System.String'.

private void CreateRow(Table table, SqlDataReader data)
{
Row row = table.Rows.Add( 20 );
 row.Cells.Add( data.GetString(0) , Font.Helvetica, 12, RgbColor.Black, RgbColor.LightGrey, 1 );
 row.Cells.Add( data.GetString(1) );
 row.Cells.Add( data.GetString(2) );
 row.Cells.Add(data.GetString(3));
 row.Cells.Add(data.GetString(4));
 row.Cells.Add(data.GetString(5));
 row.Cells.Add(data.GetString(6));
 row.Cells.Add(data.GetString(7));
 row.Cells.Add(data.GetString(8));
 row.Cells.Add(data.GetString(9));
  row.Cells.Add(data.GetString(10));
}

-Steve
one of ur columns might have been an integer. If you check which column and did a data.GetInt(x).ToString() it should solve the problem
A DataReader returns an array of objects. If you want to use data.GetString() and data.GetInt32(), you need to line them up correctly according to the data type of the returned object. You can also:
object[] results = new object[11]; // Match the number of columns returned
row.Cells.Add( results[0] as int);
row.Cells.Add( results[1] as string);
etc.
Again, each cast needs to match the data type of the returned column. You could also:
row.Cells.Add(results[0].ToString());
but then you are boxing value data types.

Jim
Thank you very much , that worked for my integer columns now I just have two datetime and two decimal  ( smallmoney actually) to deal with.

Steve - Did you want help with those too?

Jim
If you don't mind....I hate asking repeated stupid questions. Last one than I'll actually pick up a book.

Steve
For the Date column,
DateTime thisDT = Convert.ToDateTime(results[k].ToString());

For the smallmoney, you can use decimal or double as:
decimal cost = Convert.ToDecimal(results[k].ToString());
double cost = Convert.ToDouble(results[k].ToString());

Jim
Ok, I think I understand that,  I've done that before in some other code. I'm just a little confused when useing roww.Cells.Add

if Column 8 is smallmoney and 9 is date time, how should I use it
row.Cells.Add(data.GetString(8) );
row.Cells.Add(data.GetString(9) );

-Steve
ASKER CERTIFIED SOLUTION
Avatar of JimBrandley
JimBrandley
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you very very much. Everything is working for me now
Avatar of Ted Bouskill
Guys, the best practice for the original question is:

try
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
    using (SqlCommand cm = new SqlCommand(commandString, cn))
    {
        cn.Open();
        cm.ExecuteNonQuery();
    }
}
}
catch
{

}
Oops, hit Submit too soon.  Good quality classes implement the IDisposable interface which is used by the 'using' keyword.  In the example, the SqlConnection and SqlCommand will be closed automatically even if there is an exception!
My pleasure. Good luck Steve.

Jim