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.Confi gurationSe ttings.App Settings[" DFSConnect ionString2 "];
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_CheckDailySumBy SettleDate _DFSOPS");
storedProcCommand.CommandT ype = CommandType.StoredProcedur e;
storedProcCommand.Paramete rs.Add("@s inceWhen", from);
storedProcCommand.Paramete rs.Add("@u ntilWhen", to);
storedProcCommand.Paramete rs.Add("@s iteid", siteId);
storedProcCommand.Paramete rs.Add("@t ranstype", transType);
SqlDataReader dataReader = storedProcCommand.ExecuteR eader();
return dataReader;
}
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.Confi
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_CheckDailySumBy
storedProcCommand.CommandT
storedProcCommand.Paramete
storedProcCommand.Paramete
storedProcCommand.Paramete
storedProcCommand.Paramete
SqlDataReader dataReader = storedProcCommand.ExecuteR
return dataReader;
}
you are not associating the command to the connection
try
SqlCommand storedProcCommand = new SqlCommand "sp_REPORT_CheckDailySumBy SettleDate _DFSOPS", connection);
try
SqlCommand storedProcCommand = new SqlCommand "sp_REPORT_CheckDailySumBy
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();
}
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
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
ASKER
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.GetStri ng(3));
row.Cells.Add(data.GetStri ng(4));
row.Cells.Add(data.GetStri ng(5));
row.Cells.Add(data.GetStri ng(6));
row.Cells.Add(data.GetStri ng(7));
row.Cells.Add(data.GetStri ng(8));
row.Cells.Add(data.GetStri ng(9));
row.Cells.Add(data.GetStri ng(10));
}
-Steve
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.GetStri
row.Cells.Add(data.GetStri
row.Cells.Add(data.GetStri
row.Cells.Add(data.GetStri
row.Cells.Add(data.GetStri
row.Cells.Add(data.GetStri
row.Cells.Add(data.GetStri
row.Cells.Add(data.GetStri
}
-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].T oString()) ;
but then you are boxing value data types.
Jim
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].T
but then you are boxing value data types.
Jim
ASKER
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
Jim
ASKER
If you don't mind....I hate asking repeated stupid questions. Last one than I'll actually pick up a book.
Steve
Steve
For the Date column,
DateTime thisDT = Convert.ToDateTime(results [k].ToStri ng());
For the smallmoney, you can use decimal or double as:
decimal cost = Convert.ToDecimal(results[ k].ToStrin g());
double cost = Convert.ToDouble(results[k ].ToString ());
Jim
DateTime thisDT = Convert.ToDateTime(results
For the smallmoney, you can use decimal or double as:
decimal cost = Convert.ToDecimal(results[
double cost = Convert.ToDouble(results[k
Jim
ASKER
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.GetStri ng(8) );
row.Cells.Add(data.GetStri ng(9) );
-Steve
if Column 8 is smallmoney and 9 is date time, how should I use it
row.Cells.Add(data.GetStri
row.Cells.Add(data.GetStri
-Steve
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very very much. Everything is working for me now
Guys, the best practice for the original question is:
try
{
using (SqlConnection cn = new SqlConnection(connectionSt ring))
{
using (SqlCommand cm = new SqlCommand(commandString, cn))
{
cn.Open();
cm.ExecuteNonQuery();
}
}
}
catch
{
}
try
{
using (SqlConnection cn = new SqlConnection(connectionSt
{
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
Jim
storedProcCommand.Connecti
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