We help IT Professionals succeed at work.

Getting a connection not initialized when using SqldataReaer

357 Views
Last Modified: 2013-11-07
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;
    }







Comment
Watch Question

Top Expert 2007

Commented:
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);

Author

Commented:
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();
            }
Top Expert 2007

Commented:
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

Author

Commented:
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
Top Expert 2007

Commented:
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

Author

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

Top Expert 2007

Commented:
Steve - Did you want help with those too?

Jim

Author

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

Steve
Top Expert 2007

Commented:
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

Author

Commented:
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
Top Expert 2007
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you very very much. Everything is working for me now
Ted BouskillSenior Software Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
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
{

}
Ted BouskillSenior Software Developer
CERTIFIED EXPERT
Top Expert 2009

Commented:
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!
Top Expert 2007

Commented:
My pleasure. Good luck Steve.

Jim

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.