Jump to content
SnArF

SQL/Acces select query error

Recommended Posts

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?


My scripts: _ConsoleWriteLog | _FileArray2D

 

 

 

Share this post


Link to post
Share on other sites
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)

 


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
mLipok

Someone an idea?

btw.

You should use Com Error Handler with this :
 

ObjEvent()

Look in examples from HelpFile


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2018-03-17

Share this post


Link to post
Share on other sites
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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 :)

 


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2018-03-17

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

  • Similar Content

    • FrancescoDiMuro
      By FrancescoDiMuro
      Good evening everyone
      I'm working on a little project of mines, and I was trying to use WMI Object.
      The question which I don't find an answer is: 
      Once I do the query with WMI Object, something like "SELECT * FROM Win32_LogonSession", instead of specify the field of the collection returned, ( i.e. $colItems.Caption ), can I loop though each property and each value of the property, writing so one row of code only?
      Hope my question was clear enough.
      Thanks in advance.

      Best Regards.
    • dangr82
      By dangr82
       
      I have created this function for a database, but I can not make it work. I always have two error messages: "not an error" ... and the file created, in the script directory, does not contain anything.
      Global $sDBName = "Hen.db" Func DatabaseTable() Local $sConnDB _SQLite_Startup() If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite.dll Can't be Loaded!") Exit -1 EndIf $sConnDB = _SQLite_Open($sDBName) If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't Load Database!") Exit -1 EndIf If Not _SQLite_Exec($sDBName, 'CREATE TABLE Animal ("Name", "Age");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) If Not _SQLite_Exec($sDBName, 'INSERT INTO Animale VALUES ("Charlie","5");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) _SQLite_Close($sConnDB) _SQLite_Shutdown() EndFunc  
    • Ambient
      By Ambient
      I am trying to build a list from a query run against a sybase db. This part works fine. My problem is this, I want to display values in a list depending 
      on the value of certain fields  i.e. See below which works for two. Unfortunately, I need it to work for 3 values.  I don't know how I can do this for 3.  I did try doing a select case but I was getting errors.
       
      i.e. if value =0  then "Card Check" if Value = - Then "Card Spend" Else "Money Added"
      The below immediate iif works brilliantly for two but as I say I need to build this with 3 different values. 
       
       
      $idItem = GUICtrlCreateListViewItem( _iif($adoRs.Fields("TransactionAmount").value =0, "Card check","Money Added") 
       
      Any help appreciated.
    • argumentum
      By argumentum
      ..let's say I store in a mysql column "[1,2,11,12,123]".
      How can I make s query to get all those rows that is 1, or is 12, etc ?
      I'm using  mysqlnd 5.0.11-dev - 20120503 
      Any advise on what technique I should use is welcomed
      Thanks
      PS: ..I'm adding a comma to each end, as in  [,1,2,11,12,123,] and will search with like %,12,% .
      Not pretty but I'm in a hurry and can't come up with a better solution =/
    • damon
      By damon
      @JLogan3o13 I apologize, I did not think of it that way.  I have attached all the code and the Ini File information.  Please let me know if I need to add anything else to help understand what is happening.  thank you.
       
       
       
      Guys, I apologize in advance as I did not search for my answer before posting.  I just could not figure out a way to search that made since so I decided to go ahead and post my question.
       
      Getting to it.
      Background: this is being used to validate file names before moving to a new location.  though i have not included all the code, below is what i am having a problem with.
       I have an ini file that i am reading values from. Func ConfigDefineVars() -- I read the value of $ValidationRDA and everything is good then I set 
      $CaseRDALocationCheck = '$SplitFile[$ValidationRDALocation]' <> $ValidationRDA
      the code that will be used in the select case in Func FileSplitCount().  The problem i have is that even though the numbers = each other they are not recognizing as =.  
      This is what i am getting in my log file i create.
      2017-12-04 14:09:53 : RDA was 2403 should have been 2403:
      If I move $SplitFile[$ValidationRDALocation] <> $ValidationRDA to the Case line under the function it works correctly.
       
      I think this has to do with my use of ' ' around the '$SplitFile[$ValidationRDALocation]' but  i don't know what option i have in Func ConfigDefineVars I am not ready to define $SplitFile.  I have tried adding it to my Global Vars at the top of my script but that did not seem to help.
      There will be more fail this is just the first one in the select case.
       
      Any help or ideas would be greatly appreciated.
       
      thanks,
      Damon
      Example of a filename used.
      DWRSSD-37087-95-026.%-064.00-Tatum Family %-%-1230 Academy Rd-%-PERM-TRUE-2403.pdf
      This file will fail on the 95, it should be 095.  The problem i am getting is with the 2403 it fails even though the ini file value ValidationRDA = 2403 
       
       
      Ini File:
      [SSD_FolderPaths]
      pendingFolder=C:\Users\bg01152\Desktop\SSD Test\pending
      reviewFolder=C:\Users\bg01152\Desktop\SSD Test\review
      completedFolder=C:\Users\bg01152\Desktop\SSD Test\Completed
      FileNetProperties=DocumentTitle,ZipCode,County,MapAndGroupID,ParcelID,PropertyOwner,Subdivision,StreetAddress,LotNumber,DocumentType,Approved,RDANumber
      ValidationDocumentTitle=DWRSSD
      ValidationDocumentTitleLocation=1
      ValidationCountyCodeLocation=3
      ValidationRDA=2403
      ValidationRDALocation=12
      Validation3=TRUE
      Validation3Location=11
      Validation4=PERM
      Validation4Location=10
       
       
      #include <Array.au3> #include <File.au3> #include <MsgBoxConstants.au3> ;------------Global Vars --------------------- Global $SplitFile, $aFileList, $FileLog, $dFolder, $sFolder, $cFolder, $ConfigFile, $t Global $ValidationDocumentTitle, $ValidationDocumentTitleLocation, $ValidationCountyCodeLocation, $ValidationRDA Global $ValidationRDALocation, $Validation3, $Validation3Location, $Validation4, $Validation4Location, $FileNetPropertiesSplit Global $CaseCountyCheck, $CaseRDALocationCheck, $CaseValidation3LocationCheck, $CaseValidation4LocationCheck, $SplitFile ;;--------------------------------Check and Read Config files-------------------------------- $SysConfigFile = @ScriptDir & "\SysConfig.ini" ;----------------PreCheck for Config File-------------- If FileExists ($SysConfigFile) <> 1 Then Exit EndIf ;----------------End PreCheck-------------------------- $FileNetEXE = @ScriptDir & "\" & IniRead ($SysConfigFile, "FileNetUploader","FileName","") $delimiter = IniRead ($SysConfigFile, "FileInformation", "Delimiter","") $Filter = IniRead ($SysConfigFile, "FileInformation", "Filter", "") $ConfigFile = @ScriptDir & "\Config.ini" ;----------------PreCheck for Config File-------------- If FileExists ($ConfigFile) <> 1 Then Exit EndIf ;----------------End PreCheck-------------------------- ;;-------------------------------------------------------------------------------------------- $Sections = IniReadSectionNames ($ConfigFile) ;MsgBox (0,"test", $Sections[0] & @CRLF & $Sections[1] & @CRLF & $Sections[2] & @CRLF & $Sections[3] & @CRLF & $Sections[4]);TESTING ONLY - DELETE WHEN DONE $p = 0 Do $t = 0 ;used for precheck $p = $p + 1 ConfigDefineVars($Sections[$p]) ;MsgBox(0,"ConfigDefineVars", $sFolder & @CRLF & $dFolder & @CRLF & $cFolder);TESTING ONLY - DELETE WHEN DONE PreCheck($dFolder, $sFolder, $cFolder) ;Runs a Pre-check to make sure folder structure exists before running the program If $t = 0 Then ;MsgBox(0,"PreCheck Run", "Running next functions");TESTING ONLY - DELETE WHEN DONE ListArray($sFolder, $Filter) ;Puts File Names in String Array ;_ArrayDisplay ($aFileList, $Sections[$p]) If $t = 0 Then FileSplitCount($dFolder, $aFileList) ;Takes filename String Array and splits by $delimiter ;MsgBox(0,"PreCheck Run2", "Running split function");TESTING ONLY - DELETE WHEN DONE Else ;MsgBox(0,"PreCheck Run2", "Skipping split function");TESTING ONLY - DELETE WHEN DONE EndIf Else ;MsgBox(0,"PreCheck Run", "Skipping next functions");TESTING ONLY - DELETE WHEN DONE EndIf Until $p = $Sections[0] ExitScript() ;Exit script Function Func ConfigDefineVars($SectionsNum);Defines Variables from config file Sections $sFolder = IniRead ($ConfigFile, $SectionsNum, "pendingFolder","") ;Pending Folder, Folder that is awaiting the process $dFolder = IniRead ($ConfigFile, $SectionsNum, "reviewFolder","") ;Review Folder, Files that did not pass validation check and division needs to review $cFolder = IniRead ($ConfigFile, $SectionsNum, "completedFolder","") ;Completed Folder, Once process is completed this would be location files get moved to $FileNetProperties = IniRead ($ConfigFile, $SectionsNum, "FileNetProperties","") $FileNetPropertiesSplit = StringSplit ($FileNetProperties,",") $ValidationDocumentTitle = IniRead ($ConfigFile, $SectionsNum, "ValidationDocumentTitle","") $ValidationDocumentTitleLocation = IniRead ($ConfigFile, $SectionsNum, "ValidationDocumentTitleLocation","") $ValidationCountyCodeLocation = IniRead ($ConfigFile, $SectionsNum, "ValidationCountyCodeLocation","") $ValidationRDA = IniRead ($ConfigFile, $SectionsNum, "ValidationRDA","") $ValidationRDALocation = IniRead ($ConfigFile, $SectionsNum, "ValidationRDALocation","") $Validation3 = IniRead ($ConfigFile, $SectionsNum, "Validation3","") $Validation3Location = IniRead ($ConfigFile, $SectionsNum, "Validation3Location","") $Validation4 = IniRead ($ConfigFile, $SectionsNum, "Validation4","") $Validation4Location = IniRead ($ConfigFile, $SectionsNum, "Validation4Location","") If $ValidationCountyCodeLocation = 999 Then $CaseCountyCheck = 1 <> 1 Else ;MsgBox (0,"test of county code", "location = " &$ValidationCountyCodeLocation) $CaseCountyCheck = StringLen('$SplitFile[$ValidationCountyCodeLocation]') <> 3 ; Checks for 3 digit County Code EndIf If $ValidationRDALocation = 999 Then $CaseRDALocationCheck = 1 <> 1 Else $CaseRDALocationCheck = '$SplitFile[$ValidationRDALocation]' <> $ValidationRDA ; Checks for 4 Digit RDA EndIf If $Validation3Location = 999 Then $CaseValidation3LocationCheck = 1 <> 1 Else $CaseValidation3LocationCheck = '$SplitFile[$Validation3Location]' <> $Validation3 ; Checks that Approved = True EndIf If $Validation4Location = 999 Then $CaseValidation4LocationCheck = 1 <> 1 Else $CaseValidation3LocationCheck = '$SplitFile[$Validation4Location]' <> $Validation4 ; Checks that Document Type = PERM EndIf EndFunc Func ValidationCheck ($check1) $blank = StringLen ($check1) If $check1 Then EndIf EndFunc Func PreCheck($dFolder, $sFolder, $cFolder) ;----------------PreCheck for Destination Folder-------------- If FileExists ($dFolder) <> 1 Then $t = 1 Return Else $FileLog = FileOpen($dFolder & "\FileLog.log", 1) EndIf ;----------------End PreCheck-------------------------- ;----------------PreCheck for Source Folder-------------- If FileExists ($sFolder) <> 1 Then _FileWriteLog($FileLog, "Path to Pending Folder -- " & $sFolder & " -- does not exist") $t = 1 Return EndIf ;----------------End PreCheck-------------------------- ;----------------PreCheck for Completed Folder--------- If FileExists ($cFolder) <> 1 Then _FileWriteLog($FileLog, "Path to Completed Folder -- " & $cFolder & " -- does not exist") $t = 1 Return EndIf ;----------------End PreCheck-------------------------- EndFunc Func ListArray($sFolder, $Filter) $aFileList = _FileListToArray($sFolder, $Filter, 1) ;Create an array of files from the source folder filtering by filetype ;in the config.ini files for the specified section If @error = 1 Then ;MsgBox($MB_SYSTEMMODAL, "", "Path was invalid.") _FileWriteLog($FileLog, "Path to File(s) is Invalid") $t = 1 Return EndIf If @error = 4 Then ;MsgBox($MB_SYSTEMMODAL, "", "No file(s) were found.") _FileWriteLog($FileLog, "No File(s) were found, exiting.") $t = 1 Return EndIf EndFunc ;==>ListArray  
       
       
       
×