Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
COURSE of the MONTH
14 days, 12 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
Active 6 days ago
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
Active 6 days ago
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
Introduction
This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks.
Background
I had to provide a way for user…
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 you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month14 days, 12 hours left to enroll
771 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.