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");
}
}
}
