Problem in duplicate data code in C#

Question

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 3-Apr-20 20:55pm

Amar chand123

Updated 3-Apr-20 21:26pm

CPallini

v2

Add a Solution

2 solutions

Solution 1

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!

Posted 3-Apr-20 21:23pm

OriginalGriff

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.

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)

RickZeeland · Accepted Answer · 2020-04-03T21:27:00

Solution 2

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.

Posted 3-Apr-20 21:27pm

RickZeeland

Comments

Amar chand123 4-Apr-20 3:49am

Thank You