QTP always returns EOF when querying SQL statement to Access 2K3

[riechan]

Hi guys! I have this function there that runs a SQL query to an Access database in order to get the object configuration of a button. However, when it comes to querying the SQL statement, QTP always returns with an empty value. But, when I run the SQL statement using the native SQL compiler of Access 2003, it queries the command successfully, and returns the value that I'm searching for. Can anyone help me solve this?

Code:

    ' ==========================================
    Function AddItem(objWindow, strItem)
        On Error Resume Next

        Const adOpenForwardOnly = 0
        Const adOpenKeyset = 1
        Const adOpenDynamic = 2
        Const adOpenStatic = 3

        strTblName = Environment("Table_Name")
        curPage = objWindow.JavaButton("developer name:=BF.microflow.pageTitle").GetROProperty("attached text")

        Set connAccess = CreateObject("ADODB.Connection")
        Set rsObjects = CreateObject("ADODB.Recordset")
            connAccess.Provider = "Microsoft.JET.OLEDB.4.0"
            connAccess.Open "C:\BFTC\Object Config\BFTC_ObjConf.mdb"

        strSQLObjects = "SELECT * FROM " & strTblName & " WHERE Page='" & curPage & _
                        "' AND FieldName LIKE 'Add_" & strItem & "*' AND " & _
                        "(AttachedText LIKE 'Add*' OR AttachedText LIKE '*>*')"

        ' SELECT * FROM ROLE WHERE Page='Create Role' AND FieldName LIKE 'Add_Permission*' AND (AttachedText LIKE 'Add*' OR AttachedText LIKE '*>*')

        rsObjects.Open strSQLObjects, connAccess, adOpenStatic

        objWindow.JavaButton("attached text:=" & rsObjects.Fields("AttachedText").Value).Click

        connAccess.Close
        Set connAccess = Nothing
        Set rsObjects = Nothing

        strError = Err.Description
        AddItem = ErrorCheck(strError)
    End Function

[sshukla12]

Hi,

Just make sure u are passing the correct values as variable used in ur query.Try to run the query without using variable, try to pass the hardcoded values and check.

Let me know if hardcoded values help u or not.

Regards,
Sankalp

[Shridevi.Salagare]

sometimes because of quotes ..the query string is not properly created..you can put a breakpoint after the query statemnt is created.
Put it in watch..pick up that query and manually check whether it returns values in the database.
Please let me know if you need more help or clarification.

[riechan]

Solved it. The error was with the SQL statement. Looks like QTP can't "understand" the "*" wildcard, instead, I replaced it with the "%" wildcard (which oddly, does not work when querying in Access 2003):

Code:

strSQLObjects = "SELECT * FROM " & strTblName & " WHERE Page='" & curPage & _
                            "' AND FieldName LIKE 'Add_" & strItem & "%' AND " & _
                            "(AttachedText LIKE 'Add%' OR AttachedText LIKE '%>%')"