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();
}
}