I want to create a unique ID for a record being entered into a sql server database, in a web application. My logic is to count the number of records in the table, add a one to that number, then add a prefix to the new number. Check that the new id is not currently in use (records may be delected occasionally so a count won't necessarily find a unique number). If it is, add one to the existing number and try again. This works until the second check of the id -
newPhoneCheck = Convert.ToString(cmdPhoneIDcheck.ExecuteScalar in the Do Until loop) has the value of the previous id calculated instead of finding the newly created one. I would love some help in solving this, and if any of you who are much wiser than I have a better way to do this I am all ears.
<code>
Public Function CreateNewPhoneID() As String
'get a count of records in the table
Dim newPhoneID As String
Dim intRecNum As Int32
Dim PersonnelConnectionString = "Initial Catalog=Personnel;Data Source=WROBUFF6;Integrated Security=true;"
Dim sqlCount As String = "SELECT COUNT(PhoneID) AS RecNum FROM WorkPhone;"
Dim conn1 As New SqlConnection(PersonnelConnectionString)
Dim cmdCount As New SqlCommand(sqlCount, conn1)
Try
conn1.Open()
intRecNum = Convert.ToInt32(cmdCount.ExecuteScalar()) + 1
Catch ex As Exception
End Try
'Create a new phone ID based on the total number of records + 1
newPhoneID = "Ph" & Convert.ToString(intRecNum)
'Check to see if this is in use
Dim sqlCheck As String = "SELECT PhoneID FROM WorkPhone WHERE PhoneID = @PhoneID"
Dim conn2 As New SqlConnection(PersonnelConnectionString)
Dim cmdPhoneIDcheck As New SqlCommand(sqlCheck, conn2)
cmdPhoneIDcheck.Parameters.Add("@PhoneID", SqlDbType.VarChar)
cmdPhoneIDcheck.Parameters("@PhoneID").Value = newPhoneID
Dim newPhoneCheck As String
Try
conn2.Open()
newPhoneCheck = Convert.ToString(cmdPhoneIDcheck.ExecuteScalar)
If newPhoneCheck = "" Then
txtPhoneID.Text = newPhoneID
Exit Function
End If
Catch ex As Exception
End Try
'If so, take the count and add 1, then create a new phone ID and check it again.
Dim newIntRecNum As Integer
Try
conn2.Open()
newPhoneCheck = Convert.ToString(cmdPhoneIDcheck.ExecuteScalar)
Do Until newPhoneCheck <> newPhoneID
newIntRecNum = intRecNum + 1
newPhoneID = "Ph" & Convert.ToString(newIntRecNum)
newPhoneCheck = Convert.ToString(cmdPhoneIDcheck.ExecuteScalar)
Loop
txtPhoneID.Text = newPhoneID
Catch ex As Exception
End Try
</code>