Loading Data to a GUI Input Box

[tbohon]

Attempting to build a GUI interface to a simple SQLite database.  Have everything working to the point where I need to load the GUI text (input) boxes with data from an array which I have loaded from a SQLite query.  Plan to start displaying the database at record #0 (first record) and let GUI buttons dictate which record I go to next.  Simple, yes but it's the way I learn things 'for good' ... a simple example incorporating all of the different code elements that I'll need to know.

 

Any hints on how to do the actual data load from the array?  I've tried a half-dozen things without success and am hitting a wall.

 

And I appreciate your forbearance in advance - been a while since I used AutoIt and am having to relearn things I probably once knew.  The joys of being older than dirt  

 

Thanks.

 

Tom

 

[tbohon]

RESOLVED!  Was using KODA to create the GUI but couldn't get the handles for the controls to work with GUICtrlSetData() but when I created the GUI manually it's working just fine and dandy.  I've learned my one thing for today, time for a nap or something I think.  

 

Thanks.

 

Tom

Edited May 25, 2016 by tbohon

[tbohon]

So ... thought it would only be fair if I put my solution out here for others to reference if needed.

Here is the data file (d:/au3work2/contacts.csv) used for the original build:

Mikey Mike,22,Chicago IL,mikey@mike.com
Fred Flintstoner,225,Redrock UT,freddie@rockville.net
Homer Simpson,99,Someplace US,homer@simpson.net

First step was to write the following script to create and load an SQLite database and then print the contents out to the console for verification:

expandcollapse popup

#include <SQLite.au3>
#include <SQLite.dll.au3>

local $myarray, $line, $aRow, $path = "d:/au3work2/contacts.csv"
local $handle = FileOpen($path, 0)
Global $hQuery

Local $sSQliteDll = _SQLite_Startup()

If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite3.dll Can't be Loaded!" & @CRLF & @CRLF & _
            "Not FOUND in @SystemDir, @WindowsDir, @ScriptDir, @WorkingDir, @LocalAppDataDir\AutoIt v3\SQLite")
    Exit -1
EndIf

$MyDB = _SQLite_Open("d:\au3work2\MyDB2")

_SQLite_Exec(-1, "CREATE TABLE persons (Name VARCHAR(20), Age INTEGER, Address VARCHAR(30), Email VARCHAR(30));")

while 1
    $line = FileReadLine($handle)
    if @error then ExitLoop
    $myarray = StringSplit($line, ",")
    _SQLite_Exec(-1, "INSERT INTO persons VALUES ('" & $myarray[1] & "','" & $myarray[2] & "','" & $myarray[3] & "','" & $myarray[4] & "');")
WEnd

_SQLite_Close()

$MyDB2 = _SQLite_Open("d:\au3work2\MyDB2")

_SQLite_Query(-1, "SELECT * FROM persons ORDER BY Age", $hQuery)

While _SQLite_FetchData($hQuery, $aRow, False, False) = $SQLITE_OK
   ConsoleWrite(StringFormat(" %-20s  %-5s  %-30s  %-30s ", $aRow[0], $aRow[1], $aRow[2], $aRow[3]) & @CRLF)
WEnd

_SQLite_QueryFinalize($hQuery)

_SQLite_Close()

_SQLite_Shutdown()

 

Once I was satisfied with this and had a working database I set out to write a simple script to pull all 4 fields from the database, one record at a time, and to allow the user to move around (first, previous, next and last record buttons).  So I wasn't constantly hitting the SQLite database I did a simple select and loaded the values into a 2-D array from which I drew the display values.  Obviously this isn't set up to add any records (that's my next step) to the SQLite database, just display what is already there.

 

Here is that code:

expandcollapse popup

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <Array.au3>


local $hGUI = GUICreate( "SQLite DB Tester", 277, 262)

GUICtrlCreateLabel("Name", 48,32,32,17)
GUICtrlCreateLabel("Age", 48,64,23,17)
GUICtrlCreateLabel("Address", 48,96,42,17)
GUICtrlCreateLabel("Email", 48,128,30,17)

Global $txtName = GUICtrlCreateInput("", 104,29,121,21)
Global $txtAge = GUICtrlCreateInput("", 104,61,30,21)
Global $txtAddress = GUICtrlCreateInput("", 104,93,121,21)
Global $txtEmail = GUICtrlCreateInput("", 104,125,121,21)

$btnFirst = GUICtrlCreateButton("First", 64,176,35,25)
$btnPrev = GUICtrlCreateButton("Previous", 96,176,51,25)
$btnNext = GUICtrlCreateButton("Next", 144,176,35,25)
$btnLast = GUICtrlCreateButton("Last", 176,176,35,25)
$btnExit = GUICtrlCreateButton("EXIT", 104,216,75,25)

GUISetState(@SW_SHOW, $hGUI)

# SQLite setup

Local $aRow
Global $hQuery, $indx = 0, $nr_recs

Local $sSQLiteDLL = _SQLite_Startup()

if @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite3.dll Can't be Loaded!" & @CRLF & @CRLF & _
            "Not FOUND in @SystemDir, @WindowsDir, @ScriptDir, @WorkingDir, @LocalAppDataDir\AutoIt v3\SQLite")
    Exit -1
