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();
-
}
2 3485
my full code which some what 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;
-
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();
-
}
-
-
}
You need to provide an update command to your DataAdapter so that this update command can be executed when you call the DataAdapter's Update method.
You also need to provide an InsertCommand for the DataAdpater that is called when you are inserting a new record into the database through the DataAdapter's Update method.
The following code is an Example and is not meant to work (At All): -
private void Save(string tablename)
-
{
-
-
-
string selectSql = string.Format("select * from {0}", tablename);
-
-
string insertSql = string.Format("insert into {0} (col1, col2, col3) Values(@col1, @col2, @col3)",tablename);
-
-
string updateSql = string.Format("update {0} set col1=@col1, col2=@col2, col3=@col3 where colID=@id",tablename);
-
-
SqlCommand selectCmd = this.con.CreateCommand();
-
selectCmd.CommandText = string.Format(selectSql);
-
-
SqlCommand insertCmd = this.con.CreateCommand();
-
insertCmd.CommandText = string.Format(insertSql);
-
insertCmd.Parameters.AddWithValue("@col1","data for col1");
-
insertCmd.Parameters.AddWithValue("@col2","data for col2");
-
insertCmd.Parameters.AddWithValue("@col3","data for col3");
-
-
SqlCommand updateCmd = this.con.CreateCommand();
-
updateCmd.CommandText = string.Format(updateSql);
-
insertCmd.Parameters.AddWithValue("@col1","data for col1");
-
insertCmd.Parameters.AddWithValue("@col2","data for col2");
-
insertCmd.Parameters.AddWithValue("@col3","data for col3");
-
insertCmd.Parameters.AddWithValue("@id","12345");
-
-
-
SqlDataAdapter da = new SqlDataAdapter(sql, con);
-
-
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
-
da.AcceptChangesDuringUpdate = true;
-
-
da.SelectCommand = selectCmd;
-
da.InsertCommand = insertCmd;
-
da.UpdateCommand = updateCmd;
-
-
-
using (theConnection == con) {
-
theConnection.Open();
-
using (transaction == theConnection.BeginTransaction()){
-
try {
-
da.Update(ds.Tables[tablename]);
-
transaction.Commit();
-
} catch (Exception ex) {
-
transaction.Rollback();
-
}
-
}
-
}
-
}
-Frinny
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Rocket Hawk |
last post by:
Hello all,
i need to run XXXX.BAT file from ASP page use windows 2000 server
the XXXX.BAT file run as:
used exist maped drive to shared folder on ServerB and copy files to ServerA
On...
|
by: lem |
last post by:
under solaris:
# make
Making all in src
Making all in engine
make: Fatal error in reader: Makefile, line 453: Badly formed macro
assignment
Current working directory...
|
by: Seeker |
last post by:
Hello,
In using Solaris Pro Compiler to compile Pro*C code. I am getting this
error:
make: Fatal error in reader: parser_proc_online.mk, line 26: Badly
formed macro assignment
Based on other...
|
by: saminsiddiqui |
last post by:
hi
I am using .Net2.0 to talk to my db2 on AS400
My connection string is this
"Provider=IBMDA400;Persist Security Info=False;User ID="+ sUserID + ";password=" + sPassword +";Data Source=" +...
|
by: Hrvoje Voda |
last post by:
I'm using this code to get data from table 'Slike'.
I would like to get also the number of row from that table.
What must I change in code to make it work?
SqlDataReader sqlRead = null;
...
|
by: abhimanav |
last post by:
Good Morning every one,
I have an application that is going to recieve the Xml file via Http post.
the Xml file will contain one root node and around 100 child nodes.
Each child node again has 4...
|
by: Ratfish |
last post by:
I'm getting a "2014:: Commands out of sync; you can't run this command
now" error on a php page when I try to call a second stored procedure
against a MySQL db. Does anyone know why I might be...
|
by: nghivo |
last post by:
My environment
DB2 9.1.4 on Sun OS
I write a C embedded SQL to load data.
I declare host vars as:
EXEC SQL BEGIN DECLARE SECTION;
SQL TYPE IS CLOB(599999) sqlStr;
EXEC SQL END DECLARE...
|
by: sambarker123 |
last post by:
Hi all,
I am using NPGSQL to connect to postgres server.I am getting
exception at the first statement in the try block.
string not recognised as a vaild date time
NpgsqlCommand command3 =...
|
by: K Viltersten |
last post by:
Today, i run the code below and while it
works, i can't stop wondering if it can
be performed in a better way. Especially,
i'd like to know if the declaration of
the adapter is neccessary.
...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |