按Long Code排序的数据库中如何确定下一个唯一可用Short Code

• 数据库按Long Code（长编码，示例值：Y1D3D74945）排序，同时存储Short Code（短编码，示例值：L1000），两类编码均要求全局唯一，完全不可重复
• 受数据库长编码排序规则限制，无法直接获取短编码的下一个可用值，当前待分配的下一个空短编码应为L2367

Private Sub CmdSearch_Click(sender As Object, e As EventArgs) Handles CmdSearch.Click
         Dim SC As String,
             SCNew As String,
             LCNew As String,
             LC As String,
             Similiar As String,
             NotFound As Boolean,
             SCNum As Integer
 
         NotFound = True
         PartPos = 0
         TxtShortCode.Text = ""
         LCNew = Trim(TxtLongCode.Text)
         Similiar = LCNew.Substring(0, 13)
         For Each Row As DataRow In RatesDataSet.Codes.Rows
             LC = Trim(Row("Long Code"))
             SC = Trim(Row("Short Code"))
             If LC = TxtLongCode.Text Then
                 TxtShortCode.Text = Trim(Row("Short Code"))
                 NotFound = False
                 GoTo FinishLoop
             ElseIf LC.substring(0, 8) = "0 To Use" Then
                 TxtShortCode.Text = Trim(Row("Short Code"))
                 MsgBox("Update this Part Number", vbOKOnly)
                 'To get the row number to copy over
                 PartPos = Me.CodesBindingSource.Position
                 NotFound = False
                 GoTo FinishLoop
            ElseIf LCNew.Substring(0, 1) = LC.Substring(0, 1) Then
                 SCNum = SC.Substring(1, 4)
                 If LC.Substring(0, 13) = Similiar Then
                     TxtSimiliar.Text = SC
                ElseIf LC = LCNew Then
                     NotFound = False
                     TxtShortCode.Text = SC
                     GoTo FinishLoop
                 End If
             ElseIf SC = "Z9999" Then
                 SCNum = SCNum + 1
                 SCNew = LCNew.Substring(0, 1) & SCNum
                 TxtShortCode.Text = SCNew
                 MsgBox("This is a new item please add", vbOKOnly)
                 CmdAdd.Enabled = True
                 Me.AcceptButton = CmdAdd
             End If
         Next
         'if Not found
         If NotFound = False Then
             For Each Row As DataRow In RatesDataSet.Codes.Rows
                 SC = Trim(Row("Short Code"))
                 If SC.Substring(0, 1) = LCNew.Substring(0, 1) Then
                     'need to put the list in order by short code then add here what the next number would be
                 End If
 
             Next
         End If
FinishLoop:

1. 原有代码存在逻辑错误：新编码未匹配到的场景下NotFound值为True，原判断条件If NotFound = False写反，需要改为If NotFound = True才能触发生成新短编码的逻辑
2. 注释标注的空缺逻辑可替换为以下代码，实现按首字母匹配短编码、提取数字段排序后取最大值+1生成唯一新短编码：

' 提取所有同首字母的短编码数字段
Dim scNumList As New List(Of Integer)
For Each row As DataRow In RatesDataSet.Codes.Rows
    Dim currentSc As String = Trim(row("Short Code"))
    If currentSc.Substring(0, 1) = LCNew.Substring(0, 1) AndAlso IsNumeric(currentSc.Substring(1, 4)) Then
        scNumList.Add(CInt(currentSc.Substring(1, 4)))
    End If
Next
' 排序后取最大值+1，无匹配值则从1000开始
scNumList.Sort()
Dim nextScNum As Integer = If(scNumList.Count = 0, 1000, scNumList.Last() + 1)
' 补零为4位，拼接首字母生成新短编码
SCNew = LCNew.Substring(0, 1) & nextScNum.ToString("0000")
TxtShortCode.Text = SCNew
MsgBox("This is a new item please add", vbOKOnly)
CmdAdd.Enabled = True
Me.AcceptButton = CmdAdd
NotFound = False

内容的提问来源于stack exchange，提问作者Mike