VB.NET中的autoNumber

Question

我想从VB6迁移到VB.NET，但仍然是新手。到目前为止，在我的代码下面，如何使autoNumber过程像VB6 in VB.NET (VB2012)中的代码一样工作。

下面我之前在VB6中的代码，这段代码将在addbutton_click时工作，它将显示CDEUSR005，因为表中有4个记录之前，CDEUSR001，CDEUSR002，CDEUSR003，CDEUSR004。Image sample that works in VB6

Sub autoNumber()
    Rs_User.Requery
    Set Rs_User = New ADODB.Recordset
    strSQL = "SELECT userCode FROM tblUser ORDER BY userCode"
    Rs_User.Open strSQL, Conn, adOpenDynamic, adLockBatchOptimistic
    If Rs_User.BOF Then
        NewCode = "CDEUSR001"
        Exit Sub
    Else
        Rs_User.MoveLast
        NewCode = Rs_User!userCode
        userCode = Right(userCode, 3)
        userCode = Val(userCode) + 1
        If Len(userCode) > 5 Then
            Exit Sub
        End If
    End If
    userCode = "CDEUSR" & Format(NewCode, "000")
 End Sub

这是我的VB.NET(VB2012)代码，当点击add按钮时，它会显示CDEUSER001，而不是CDEUSR005。Image sample that wont' works in VB.NET

Sub autoNumber()
    sql = New Odbc.OdbcCommand("SELECT userCode FROM tblUser ORDER BY userCode", conn)
    dr = sql.ExecuteReader
    dr.Read()

    If (xxxx which has same function with Rs_User.BOF) Then
        NewCode = "CDEUSR001"
        Exit Sub
    Else
        NewCode = Microsoft.VisualBasic.Right(dr.GetString(0), 3)
        NewCode = Val(NewCode) + 1
        If Len(NewCode) > 5 Then
            Exit Sub
        End If
    End If
    NewCode = "CDEUSR" & Format(NewCode, "000")
End Sub

Answer 1

VB6中的ADODB与VB.NET中的OdbcConnection的不同之处在于，OdbcConnection经常使用OdbcDataReader.HasRows属性作为ADODB.Recordset.BOF或ADODB.Recordset.EOF的对应项。

您的VB.NET代码应按如下方式修改(作为初始化OdbcConnection时的通用约定添加Using块)：

Sub autoNumber()
    Using conn As New OdbcConnection(...)
        Using sql As New OdbcCommand("SELECT userCode FROM tblUser ORDER BY userCode", conn)
           Using dr As OdbcDataReader = sql.ExecuteReader()

               ' similar like Recordset.EOF or BOF
               If dr.HasRows = False Then 
                   NewCode = "CDEUSR001"
                   Exit Sub
               Else
                   dr.Read()
                   ' dr("userCode") may also work here
                   NewCode = Right(dr.GetString(0), 3)
                   NewCode = Val(NewCode) + 1
                   If Len(NewCode) > 5 Then
                      Exit Sub
                   End If
               End If
            NewCode = "CDEUSR" & Format(NewCode, "000")
            End Using
        End Using
    End Using
End Sub

注意:与ADODB.Recordset不同的是，OdbcDataReader没有行索引来确定最后一行，但DataSet有。您可以检索DataSet中的最后一条记录，如下所示：

Dim ds As DataSet = New DataSet()

Using conn As New OdbcConnection(...)
   Using sql As New OdbcCommand("SELECT userCode FROM tblUser ORDER BY userCode", conn)
       Dim adapter As OdbcDataAdapter = New OdbcDataAdapter(sql)
       adapter.Fill(ds)

       ' here to get last row from DataSet
       ' related: stackoverflow.com/questions/2099379/
       NewCode = Right(ds.Tables(0)(ds.Tables(0).Rows.Count - 1)("userCode"), 3)

       ' other stuff

   End Using
End Using