[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Concurrency violation: the DeleteCommand affected 0 records

Posted on 2005-04-30
6
Medium Priority
?
1,168 Views
Last Modified: 2012-08-13
Hello,
I can't seem to get a simple deletecommand to work.  I set up a little test scenario with two buttons.
Page_Load: set up dataset, dataAdapter etc.  
Button 1 adds a record to the dataSet and then calls the dataAdapter.Update method.
Button 2 marks the recently (from button 1) added record as deleted and then calls the dataAdpater.Update method.  However, I always get the Concurrency violation error (see title).  But there is no one using the system but me.  

Below is the code that will hopefully provide some sort of an idea where i am going wrong:

private void btnAdd_Click(object sender, System.EventArgs e)
{
      DataRow newDr = m_ds.Tables["OrderStatus"].NewRow();
      newDr["StatusID"] = "2";
      newDr["UserID"] = "1283245047";
      newDr["OrderID"] = "130";

      m_ds.Tables["OrderStatus"].Rows.Add(newDr);

      PrepareDataAdapters();

      if (m_ds.HasChanges())
      {
            if (m_ds.Tables["OrderStatus"].Rows.Count > 0)
            {
                  m_da.Update(m_ds, "OrderStatus");
                  m_ds.Tables["OrderStatus"].AcceptChanges();
      }
}


private void btnRemove_Click(object sender, System.EventArgs e)
{
      m_ds.Tables["OrderStatus"].Rows[0].Delete();
      PrepareDataAdapters();
      if (m_ds.HasChanges())
      {
            DataSet dsChanges = m_ds.GetChanges();
            if (dsChanges.Tables["OrderStatus"].Rows.Count >0)
            {
                  m_da.Update(m_ds, "OrderStatus");
            }
      }
}

private void PrepareDataAdapters()
{
      m_da = new SqlDataAdapter();
      m_da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

      //select command
      SqlCommand cmdOrderStatus = new SqlCommand();
      cmdOrderStatus.CommandText = "usp_OrderStatusSelect";
      cmdOrderStatus.CommandType = CommandType.StoredProcedure;
      cmdOrderStatus.Connection = m_cn;
      SqlParameter paramOrderID = new SqlParameter("@myOrderID", SqlDbType.Int);
      cmdOrderStatus.Parameters.Add(paramOrderID);
      paramOrderID.Direction = ParameterDirection.Input;
      paramOrderID.Value = System.Convert.ToInt32( 130 );
      m_da.SelectCommand = cmdOrderStatus;

      //insert
      m_cmdInsert = new SqlCommand();
      m_cmdInsert.CommandText = "usp_OrderStatusInsert";
      m_cmdInsert.CommandType = CommandType.StoredProcedure;
      m_cmdInsert.Connection = m_cn;
      m_cmdInsert.Parameters.Add(new SqlParameter("@myOrderID", SqlDbType.Int, 4, "OrderID"));
      m_cmdInsert.Parameters.Add(new SqlParameter("@myStateID", SqlDbType.Int, 4, "StatusID"));
      m_cmdInsert.Parameters.Add(new SqlParameter("@myUserID", SqlDbType.Int, 4, "UserID"));
      m_da.InsertCommand = m_cmdInsert;

      //DeleteCommand
      m_cmdDelete = new SqlCommand();
      m_cmdDelete.CommandText = "[usp_OrderStatusDelete]";
      m_cmdDelete.CommandType = CommandType.StoredProcedure;
      m_cmdDelete.Connection = m_cn;
      //m_cmdDelete.Parameters.Add(new SqlParameter("@myStatusID", SqlDbType.Int, 16, "OrderStatusID"));

      SqlParameter param = new SqlParameter();
      param.ParameterName = "@myStatusID";
      param.IsNullable = true;
      param.SqlDbType = SqlDbType.Decimal;
      param.Direction = ParameterDirection.Input;
      param.SourceColumn = "iD";
      param.SourceVersion = DataRowVersion.Original;
      param.Size = 4;
      m_cmdDelete.Parameters.Add(param);

      m_da.DeleteCommand = m_cmdDelete;
            
}

private void Form1_Load(object sender, System.EventArgs e)
{
      string sql = "SELECT * FROM OrderStatus WHERE OrderID = 130";
      m_cn = new SqlConnection(@"Server=xxx.xx.xxx.xx;Database=SWOL2000;User ID=yechan;Password=xxxxx;Trusted_Connection=False");
      m_da = new SqlDataAdapter(sql, m_cn);
      m_ds = new DataSet();

      m_da.Fill(m_ds, "OrderStatus");
}


also, here is another link/question (unanswered) that precipated this question.....

http://www.experts-exchange.com/Programming/Programming_Languages/C_Sharp/Q_21406046.html
0
Comment
Question by:brdrok
  • 5
6 Comments
 
LVL 23

Expert Comment

by:b1xml2
ID: 13907083
*sigh*
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 13907090
whenever you try to set the CommandType to CommandType.StoredProcedure, the first parameter is always the called @RETURN_VALUE regardless of whether the stored procedure has any parameters.

VS.NET 2002/2003
To show this, create a component class in your project. In your server explorer, if you do not already have a connection to your database, create one. Now expand that connection and go to your stored procedure of choice. Now, just simply drag and drop the procedure from the Server Explorer to the Design View of the component class.

You'll see this:

this.<command_name>.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));

