Datatype mismatch in criteria expression , when querying excel file using vbscript

I have a scenario in which I have to query an excel sheet using SQL. In excel I have a column called "test_case_id", which holds alphanumeric value I use the following vbscript code to get the values from excel. The "test_case_id" column in excel has been formatted as "General". When the value for this column is an integer ( ex: 1,2,3... )

SELECT  *   FROM [05 Invest Allocate$] WHERE [test_case_id] =1

The above query seems to work

But when the "test_case_id" column has a character in it ( ex: 5b, 6b,7b)

SELECT  *   FROM [05 Invest Allocate$] WHERE [test_case_id] ='5b'

the above query gives an error "Data type mismatch in criteria expression."

Could some one please help me resolve this in such a way that i can query either as a string or as an integer in the where clause ?

Full code is :

strQuery = "SELECT  *   FROM [05 Invest Allocate$] WHERE [test_case_id] ='5b'"
j_drive_root_path="C:\Documents and Settings\Desktop\"

Set objNetwork = CreateObject("WScript.Network")
            strUserName =objNetwork.UserName

            Set fso = CreateObject("Scripting.FileSystemObject")
            Set original = fso.GetFile(j_drive_root_path & "temp_invest_allocate.xls")

            strCopyPath = "C:\Documents and Settings\" & strUsername & "\copyofexceltable.xls"
            original.Copy (strCopyPath)

            'Set the connection object and path to the copied spreadsheet       
            Set objAdCon =  CreateObject("ADODB.Connection")

            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & strCopyPath & ";" & _
                      "Extended Properties=""Excel 8.0;HDR=Yes"""

            'Open spreadsheet
            objAdCon.Open strConn

            'Create a recordest object (to store the resul开发者_运维百科ts of the query)
            Set record_set = CreateObject("ADODB.Recordset")

            'Run the query
            record_set.CursorLocation = 3

            record_set.Open strQuery, objAdCon, 1, 3

            If Not record_set.EOF Then
                Set client_to_accountID = CreateObject("Scripting.Dictionary")
                client_to_accountID.CompareMode=vbTextCompare
                client_to_accountID.RemoveAll

                Do Until record_set.EOF
                        tmp_val=Trim(Cstr(record_set("id").Value))
                        print "id=" & tmp_val
                        record_set.MoveNext
                Loop
            End If ' End of if not record_set.EOF

---

if you are using Win7 64bit the location is My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel

---

In http://support.microsoft.com/kb/257819 it states:

For example: In your eight (8) scanned rows, if the column contains five (5) numeric values and three (3) text values, the provider returns five (5) numbers and three (3) null values. In your eight (8) scanned rows, if the column contains three (3) numeric values and five (5) text values, the provider returns three (3) null values and five (5) text values. In your eight (8) scanned rows, if the column contains four (4) numeric values and four (4) text values, the provider returns four (4) numbers and four (4) null values. As a result, if your column contains mixed values, your only recourse is to store numeric values in that column as text, and to convert them back to numbers when needed in the client application by using the Visual Basic VAL function or an equivalent.

To work around this problem for read-only data, enable Import Mode by using the setting "IMEX=1" in the Extended Properties section of the connection string. This enforces the ImportMixedTypes=Text registry setting. However, note that updates may give unexpected results in this mode. For additional information about this setting, click the article number below to view the article in the Microsoft Knowledge Base: 194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset

You may need to change TypeGuessRows to 0 in the registry at

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/