Click here to Skip to main content
15,940,271 members
Please Sign up or sign in to vote.
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
Updated 23-Mar-13 23:18pm
v2

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
 
Share this answer
 
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.
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,
 
Share this answer
 

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