I am writing a code in C# to read, add, delete and update a sql database row by row. Whenever I try to delete an entry, i get a "concurrency violation error: the DeleteCommand affected 0 of the expected 1 records.". Please help me to solve that error and also add code lines I can use to view the whole database like it is in Sql or Access.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace SQLA
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        System.Data.SqlClient.SqlConnection con;
        DataSet ds1;
        System.Data.SqlClient.SqlDataAdapter da;
        int MaxRows = 0;
        int inc = 0;

        //Loading Database
        private void Form1_Load(object sender, EventArgs e)
        {
            con = new System.Data.SqlClient.SqlConnection();
            ds1 = new DataSet();
            con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Workers.mdf;Integrated Security=True;User Instance=True";
            con.Open();
            string sql = "SELECT * From TableWorkers";

            da = new System.Data.SqlClient.SqlDataAdapter(sql, con);
            MessageBox.Show("Database open");
            da.Fill(ds1, "Workers");
            NavigateRecords();
            MaxRows = ds1.Tables["Workers"].Rows.Count;
            con.Close();
            MessageBox.Show("Database Close");
            con.Dispose();
        }
        //Navigating through Database
        private void NavigateRecords()
        {
            
            DataRow dRow = ds1.Tables["Workers"].Rows[inc];
            textBox1.Text = dRow.ItemArray.GetValue(1).ToString();
            textBox2.Text = dRow.ItemArray.GetValue(2).ToString();
            textBox3.Text = dRow.ItemArray.GetValue(3).ToString();
        }
        //Next Record
        private void btNext_Click(object sender, EventArgs e)
        {
            if (inc != MaxRows - 1)
            {
                inc++;
                NavigateRecords();

            }
            else
            {
                MessageBox.Show("No more records");
            }
        }
        //Previous Record
        private void btPrevious_Click(object sender, EventArgs e)
        {
            if (inc > 0)
            {
                inc--;
                NavigateRecords();

            }
            else
            {
                MessageBox.Show("No more records");
            }
        }

        //First Record
        private void btFirst_Click(object sender, EventArgs e)
        {
            if (inc != 0)
            {
                inc = 0;
                NavigateRecords();
            }
        }

        //Last Record
        private void btLast_Click(object sender, EventArgs e)
        {
            if (inc != MaxRows - 1)
            {
                inc = MaxRows - 1;
                NavigateRecords();
            }
        }

        //Adding or Clearing Record
        private void btAddNew_Click(object sender, EventArgs e)
        {
            textBox1.Clear();
            textBox2.Clear();
            textBox3.Clear();
        }

        //Saving Record
        private void btSave_Click(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlCommandBuilder cb;
            cb = new System.Data.SqlClient.SqlCommandBuilder(da);

            DataRow dRow = ds1.Tables["Workers"].NewRow();
            
            //Cheaking to see if an entry is add or textbox is empty
            if (textBox1.Text == "") //&& textBox2.Text == "" && || textBox3.Text == "")
            {
                MessageBox.Show("Required field is empty");
                MessageBox.Show("No Entry added");
            }
            else
            {
                dRow[1] = textBox1.Text;
                dRow[2] = textBox2.Text;
                dRow[3] = textBox3.Text;

                con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Workers.mdf;Integrated Security=True;User Instance=True";
                ds1.Tables["Workers"].Rows.Add(dRow);

                MaxRows++;
                inc = MaxRows - 1;

                da.Update(ds1, "Workers");
                MessageBox.Show("Entry added");
            }
            
        }

        //Updating Record
        private void btUpdate_Click(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlCommandBuilder cb;
            cb = new System.Data.SqlClient.SqlCommandBuilder(da);

            System.Data.DataRow dRow2 = ds1.Tables["Workers"].Rows[inc];
            dRow2[1] = textBox1.Text;
            dRow2[2] = textBox2.Text;
            dRow2[3] = textBox3.Text;
            con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Workers.mdf;Integrated Security=True;User Instance=True";
            da.Update(ds1, "Workers");
            MessageBox.Show("Record Updated");
        }

        private void btDelete_Click(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlCommandBuilder cb;
            cb = new System.Data.SqlClient.SqlCommandBuilder(da);

            ds1.Tables["Workers"].Rows[inc].Delete();
            MaxRows--;
            inc = 0;
            NavigateRecords();

            //con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Workers.mdf;Integrated Security=True;User Instance=True";
            da.Update(ds1, "Workers");
            MessageBox.Show("Record Deleted");

        }
    }
}

the problem is this, ADO.NET is able to work in disconnected manner, the records from database are stored in RAM then you make the changes, but not only you and also other people modify those records in their RAM. let say i deleted the row in my RAM, and you updated the record in your RAM. How the server is going to handle if we both submit our changes to server. That is when concurrency conflict occurs. there are patterns to handle it you can search google or probably if you are lucky enough sknake will see your thread and post some loooong code samples :D

No -- no long code samples for this. I hate the .NET datasets for the most parts. How they handle nulls (and concurrency) really irks me.

When you configure your table adapter one of the options under the "Advanced" button is "Use Optimistic Concurrency" you can turn that off to stop this from happening. I have attached a screenshot instead of code. <3 serkan

commented: chic +7

SAINTJAB, you must feel special. this is the first time Scott didnt bother to write long code :D

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.