0

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

        }

    }
2
Contributors
1
Reply
15
Views
1 Month
Discussion Span
Last Post by rproffitt
0

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.

Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.