473,320 Members | 2,000 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

error in execute reader

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();



Expand|Select|Wrap|Line Numbers
  1. private void Save(string tablename)
  2.     {
  3.         con.Open();
  4.  
  5.         string sql = string.Format("select * from {0}", tablename);
  6.  
  7.         SqlCommand cmd = this.con.CreateCommand();
  8.         cmd.CommandText = string.Format("Select * from {0}", tablename);
  9.  
  10.         SqlTransaction trans = this.con.BeginTransaction();
  11.  
  12.         SqlDataAdapter da = new SqlDataAdapter(sql, con);
  13.         new SqlCommandBuilder(da);
  14.  
  15.  
  16.  
  17.         da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
  18.         da.AcceptChangesDuringUpdate = true;
  19.  
  20.         da.Update(ds.Tables[tablename]);
  21.         trans.Commit();
  22.         con.Close();
  23.     }
Aug 17 '12 #1
2 3485
my full code which some what i tried to develop

Expand|Select|Wrap|Line Numbers
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Web.UI;
  6. using System.Web.UI.WebControls;
  7. using System.Configuration;
  8. using System.Data;
  9. using System.Data.Sql;
  10. using System.Data.SqlClient;
  11.  
  12.  
  13. public partial class Applying : System.Web.UI.Page
  14. {
  15. String connectionString = ConfigurationManager.ConnectionStrings["SQLDbconnection"].ToString();
  16. SqlConnection con;
  17. DataTable dtEmp, dtLeave, dtLeaveRemaining, dtPublicHolidays;
  18.  
  19. DataSet ds;
  20.  
  21.  
  22. public Applying()
  23. {
  24. ds = new DataSet();
  25. con = new SqlConnection(connectionString);
  26. }
  27.  
  28.  
  29.  
  30. private DataTable GetTable(string tablename)
  31. {
  32. DataTable temp = null;
  33. try
  34. {
  35. if (con != null)
  36. {
  37. con.Open();
  38. string query = string.Format("Select * from {0}", tablename);
  39. SqlDataAdapter da = new SqlDataAdapter(query, con);
  40. da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
  41. da.Fill(ds, tablename);
  42. temp = ds.Tables[tablename];
  43. }
  44. }
  45. catch
  46. { }
  47. finally
  48. {
  49. con.Close();
  50. }
  51. return temp;
  52. }
  53.  
  54.  
  55. protected void Page_Load(object sender, EventArgs e)
  56. {
  57.  
  58. }
  59.  
  60.  
  61.  
  62. public void btnOk_Click(object sender, EventArgs e)
  63. {
  64. ddlLeaveType.SelectedIndex = 0;
  65. tbStartdate_CalendarExtender.StartDate = DateTime.Today;
  66. tbEndDate_CalendarExtender.StartDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day + 1);
  67. dtEmp = this.GetTable("Login");
  68. dtLeave = this.GetTable("LeaveDetails");
  69. dtPublicHolidays = this.GetTable("PublicHolidays");
  70. dtLeaveRemaining = this.GetTable("LeaveRecord");
  71. this.UpdateLeaveRecord();
  72.  
  73.  
  74. bool EmpFound = false;
  75. int ID = Convert.ToInt32(tbID.Text);
  76. int leavesRemaining = 0;
  77. SqlCommand cmd = new SqlCommand("SELECT * FROM Login WHERE LogId='"+tbID.Text+"'",con);
  78. con.Open();
  79. SqlDataReader sdr = cmd.ExecuteReader();
  80. if (sdr.Read() == true)
  81. {
  82. lblMessage.Text = "the user is valid";
  83. EmpFound = true;
  84. DataRow rowLeaveRemaining = dtLeaveRemaining.Rows[ID];
  85. if (rowLeaveRemaining != null)
  86. {
  87. leavesRemaining = Convert.ToInt32(rowLeaveRemaining["LeavesRemaining"]);
  88.  
  89. }
  90. }
  91. if (!EmpFound)
  92. {
  93. lblMessage.Text = "invalid Employe";
  94. }
  95. else
  96. {
  97. //int numOfDaysApplied;
  98. if (leavesRemaining >= 1)
  99. {
  100. DataRow row = dtLeave.NewRow();
  101. row["ID"] = Convert.ToInt32(tbID.Text);
  102. row["LeaveType"] = ddlLeaveType.SelectedItem.ToString();
  103. row["StartDate"] = tbStartdate_CalendarExtender.ToString();
  104. row["EndDate"] = tbEndDate_CalendarExtender.ToString();
  105.  
  106. dtLeave.Rows.Add(row);
  107.  
  108. DataRow rowLeaveRemaing = dtLeaveRemaining.Rows.Find(Convert.ToInt32(tbID.Text));
  109. rowLeaveRemaing["LeavesRemaining"] = leavesRemaining ;
  110.  
  111. this.Save("LeaveRecord");
  112. this.Save("LeaveDetails");
  113. }
  114. }
  115.  
  116. }
  117.  
  118.  
  119.  
  120. private void UpdateLeaveRecord()
  121. {
  122. for (int i = 0; i < dtEmp.Rows.Count; i++)
  123. {
  124. DataRow rowEmployee = dtEmp.Rows;
  125. int ID = Convert.ToInt32(rowEmployee["LogId"]);
  126. DataRow rowLeaveRemaining = dtLeaveRemaining.Rows.Find(ID);
  127. if (rowLeaveRemaining == null)
  128. {
  129. DataRow newrow = dtLeaveRemaining.NewRow();
  130. newrow["ID"] = ID;
  131. newrow["LeavesRemaining"] = 20;
  132. dtLeaveRemaining.Rows.Add(newrow);
  133. }
  134. this.Save("LeaveRecord");
  135. }
  136.  
  137. }
  138.  
  139.  
  140. private void Save(string tablename)
  141. {
  142. con.Open();
  143.  
  144. string sql = string.Format("select * from {0}", tablename);
  145.  
  146. SqlCommand cmd = this.con.CreateCommand();
  147. cmd.CommandText = string.Format("Select * from {0}", tablename);
  148.  
  149. SqlTransaction trans = this.con.BeginTransaction();
  150.  
  151. SqlDataAdapter da = new SqlDataAdapter(sql, con);
  152. new SqlCommandBuilder(da);
  153.  
  154. da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
  155. da.AcceptChangesDuringUpdate = true;
  156.  
  157. da.Update(ds.Tables[tablename]);
  158. trans.Commit();
  159. con.Close();
  160. }
  161.  
