SQL Query question

[synxer]

Hey everyone, I have been working on a little tool to use to check to see if someones username is in a database. 

I have been reading through the forums the past week trying to find something, but have been unable too.

So here is what I am having an issue with, the query is working properly and will display the results of the field if it finds the user name in a message box.  But if the user name doesn't existno results it will give me a:

AutoIt Error

Line 16  (File "C:testapp.exe)"

Error: The requested action with this object has failed. 

Here is the code:

$constrim="DRIVER={SQL Server};SERVER=db-server;DATABASE=DB"
$adCN = ObjCreate ("ADODB.Connection") ;<==Create SQL connection
$adCN.Open ($constrim) ;<==Connect with required credentials
;MsgBox(0,"",$constrim )
if @error Then
  MsgBox(0, "ERROR", "Failed to connect to the database")
  Exit
Else
  MsgBox(0, "Success!", "Connection to database successful!")
EndIf
$UserID = InputBox("Username Checker", "Enter User's Username")
$sQuery= "Select * from UserAccount WHERE UserName = '"&$UserID&"'"
$result= $adCN.Execute($sQuery)
  MsgBox(0,"Users Found:",$result( "ProperName" ).Value )
$adCN.Close ;==>Close the database

Just wondering if anyone had any hints or tips on how to make it display a msgbox that no results were found?

Thanks

[DW1]

Untested.  Does this work?

$result = $adCN.Execute($sQuery)
If IsObj($result) Then
    MsgBox(0, "Users Found:", $result("ProperName").Value)
Else
    MsgBox(0, "Error", "Query returned no results")
EndIf

Edited July 17, 2013 by danwilli

[synxer]

Thanks for the quick reply danwilli, but unfortunately it is acting the same way. :

The requested action with this object has failed.:
MsgBox(0, "Users Found:", $result("ProperName").Value)
MsgBox(0, "Users Found:", $result("ProperName").Value^ ERROR
->15:16:27 AutoIT3.exe ended.rc:1
>Exit code: 1    Time: 5.237

the debug from launching it from the script editor.

[DW1]

I'm unable to test with a database at the moment, but does $result.count return anything useful?  If not, I'll test against a database later and reply with something tested.

[synxer]

Hey Dan, thanks for the reply again.  I messed around with $result.count but it didnt give me much useful information either :.  I hope I'm not bugging you too much, I am still pretty new to autoit and not having much luck with this little bit of code.

[DW1]

I'm in the office today so I had access to a database.  EOF is the property you are looking for.

If $result.EOF Then
    MsgBox(0,'EOF', 'We have not moved the record pointer and we are already past the first record, so there were no results')
Else
    MsgBox(0, "Users Found:", $result("ProperName").Value)
EndIf

Edited July 18, 2013 by danwilli

[synxer]

You are the man!

Thank you so much for the help, I really appreciate it.

[DW1]

You're welcome.  You could also install a custom error handler so errors are not fatal.

Something like this:

$oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")
$adCN = ObjCreate("ADODB.Connection") ;<==Create SQL connection

;...

Func MyErrFunc()
    Local $sErr
    For $oErr In $adCN.Errors
        $sErr = ''
        $sErr &= "Description: " & $oErr.Description & @CRLF
        $sErr &= "Help context: " & $oErr.HelpContext & @CRLF
        $sErr &= "Help file: " & $oErr.HelpFile & @CRLF
        $sErr &= "Native error: " & $oErr.NativeError & @CRLF
        $sErr &= "Error number: " & $oErr.Number & @CRLF
        $sErr &= "Error source: " & $oErr.Source & @CRLF
        $sErr &= "SQL state: " & $oErr.SQLState
        ConsoleWrite($sErr)
    Next
EndFunc   ;==>MyErrFunc

 

Though, avoiding the errors in the first place is a better practice.  Sometimes though, it's nice to get details on the error when debugging and you are not sure why you are getting the error.