Auto number

Question

Good day. I am a beginner in VB.NET. I have a problem that I used (TheTable.Rows.Count + 1) to generate the no. for the customer ID. If I deleted some of the records and then add a new records, the customer ID will not correct. This is because I am using TheTable.Rows.Count + 1, what I can do to solve this problem? How do check the last customerID in the Customer table in SQL server? can this solve my problem? I am using ADO.net. The code i am writing is : Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click SqlDataAdapter1.Fill(Me.DataSet11, "Customer") Dim TheTable As DataTable = Me.DataSet11.Tables(0) Dim aRow As DataRow = TheTable.NewRow() txtCustID.Text = TheTable.Rows.Count + 1 ClearForm() txtCustName.Focus() end sub Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click Try Dim TheTable As DataTable = Me.DataSet11.Tables(0) Dim aRow As DataRow = TheTable.NewRow() aRow("CustID") = TheTable.Rows.Count + 1 aRow("CustName") = txtCustName.Text aRow("CustAdd") = txtAdd.Text aRow("Postcode") = txtPostcode.Text aRow("City") = txtCity.Text TheTable.Rows.Add(aRow) SqlDataAdapter1.Update(Me.DataSet11, "Customer") txtCustID.Text = aRow("CustID") MessageBox.Show("Customer Was Successfully Added ", "VIP", MessageBoxButtons.OK, MessageBoxIcon.Information) txtCustID.Text = TheTable.Rows.Count + 1 ClearForm() txtCustName.Focus() Catch ex As Exception MessageBox.Show(ex.Message.ToString()) End Try End Sub If you know how to solve this problem, please post! Thanks in advance and best regards, viv