d_li1121 d_li1121 - 3 months ago 13
ASP.NET (C#) Question

Connection was not closed, Connection's current state is open error in foreach loop

I have a web app using a databound checkboxlist. I have an update stored procedure that I placed in a foreach loop. If one CheckboxList is checked, the update is fine, but if I have more than one checked it throws the connection is open error. I tried a

try{}catch{}finally{}
but it's still giving me the same error

CultureInfo provider = CultureInfo.InvariantCulture;
System.Globalization.DateTimeStyles style = DateTimeStyles.None;
DateTime dt;
DateTime.TryParseExact(datepicker.Text, "mmddy", provider, style, out dt);
int i = Int32.Parse(amount.Text);

SqlConnection conn = new SqlConnection(GetConnectionString());
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;


foreach (ListItem item in CheckBoxList1.Items)
{
if(item.Selected)
{

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[dbo].[AccountCode_Update]";
cmd.Parameters.AddWithValue("@Batch_Num", SqlDbType.Int).Value = i;
cmd.Parameters.AddWithValue("@Batch_Date", SqlDbType.DateTime).Value = dt;
cmd.Parameters.AddWithValue("@Account_Code", SqlDbType.VarChar).Value = BatchCodeList.SelectedValue;
conn.Open();
cmd.ExecuteNonQuery();
}


}
conn.Close();


SQL

CREATE TABLE AccountTable
(
RowID int IDENTITY(1, 1),
AccountID varchar(2),
AccountName varchar(50),
SeqNum int,
SeqDate datetime
)

CREATE PROCEDURE [AccountCode_Update]
(
@Batch_Num int,
@Batch_Date datetime,
@Account_Code varchar(2)
)

AS
SET NOCOUNT ON
BEGIN
UPDATE AccountTable
SET SeqNum = @Batch_Num, SeqDate = @Batch_Date
WHERE AccountID = @Account_Account_Code
END

Answer

Move your conn.Open(); call before your foreach loop.

SqlConnection conn = new SqlConnection(GetConnectionString());
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;

conn.Open();

foreach (ListItem item in CheckBoxList1.Items)
{
    if(item.Selected)
    {

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "[dbo].[AccountCode_Update]";
        cmd.Parameters.AddWithValue("@Batch_Num", SqlDbType.Int).Value = i;
        cmd.Parameters.AddWithValue("@Batch_Date", SqlDbType.DateTime).Value = dt;
        cmd.Parameters.AddWithValue("@Account_Code", SqlDbType.VarChar).Value = BatchCodeList.SelectedValue;

        cmd.ExecuteNonQuery();
    }
}

conn.Close();

What's happening is that you are calling conn.Open() of a connection that is already open and it throws an error. This is why the first call works and the next ones fail.

Take a look at the MSDN documentation for the Open() method. It has some examples of what will cause exceptions.

In this case

InvalidOperationException

Cannot open a connection without specifying a data source or server. or The connection is already open.

Comments