My code checks for duplicate values before a user is allowed to save into the database. Problem has to do with the logic, every new user gets a new ID increment by 1. So user one has an ID1 user two has an ID2 etc. Problem is when l delete say user one from the table using his ID and l try to generate a new ID for another user,l get ID2, which means two users have the same ID and the systems rejects it.Now how do l get the ID, l made a loop to count the number of ID in ID column and generate a plus one. How can l refine my code so that if l have only one record in the database and its ID2 the code, generates ID3 instead of ID2.

Here is the code for getting the ID

     private void btnid_Click(object sender, EventArgs e)
    {

        string id = "GH00";
         cn.Open();
        cmd = new SqlCommand("select count(Id) from jimmy", cn);
        int i = Convert.ToInt32(cmd.ExecuteScalar());
        cn.Close();
        i++;

        lblID.Text = id + i.ToString();

    }

Here is the code for saving into the database:

       private void button1_Click(object sender, EventArgs e)
    {

        //if (this.Controls.OfType<TextBox>().Any(t => string.IsNullOrEmpty(t.Text))) 
        if (txtfirstname.Text == "" || txtlastname.Text == "" || txttelephone.Text == "" && txtaddress.Text == "" || txtcity.Text == "" || txtcountry.Text == "" || lblID.Text == "")
        {
            MessageBox.Show("Fill all fields");
        }
        else
        {
            string check = @"(select count(*) from jimmy where Id='" + lblID.Text + "')";

            cn.Open();
            cmd = new SqlCommand("INSERT INTO jimmy (Id, FirstName, LastName, Telephone, Address, City, Country, Image ) VALUES (@Id, @FirstName, @LastName, @Telephone, @Address, @City, @Country,@Image)", cn);

            cmd.Parameters.AddWithValue("@Id", lblID.Text);
            cmd.Parameters.AddWithValue("@FirstName", txtfirstname.Text);
            cmd.Parameters.AddWithValue("@LastName", txtlastname.Text);
            cmd.Parameters.AddWithValue("@Telephone", txttelephone.Text);
            cmd.Parameters.AddWithValue("@Address", txtaddress.Text);
            cmd.Parameters.AddWithValue("@City", txtcity.Text);
            cmd.Parameters.AddWithValue("@Country", txtcountry.Text);
            cmd.Parameters.AddWithValue("@Image"+ folderpath, Path.GetFileName(open.FileName));

            SqlCommand cmda = new SqlCommand(check, cn);
            int count = (int)cmda.ExecuteScalar();
            if (count > 0)
            {

                MessageBox.Show("Duplicate Record");
                cleartext();
            }
            else
            {

                cmd.ExecuteNonQuery();
                cn.Close();
                MessageBox.Show("Saved");

                File.Copy(filePath, Path.Combine(folderpath, Path.GetFileName(filePath)), true);
                cleartext();
            }
            cn.Close();

        }

    }

A question. Why isn't your unique ID generated just prior to line 14 in your "saving" code?

Generating unique incrementing values is a long standing discussion. But as presented I don't see why you need this ID until you perform the insert.