ADODB & Excel - select from table where... error

[darzanmihai]

Hi

I want to use ADODB to read from excel using select, but there is a problem:

When I use "select * from table" it works, but when I use "select * from table where col='val'" I get the error "Data type mismatch in criteria expression". the table name ant the column name is correct, so I do not understand.

This is the script I used:

#include <_sql_mod.au3>
#include <array.au3>

_SQL_RegisterErrorHandler()

$path = @ScriptDir&"\Export_temp\DocRsA.xls"
$vQuery="select * from [DocRsA$] where [DocRsA$].[NrIDoc]='3';"
Querry_excel($path, $vQuery)

_SQL_UnRegisterErrorHandler()

Func Querry_excel($path, $Querry)
    Const $adSchemaTables = 20
    Const $adOpenStatic = 3
    Const $adLockOptimistic = 3
    Const $adCmdText = 0x0001 ;  =&H0001
    Global $s_Filename=FileGetShortName($path)

    $objConnection = ObjCreate("ADODB.Connection")
    $objRecordSet = ObjCreate("ADODB.Recordset")

    $objConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source="&$s_Filename&";" & _
                    "Extended Properties=""Excel 8.0;HDR=Yes;"";")
    Local $aResult, $iRows, $iColumns
    _SQL_GetTable2D($objConnection, $Querry, $aResult, $iRows, $iColumns)
    _ArrayDisplay($aResult)
    _SQL_Close($objConnection)

EndFunc

Instead of "[DocRsA$].[NrIDoc"] I also tryed "[NrIDoc]" and "NrIDoc" (the column name - in excel it is just "NrIDoc")

Could someone help me with this issue?

Thx

Mihai

[PsaltyDS]

I don't understand how you "named" the column. Do you mean you defined the column as a named range? If so, are you sure that kind of meta-data is exposed in the ADODB interface?

[darzanmihai]

I don't understand how you "named" the column. Do you mean you defined the column as a named range? If so, are you sure that kind of meta-data is exposed in the ADODB interface?

This is the way the excel file looks:

The first row represents (I think) the column names...if if is not corect please guide me.

How could I querry this tipe of excel?

by using "select * from [Pret$]" it returns correctly al the table.

[PsaltyDS]

You were right, the first row is assumed to be column names. This works:

#include <array.au3>

$oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")

$path = @ScriptDir & "\Test1.xls"
$vQuery = "select * from [DocRsA$] where IdSortOrder=4;"
Querry_excel($path, $vQuery)

Func Querry_excel($path, $Querry)
    Const $adSchemaTables = 20
    Const $adOpenStatic = 3
    Const $adLockOptimistic = 3
    Const $adCmdText = 0x0001 ;  =&H0001
    Global $s_Filename = FileGetShortName($path)

    $objConnection = ObjCreate("ADODB.Connection")
    $objConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & $s_Filename & ";" & _
            "Extended Properties=""Excel 8.0;HDR=Yes;"";")
    ConsoleWrite("Opened DB" & @LF)
    $oRS = $objConnection.Execute($vQuery)
    $aResult = $oRS.GetRows()
    _ArrayDisplay($aResult, "$aResult")
EndFunc   ;==>Querry_excel

Func MyErrFunc()
    $HexNumber = Hex($oMyError.number, 8)
    MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _
            "Number is: " & $HexNumber & @CRLF & _
            "Windescription is: " & $oMyError.windescription)

    $g_eventerror = 1 ; something to check for when this function returns
EndFunc   ;==>MyErrFunc

Your example screen shot does not contain a column named "NrlDoc", so this searches "IdSortOrder" for the number 4. Note the sheet name is used for the Table, and the column name is used simply. There are also no quotes around 4 (because it is a numeric 4, not a string "4").

The resulting array looks like this:

[0] = |15|0|1|4|||True|
[1] = |15|0|2|4|||True|
[2] = |15|0|3|4|||True|
[3] = |15|0|4|4|||True|
[4] = |15|0|5|4|||True|

Edited May 25, 2010 by PsaltyDS

[darzanmihai]

You were right, the first row is assumed to be column names. This works:

#include <array.au3>

$oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")

$path = @ScriptDir & "\Test1.xls"
$vQuery = "select * from [DocRsA$] where IdSortOrder=4;"
Querry_excel($path, $vQuery)

Func Querry_excel($path, $Querry)
    Const $adSchemaTables = 20
    Const $adOpenStatic = 3
    Const $adLockOptimistic = 3
    Const $adCmdText = 0x0001 ; =&H0001
    Global $s_Filename = FileGetShortName($path)

    $objConnection = ObjCreate("ADODB.Connection")
    $objConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & $s_Filename & ";" & _
            "Extended Properties=""Excel 8.0;HDR=Yes;"";")
    ConsoleWrite("Opened DB" & @LF)
    $oRS = $objConnection.Execute($vQuery)
    $aResult = $oRS.GetRows()
    _ArrayDisplay($aResult, "$aResult")
EndFunc ;==>Querry_excel

Func MyErrFunc()
    $HexNumber = Hex($oMyError.number, 8)
    MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _
            "Number is: " & $HexNumber & @CRLF & _
            "Windescription is: " & $oMyError.windescription)

    $g_eventerror = 1 ; something to check for when this function returns
EndFunc ;==>MyErrFunc

Your example screen shot does not contain a column named "NrlDoc", so this searches "IdSortOrder" for the number 4. Note the sheet name is used for the Table, and the column name is used simply. There are also no quotes around 4 (because it is a numeric 4, not a string "4").

The resulting array looks like this:

[0] = |15|0|1|4|||True|
[1] = |15|0|2|4|||True|
[2] = |15|0|3|4|||True|
[3] = |15|0|4|4|||True|
[4] = |15|0|5|4|||True|

Thank you very much. It worked.