Sorting numbers in Access and .NET

I have an Access table which has a Number field and a Text field.

I can run a query like this:

SELECT * FROM Table ORDER BY intID ASC
//outputs 1,2,3,10

But when I try to run the same query through the .NET OleDB client,开发者_运维百科 like this:

Private Sub GetData()    
   Using cnDB As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path)
        cnDB.Open()
        Dim SQL As String = "SELECT * FROM Table ORDER BY intID ASC"
        Dim cmd As New OleDbCommand(SQL, cnDB)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        While dr.Read()
            lst.Items.Add(dr.Item("intID") & " - " & dr.Item("strName"))
        End While
        cnDB.Close()
    End Using
End Sub

I get items in the order 1,10,2,3.

What's going on here, and how can I have the data sort "naturally" (1,2,3,10) in both places?

try

SELECT * FROM Table ORDER BY CInt(intID) ASC

to explicitly tell Access to treat this as an integer and not a string. Obviously, something in the OleDbClient is seeing this field as a string (text field) and sorting accordingly.

I suspect the problem is your connection string. If you're connecting to an Access database and include IMEX=1 in your connection string, the provider will treat all data as string. As such, the ordering will order by the string value, giving you 1, 10, 2, 3, as opposed to leaving the intID as an integer, and ordering it in numerical order.

It looks like you're getting a lexical (alphabetic) order. This will be correct if something in your database or query thinks that is a varchar/text column type instead of a numeric type.