How to avoid insertion of same record with different primaray key

[sania khan]

// This code is not solving my problem .Data redundency still exist... lblresult.Text always give 0 :(

 int count=0 ;


                string selectSQL = "SELECT Equipment_Name,Vendor_Name FROM CatC";
                SqlConnection myConn = new SqlConnection();
                myConn.ConnectionString = "Data Source=.;Initial Catalog=ecsd1;User ID=sa;Password=786";
                myConn.Open();
                SqlCommand cmd1 = new SqlCommand(selectSQL, myConn);
                SqlDataReader reader;
                try
                {
                    myConn.Open();
                    reader = cmd1.ExecuteReader();
                    // For each item, add the author name to the displayed list box text, and store the unique ID in the Value property.
                    while (reader.Read())
                    {

                        String abc=reader["Equipment_Name"].ToString() ;
                        String fgh=reader["Vendor_Name"].ToString();
                        if (TextBox1.Text == abc && TextBox10.Text ==fgh )
                        {
                            lblStatus.Text = "***Data Already exist***";
                            count = 1;
                            return;
                        }

                    }
                    reader.Close();

                }
                catch (Exception err)
                {
                     lblresult.Text = "Error reading list of References. ";
                     lblresult.Text += err.Message;

                }
                finally
                {
                    myConn.Close();
                }




         lblresult.Text =count.ToString();


            if (count != 1)
            {
                string sqlIns = "INSERT INTO CatC (Serial_No,Equipment_Name,Vendor_Name) VALUES (@Serial_No,@Equipment_Name,@Vendor_Name)";
                SqlConnection con = new SqlConnection();

                con.ConnectionString = "Data Source=.;Initial Catalog=ecsd1;User ID=sa;Password=786";
                SqlCommand cmd = new SqlCommand(sqlIns, con);

                try
                {
                    con.Open();
                    cmd.Parameters.Add("@Serial_No", TextBox9.Text);
                    cmd.Parameters.Add("@Equipment_Name", TextBox1.Text);
                    cmd.Parameters.Add("@Vendor_Name", TextBox10.Text);
                    int ab = cmd.ExecuteNonQuery();
                    lblStatus.Text = "***Record Inserted.***";
                    if (ab == 1)
                    {
                        TextBox1.Text = "";
                        TextBox9.Text = "";

                    }

                }


                catch (Exception err)
                {
                    lblStatus.Text = "***Error in inserting  record***";
                    lblStatus.Text += err.Message;
                }
                finally { con.Close(); }
            }
        }
        else if (Labelll.Text == "D. Components And Devices")
        {
            string sqlIns = "INSERT INTO CatD (Serial_No,Equipment_Name) VALUES (@Serial_No,@Equipment_Name)";
            SqlConnection con = new SqlConnection();

            con.ConnectionString = "Data Source=.;Initial Catalog=ecsd1;User ID=sa;Password=786";
            SqlCommand cmd = new SqlCommand(sqlIns, con);

            try
            {
                con.Open();
                cmd.Parameters.Add("@Serial_No", TextBox9.Text);
                cmd.Parameters.Add("@Equipment_Name", TextBox1.Text);

                int ab = cmd.ExecuteNonQuery();
                lblStatus.Text = "***Record Inserted.***";
                if (ab == 1)
                {
                    TextBox1.Text = "";
                    TextBox9.Text = "";

                }

            }


            catch (Exception err)
            {
                lblStatus.Text = "***Error in inserting  record***";
                lblStatus.Text += err.Message;
            }
            finally { con.Close(); }

[urtrivedi]

I am not sure about how asp.net uses cookies, But in php I used cookies to solve this kind of issues.
I had problem when user presses refresh button, it was inserting same record with new id. So I used cookies to track where record inserted or not.

[hericles]

If lblResult.Text is always zero then this code is not being hit:

if (TextBox1.Text == abc && TextBox10.Text ==fgh )
{
lblStatus.Text = "***Data Already exist***";
count = 1;
return;
}

as count is never being incremented. If equipment_name and vendor_name mark a unique item why don't you check for that in the database rather than pull out all of the info and cycle through it.

SELECT COUNT(product_name) FROM table WHERE equipment_name = something AND vendor_name = something;

If the product is in there the result will be 1 (or more). If zero then do your insert.

[sania khan]

I am facing problem in getting the value of count .. I tried this code but not getting the result From COUNT statement :(

            string  selectSQL;
            int a;


            selectSQL = "SELECT COUNT(Equipment_Name) FROM table WHERE Equipment_Name=@Equipment_Name AND Bill_No =@Bill_No AND Date_Of_Purchase=@Date_Of_Purchase; ";



            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = "Data Source=.;Initial Catalog=ecsd1;User ID=sa;Password=786";
            SqlCommand cmdd = new SqlCommand(selectSQL, conn);




            try
            {
                conn.Open();
                cmdd.Parameters.AddWithValue("@Equipment_Name ", TextBox1.Text);
                cmdd.Parameters.AddWithValue("@Bill_No ", TextBox11.Text);
                cmdd.Parameters.AddWithValue("@Date_Of_Purchase ", TextBox10.Text);

                a = cmdd.ExecuteNonQuery();
                Label21.Text = a.ToString();

            }
            catch (Exception err)
            {
                lblStatus.Text = "Error deleting record ";
                 lblStatus.Text += err.Message;
            }
            finally
            {
                conn.Close();
            }





            if (a!=0)
            {
                /////////////////////////////////////////////////
                string sqlIns = "INSERT INTO CatA (Serial_No,Equipment_Name,Bill_No,Date_Of_Purchase)  VALUES (@Serial_No,@Equipment_Name,@Bill_No,@Date_Of_Purchase)";
                SqlConnection con = new SqlConnection();

                con.ConnectionString = "Data Source=.;Initial Catalog=ecsd1;User ID=sa;Password=786";
                SqlCommand cmd = new SqlCommand(sqlIns, con);

                try
                {
                    con.Open();
                    cmd.Parameters.Add("@Serial_No", TextBox9.Text);
                    cmd.Parameters.Add("@Equipment_Name", TextBox1.Text);
                    cmd.Parameters.Add("@Bill_No", TextBox11.Text);
                    cmd.Parameters.Add("@Date_Of_Purchase", TextBox10.Text);
                    int ab = cmd.ExecuteNonQuery();
                    lblStatus.Text = "***Record Inserted.***";
                    if (ab == 1)
                    {
                        TextBox1.Text = "";
                        TextBox9.Text = "";

                    }

                }


                catch (Exception err)
                {
                    lblStatus.Text = "***Error in inserting  record***";
                    lblStatus.Text += err.Message;
                }
                finally { con.Close(); }

[hericles]

For a start you copied my line of code in and didn't change where I wrote 'table' for the table name. Putting the correct table name in there will help.
Secondly, you are calling a SELECT statement with ExecuteNonQuery(), it should be with ExecuteScalar() as ExecuteNonQuery returns the number of rows affected by the statement not the rows themselves.

[sania khan]

Thanks.. my problem is solved... :)))))))