How to prevent Duplicate value in sql server

Question

0.00/5 (No votes)

See more:

I want to enter data in database from a textbox. The name of the table is tblMachinaryType. But I don't want to insert duplicate data. I mention my method below : But it does not result that I want
Please give me solution as early as possible.

C#

private void SaveData()
    {
        DataTable DTLocal = null;
        DataView DVLocal = null;
        DataRow DRLocal = null;
        string strSQL = "";
        try
        {
            DTLocal = new DataTable();
            DVLocal = new DataView();
            strSQL = "Select * from tblMachinaryType where MTCode = '" + txtMachinaryCode.Text.Trim() + "'";
            ConManager.DBConnection("TERMSHR");
            ConManager.OpenDataTableThroughAdapter(strSQL, out DTLocal, true);
            DTLocal.TableName = "MachinaryType";
            DVLocal.Table = DTLocal;
 
                DRLocal = DTLocal.NewRow();
                DataTable dt = new DataTable();
                string strsql = "select * from tblMachinaryType";
                ConManager.OpenDataTableThroughAdapter(strsql, out dt, true);
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        if (dt.Rows[i][1].ToString().Trim() != txtMachinaryType.Text.Trim())
                        {
                            DRLocal["MTCode"] = txtMachinaryCode.Text.Trim();
                            DRLocal["MachinaryType"] = txtMachinaryType.Text.Trim();
                        }

                    }
                }

               // DRLocal["MTCode"] = txtMachinaryCode.Text.Trim();
               // DRLocal["MachinaryType"] = txtMachinaryType.Text.Trim();
                DTLocal.Rows.Add(DRLocal);
                       
 
            ConManager.BeginTransaction();
            ConManager.SaveDataTableThroughAdapter(ref DTLocal, true);
            ConManager.CommitTransaction();
            ConManager.CloseConnection();
        }
        catch (System.Exception ex)
        {
            throw ex;
 
        }
        finally
        {
            //         

        }
    }

Posted 23-Mar-13 20:24pm

Sumon562

Updated 23-Mar-13 23:18pm

Maciej Los

v2

Add a Solution

2 solutions

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)

Prasad Khandekar · Answer 1 · 2013-03-23T21:53:00

Hello,

The simplest way would be to define a unique index on the column in which you don't want duplicate values. This way the database server it self will throw an exception if such an attempt is made.

Other way is to check with a SQL statement. eg.

SQL

SELECT COUNT(1) FROM table_name WHERE dup_col_name = value_to_insert

If this query returns a count greater than 0 then it means that value_to_insert is already present in the table and you should not proceed with insert.

Regards,

Maciej Los · Answer 2 · 2013-03-23T23:35:00

Solution 2

The simplest way to avoid inserting multiple data is to write stored procedure[^]. More about stored procedures, you'll find on MSDN[^].

You need something like this:

SQL

CREATE PROCEDURE GetMachineCode
    @mc NVARCHAR(30)
AS
BEGIN
    --Select * from tblMachinaryType where MTCode = '" + txtMachinaryCode.Text.Trim() + "'"
    DECLARE @tmpmc NVARCHAR(30)
    
    SELECT @tmpmc = MTCode
    FROM tblMachinaryType
    WHERE MTCode = @mc

    IF (ISNULL(@tmpmc)) 
    BEGIN
        INSERT INTO tblMachinaryType (MTCode) 
        VALUES(@mc)
    END

    --return the number of rows affected by INSERT INTO statement
    RETURN @@ROWCOUNT

END

Posted 23-Mar-13 23:35pm

Maciej Los

Comments

Sumon562 24-Mar-13 6:19am

Thank you for your solution. But I don't want to use store procedure. I want to use sql query
Help me pls.

Maciej Los 24-Mar-13 6:30am

Maybe you don't want to use stored procedure (SP), but using SP is simplest and safest way to avoid duplicates. Read about SQL injection[^] to understand why not to use queries in code.

Sandeep Mewara 24-Mar-13 12:27pm

Why not SP?

Sumon562 24-Mar-13 6:35am

You can show a way programatically not using procedure
thanks Mr. Maciej Los

Maciej Los 24-Mar-13 6:45am

Mohi Uddin Sumon, i can show you a way to do it programmatically, but... i don't want to show bad practice. Belive me, it's for your goodness.

Sumon562 24-Mar-13 6:52am

Thank you for your decision.
I have run your store procedure but It does not work
Please check it and let me know a solution.

Maciej Los 24-Mar-13 7:00am

It's only example. I don't know the structure of your database and model of data. You need to modify SP to your needs. Please, follow the links to read about stored procedures and to find more examples.

Sandeep Mewara 24-Mar-13 12:27pm

Good suggestion. 5!

Maciej Los 24-Mar-13 12:30pm

Thank you, Sandeep ;)