How Do I Update A Record In A Database After Searching A Data

Question

1.00/5 (1 vote)

See more:

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.
------------------------------------------------------------------------------------------------

C#

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]

Posted 21-Aug-14 4:09am

DualcoreMax

Updated 21-Aug-14 4:17am

OriginalGriff

v2

Add a Solution

Comments

[no name] 21-Aug-14 10:12am

No one is probably going to read through all of this unformatted code to try and find your problem. The answer to your problem is really simple though. Stop trying to insert a duplicate primary key.

OriginalGriff 21-Aug-14 10:19am

If you are going to post code, there are a few things you should try doing:
1) Format it, using pre tags (the "code" widget above the text box). This engages the syntax highlighter, and preserves the formatting. I've done this for you so you can see what it looks like.
2) Only show us the relevant code fragments: nobody wants to wade through your whole code trying to work out what bit you are talking about.
3) For goodness sake learn to document your code! Comments at least!
4) Stop using Visual Studio default names for controls: they make the purpose of your code incomprehensible since we cant tell what the heck you wrote on the buttons...

Help us to help you!
Use the "Improve question" widget to edit your question and provide better information.

Herman<T>.Instance 21-Aug-14 10:29am

if you use the INSERT command when updating you get these errormesssages

Enrique J. Gonzalez Fernandez 21-Aug-14 10:35am

It's impossible to read all of your code but I did a quick search on it, so I'll give you a hint. I think you're first deleting the row and them inserting the record again, that's wrong, plain and simple. There is a feature in all DBMS an programming languages called "Update", use it.
Greetings

Herman<T>.Instance 21-Aug-14 11:10am

We are lucky! He claims this is ALL his code. What if he had build more :D

[no name] 10-Sep-14 0:25am

i have founded the error.
it is in button09 . when a search is made my program is take search results to the first row of the database. then it cannot update because first row is already have another primary key. so please help me with different codes.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PhilLenoir · Accepted Answer · 2014-08-21T04:58:00

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!