Aug 17 '12 #2
Frinavale
9,735 Expert Mod 8TB
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):
Expand|Select|Wrap|Line Numbers
  1. private void Save(string tablename)
  2. {
  3.  
  4.  
  5.    string selectSql = string.Format("select * from {0}", tablename);
  6.  
  7.    string insertSql = string.Format("insert into {0} (col1, col2, col3) Values(@col1, @col2, @col3)",tablename);
  8.  
  9.    string updateSql = string.Format("update {0} set col1=@col1, col2=@col2, col3=@col3 where colID=@id",tablename);
  10.  
  11.    SqlCommand selectCmd = this.con.CreateCommand();
  12.    selectCmd.CommandText = string.Format(selectSql);
  13.  
  14.    SqlCommand insertCmd = this.con.CreateCommand();
  15.    insertCmd.CommandText = string.Format(insertSql);
  16.    insertCmd.Parameters.AddWithValue("@col1","data for col1");
  17.    insertCmd.Parameters.AddWithValue("@col2","data for col2");
  18.    insertCmd.Parameters.AddWithValue("@col3","data for col3");
  19.  
  20.    SqlCommand updateCmd = this.con.CreateCommand();
  21.    updateCmd.CommandText = string.Format(updateSql);  
  22.    insertCmd.Parameters.AddWithValue("@col1","data for col1");
  23.    insertCmd.Parameters.AddWithValue("@col2","data for col2");
  24.    insertCmd.Parameters.AddWithValue("@col3","data for col3");
  25.    insertCmd.Parameters.AddWithValue("@id","12345");
  26.  
  27.  
  28.    SqlDataAdapter da = new SqlDataAdapter(sql, con);
  29.  
  30.    da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
  31.    da.AcceptChangesDuringUpdate = true;
  32.  
  33.    da.SelectCommand = selectCmd; 
  34.    da.InsertCommand = insertCmd;
  35.    da.UpdateCommand = updateCmd;
  36.  
  37.  
  38.    using (theConnection == con) {
  39.     theConnection.Open();
  40.     using (transaction == theConnection.BeginTransaction()){
  41.        try {
  42.                da.Update(ds.Tables[tablename]);
  43.                transaction.Commit();
  44.        } catch (Exception ex) {
  45.                transaction.Rollback();
  46.        }
  47.     }
  48.     }  
  49. }
-Frinny
Aug 17 '12 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

4
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...
1
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...
8
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...
0
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=" +...
8
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; ...
4
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...
9
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...
3
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...
1
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 =...
4
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. ...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
0
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...
1
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)...
0
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...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.