Sign in to follow this  
Followers 0
darzanmihai

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

5 posts in this topic

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


I do not like stupid and idiot people that write idiot things...If you are one, do not write.

Share this post


Link to post
Share on other sites



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?

:idea:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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?

:idea:

This is the way the excel file looks: post-36572-12747768064667_thumb.jpg

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.


I do not like stupid and idiot people that write idiot things...If you are one, do not write.

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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|

:mellow:

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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|

:mellow:

Thank you very much. It worked.

I do not like stupid and idiot people that write idiot things...If you are one, do not write.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0