EndIf

$MyDB = _SQLite_Open("d:\au3work2\MyDB2")

_SQLite_Query(-1, "SELECT * FROM persons", $hQuery)

$nr_recs = 0

While _SQLite_FetchData($hQuery, $aRow, False, False) = $SQLITE_OK
   $nr_recs = $nr_recs + 1
Wend

_SQLite_QueryFinalize($hQuery)

MsgBox(0,"Record Count",$nr_recs)

Global $myarray[$nr_recs][4]

_SQLite_Query(-1, "SELECT * FROM persons ORDER BY Age", $hQuery)

While _SQLite_FetchData($hQuery, $aRow, False, False) = $SQLITE_OK
   ConsoleWrite(StringFormat(" %-20s  %-5s  %-30s  %-30s ", $aRow[0], $aRow[1], $aRow[2], $aRow[3]) & @CRLF)
   $myarray[$indx][0] = $aRow[0]
   $myarray[$indx][1] = $aRow[1]
   $myarray[$indx][2] = $aRow[2]
   $myarray[$indx][3] = $aRow[3]
   $indx = $indx + 1
WEnd

_SQLite_QueryFinalize($hQuery)

_SQLite_Close()

_SQLite_Shutdown()

$indx = 0
show_screen()

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit

        Case $btnFirst
           $indx = 0
           show_screen()

        Case $btnPrev
            if $indx = 0 Then
              MsgBox(0, "Access Error!","Attempted to access non-existent record!")
           Else
              $indx = $indx - 1
              show_screen()
           EndIf

        Case $btnNext
            if $indx+1 >= $nr_recs Then
               MsgBox(0, "Access Error at EOF!","Attempted to read beyond EOF!")
            Else
               $indx = $indx + 1
               show_screen()
            EndIf

         Case $btnLast
            $indx = $nr_recs - 1
            show_screen()

        Case $btnExit
            Exit

    EndSwitch
 WEnd

 func show_screen()
   GUICtrlSetData($txtName, $myarray[$indx][0])
   GUICtrlSetData($txtAge, $myarray[$indx][1])
   GUICtrlSetData($txtAddress, $myarray[$indx][2])
   GUICtrlSetData($txtEmail, $myarray[$indx][3])
 EndFunc

 

Is this the most efficient way to do things and could it be improved?  No and Yes in that order.  However, after 50 years in IT (mostly) as a software developer I always go with the KISS principle when trying out new languages and new ideas.  Once I've completed the 'Update', 'Edit' and 'Delete' functions and have them working I'll step back, take a long look over the entire code set and see where I could improve it.

 

Feel free to ask any questions although I think this is pretty straightforward ... and I'll post the completed 'full' program when I get it done in the next week or so.

 

Best!

 

Tom

Edited May 25, 2016 by tbohon

[AutoBert]

7 hours ago, tbohon said:

Here is the data file (d:/au3work2/contacts.csv) used for the original build:

Mikey Mike,22,Chicago IL,mikey@mike.com
Fred Flintstoner,225,Redrock UT,freddie@rockville.net
Homer Simpson,99,Someplace US,homer@simpson.net

 

Thanks for the email from Homer, i never found until yet.

[tbohon]

17 hours ago, AutoBert said:

Thanks for the email from Homer, i never found until yet.

Glad I could be of assistance