Q:

Adding Data with Same Primary Key Data in ASP.Net

I have a table name AVUKAT and it's columns (AVUKAT, HESAP(Primary KEY), MUSTERI)
All MUSTERI has a one unique HESAP (int).
Simple I have a page like this.

First dropdown is selected MUSTERI, second is AVUKAT
And i automaticly calculating HESAP (int and Primary KEY) with this code. (On the background.)
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        string strConnectionString = ConfigurationManager.ConnectionStrings["SqlServerCstr"].ConnectionString;

        SqlConnection myConnection = new SqlConnection(strConnectionString);
        myConnection.Open();
        string hesapNo = DropDownList1.SelectedItem.Value;

        string query = "select A.HESAP_NO from YAZ..MARDATA.S_TEKLIF A where A.MUS_K_ISIM = '" + hesapNo + "'";

        SqlCommand cmd = new SqlCommand(query, myConnection);

        if (DropDownList1.SelectedValue != "0" && DropDownList2.SelectedValue != "0")
        {
            Add.Enabled = true;
            Label1.Text = cmd.ExecuteScalar().ToString();
        }
        else
        {
            Add.Enabled = false;

        }
        Label1.Visible = false;
        myConnection.Close();
    }

I just calculating HESAP with this code.
And my ADD button click function is;
protected void Add_Click(object sender, EventArgs e)
    {

        try
        {
            string strConnectionString = ConfigurationManager.ConnectionStrings["SqlServerCstr"].ConnectionString;

            SqlConnection myConnection = new SqlConnection(strConnectionString);
            myConnection.Open();

            string hesap = Label1.Text;
            string musteriadi = DropDownList1.SelectedItem.Value;
            string avukat = DropDownList2.SelectedItem.Value;

            SqlCommand cmd = new SqlCommand("INSERT INTO AVUKAT VALUES (@MUSTERI, @AVUKAT, @HESAP)", myConnection);

            cmd.Parameters.AddWithValue("@HESAP", hesap);
            cmd.Parameters.AddWithValue("@MUSTERI", musteriadi);
            cmd.Parameters.AddWithValue("@AVUKAT", avukat);
            cmd.Connection = myConnection;

            SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            Response.Redirect(Request.Url.ToString());
            myConnection.Close();
        }
        catch (Exception)
        { 
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('Bu Müşteri Zaten Mevcut!')", true);

        }
    }

The reason use try catch , if anybody try to add add with same HESAP (int) value for the MUSTERI i want show an error message and don't add the table.
But when i try to add same MUSTERI (also same HESAP) adding same MUSTERI with HESAP=0 value.

How can i prevent this situation? I select HESAP column is Primary KEY, but still add same MUSTERI.

A:

There's nothing too obvious here that explains the behaviour you're seeing. The most likely problem really is that the value of Label1.Text is 0 before the insert is executed, maybe set somewhere else in the ASP.NET page lifecycle. To make sure add a line of code after hesap is initialised in Add_Click like...
Response.Write("<strong>hesap == " + hesap + "</strong>");

...and comment out the Response.Redirect so you can see the output.
There are also some improvements you can make to the code to make problems less likely to occur.
It's really important that you sanitise the input to avoid SQL injection. Hopefully you're already doing this elsewhere that's not shown in your code snippet. If you don't know what this is then there's heaps of questions about it here on SO.
Also, you're not doing a query for the purpose of retrieving any rows, so use ExecuteNonQuery. So I'd also replace this line...
SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

...with...
int numberOfRows = cmd.ExecuteNonQuery();

Then check the value of ExecuteNonQuery to ensure numberOfRows == 1 so you know something actually happened.
You should also wrap your SqlConnection and SqlCommand initialisers with using statements. This means that they will automatically be disposed even if something goes wrong. This will prevent memory issues and problems with connections being left open.
Finally, let the exception value flow through into the catch statement by changing that line to catch (Exception ex). Output the exception using ex.ToString() to see all of its details. Right now you don't know what might have gone wrong if an exception occurs.

A:

