SQL/Acces select query error

[SnArF]

Hello,

I,m connecting to a access mdb, it works fine except when i use a select query and the requested data does not exists, then i get an error.

; Example

Local $dbName = @ScriptDir & "\test.mdb"

$dbCon = ObjCreate("ADODB.Connection") ; Create DataBase connection
$dbCon.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $dbName)

$sQuery = "select * from sourcefiles where Text='test'"
$result = $dbCon.Execute($sQuery)

MsgBox(0, "", $result.Fields( "ID" ).Value)

If "test" exists in column Text then i get the ID number from column ID

But if it doesn't exist i get an error:

MsgBox(0, "", $result.Fields( "ID" ).Value)
MsgBox(0, "", $result.Fields( "ID" )^ ERROR

If the value "test"does not exist i just want $result to be 0 or ""

Someone an idea?

[jchd]

This should do:

Local $dbName = @ScriptDir & "\test.mdb"

$dbCon = ObjCreate("ADODB.Connection") ; Create DataBase connection
$dbCon.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $dbName)

$sQuery = "select * from sourcefiles where Text='test'"
$result = $dbCon.Execute($sQuery)

If Not $result.EOF Then
    MsgBox(0, "", $result.Fields( "ID" ).Value)
    ; ...
EndIf

I'm not familiar with Access, but typical SQL engine would allow that as well:

$sQuery = "select coalesce(ID, 0) from sourcefiles where Text='test'"
$result = $dbCon.Execute($sQuery)

MsgBox(0, "", $result.Fields(0).Value)

 

[mLipok]

Someone an idea?

btw.

You should use Com Error Handler with this :
 

ObjEvent()

Look in examples from HelpFile

[jchd]

Yes but in this case this is not an actual error: it's indeed to be expected that some SELECT returns an empty set. So testing for the resultset being empty seems appropriate here.

Of course it's always a good idea to have a genuine COM error handler setup.

[mLipok]

Yes but in this case this is not an actual error: it's indeed to be expected that some SELECT returns an empty set. So testing for the resultset being empty seems appropriate here.

Of course it's always a good idea to have a genuine COM error handler setup.

For this I use "btw."

Sorry for so short help/support but actually I get 100 mail / day  and 50 phone call / day.
I hope my hell will end in next 4 weeks