There is already an open DataReader associated with this Command which must be closed first.

Posted by Sankarreddyece under Sql Server on 7/12/2012 | Points: 10 | Views : 20269 | Status : [Member] | Replies : 9
hi,

i got an error in transaction

this is my error

There is already an open DataReader associated with this Command which must be closed first.

this is my data base
Field Name Field Type
AccNumber Text
dBalance Double

my aspx code page

Collapse | Copy Code

Listing 1: Test.aspx : Transfer Funds Web Page.
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<html>
<head>
<title>Transfer Funds</title>

<script language="C#" runat="server">
protected void TransferFund(Object Sender, EventArgs e)
{
String strSQL = "Select dBalance FROM tblAccount where AccNumber='" + txtFrom.Text + "'";
double dCurrBalance;
OleDbConnection Conn = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
SOURCE=c:\\inetpub\\wwwroot\\dotnet\\test.mdb;");
Conn.Open();
OleDbDataReader oReader;
OleDbCommand cmd = new OleDbCommand(strSQL, Conn);
OleDbTransaction Trans = Conn.BeginTransaction(IsolationLevel.ReadCommitted);
cmd.Transaction = Trans;
try
{
oReader = cmd.ExecuteReader();
oReader.Read();
dCurrBalance = oReader.GetDouble(0);
oReader.Close();
if (dCurrBalance < Convert.ToDouble(txtAmt.Text))
{
throw (new Exception("Insufficient funds for transfer"));
}
strSQL = "Update tblAccount set dbalance = dBalance - " + txtAmt.Text + " where AccNumber = '"
+ txtFrom.Text + "'";
cmd.CommandText = strSQL;
cmd.ExecuteNonQuery();
strSQL = "Update tblAccount set dbalance = dBalance + " + txtAmt.Text + " where AccNumber = '"
+ txtTo.Text + "'";
cmd.CommandText = strSQL;
cmd.ExecuteNonQuery();
Trans.Commit();
lbl.Text = "true";
}
catch (Exception ex)
{
Trans.Rollback();
lbl.Text = "Error: " + ex.Message;
}
finally
{
Conn.Close();
}
}

</script>

</head>
<body>
<form id="frmTransfer" runat="server">
<asp:Label ID="lblFrom" runat="server">Enter the account number from which to transfer
funds</asp:Label>
<asp:TextBox ID="txtFrom" runat="server"></asp:TextBox><br />
<asp:Label ID="lblTo" runat="server">Enter the account number to which to transfer funds</asp:Label>
<asp:TextBox ID="txtTo" runat="server"></asp:TextBox><br />
<asp:Label ID="lblAmount" runat="server">Enter the amount to transfer</asp:Label>
<asp:TextBox ID="txtAmt" runat="server"></asp:TextBox><br />
<asp:Button ID="Button1" OnClick="TransferFund" runat="server" Text="Start Transfer">
</asp:Button><br />
<asp:Label ID="lbl" runat="server"></asp:Label>
</form>
</body>




Responses

Posted by: CGN007 on: 7/13/2012 [Member] Silver | Points: 25

Up
0
Down
Please try this

Add this code
   if (conn.State == ConnectionState.Open)

conn.Close();


before you open the connection.

Sankarreddyece, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Dotnetrajanikanth on: 7/13/2012 [Member] Starter | Points: 25
Posted by: Dotnetrajanikanth on: 7/13/2012 [Member] Starter | Points: 25

Up
0
Down
http://stackoverflow.com/questions/1605504/datareader-associated-with-this-command-which-must-be-closed-first

____________
www.flickr.com/photos/psdesigner/

Sankarreddyece, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hemanthlaxmi on: 7/13/2012 [Member] Starter | Points: 25

Up
0
Down
Hi ,

You have to set this property in your connection string
MultipleActiveResultSets=True;

If this helps you .
Please "Mark as Answer"

Sankarreddyece, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sankarreddyece on: 7/13/2012 [Member] Starter | Points: 25

Up
0
Down
hi

from where you can set that MARS="true" give me clearance

Sankarreddyece, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Patel28rajendra on: 7/14/2012 [Member] Starter | Points: 25

Up
0
Down
Hi

write code like this

while (reader.Read())

{
// your code
}
reader.Close();


R D Patel

Sankarreddyece, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Patel28rajendra on: 7/14/2012 [Member] Starter | Points: 25

Up
0
Down
Hi
Correct this code
Conn.Open();

OleDbDataReader oReader;
OleDbCommand cmd = new OleDbCommand(strSQL, Conn);


as

OleDbCommand cmd = new OleDbCommand(strSQL, Conn);
Conn.Open();
OleDbDataReader oReader;


hope it may help you

R D Patel

Sankarreddyece, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hemanthlaxmi on: 7/16/2012 [Member] Starter | Points: 25

Up
0
Down
In the Connection String you need to set the MARS property as given below
<add name="ConnectionString" connectionString="Data Source=111.111.1.125;Initial Catalog=student;Persist Security Info=True;User ID=sa;Password=tiger_123;MultipleActiveResultSets=True;Max Pool Size=60;Pooling=True;"  />


If this helps you .
Please "Mark as Answer"

Sankarreddyece, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 7/31/2012 [Member] Silver | Points: 25

Up
0
Down
Mark as answer if it helps...,That motivates...!!!

Sankarreddyece, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response