?
Solved

Getting a connection not initialized when using SqldataReaer

Posted on 2007-09-29
17
Medium Priority
?
337 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;
    }







0
Comment
Question by:svasilakos
  • 7
  • 6
  • 2
  • +1
17 Comments
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19984373
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
0
 
LVL 21

Expert Comment

by:surajguptha
ID: 19984378
you are not associating the command to the connection
try
SqlCommand storedProcCommand = new  SqlCommand   "sp_REPORT_CheckDailySumBySettleDate_DFSOPS", connection);
0
 

Author Comment

by:svasilakos
ID: 19984462
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();
            }
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 22

Expert Comment

by:JimBrandley
ID: 19984470
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
0
 

Author Comment

by:svasilakos
ID: 19984498
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
0
 
LVL 21

Expert Comment

by:surajguptha
ID: 19984536
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
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19984558
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
0
 

Author Comment

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

0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19984584
Steve - Did you want help with those too?

Jim
0
 

Author Comment

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

Steve
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19984621
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
0
 

Author Comment

by:svasilakos
ID: 19984655
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
0
 
LVL 22

Accepted Solution

by:
JimBrandley earned 1000 total points
ID: 19984718
I would try:
data.GetDecimal(8)
data.GetDateTime(9)

Jim
0
 

Author Comment

by:svasilakos
ID: 19985559
Thank you very very much. Everything is working for me now
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 19985568
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
{

}
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 19985575
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!
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19985672
My pleasure. Good luck Steve.

Jim
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Screencast - Getting to Know the Pipeline

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question