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]