Click here to Skip to main content
15,882,837 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I use this Code For check Duplicate Data Before Save in Database But this Code Note Work


my Save Button Code

C#
private void btnsaveCustomerEntry_Click(object sender, EventArgs e)
        {
            if (IsValidated())
            {
                try
                {
                    SaveRecord(); Calculat(); SaveRecord1(); SaveRecord2(); LoanDetails();
                    reset(); tabControl1.SelectTab(tabPage1); txtCustomerName.Focus(); Autonumber1(); AutoDate1();
                    label27.Text = ""; dataGridView1.Rows.Clear();
                }
                catch (ApplicationException ex)
                {
                    MessageBox.Show("Error:" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }



when I Click Save Button Error Show

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
Additional information: The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

What I have tried:

C#
private bool IsValidated()
        {
            OleDbConnection con = new OleDbConnection(ConfigurationManager.ConnectionStrings["FincorpData"].ConnectionString);
            string sql1 = "select count (LoanNumber) from customer Where LoanNumber = '" + textBoxLoanNumber + "' ";
            OleDbCommand cmd = new OleDbCommand(sql1, con);
            con.Open();
            int temp = Convert.ToInt32(cmd.ExecuteScalar());
            if (temp > 0)
            {
                MessageBox.Show("Loan Number Already Exists");
                return false;
            }

           if (txtCustomerName.Text.Trim() == string.Empty)
           {
               MessageBox.Show("Customer Name is Requied", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
               tabControl1.SelectTab(tabPage1); txtCustomerName.Focus();
               return false;
           }

           if (txtFatherName.Text.Trim() == string.Empty)
           {
               MessageBox.Show("Father's Name is Requied", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
               tabControl1.SelectTab(tabPage1); txtFatherName.Focus();
               return false;
           }
 
            return true;
        }
Posted
Updated 3-Apr-20 21:26pm
v2

First off, change your style.
Stuff like this is hard to read:
SaveRecord(); Calculat(); SaveRecord1(); SaveRecord2(); LoanDetails();
                    reset(); tabControl1.SelectTab(tabPage1); txtCustomerName.Focus(); Autonumber1(); AutoDate1();
Separate each instruction on it's own line, and it becoems much more obvious:
SaveRecord(); 
Calculat(); 
SaveRecord1(); 
SaveRecord2(); 
LoanDetails();
                    
reset(); 
tabControl1.SelectTab(tabPage1); 
txtCustomerName.Focus(); 
Autonumber1(); 
AutoDate1();
And then change your method names to something more self documenting than "SaveRecord", "SaveRecord1", "SaveRecord2", and so on - it is far too easy to make mistakes when you just add a number on the end!

Then, the big one: Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Now we start to get to the problem you have noticed - and we can't help. The code you show is doesn't even call the "Is Validated" method so that possibly a reason why your code fais with a duplicate key error - but we don't have any access to you data or user input so we can't tell wheat is going on at all.

So, it's going to be up to you.
Fortunately, you have a tool available to you which will help you find out what is going on: the debugger. If you don't know how to use it then a quick Google for "Visual Studio debugger" should give you the info you need.

Put a breakpoint on the first line in the function, and run your code through the debugger. Then look at your code, and at your data and work out what should happen manually. Then single step each line checking that what you expected to happen is exactly what did. When it isn't, that's when you have a problem, and you can back-track (or run it again and look more closely) to find out why.

Sorry, but we can't do that for you - time for you to learn a new (and very, very useful) skill: debugging!
 
Share this answer
 
Comments
Amar chand123 4-Apr-20 3:58am    
Reason For Using SaveRecord, SaveRecord1, because I Have Three Table in DataBase and i don't Know any Code For Save Data in all Three Table by a Single Code For Save Data So I use Three Code, Because i Started Learning Coding from last 2 months.
Sir can you Any Code Save data in Three Different Table by one code
OriginalGriff 4-Apr-20 4:46am    
Oh boy. This may take some time ... I suspect there is a lot here that you have got wrong, and it may mean big changes to your DB and application. But they are probably needed.

Lets start small: if you are updating three tables with related information then you absolutely need to use a Transaction - if you don't and one fails a Transaction allows you to cancel (or "Rollback" as it's known) all the changes so far, so your DB doesn't get stuffed with "orphaned" records that you don't even know are incomplete. There are two ways to do Transactions: C# provides classes for it, and SQL has commands. Have a look and read up on them to start with and decide which route you want to take.

Having said that, throw your "three methods" idea away: this is related data, so you are probably going to need information form one INSERT to be part of another - and that needs to be clear and obvious, so splitting this into multiple methods just adds to the complexity and increases the changes of problems. There are two ways - again - to do this: three INSERT operations in C# or write a Stored Procedure in your database and pass it all the info it needs. Which is best depends hugely on the data and your tables neither of which is accessible to us, so you probably need to do some reading here as well.

Your "AutoNumber" and "AutoDate" methods worry me as well as they imply you are generating DB row IDs yourself, and generally speaking that's a very bad idea - particularly if there is any chance of multiple users ever accessing the same DB. You should be using IDENTITY fields, and letting the DB sort out numbering for you - it's much, much better at it than you are!

Have a good read on those subjects, think about your DB design, and then come back with some ideas.
don;t use three methods to do teh updates.
I think the problem is that you use
textBoxLoanNumber

it should be:
textBoxLoanNumber.Text

If your application is for public use you should not concatenate values this way, use parameterization instead to avoid SQL injection attacks.
 
Share this answer
 
Comments
Amar chand123 4-Apr-20 3:49am    
Thank You

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900