error related to execute reader

Posted by Siddu1281 under ASP.NET on 8/17/2012 | Points: 10 | Views : 1545 | Status : [Member] | Replies : 2
hello iam getting an error as follows

ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.

Source Error:


Line 225: da.AcceptChangesDuringUpdate = true;
Line 226:
Line 227: da.Update(ds.Tables[tablename]);
Line 228: trans.Commit();
Line 229: con.Close();


private void Save(string tablename)
{
con.Open();

string sql = string.Format("select * from {0}", tablename);

SqlCommand cmd = this.con.CreateCommand();
cmd.CommandText = string.Format("Select * from {0}", tablename);

SqlTransaction trans = this.con.BeginTransaction();

SqlDataAdapter da = new SqlDataAdapter(sql, con);
new SqlCommandBuilder(da);



da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.AcceptChangesDuringUpdate = true;

da.Update(ds.Tables[tablename]);
trans.Commit();
con.Close();
}




Responses

Posted by: Shameer.Ali87 on: 8/17/2012 [Member] Starter | Points: 25

Up
0
Down

hi siddu,
first you tell me which operation you actually want to do i.e..

1. insert data (or)
2. retrieve data..

or you want to perform both one after the other...
first you clear me with these..

here you have two options(if you want to do above operations)
1. data reader is to read values, i.e. to view the inserted data..
2. where as update is to check operation..(either insert/delete of data..)
you make clear of these things first..

hope it gets your point..


shameer ali shaik

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

Posted by: Siddu1281 on: 8/17/2012 [Member] Starter | Points: 25

Up
0
Down
hello shammer ,
thank you for responding.i am learner in asp.net
i want to insert data i mean i want to save data into data table
i will provide full code i tried to develop



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;


public partial class Applying : System.Web.UI.Page
{
String connectionString = ConfigurationManager.ConnectionStrings["SQLDbconnection"].ToString();
SqlConnection con;
DataTable dtEmp, dtLeave, dtLeaveRemaining, dtPublicHolidays;

DataSet ds;


public Applying()
{
ds = new DataSet();
con = new SqlConnection(connectionString);
}



private DataTable GetTable(string tablename)
{
DataTable temp = null;
try
{
if (con != null)
{
con.Open();
string query = string.Format("Select * from {0}", tablename);
SqlDataAdapter da = new SqlDataAdapter(query, con);
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.Fill(ds, tablename);
temp = ds.Tables[tablename];
}
}
catch
{ }
finally
{
con.Close();
}
return temp;
}


protected void Page_Load(object sender, EventArgs e)
{

}



public void btnOk_Click(object sender, EventArgs e)
{
ddlLeaveType.SelectedIndex = 0;
tbStartdate_CalendarExtender.StartDate = DateTime.Today;
tbEndDate_CalendarExtender.StartDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day + 1);
dtEmp = this.GetTable("Login");
dtLeave = this.GetTable("LeaveDetails");
dtPublicHolidays = this.GetTable("PublicHolidays");
dtLeaveRemaining = this.GetTable("LeaveRecord");
this.UpdateLeaveRecord();


bool EmpFound = false;
int ID = Convert.ToInt32(tbID.Text);
int leavesRemaining = 0;
SqlCommand cmd = new SqlCommand("SELECT * FROM Login WHERE LogId='"+tbID.Text+"'",con);
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
if (sdr.Read() == true)
{
lblMessage.Text = "the user is valid";
EmpFound = true;
DataRow rowLeaveRemaining = dtLeaveRemaining.Rows[ID];
if (rowLeaveRemaining != null)
{
leavesRemaining = Convert.ToInt32(rowLeaveRemaining["LeavesRemaining"]);

}
}
if (!EmpFound)
{
lblMessage.Text = "invalid Employe";
}
else
{
//int numOfDaysApplied;
if (leavesRemaining >= 1)
{
DataRow row = dtLeave.NewRow();
row["ID"] = Convert.ToInt32(tbID.Text);
row["LeaveType"] = ddlLeaveType.SelectedItem.ToString();
row["StartDate"] = tbStartdate_CalendarExtender.ToString();
row["EndDate"] = tbEndDate_CalendarExtender.ToString();

dtLeave.Rows.Add(row);

DataRow rowLeaveRemaing = dtLeaveRemaining.Rows.Find(Convert.ToInt32(tbID.Text));
rowLeaveRemaing["LeavesRemaining"] = leavesRemaining ;

this.Save("LeaveRecord");
this.Save("LeaveDetails");
}
}

}



private void UpdateLeaveRecord()
{
for (int i = 0; i < dtEmp.Rows.Count; i++)
{
DataRow rowEmployee = dtEmp.Rows[i];
int ID = Convert.ToInt32(rowEmployee["LogId"]);
DataRow rowLeaveRemaining = dtLeaveRemaining.Rows.Find(ID);
if (rowLeaveRemaining == null)
{
DataRow newrow = dtLeaveRemaining.NewRow();
newrow["ID"] = ID;
newrow["LeavesRemaining"] = 20;
dtLeaveRemaining.Rows.Add(newrow);
}
this.Save("LeaveRecord");
}

}


private void Save(string tablename)
{
con.Open();

string sql = string.Format("select * from {0}", tablename);

SqlCommand cmd = this.con.CreateCommand();
cmd.CommandText = string.Format("Select * from {0}", tablename);

SqlTransaction trans = this.con.BeginTransaction();

SqlDataAdapter da = new SqlDataAdapter(sql, con);
new SqlCommandBuilder(da);

da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.AcceptChangesDuringUpdate = true;

da.Update(ds.Tables[tablename]);
trans.Commit();
con.Close();
}

}



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

Login to post response