ADODB SQL Statement ORDER BY Statement

[maecky]

Hi,

I have been reading a lot about ADODB but I couldn't figure my problem. I hope someone here can help.
I have an Excel Sheet which I use as Database. The database has 11 columns and I insert data with the following function:

Sub testInsert()
    Dim adoCommand As New ADODB.Command
    Dim sQuery As String
    Dim i As Integer
    
    Dim strTest As String
    
    strTest = "test"
    
    sQuery = "INSERT INTO " & sDBName & " (F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11) VALUES (@F1, @F2, @F3, @F4, @F5, @F6, @F7, @F8, @F9, @F10, @F11)"
    
    sAdoPath = ThisWorkbook.FullName
    sAdoConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & sAdoPath & ";Extended Properties=""Excel 12.0;HDR=No"";"
    
    With adoCommand
        .ActiveConnection = sAdoConnectString
        .CommandType = adCmdText
        .CommandText = sQuery
        .Prepared = True
        .Parameters.Append .CreateParameter("F1", adInteger, adParamInput, , 1)
        .Parameters.Append .CreateParameter("F2", adInteger, adParamInput, , 0)
        .Parameters.Append .CreateParameter("F3", adInteger, adParamInput, , 0)
        .Parameters.Append .CreateParameter("F4", adVarChar, adParamInput, 8000, strTest)
        .Parameters.Append .CreateParameter("F5", adInteger, adParamInput, , 1111111)
        .Parameters.Append .CreateParameter("F6", adVarChar, adParamInput, 8000, "Description")
        .Parameters.Append .CreateParameter("F7", adVarChar, adParamInput, 8000, "Responsibility")
        .Parameters.Append .CreateParameter("F8", adVarChar, adParamInput, 8000, "Involved")
        .Parameters.Append .CreateParameter("F9", adInteger, adParamInput, , 546465)
        .Parameters.Append .CreateParameter("F10", adInteger, adParamInput, , 546465)
        .Parameters.Append .CreateParameter("F11", adVarChar, adParamInput, 10, "o")
        
        '.Execute , , adCmdText + adExecuteNoRecords
    End With
    
    For i = 1 To 15
        adoCommand("F1") = i
        adoCommand.Execute , , adCmdText + adExecuteNoRecords
    Next i
    
    
End Sub

Now I want to retrieve this data. i.e. I want all F1 where F2 and F3 are 0 AND I want them ordered descending. I'm trying to achieve this with:

Sub testSelect()
    Dim adoCommand As New ADODB.Command
    Dim sQuery As String
    Dim mrs As New ADODB.Recordset
    Dim strTest As String
    
    strTest = "test"
    
    sQuery = "SELECT F1 FROM " & sDBName & " WHERE F2=@F2 AND F3=@F3 ORDER BY F1 DESC"
    
    sAdoPath = ThisWorkbook.FullName
    sAdoConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & sAdoPath & ";Extended Properties=""Excel 12.0;HDR=No"";Persist Security Info=True;"
    'sAdoConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & sAdoPath & ";Extended Properties=""Excel 12.0;HDR=No"";"
    
    With adoCommand
        .ActiveConnection = sAdoConnectString
        .CommandType = adCmdText
        .CommandText = sQuery
        .Prepared = True
        .Parameters.Append .CreateParameter("F2", adInteger, adParamInput, , 0)
        .Parameters.Append .CreateParameter("F3", adInteger, adParamInput, , 0)
       
   End With
   Set mrs = adoCommand.Execute


   With mrs
       Do While Not .EOF
           For k = 0 To .Fields.Count - 1
               Debug.Print .Fields(k)
                
            Next k
       .MoveNext
       Loop
    End With
    mrs.Close

End Sub

The result I am getting looks like this:
9
8
7
6
5
4
3
2
15
14
13
12
11
10
1

I assume, that the data is interpreted as String instead of an integer. But I explicitely stated the data as Integer when storing the data into the DB.
Am I missing something here?

Thanks for your help,

kind regards,
m

 

[xenou]

Presumably the data type of F1 is string/text. You can try putting a conversion function into the query:

SELECT SomeConversionFunction(F1) AS F1Numeric, ...
...
ORDER BY SomeConversionFunction(F1)


I don't use SQL Server often. I think they have a CAST() as well as an actual Convert() function

 

[maecky]

Thanks,

your answer pointed me in the right direction:

sQuery = "SELECT CInt(F1) FROM " & sDBName & " WHERE F2=@F2 AND F3=@F3 ORDER BY CInt(F1) DESC"

This is working.

But I am wondering why I can pass the datatype to the SQL String when inserting into the database, when the information gets lost.
Is there an alternative, to retrieve the data correctly or do we have to CAST explicitely?

Thanks,
m

 

[xenou]

Hi, the datatype really needs to be determined on the other side - in the design/structure of the table. I'm not sure what kind of database you are working with -- I thought at first it was SQL Server but maybe you are using Access. Anyway, if the datatype is Text and you give it Number, either one of two things will happen: the query will fail/crash, or it will implicitly coerce your values to the correct datatype. Normally, the first will happen with queries, and the second will happen with imports of data.


If you are using Access, I consider Make Table Queries unreliable because you have no control over the datatype. However, it is possible to append to a table rather than make one every time. Also you can change field datatypes, but I would normally do this with DAO rather than Alter Table SQL commands. I just don't consider Alter Table robust enough for anything but the most primitive kinds of changes (as a rule, you really don't create Access database structures with DDL, as you do/can in most other databases).

 

[maecky]

Hi,
thanks for your answer. As I stated in the first post I use a simple Excel Sheet as "Database". I assume, according to your explanation, that this kind of "database" doesn't store the types and therefore I have to cast.

Thanks again,
m

 

[xenou]

Hi,
Okay sorry I missed that. You will need to cast everything to numbers to be sure. Excel can be very unreliable when it comes to datatypes. Generally speaking it will only return numbers if every single cell in a column contains a numbers. Blanks can really throw it off. What's worse, blanks at the bottom of the table (where you think there is "nothing") can sometimes be part of the dataset. I will usually use "clear all" with all the cells below my data, then delete all the rows under my data, and likewise for everything to the right of the last column, unless I know for sure that the data is clean, which I usually take for granted only for machine-generated data.

Note: I think I was confusing your thread with another one, btw. Ignore anything that makes no sense at all!