auto number

[vivsm]

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

[ks5d]

Why not just define the Customer ID as an autoincrement field in SQL Server? In that case, the addition of new records gives unique results regardless of whether or not earlier records have been deleted. Plus, it has the benefit of not having to ever read the last record before inserting a new one.

Best regards,
Edwin

[vivsm]

Thank you very much!

[vivsm]

if I used auto increment for the CustID in the SQL constrain, than I do have a problem to have the duplicate records (The same customer records). So, in this case I would like to check for the CustIC since this is an unique no. for our identity. How do I check CustIC in the Customer table in SQL server. If the CustIC exist than the msg will show that "The customer is exist in cutomer no. ###(CustID)"
Please help!

Thanks and regards,
Viv

[ks5d]

I'd need to know more about your database structure to be able to assist you with this. What exactly is CustIC?

There are several ways you can check for duplicate records with an autoincremental PK. I wouldn't abandon the idea yet since that fixes your problem of creating new and unique PKs.

--Edwin

[vivsm]

First, Thank you very much for your quick reply.

I was using CustID as PK and it is an auto generate number which i set the increment by 1 in SQL, but i do have a problem that it can save a duplicate customer records since there are no checking on the duplicate records. So, i can check the duplicate data by checking the CustIC. This is an identty card with an unique number for everybody in our country.

I have a database of a Customer table in SQL server. It contains of CustID(PK) ,CustFName, CustLName,CustIC, CustDoB, Address,Postcode, State,Phone.

I create a form called frmCustomer that contains a few textbox for entering the data. By pressing the Add button, i would like to have a checking on the CustIC, if the CustIC already exist, this means that the customer already exist and pop up the msg that (The customer is already exist in "CustID".) . if the CustIC is not in database and then the data can be added to the Customer database.

Hopefully, you can get the picture now. Thanks for your help .

Have a nice weekend,
Best regards,
Viv

[ks5d]

Okay, so as I understand it, you're worried about having duplicate customer entries since the PK is now an autoincremented ID field. That's always a concern when using autoincremented IDs; however, you can get around it fairly easily by testing against a candidate key (CK).

In this case, I would venture that a viable CK would be CustFName+CustLName+CustDoB. There are certainly others, and the design guidelines for the database would highlight them. I don't thing first and last name is enough since one can open the phone book and find many "John Smith" entries. Moreover, your schema cannot easily discern between generations without examining the date of birth; case in point: Bill Jones, Sr. vs. Bill Jones, Jr. Both are Bill Jones and, if "Junior" is still living at home then your schema would be wrecked. However, DOB is the savior -- unless you have two people with the same name born on the same day (unlikely). If you want to make the chance of a duplicate more remote, add in address.

Remember, you can always check all fields prior to inserting a new customer, i.e.,

SELECT *
FROM Customer
WHERE CustFName = newCust.FirstName()
AND CustLName = newCust.LastName()
AND CustIC = newCust.IC()
AND CustDoB = newCust.DOB()
AND Address = newCust.Addr()
AND Postcode = newCust.ZIP()
AND State = newCust.State()
AND Phone = newCust.Phone();

If that query has zero results in the set that it returns then you have a new customer. Just be sure to uppercase all strings before you compare them and enter them into the database so you don't wind up considering John, JOHN, john, jOHN, JoHn, etc., to be different names. You might also want to give the data entry person a chance to double-check the spelling in a confirmation dialog to minimize typos.

Does this help any?

--Edwin