Sign in to follow this  
Followers 0
synxer

SQL Query question

8 posts in this topic

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by danwilli

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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 by danwilli

Share this post


Link to post
Share on other sites

You are the man!

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0