HESAP is the primary key. However, does MUSTERI also have a Unique constraint which prevents someone from entering two MUSTERI values? That would at least prevent the data from getting into the database. So something like:
Alter Table AVUKAT Add Constraint UC_AVUKAT Unique ( MUSTERI )
Is there a CHECK constraint on HESAP which requires that the value be greater than zero? So something like:
Alter Table AVUKAT Add Constraint CK_AVUKAT_HESAP Check ( HESAP > 0 )
It should be noted that MySQL will ignore Check constraints. Thus, you would need to enforce this rule in a Trigger. However, many database systems such as SQL Server, Oracle, Postgres, Informix and others will enforce check constraints.
I would make the following revisions

I would alter the query to check for whether the value exists.
I would incorporate the using statement to ensure that my objects were disposed.
I would use ExecuteNonQuery and use the number of rows returned to determine if query did not insert anything rather than implementing a global catch-all. Unless you know exactly which error you expect, you should not use Catch ( Exception ) to catch any exception no matter the type.

protected void Add_Click(object sender, EventArgs e)
{
    string strConnectionString = ConfigurationManager.ConnectionStrings["SqlServerCstr"].ConnectionString;

    using( SqlConnection myConnection = new SqlConnection(strConnectionString) )
    {
        myConnection.Open();

        string hesap = Label1.Text;
        string musteriadi = DropDownList1.SelectedItem.Value;
        string avukat = DropDownList2.SelectedItem.Value;
        string sql = @"INSERT INTO AVUKAT( MUSTERI, AVUKAT, HESAP)
                                Select @MUSTERI, @AVUKAT, @HESAP
                                From ( Select 1 As Value ) As Z
                                Where Not Exists    (
                                                    Select 1
                                                    From AVUKAT As T1
                                                    Where T1.HESAP = @HESAP
                                                    )";

        using ( SqlCommand cmd = new SqlCommand(sql, myConnection) )
        {
            cmd.Parameters.AddWithValue("@HESAP", hesap);
            cmd.Parameters.AddWithValue("@MUSTERI", musteriadi);
            cmd.Parameters.AddWithValue("@AVUKAT", avukat);
            cmd.Connection = myConnection;

            int rowsAffected = cmd.ExecuteNonQuery();

            if ( rowsAffected = 0 )
                // tell user that ID exists and their data couldn't be inserted.

            Response.Redirect(Request.Url.ToString());
            myConnection.Close();
        }
    }
}

When you eliminate the impossible, whatever remains, however improbable, must be the truth.

If the HESAP value being inserted is zero, then Label1.Text must contain a zero when the Add_Click event is fired. Looking at your DropDown event handler, there are a couple of items of note. 

If HESAP is supposed to be an integer, you should verify that it is an integer using int.TryParse.
The query should be parameterized. Even the contents of a DropDownList should be considred user input.
As before, it is best to incorporate the using construct.

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
    int avukat;
    int hesapNo;
    bool enabled = int.TryParse( DropDownList1.SelectedItem.Value, out hesapNo ) 
        && int.TryParse( DropDownList2.SelectedItem.Value, out avukat ) 
        && hesapNo != 0 
        && avukat != 0;

    if ( enabled )
    {
        string strConnectionString = ConfigurationManager.ConnectionStrings["SqlServerCstr"].ConnectionString;

        using( SqlConnection myConnection = new SqlConnection(strConnectionString) )
        {
            myConnection.Open();

            string query = @"Select A.HESAP_NO 
                             From YAZ..MARDATA.S_TEKLIF A 
                             Where A.MUS_K_ISIM = @HesapNo"

            using( SqlCommand cmd = new SqlCommand(query, myConnection) )
            {
                cmd.AddParameterWithValue( "@HesapNo", hesapNo );
                Label1.Text = cmd.ExecuteScalar().ToString();
            }
        }
    }

    Add.Enabled = enabled;
    Label1.Visible = false;
}
If you add the CHECK constraint I mentioned at the top, then the code will error on insert and the bad row will not get into the database. That should lead you back to the DataSource for DropDownList1. It would appear that its SelectedValue is being returned as zero. That would imply that source that populates DropDownList1 is pushing a value with zero in it. What is the source that populates DropDownList1?

