vb.net - 在具有唯一条件的数据表中查找第一行

Question

我在 Microsoft Access 中有一个包含两列的数据表：

+-----+-------------+
| Row | Status      |
+-----+-------------+
| 1   | In-Progress |
| 2   | In-Progress |
| 3   | In-Progress |
| 4   | TBR         |
| 5   | TBR         |
| 6   | TBR         |
+-----+-------------+

我需要查询表，以便查询将返回第 4 行。我想知道状态为 TBR 的数据表中的第一行是什么。在 VB 中编码非常新。

Public Function FindFirstActiveRow() As Integer
    Dim inum As Integer
    inum = 1

    Dim myconnection As OleDbConnection = New OleDbConnection
    Dim query As String = "Select [ROW] From Table3 where [STATUS]='TBR', AND [ROW]= (select MIN(ROW) from table 3);"
    Dim dbsource As String = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=S:\Docs\PRODUCTION\Shop Manager\Shop_Manager\Shop_Manager\Database2.accdb;")
    Dim conn = New OleDbConnection(dbsource)
    Dim cmd As New OleDbCommand(query, conn)

    Try
        conn.Open()
        Dim myreader As OleDbDataReader = cmd.ExecuteReader()
        myreader.Read()
        FindFirstActiveRow = Val(myreader("ROW"))

        conn.Close()
    Catch ex As OleDbException
        MessageBox.Show("Error Pull Data from Table3")
        FindFirstActiveRow = 1
    End Try

End Function

标签： vb.netms-access

Answer 1

这里有两种可能的方法：

select min(table3.row) from table3 where table3.status = 'TBR'

select top 1 table3.row from table3 where table3.status = 'TBR' order by table3.row