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>

you messed up on your tags its ["code"]["/code"] Not <code></code> just a little heads up but the first code tags dont have the quotes in them

Edited 7 Years Ago by PysKo: n/a

So many problems in so few lines
1. If you "Try Catch End Try", DO SOMETHING after the Catch. This function goes blythly on when the most blatent errors can occur. (IE it would run OK on my machine with your connection string. Or would it? Everything fails in the try blocks. Is Do Until Nothing <> newPhoneID an infinite loop? It still runs because this loop would eventually cause an exception.)
2. Have you debugged this function? IE stepped through the function step by step making sure your code isn't blowing up.
3. functions expect to return an object. IE in this string function execute "Return newPhoneID". With txtPhoneID.Text being set, this is basically a Sub. It should be txtPhoneID.Text = CreateNewPhoneID() if you want this to be a function.
4. Nothing in this function changes the database, is something else doing so?
5. Does the string set a phone id that doesn't exist?
6. Database developers - shut your eyes, walk away. (Shaking your head while doing so is OK.)

Whoops, forgot something Do Until REQUIRES the loop to execute at least one time. You sure you don't want "Do While" instead?

Thanks for the input - for those of us who were not born with this knowledge encoded in our dna there is a steep learning curve. Trying to learn it on your own at work with boss and co-workers breathing down your neck and juggling a load of other responsibilities has been a fascinating and most wonderful challenge. Very best to you kplcjl, and thanks for taking your time.

Been there, done that. Doesn't really require DNA encoding, just experience. At my former company, they expected a 3 person team to build a brand new application in 3 months. A. We couldn't touch a DB, but we could tell someone else what we wanted. B. We had to use a program none of us had even looked at. C. On an OS none of us had used before. (I think it was written in 1970.) D. Building forms on a prehistoric dinosaur of a forms language none of us had used before. We made it, no thanks to the DB team we worked with.

This article has been dead for over six months. Start a new discussion instead.