如何在搜索数据后更新数据库中的记录 [英] How Do I Update A Record In A Database After Searching A Data
本文介绍了如何在搜索数据后更新数据库中的记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我已经制作了一个数据库管理软件，它有搜索选项。
当我进行搜索时，如果它存在，记录将显示在文本框中。
如果我想要更改数据（主键（Index_No）除外）它将收到错误消息违反PRIMARY KEY约束'PK_Table1'。无法在对象'dbo.Table1'中插入重复键。
但更新时更新很好，无需搜索记录即可更新。
这是我的完整代码。
请帮助我。
------------------------------ -------------------------------------------------- ----------------
I have make a database management software and it has search option.
When I make a search if it is present records will display in text boxes.
If I want to change data (except primary key(Index_No)) it will get an error message "Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key in object 'dbo.Table1'."
But Update work nicely when updated without searching a record.
here is my complete code.
Please Help ME.
------------------------------------------------------------------------------------------------
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 Prefect_Reg
{
public partial class Form2 : Form
{
public Form2()
{
InitializeComponent();
}
System.Data.SqlClient.SqlConnection con;
DataSet ds1;
System.Data.SqlClient.SqlDataAdapter da;
int MaxRows = 0;
int inc = 0;
private void Form2_Load(object sender, EventArgs e)
{
button11.Visible = false;
con = new System.Data.SqlClient.SqlConnection();
ds1 = new DataSet();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database1.mdf;Integrated Security=True;User Instance=True";
String sql = "SELECT * From Table1";
da = new System.Data.SqlClient.SqlDataAdapter(sql, con);
da.Fill(ds1, "Table1");
con.Open();
toolStripStatusLabel1.Text = "Status : Online";
label15.Visible = false;
NavigateRecords();
MaxRows = ds1.Tables["Table1"].Rows.Count;
textBox1.Text = ds1.Tables["Table1"].Rows.Count.ToString() + " " + "Regisrations";
}
private void NavigateRecords()
{
textBox1.Text = ds1.Tables["Table1"].Rows.Count.ToString() +" " + "Regisrations";
DataRow dRow = ds1.Tables["Table1"].Rows[inc];
txtindex.Text = dRow.ItemArray.GetValue(0).ToString();
txtname.Text = dRow.ItemArray.GetValue(1).ToString();
txtname2.Text = dRow.ItemArray.GetValue(2).ToString();
txtadd.Text = dRow.ItemArray.GetValue(3).ToString();
txtdb.Text = dRow.ItemArray.GetValue(4).ToString();
txtsx.Text = dRow.ItemArray.GetValue(5).ToString();
txts7.Text = dRow.ItemArray.GetValue(12).ToString();
txts8.Text = dRow.ItemArray.GetValue(13).ToString();
txts9.Text = dRow.ItemArray.GetValue(14).ToString();
txts10.Text = dRow.ItemArray.GetValue(15).ToString();
txtr1.Text = dRow.ItemArray.GetValue(16).ToString();
txtr2.Text = dRow.ItemArray.GetValue(17).ToString();
txtr3.Text = dRow.ItemArray.GetValue(18).ToString();
txtr4.Text = dRow.ItemArray.GetValue(19).ToString();
txtr5.Text = dRow.ItemArray.GetValue(20).ToString();
txtr6.Text = dRow.ItemArray.GetValue(21).ToString();
txtr7.Text = dRow.ItemArray.GetValue(22).ToString();
txtr8.Text = dRow.ItemArray.GetValue(23).ToString();
txtr9.Text = dRow.ItemArray.GetValue(24).ToString();
txtr10.Text = dRow.ItemArray.GetValue(25).ToString();
txtst.Text = dRow.ItemArray.GetValue(26).ToString();
txta1.Text = dRow.ItemArray.GetValue(27).ToString();
txta2.Text = dRow.ItemArray.GetValue(28).ToString();
txta3.Text = dRow.ItemArray.GetValue(29).ToString();
txtar1.Text = dRow.ItemArray.GetValue(30).ToString();
txtar2.Text = dRow.ItemArray.GetValue(31).ToString();
txtar3.Text = dRow.ItemArray.GetValue(32).ToString();
txtcn.Text = dRow.ItemArray.GetValue(33).ToString();
txtrank.Text = dRow.ItemArray.GetValue(34).ToString();
txtsts.Text = dRow.ItemArray.GetValue(35).ToString();
switch (txtsts.Text)
{
case "Inactive":
Color c3 = Color.FromKnownColor(KnownColor.Cyan);
txtindex.BackColor = c3;
txtname.BackColor = c3;
txtname2.BackColor = c3;
txtadd.BackColor = c3;
txtdb.BackColor = c3;
txtsx.BackColor = c3;
txtcn.BackColor = c3;
txtrank.BackColor = c3;
txtsts.BackColor = c3;
break;
case "Active":
txtindex.BackColor = Color.FromKnownColor(KnownColor.Window);
txtname.BackColor = Color.FromKnownColor(KnownColor.Window);
txtname2.BackColor = Color.FromKnownColor(KnownColor.Window);
txtadd.BackColor = Color.FromKnownColor(KnownColor.Window);
txtdb.BackColor = Color.FromKnownColor(KnownColor.Window);
txtsx.BackColor = Color.FromKnownColor(KnownColor.Window);
txtcn.BackColor = Color.FromKnownColor(KnownColor.Window);
txtrank.BackColor = Color.FromKnownColor(KnownColor.Window);
txtsts.BackColor = Color.FromKnownColor(KnownColor.Window);
break;
default:
txtindex.BackColor = Color.FromKnownColor(KnownColor.Window);
txtname.BackColor = Color.FromKnownColor(KnownColor.Window);
txtname2.BackColor = Color.FromKnownColor(KnownColor.Window);
txtadd.BackColor = Color.FromKnownColor(KnownColor.Window);
txtdb.BackColor = Color.FromKnownColor(KnownColor.Window);
txtsx.BackColor = Color.FromKnownColor(KnownColor.Window);
txtcn.BackColor = Color.FromKnownColor(KnownColor.Window);
txtrank.BackColor = Color.FromKnownColor(KnownColor.Window);
txtsts.BackColor = Color.FromKnownColor(KnownColor.Window);
break;
}
}
private void button1_Click(object sender, EventArgs e)
{
if (inc > 0)
{
inc--;
NavigateRecords();
}
else
{
MessageBox.Show("You Are At First Student");
}
radioButton1.Checked = false;
radioButton2.Checked = false;
radioButton3.Checked = false;
radioButton4.Checked = false;
}
private void button2_Click(object sender, EventArgs e)
{
if (inc != MaxRows - 1)
{
inc++;
NavigateRecords();
}
else
{
MessageBox.Show("No More Registrations");
}
radioButton1.Checked = false;
radioButton2.Checked = false;
radioButton3.Checked = false;
radioButton4.Checked = false;
}
private void button3_Click(object sender, EventArgs e)
{
if (inc != 0)
{
inc = 0;
NavigateRecords();
}
radioButton1.Checked = false;
radioButton2.Checked = false;
radioButton3.Checked = false;
radioButton4.Checked = false;
}
private void button4_Click(object sender, EventArgs e)
{
if (inc != MaxRows - 1)
{
inc = MaxRows - 1;
NavigateRecords();
}
radioButton1.Checked = false;
radioButton2.Checked = false;
radioButton3.Checked = false;
radioButton4.Checked = false;
}
private void button5_Click(object sender, EventArgs e)
{
txtindex.BackColor = Color.FromKnownColor(KnownColor.Window);
txtname.BackColor = Color.FromKnownColor(KnownColor.Window);
txtname2.BackColor = Color.FromKnownColor(KnownColor.Window);
txtadd.BackColor = Color.FromKnownColor(KnownColor.Window);
txtdb.BackColor = Color.FromKnownColor(KnownColor.Window);
txtsx.BackColor = Color.FromKnownColor(KnownColor.Window);
txtcn.BackColor = Color.FromKnownColor(KnownColor.Window);
txtrank.BackColor = Color.FromKnownColor(KnownColor.Window);
txtsts.BackColor = Color.FromKnownColor(KnownColor.Window);
label15.Visible = true;
toolStripStatusLabel2.Text="Warning; You should press 'Register' Button to save changes";
txtindex.Clear();
txtname.Clear();
txtname2.Clear();
txtadd.Clear();
txtdb.Clear();
txtsx.Clear();
txts7.Clear();
txts8.Clear();
txts9.Clear();
txts10.Clear();
txtr1.Clear();
txtr2.Clear();
txtr3.Clear();
txtr4.Clear();
txtr5.Clear();
txtr6.Clear();
txtr7.Clear();
txtr8.Clear();
txtr9.Clear();
txtr10.Clear();
txtst.Clear();
txta1.Clear();
txta2.Clear();
txta3.Clear();
txtar1.Clear();
txtar2.Clear();
txtar3.Clear();
txtcn.Clear();
txtrank.Clear();
txtsts.Clear();
radioButton1.Checked = false;
radioButton2.Checked = false;
radioButton3.Checked = false;
radioButton4.Checked = false;
}
private void button6_Click(object sender, EventArgs e)
{
toolStripStatusLabel2.Text = "";
System.Data.SqlClient.SqlCommandBuilder cb;
cb = new System.Data.SqlClient.SqlCommandBuilder(da);
DataRow dRow = ds1.Tables["Table1"].NewRow();
dRow[0] = txtindex.Text;
dRow[1] = txtname.Text;
dRow[2] = txtname2.Text;
dRow[3] = txtadd.Text;
dRow[4] = txtdb.Text;
dRow[5] = txtsx.Text;
dRow[12] = txts7.Text;
dRow[13] = txts8.Text;
dRow[14] = txts9.Text;
dRow[15] = txts10.Text;
dRow[16] = txtr1.Text;
dRow[17] = txtr2.Text;
dRow[18] = txtr3.Text;
dRow[19] = txtr4.Text;
dRow[20] = txtr5.Text;
dRow[21] = txtr6.Text;
dRow[22] = txtr7.Text;
dRow[23] = txtr8.Text;
dRow[24] = txtr9.Text;
dRow[25] = txtr10.Text;
dRow[26] = txtst.Text;
dRow[27] = txta1.Text;
dRow[28] = txta2.Text;
dRow[29] = txta3.Text;
dRow[30] = txtar1.Text;
dRow[31] = txtar2.Text;
dRow[32] = txtar3.Text;
dRow[33] = txtcn.Text;
dRow[34] = txtrank.Text;
dRow[34] = txtsts.Text;
ds1.Tables["Table1"].Rows.Add(dRow);
MaxRows = MaxRows + 1;
inc = MaxRows - 1;
da.Update(ds1, "Table1");
MessageBox.Show("Registered", "Registration");
radioButton1.Checked = false;
radioButton2.Checked = false;
radioButton3.Checked = false;
radioButton4.Checked = false;
textBox1.Text = ds1.Tables["Table1"].Rows.Count.ToString() + " " + "Regisrations";
}
private void button7_Click(object sender, EventArgs e)
{
System.Data.SqlClient.SqlCommandBuilder cb;
cb = new System.Data.SqlClient.SqlCommandBuilder(da);
System.Data.DataRow dRow2 = ds1.Tables["Table1"].Rows[inc];
dRow2[0] = txtindex.Text;
dRow2[1] = txtname.Text;
dRow2[2] = txtname2.Text;
dRow2[3] = txtadd.Text;
dRow2[4] = txtdb.Text;
dRow2[5] = txtsx.Text;
dRow2[12] = txts7.Text;
dRow2[13] = txts8.Text;
dRow2[14] = txts9.Text;
dRow2[15] = txts10.Text;
dRow2[16] = txtr1.Text;
dRow2[17] = txtr2.Text;
dRow2[18] = txtr3.Text;
dRow2[19] = txtr4.Text;
dRow2[20] = txtr5.Text;
dRow2[21] = txtr6.Text;
dRow2[22] = txtr7.Text;
dRow2[23] = txtr8.Text;
dRow2[24] = txtr9.Text;
dRow2[25] = txtr10.Text;
dRow2[26] = txtst.Text;
dRow2[27] = txta1.Text;
dRow2[28] = txta2.Text;
dRow2[29] = txta3.Text;
dRow2[30] = txtar1.Text;
dRow2[31] = txtar2.Text;
dRow2[32] = txtar3.Text;
dRow2[33] = txtcn.Text;
dRow2[34] = txtrank.Text;
dRow2[35] = txtsts.Text;
NavigateRecords();
da.Update(ds1, "Table1");
MessageBox.Show("Data Updated");
radioButton1.Checked = false;
radioButton2.Checked = false;
radioButton3.Checked = false;
radioButton4.Checked = false;
textBox1.Text = ds1.Tables["Table1"].Rows.Count.ToString() + " " + "Regisrations";
}
}
private void button8_Click(object sender, EventArgs e)
{
System.Data.SqlClient.SqlCommandBuilder cb;
cb = new System.Data.SqlClient.SqlCommandBuilder(da);
ds1.Tables["Table1"].Rows[inc].Delete();
MaxRows--;
inc = 0;
NavigateRecords();
da.Update(ds1, "Table1");
MessageBox.Show("Entry Deleted");
radioButton1.Checked = false;
radioButton2.Checked = false;
radioButton3.Checked = false;
radioButton4.Checked = false;
textBox1.Text = ds1.Tables["Table1"].Rows.Count.ToString() + " " + "Regisrations";
}
private void button10_Click(object sender, EventArgs e)
{
txtfind.Clear();
}
private void button9_Click(object sender, EventArgs e)
{
String searchFor;
int results = 0;
DataRow[] returnedRows;
searchFor = txtindex.Text;
returnedRows = ds1.Tables["Table1"].Select("Index_No='" + searchFor + "'");
results = returnedRows.Length;
if (results > 0)
{
DataRow dr1;
dr1 = returnedRows[0];
txtindex.Text = dr1["Index_No"].ToString();
txtname.Text = dr1["Name"].ToString();
txtname2.Text = dr1["FName"].ToString();
txtadd.Text = dr1["Add"].ToString();
txtdb.Text = dr1["DOB"].ToString();
txtsx.Text = dr1["Sex"].ToString();
txts7.Text = dr1["Sub7"].ToString();
txts8.Text = dr1["Sub8"].ToString();
txts9.Text = dr1["Sub9"].ToString();
txts10.Text = dr1["Sub10"].ToString();
txtr1.Text = dr1["R1"].ToString();
txtr2.Text = dr1["R2"].ToString();
txtr3.Text = dr1["R3"].ToString();
txtr4.Text = dr1["R4"].ToString();
txtr5.Text = dr1["R5"].ToString();
txtr6.Text = dr1["R6"].ToString();
txtr7.Text = dr1["R7"].ToString();
txtr8.Text = dr1["R8"].ToString();
txtr9.Text = dr1["R9"].ToString();
txtr10.Text = dr1["R10"].ToString();
txtst.Text = dr1["Str"].ToString();
txta1.Text = dr1["Al1"].ToString();
txta2.Text = dr1["Al2"].ToString();
txta3.Text = dr1["Al3"].ToString();
txtar1.Text = dr1["Ar1"].ToString();
txtar2.Text = dr1["Ar2"].ToString();
txtar3.Text = dr1["Ar3"].ToString();
txtcn.Text = dr1["Cn"].ToString();
txtrank.Text = dr1["Rank"].ToString();
txtsts.Text = dr1["Status"].ToString();
radioButton1.Checked = false;
radioButton2.Checked = false;
radioButton3.Checked = false;
radioButton4.Checked = false;
txtfind.Clear();
textBox1.Text = ds1.Tables["Table1"].Rows.Count.ToString() + " " + "Regisrations";
}
else
{
MessageBox.Show("No Such Record");
}
switch (txtsts.Text)
{
case "Inactive":
Color c3 = Color.FromKnownColor(KnownColor.Cyan);
txtindex.BackColor = c3;
txtname.BackColor = c3;
txtname2.BackColor = c3;
txtadd.BackColor = c3;
txtdb.BackColor = c3;
txtsx.BackColor = c3;
txtcn.BackColor = c3;
txtrank.BackColor = c3;
txtsts.BackColor = c3;
break;
case "Active":
txtindex.BackColor = Color.FromKnownColor(KnownColor.Window);
txtname.BackColor = Color.FromKnownColor(KnownColor.Window);
txtname2.BackColor = Color.FromKnownColor(KnownColor.Window);
txtadd.BackColor = Color.FromKnownColor(KnownColor.Window);
txtdb.BackColor = Color.FromKnownColor(KnownColor.Window);
txtsx.BackColor = Color.FromKnownColor(KnownColor.Window);
txtcn.BackColor = Color.FromKnownColor(KnownColor.Window);
txtrank.BackColor = Color.FromKnownColor(KnownColor.Window);
txtsts.BackColor = Color.FromKnownColor(KnownColor.Window);
break;
default:
txtindex.BackColor = Color.FromKnownColor(KnownColor.Window);
txtname.BackColor = Color.FromKnownColor(KnownColor.Window);
txtname2.BackColor = Color.FromKnownColor(KnownColor.Window);
txtadd.BackColor = Color.FromKnownColor(KnownColor.Window);
txtdb.BackColor = Color.FromKnownColor(KnownColor.Window);
txtsx.BackColor = Color.FromKnownColor(KnownColor.Window);
txtcn.BackColor = Color.FromKnownColor(KnownColor.Window);
txtrank.BackColor = Color.FromKnownColor(KnownColor.Window);
txtsts.BackColor = Color.FromKnownColor(KnownColor.Window);
break;
}
}
private void radioButton1_CheckedChanged(object sender, EventArgs e)
{
txtsts.Text = "Active";
}
private void radioButton2_CheckedChanged(object sender, EventArgs e)
{
txtsts.Text = "Inactive";
}
private void button11_Click(object sender, EventArgs e)
{
Form4 form4 = new Form4();
form4.Show();
}
private void radioButton3_CheckedChanged(object sender, EventArgs e)
{
txtsx.Text = "Male";
}
private void radioButton4_CheckedChanged(object sender, EventArgs e)
{
txtsx.Text = "Female";
}
}
}
[edit]Code block added - OriginalGriff[/edit]
[edit]Code block added - OriginalGriff[/edit]
推荐答案
You are always inserting a new record in the database table even when an existing record has been edited. The row is being added in button6_Click.
There are several ways to solve this:
1) keep track of new records and only insert these, otherwise use an update
2) If your Index_No or one of your other fields is an Identity field (Autonumber), when it is balnk/null do an insert else do an update.
3) delegate the decision to a stored procedure that looks something like this
CREATE PROCEDURE prc_AddOrInsert
@Index_No int,
{... other params here}
AS
BEGIN
SET NOCOUNT ON;
DECLARE @MyCount int
SELECT @MyCount=COUNT(*) FROM Table1
WHERE Index_No = @Index_No
IF (@MyCount=1 )
BEGIN
UPDATE Table1 SET Field1=@param1, {.... other params here) WHERE Index_No = @Index_No
END
ELSE
BEGIN
INSERT INTO Table1 (Field1, {... . field list here omitting Identity fields}
VALUES (@param1, {.... matching param list})
END
-- You might want to return your new Index_No here
-- If it’s an identity field then RETURN @@IDENTITY
END
You can find plenty of examples of using stored procedures on-line.
Please note Griff’s and Wes’ comments about commenting and naming using default names for tables, fields variables of form objects makes code extremely hard to read and maintain. Even your own code 6 months later will give you a headache.
Finally all I/O should be protected by Try/Catch blocks. None of your database access uses these and that is sure to end in tears!
You are always inserting a new record in the database table even when an existing record has been edited. The row is being added in button6_Click.
There are several ways to solve this:
1) keep track of new records and only insert these, otherwise use an update
2) If your Index_No or one of your other fields is an Identity field (Autonumber), when it is balnk/null do an insert else do an update.
3) delegate the decision to a stored procedure that looks something like this
CREATE PROCEDURE prc_AddOrInsert
@Index_No int,
{... other params here}
AS
BEGIN
SET NOCOUNT ON;
DECLARE @MyCount int
SELECT @MyCount=COUNT(*) FROM Table1
WHERE Index_No = @Index_No
IF (@MyCount=1 )
BEGIN
UPDATE Table1 SET Field1=@param1, {.... other params here) WHERE Index_No = @Index_No
END
ELSE
BEGIN
INSERT INTO Table1 (Field1, {.... field list here omitting Identity fields}
VALUES (@param1, {.... matching param list})
END
-- You might want to return your new Index_No here
-- If it's an identity field then RETURN @@IDENTITY
END
You can find plenty of examples of using stored procedures on-line.
Please note Griff's and Wes' comments about commenting and naming using default names for tables, fields variables of form objects makes code extremely hard to read and maintain. Even your own code 6 months later will give you a headache.
Finally all I/O should be protected by Try/Catch blocks. None of your database access uses these and that is sure to end in tears!
这篇关于如何在搜索数据后更新数据库中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
