Why is the 'if' condition in my while loop only checked once?

Question

I have written code to stop duplication of my Primary Key. I have two tables. One table saves the company details and the other table saves other details. In the table which saves company details, the company registration number is being given as the Primary Key. It should not be duplicated. But the registration number is a Foreign Key to the other table where it can be duplicated as much as it wants.

Here's my Code :

public int checkComRegnumberAvailable(string conRegnumber)
{   
    int check = 2;
    int i = 0;

    List<OtherCompany> checklist = getCompanyDetails();

    while (i < checklist.Count)
    { 
        if (checklist[i].RegNumber != conRegnumber)
        {
            check = 0;
            i++;
        }

        else
        {
            check = 1;
            i++;
        }
    }

    return check; 
}

Below is the method where I execute the SQL statements according to the value of the integer check :

public void AddCompanyDetails(int NDAid)
{
    using (SqlConnection con = new SqlConnection(cs))
    {
        int check = checkComRegnumberAvailable(oc.RegNumber);

        if (check == 0)
        {
            SqlCommand cmd = new SqlCommand("INSERT INTO OtherCompanyData (RegNumber,ComName,Country,Address,CoreBusi) values (@regnum,@comname,@country,@address,@corebusi) ", con);

            cmd.Parameters.AddWithValue("@regnum", oc.RegNumber);
            cmd.Parameters.AddWithValue("@comname", oc.ComName);
            cmd.Parameters.AddWithValue("@country", oc.Country);
            cmd.Parameters.AddWithValue("@address", oc.RegOfficeAddress);
            cmd.Parameters.AddWithValue("@corebusi", oc.CoreBuss);

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                AddNDADetails(NDAid);
            }

            catch (SqlException ex)
            {
                throw ex;
            }
        }

        else if (check == 1)
        {
            AddNDADetails(NDAid);
        }
    }
}

On executing the code I get the following exception :

Please help me. Thanks in advance.

Answer 1

You want your code to find out if there is a duplicate before adding, but what you are really doing is just verifying that the last element does not match.

Instead, do this:

public int checkComRegnumberAvailable(string conRegnumber)
{   
    int i = 0;

    List<OtherCompany> checklist = getCompanyDetails();
    foreach(var company in checklist)
    {
        if(company.RegNumber == conRegnumber)
        { 
            return 1;
        }
    }

    return 0;
}

There are much better ways to accomplish what you are doing, but this should demonstrate the problem in your current logic.