I ran the following error message while running my Sql / Visual C # beginner program:
ViolationConcurrency: UpdateCommand has affected 0 expected 1 records.
I searched a bit for this problem and saw several threads, but this is not enough for me to solve the problem. I noticed that people talk about how often this happens when you use a primary key with auto-increment. This is the case with this program. Also there is no multithreading or anything else that I encode into this program. Therefore, I think auto-increment is perhaps the only problem.
As the saying goes, how do I get the update team to work, despite the automatic increase? I can't stand on two legs when it comes to database programming, so please be detailed if you don't mind. Also the command I use is through the SqlCommandBuilder object. I set this object for the new SqlCommandBuilder (DataAdapter), and I did nothing special with it.
Thank.
This edit is second. The first one is below.
- , . , , , , , . , , , , , . , , . , . delete, .
, ? , ? !
- . , . . , .
private void btnUpdate_Click(object sender, EventArgs e)
{
if (recordShown)
{
con.Open();
currentRow[1] = tbFirstName.Text;
currentRow[2] = tbMiddleName.Text;
currentRow[3] = tbLastName.Text;
currentRow[4] = tbSuffix.Text;
currentRow[5] = tbHomePhone.Text;
currentRow[6] = tbCellPhone.Text;
currentRow[7] = tbOtherPhone.Text;
currentRow[8] = tbStreetAddress.Text;
currentRow[9] = tbCityAndState.Text;
currentRow[10] = tbCountry.Text;
currentRow[11] = tbEmail.Text;
dAdapter.Update(dataset, "Contacts");
con.Close();
}
else
{
MessageBox.Show("Please locate/add a record first.");
}
}
:
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;
using System.Data.SqlClient;
namespace Dakota
{
public partial class Form1 : Form
{
SqlConnection con;
DataSet dataset;
SqlDataAdapter dAdapter;
DataRow currentRow;
string primaryKey;
SqlCommandBuilder cmdBuilder;
bool recordShown = false;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
dataset = new DataSet();
con = new SqlConnection();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;" +
"AttachDbFilename=C:\\Users\\Sterling\\Documents\\Contacts.mdf;" +
"Integrated Security=True;Connect Timeout=30;User Instance=True";
con.Open();
string getData = "SELECT * FROM tblContacts";
dAdapter = new SqlDataAdapter(getData, con);
dAdapter.Fill(dataset, "Contacts");
cmdBuilder = new SqlCommandBuilder(dAdapter);
cmdBuilder.ConflictOption = ConflictOption.OverwriteChanges;
con.Close();
}
private void clearTextBoxes()
{
tbFirstName.Clear();
tbMiddleName.Clear();
tbLastName.Clear();
tbSuffix.Clear();
tbHomePhone.Clear();
tbCellPhone.Clear();
tbOtherPhone.Clear();
tbStreetAddress.Clear();
tbCityAndState.Clear();
tbCountry.Clear();
tbEmail.Clear();
}
private void fillTextBoxes(int row)
{
DataRow dr = dataset.Tables["Contacts"].Rows[row];
tbFirstName.Text = dr.ItemArray.GetValue(1).ToString();
tbMiddleName.Text = dr.ItemArray.GetValue(2).ToString();
tbLastName.Text = dr.ItemArray.GetValue(3).ToString();
tbSuffix.Text = dr.ItemArray.GetValue(4).ToString();
tbHomePhone.Text = dr.ItemArray.GetValue(5).ToString();
tbCellPhone.Text = dr.ItemArray.GetValue(6).ToString();
tbOtherPhone.Text = dr.ItemArray.GetValue(7).ToString();
tbStreetAddress.Text = dr.ItemArray.GetValue(8).ToString();
tbCityAndState.Text = dr.ItemArray.GetValue(9).ToString();
tbCountry.Text = dr.ItemArray.GetValue(10).ToString();
tbEmail.Text = dr.ItemArray.GetValue(11).ToString();
}
private void fillTextBoxes(DataRow dr)
{
tbFirstName.Text = dr.ItemArray.GetValue(1).ToString();
tbMiddleName.Text = dr.ItemArray.GetValue(2).ToString();
tbLastName.Text = dr.ItemArray.GetValue(3).ToString();
tbSuffix.Text = dr.ItemArray.GetValue(4).ToString();
tbHomePhone.Text = dr.ItemArray.GetValue(5).ToString();
tbCellPhone.Text = dr.ItemArray.GetValue(6).ToString();
tbOtherPhone.Text = dr.ItemArray.GetValue(7).ToString();
tbStreetAddress.Text = dr.ItemArray.GetValue(8).ToString();
tbCityAndState.Text = dr.ItemArray.GetValue(9).ToString();
tbCountry.Text = dr.ItemArray.GetValue(10).ToString();
tbEmail.Text = dr.ItemArray.GetValue(11).ToString();
}
private void btnSearch_Click(object sender, EventArgs e)
{
string searchFor = tbSearchFor.Text;
string column;
if (rbFirstName.Checked)
{
column = "firstName";
}
else
{
column = "lastName";
}
DataRow[] rows = dataset.Tables["Contacts"].Select(column + "='" + searchFor + "'");
int number = rows.Length;
if (number == 0)
{
MessageBox.Show("No such records were found.");
}
else if (number > 1)
{
string[] strings = new string[rows.Length];
for (int i = 0; i < strings.Length; i++)
{
bool hasFirst = false;
bool hasMiddle = false;
strings[i] = "";
if (rows[i].ItemArray.GetValue(1).ToString() != "")
{
hasFirst = true;
strings[i] += rows[i].ItemArray.GetValue(1).ToString();
}
if (rows[i].ItemArray.GetValue(2).ToString() != "")
{
hasMiddle = true;
if (hasFirst)
{
strings[i] += " ";
}
strings[i] += rows[i].ItemArray.GetValue(2).ToString();
}
if (rows[i].ItemArray.GetValue(3).ToString() != "")
{
if ((hasFirst && !hasMiddle) || (hasMiddle))
{
strings[i] += " ";
}
strings[i] += rows[i].ItemArray.GetValue(3).ToString();
}
if (rows[i].ItemArray.GetValue(4).ToString() != "")
{
strings[i] += " " + rows[i].ItemArray.GetValue(4).ToString();
}
}
Form2 form2 = new Form2(strings);
if (form2.ShowDialog(this) == DialogResult.OK)
{
primaryKey = rows[form2.choice].ItemArray.GetValue(0).ToString();
fillTextBoxes(rows[form2.choice]);
currentRow = rows[form2.choice];
recordShown = true;
}
}
else
{
primaryKey = rows[0].ItemArray.GetValue(0).ToString();
currentRow = rows[0];
fillTextBoxes(rows[0]);
recordShown = true;
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
con.Open();
DataRow row = dataset.Tables["Contacts"].NewRow();
row[1] = tbFirstName.Text;
row[2] = tbMiddleName.Text;
row[3] = tbLastName.Text;
row[4] = tbSuffix.Text;
row[5] = tbHomePhone.Text;
row[6] = tbCellPhone.Text;
row[7] = tbOtherPhone.Text;
row[8] = tbStreetAddress.Text;
row[9] = tbCityAndState.Text;
row[10] = tbCountry.Text;
row[11] = tbEmail.Text;
currentRow = row;
dataset.Tables["Contacts"].Rows.Add(row);
dAdapter.Update(dataset, "Contacts");
recordShown = true;
con.Close();
}
private void btnUpdate_Click(object sender, EventArgs e)
{
if (recordShown)
{
con.Open();
currentRow[1] = tbFirstName.Text;
currentRow[2] = tbMiddleName.Text;
currentRow[3] = tbLastName.Text;
currentRow[4] = tbSuffix.Text;
currentRow[5] = tbHomePhone.Text;
currentRow[6] = tbCellPhone.Text;
currentRow[7] = tbOtherPhone.Text;
currentRow[8] = tbStreetAddress.Text;
currentRow[9] = tbCityAndState.Text;
currentRow[10] = tbCountry.Text;
currentRow[11] = tbEmail.Text;
dAdapter.Update(dataset, "Contacts");
con.Close();
}
else
{
MessageBox.Show("Please locate/add a record first.");
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
con.Open();
currentRow.Delete();
dAdapter.Update(dataset, "Contacts");
clearTextBoxes();
recordShown = false;
con.Close();
}
}
}
!