Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.
Course Of The Month
6 days, 20 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
There is already an open DataReader associated with this Command which must be closed first.
Posted on 2011-03-09
I receivr the following error when executing this code:
There is already an open DataReader associated with this Command which must be closed first.
Code:
String sConnectionString = "Provider=Microsoft.ACE.OL
"Data Source=" + Server.MapPath("file.xlsx"
"Extended Properties=Excel 12.0";
DbProviderFactory factory = DbProviderFactories.GetFac
SqlConnection sqlConnectionString = new SqlConnection("Data Source=server;Initial Catalog=database;Persist Security Info=True;MultipleActiveRe
sqlConnectionString.Open()
SqlCommand cmd = new SqlCommand();
try
{
using (DbConnection connection = factory.CreateConnection()
{
connection.ConnectionStrin
using (DbCommand command = connection.CreateCommand()
{
command.CommandText = "SELECT [col1],[col2] FROM [Sheet1$]";
connection.Open();
using (DbDataReader dr = command.ExecuteReader())
{
int lineItems = 0;
while (dr.Read())
{
string strItemNum = dr["col1"].ToString().Trim
string strPart = dr["col2"].ToString().Trim
cmd.CommandText = "INSERT INTO table1 VALUES (@ProjectID, @VersionID)";
cmd.Parameters.Add("@Proje
cmd.Parameters["@ProjectID
cmd.Parameters.Add("@Versi
cmd.Parameters["@VersionID
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnectionString;
cmd.ExecuteReader();
}
dr.Close();
}
}
connection.Close();
}
}
catch (Exception ex)
{
Response.Write(ex.Message)
}
sqlConnectionString.Close(
There is already an open DataReader associated with this Command which must be closed first.
Code:
String sConnectionString = "Provider=Microsoft.ACE.OL
"Data Source=" + Server.MapPath("file.xlsx"
"Extended Properties=Excel 12.0";
DbProviderFactory factory = DbProviderFactories.GetFac
SqlConnection sqlConnectionString = new SqlConnection("Data Source=server;Initial Catalog=database;Persist Security Info=True;MultipleActiveRe
sqlConnectionString.Open()
SqlCommand cmd = new SqlCommand();
try
{
using (DbConnection connection = factory.CreateConnection()
{
connection.ConnectionStrin
using (DbCommand command = connection.CreateCommand()
{
command.CommandText = "SELECT [col1],[col2] FROM [Sheet1$]";
connection.Open();
using (DbDataReader dr = command.ExecuteReader())
{
int lineItems = 0;
while (dr.Read())
{
string strItemNum = dr["col1"].ToString().Trim
string strPart = dr["col2"].ToString().Trim
cmd.CommandText = "INSERT INTO table1 VALUES (@ProjectID, @VersionID)";
cmd.Parameters.Add("@Proje
cmd.Parameters["@ProjectID
cmd.Parameters.Add("@Versi
cmd.Parameters["@VersionID
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnectionString;
cmd.ExecuteReader();
}
dr.Close();
}
}
connection.Close();
}
}
catch (Exception ex)
{
Response.Write(ex.Message)
}
sqlConnectionString.Close(
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
2
4 Comments
Can you try the following
String sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + Server.MapPath("file.xlsx") + ";" +
"Extended Properties=Excel 12.0";
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
try
{
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = sConnectionString;
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT [col1],[col2] FROM [Sheet1$]";
connection.Open();
using (DbDataReader dr = command.ExecuteReader())
{
int lineItems = 0;
while (dr.Read())
{
string strItemNum = dr["col1"].ToString().Trim();
string strPart = dr["col2"].ToString().Trim();
cmd.CommandText = "INSERT INTO table1 VALUES (@ProjectID, @VersionID)";
cmd.Parameters.Add("@ProjectID", SqlDbType.NVarChar);
cmd.Parameters["@ProjectID"].Value = ProjectDropDown.SelectedValue;
cmd.Parameters.Add("@VersionID", SqlDbType.NVarChar);
cmd.Parameters["@VersionID"].Value = Label3.Text;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnectionString;
cmd.ExecuteReader();
}
dr.Close();
}
}
connection.Close();
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
Also you may want to use:
cmd.ExecuteNonQuery();
instead of:
cmd.ExecuteReader();
when it's a simple insert command
cmd.ExecuteNonQuery();
instead of:
cmd.ExecuteReader();
when it's a simple insert command
Might be because you are repeatedly adding the connection to the command (you are also repeatedly adding parameters which will also make it fallover with parameter already exists) can you try this:
Another tip is to check the state of a connection before you open/close it i.e.:
in your case we know it won't be open because it has only just been created but good practise in other scenarios.
HTH
Gav
String sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + Server.MapPath("file.xlsx") + ";" +
"Extended Properties=Excel 12.0";
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
SqlConnection sqlConnectionString = new SqlConnection("Data Source=server;Initial Catalog=database;Persist Security Info=True;MultipleActiveResultSets=True;User ID=user;Password=password");
sqlConnectionString.Open();
SqlCommand cmd = new SqlCommand(sqlConnectionString);
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO table1 VALUES (@ProjectID, @VersionID)";
cmd.Parameters.Add("@ProjectID", SqlDbType.NVarChar);
cmd.Parameters.Add("@VersionID", SqlDbType.NVarChar);
try
{
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = sConnectionString;
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT [col1],[col2] FROM [Sheet1$]";
connection.Open();
using (DbDataReader dr = command.ExecuteReader())
{
int lineItems = 0;
while (dr.Read())
{
string strItemNum = dr["col1"].ToString().Trim();
string strPart = dr["col2"].ToString().Trim();
cmd.Parameters["@ProjectID"].Value = ProjectDropDown.SelectedValue;
cmd.Parameters["@VersionID"].Value = Label3.Text;
cmd.ExecuteNonQuery();
}
dr.Close();
}
}
connection.Close();
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
sqlConnectionString.Close();
Another tip is to check the state of a connection before you open/close it i.e.:
if (sqlConnectionString.State == ConnectionState.Closed)
sqlConnectionString.Open();
in your case we know it won't be open because it has only just been created but good practise in other scenarios.
HTH
Gav
Another remark: when you use the using-statement, you don't have to close your connection (this will be done)
http://www.hanselman.com/blog/WhyTheUsingStatementIsBetterThanASharpStickInTheEyeAndASqlConnectionRefactoringExample.aspx
http://www.hanselman.com/blog/WhyTheUsingStatementIsBetterThanASharpStickInTheEyeAndASqlConnectionRefactoringExample.aspx
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article is for Object-Oriented Programming (OOP) beginners.
An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this?
(CODE)
In .NET 3.5, this is possible…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Suggested Courses
Course of the Month6 days, 20 hours left to enroll
726 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.