"variable must be of type Object" - Error at SQL Query

[Whenever]

Hello,

I hope you can help me with the following problem:

I am trying to retrieve data from a database table with a very simple query, I have the connection to the ADODB and to the server, but when I try to save the executed query into a variable and then get the rows by using the .GetRows() method, i get the error message "Variable must be of type 'Object' ".

Below you can see the code that is used to get the data.

Func _GetData()
    MsgBox(0,"","Get Dat function")
    ;"ARServerPort=3003;DSN=AR System ODBC Data Source;ARServer=DESQLAPPSP01;UID=RZURJAC;PWD=test232;SERVER=NotTheServer;"
    ;$sDriver, $sDatabase, $ARServer, $ARServerPort, $sUsername = "", $sPassword = "", $fAuthMode = 1
    $driver = "{AR System ODBC Driver}"
    $DSN = "AR System ODBC Data Source"
    $ARServer = "DESQLAPPSP01"
    $ARServerPort = "3003"
    $user = "RZURJAC"
    $password = "test232"

    $SQLConn = _SQLConnect($driver,$DSN,$ARServer,$ARServerPort, $user, $password, 1)

    $query = 'SELECT Sammelgang_DB.Short_Description, Sammelgang_DB.Status, Sammelgang_DB.Wunschtermin FROM Sammelgang_DB Sammelgang_DB WHERE (Sammelgang_DB.Status=' & "'Angefordert' ) ORDER BY " & '"Sammelgang_DB".Wunschtermin'
    $ArrayResult = _SQLQuery($SQLConn, $query)
    _ArrayDisplay($ArrayResult)
    _SQLDisconnect($SQLConn)
EndFunc

Func _SQLConnect($sDriver, $sDatabase, $ARServer, $ARServerPort, $sUsername = "", $sPassword = "", $fAuthMode = 1)
    MsgBox(0,"","In Connection")
    $oConn = ObjCreate("ADODB.Connection")
    If NOT IsObj($oConn) Then
        MsgBox(0,"","Failure in ADODB Conenction")
        Return SetError(3, 0, 0)
    EndIf
    If $fAuthMode Then $oConn.Open ("DRIVER= " & $sDriver & ";DSN= " & $sDatabase & ";ARServer=" & $ARServer & ";ARServerPort=" & $ARServerPort & ";UID= " & $sUsername & ";PWD=" & $sPassword & ";ARAuthentication=;SERVER=NotTheServer")
    If @error Then
        MsgBox(0,"","Failure in Database" & @error)
        Return SetError(1, 0, 0)
    EndIf
    MsgBox(0,"","It worked")
    Return $oConn
EndFunc   ;==>_SQLConnect

Func _SQLQuery($oConn, $sQuery)
    If IsObj($oConn) Then
        MsgBox(0,"","Its an object")
        MsgBox(0,"",$sQuery)
        $return = $oConn.Execute($sQuery)
        $ArrayReturn = $return.GetRows()
        _ArrayDisplay($ArrayReturn)
    EndIf
    Return SetError(1, 0, 0)
EndFunc ;==>_SQLQuery

In the function _SQLQuery(), it gets into the if sentence, but it stops at $ArrayReturn = $return.GetRows() and tells me that the variable is not an object.

Thank you for your help...

[water]

You need to add some error checking. Either check the value of @error after a function call or add a COM error handler.

[Whenever]

Well I just did an @error checking with a MsgBox showing me what the @error is, all I can get from this is that the execute method doesnt seem to work

[water]

In this case I suggest to add a COM error handler because you get detailed error information. An example can be found in the help file for ObjEvent.

[Whenever]

err.number is: 0x80020009

err.windescription: Exception occurred.

 

err.description is: Driver][iSAM]No data found

err.source is: Microsoft OLE DB Provider for ODBC Drivers

err.helpfile is:

err.helpcontext is: 0

err.lastdllerror is: 0

err.scriptline is: 69

err.retcode is: 0x80004005

 

This is the Error Message I get with the errorHandler

[water]

You are sure that the query should return some data?

[Whenever]

I copied it from a friends program where it worked perfectly. I also looked at his code in case he has done something different, but he said that my code is good, so I am absolutely not sure what it might be...

[dragan]

Can you try this query as a test?

 

$query = "SELECT Sammelgang_DB.Short_Description, Sammelgang_DB.Status, Sammelgang_DB.Wunschtermin FROM Sammelgang_DB WHERE Sammelgang_DB.Status LIKE 'Angefordert' ORDER BY Sammelgang_DB.Wunschtermin"

[Whenever]

I solved the problem, thank you for your help ! The error handler was very very useful

[water]

Glad it was that easy!