0
 
LVL 23

Accepted Solution

by:
b1xml2 earned 2000 total points
ID: 13907100
private void PrepareDataAdapters()
{
      m_da = new SqlDataAdapter();
      m_da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
      
      //select command
      SqlCommand cmdOrderStatus = new SqlCommand();
      cmdOrderStatus.CommandText = "usp_OrderStatusSelect";
      cmdOrderStatus.CommandType = CommandType.StoredProcedure;
      cmdOrderStatus.Connection = m_cn;
      cmdOrderStatus..Parameters.Add(new SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", DataRowVersion.Current, null));
      SqlParameter paramOrderID = new SqlParameter("@myOrderID", SqlDbType.Int);
      cmdOrderStatus.Parameters.Add(paramOrderID);
      paramOrderID.Direction = ParameterDirection.Input;
      paramOrderID.Value = System.Convert.ToInt32( 130 );
      m_da.SelectCommand = cmdOrderStatus;
      
      //insert
      m_cmdInsert = new SqlCommand();
      m_cmdInsert.CommandText = "usp_OrderStatusInsert";
      m_cmdInsert.CommandType = CommandType.StoredProcedure;
      m_cmdInsert.Connection = m_cn;
      m_cmdInsert.Parameters.Add(new SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", DataRowVersion.Current, null));
      m_cmdInsert.Parameters.Add(new SqlParameter("@myOrderID", SqlDbType.Int, 4, "OrderID"));
      m_cmdInsert.Parameters.Add(new SqlParameter("@myStateID", SqlDbType.Int, 4, "StatusID"));
      m_cmdInsert.Parameters.Add(new SqlParameter("@myUserID", SqlDbType.Int, 4, "UserID"));
      m_da.InsertCommand = m_cmdInsert;
      
      //DeleteCommand
      m_cmdDelete = new SqlCommand();
      m_cmdDelete.CommandText = "[usp_OrderStatusDelete]";
      m_cmdDelete.CommandType = CommandType.StoredProcedure;
      m_cmdDelete.Connection = m_cn;
      m_cmdDelete.Parameters.Add(new SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", DataRowVersion.Current, null));
      //m_cmdDelete.Parameters.Add(new SqlParameter("@myStatusID", SqlDbType.Int, 16, "OrderStatusID"));
      
      SqlParameter param = new SqlParameter();
      param.ParameterName = "@myStatusID";
      param.IsNullable = true;
      param.SqlDbType = SqlDbType.Decimal;
      param.Direction = ParameterDirection.Input;
      param.SourceColumn = "iD";
      param.SourceVersion = DataRowVersion.Original;
      param.Size = 4;
      m_cmdDelete.Parameters.Add(param);
      
      m_da.DeleteCommand = m_cmdDelete;
         
}
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 23

Expert Comment

by:b1xml2
ID: 13907103
so, I would strongly recommend that you create SqlCommand objects through Component Classes. One should not be having the added burden of ensuring the order of parameters is correct.
0
 
LVL 7

Author Comment

by:brdrok
ID: 13909749
thanks....
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 13909799
welcome =)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Integration Management Part 2
Suggested Courses
Course of the Month20 days, 6 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question