Aphotic

Simple SQL Query Tool

7 posts in this topic

#1 ·  Posted (edited)

Hey Guys,

I've been using AutoIT for about 3 years now, lurking the forums, scouring documentation, and creating things relevant to my job function.

I work entry level IT and utilizing AutoIT has earned me much respect in the workplace. I made my first application on the level of sharing with the community that has no relevance to my company other than the data that is used with it.

Our tech's were using development software to make simple pre-written queries. I was made aware of the process in order to assist in automating it. We found that the software they were using was being sunset in our environment so they'd need a replacement; and upon me realizing that they only needed a simple query tool, decided to 'homegrow' a simple app and save some hefty licensing fees.

I built in some versatility to the SQL database you can connect to but it is only tested working with a Sybase 15 system (note the example connection string).

I'd love to hear some suggestions and critique. I'm sure there are some editing functionalities that I could implement into the query edit box that I haven't bothered looking at. (other than CTRA+A to select all)

 

Thanks guys!

#include <WindowsConstants.au3>
#include <GUIConstants.au3>
#include <GUIConstantsEx.au3>
#include <GuiEdit.au3>
#include <GuiListView.au3>
#include <File.au3>
#include <Crypt.au3>
#include <Date.au3>

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")
Local $unique = "SQL-Query-Tool" ;TO ALLOW ONLY ONE INSTANCE OF THE TOOL
If WinExists($unique) Then
   MsgBox(0,'Duplicate Process', 'This script is already running....' & @CRLF & "If the window is not visible, terminate the process from Task Manager" & @CRLF & 'This instance will terminate after clicking OK')
   Exit
EndIf
AutoItWinSetTitle($unique)

Local $fResized = False, $GUIs, $temp, $Qlist[0][3], $x, $selQ[2], $guics[4]
Local $conName, $conStr, $conPW, $selC, $selConn, $sqlErr
Local $fh

Local $readme = "You may delete this entry if desired once other ." & @CRLF & _
"Although it is prefaced with 'zz' for sorting" & @CRLF & _
@CRLF & _
"-Select a Query from the drop-down" & @CRLF & _
"-Note: changing selection will not lose" & @CRLF & _
"        changes made during this session" & @CRLF & _
"-Click Run Query to produce results" & @CRLF & _
@CRLF & _
"-Select from 'Query Options' to create new," & @CRLF & _
"        edit name, save, and delete queries" & @CRLF & _
@CRLF & _
"-Click 'Connection Settings' to change" & @CRLF & _
"        server settings"

$conName = IniReadSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Name")
If @error = 1 Then
   DirCreate(@AppDataDir & "\SQL-Query-Tool\")
   IniWrite(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Name", "1", "Connection_Name_Placeholder")
   IniWrite(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-String", "1", "Connection_String_Placeholder")
   IniWrite(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Password", "1", "Connection_Password_Placeholder")
EndIf

$selConn = IniRead(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Selected", "Number", "Placeholder")
If $selConn = "Placeholder" Then
   DirCreate(@AppDataDir & "\SQL-Query-Tool\")
   IniWrite(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Selected", "Number", "1")
   $selConn = 1
EndIf

$temp = _FileListToArray(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\")
If @error = 1 Or @error = 4 Then
   DirCreate(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\")
   FileWrite(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\zz_Readme.txt", $readme)
   $temp = _FileListToArray(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\")
EndIf

QueryLoad(False)
$selQ[0] = $Qlist[1][0]

Local $GUI = GUICreate("Simple SQL Query Tool", 260, 320, -1, -1, BitOR($WS_SIZEBOX,$WS_MINIMIZEBOX));, @DesktopWidth-275, @DesktopHeight-500)
GUIRegisterMsg($WM_SIZE, 'MY_WM_SIZE')
Local $querysel = GUICtrlCreateCombo("", 5, 5, 155, 25, $CBS_DROPDOWNLIST)
GUICtrlSetData($querysel, $Qlist[0][1], $Qlist[1][0])
GUICtrlSetResizing(-1, 552)
Local $queryopt = GUICtrlCreateCombo("Query Options", 165, 5, 90, 20, $CBS_DROPDOWNLIST)
GUICtrlSetData($queryopt, "Save to File|New|Edit Name|Delete|Open Query Folder")
GUICtrlSetResizing(-1, 552)
Local $tab = GUICtrlCreateTab(5, 35, 235, 200)
$qtab = GUICtrlCreateTabItem("            Query            ")
Local $query = GuiCtrlCreateEdit("", 10, 60, 240, 170)
GUICtrlSetFont(-1, 9, 0, 0, "Lucida Console")
GUICtrlSetData($query, $Qlist[1][1])
$rtab = GUICtrlCreateTabItem("            Results            ")
Local $result = GuiCtrlCreateEdit("", 10, 80, 240, 150)
GUICtrlSetFont(-1, 9, 0, 0, "Lucida Console")
Local $status = GUICtrlCreateLabel("Last Ran: None", 15, 62, 500)
GUICtrlSetResizing(-1, 802)
GUICtrlCreateTabItem("")
Local $run = GUICtrlCreateButton("Run Query", 5, 270, 130, 25)
GUICtrlSetResizing(-1, 584)
Local $settings = GUICtrlCreateButton("Connection Settings", 140, 270, 115, 25)
GUICtrlSetResizing(-1, 584)

Local $consel = GUICtrlCreateCombo("", 5, 5, 390, 25, $CBS_DROPDOWNLIST)
GUICtrlSetResizing(-1, 802)
Local $newc = GUICtrlCreateButton("New", 400, 5, 40, 20)
GUICtrlSetResizing(-1, 802)
Local $edic = GUICtrlCreateButton("Edit", 445, 5, 40, 20)
GUICtrlSetResizing(-1, 802)
Local $delc = GUICtrlCreateButton("Delete", 490, 5, 40, 20)
GUICtrlSetResizing(-1, 802)
Local $test = GUICtrlCreateButton("Test Connection", 540, 5, 95, 20)
GUICtrlSetResizing(-1, 802)
Local $return = GUICtrlCreateButton("Return to Query", 640, 5, 95, 20)
GUICtrlSetResizing(-1, 802)
Local $divider = GUICtrlCreateLabel("",-5,30,760,3,BitOR($SS_SUNKEN,$WS_BORDER))
GUICtrlSetResizing(-1, 802)
Local $cnamel = GUICtrlCreateLabel("Connection Name:", 15, 45)
GUICtrlSetResizing(-1, 802)
Local $cname = GUICtrlCreateInput("", 110, 41, 200, 20)
GUICtrlSetResizing(-1, 802)
Local $cstringl = GUICtrlCreateLabel("                Connection String" & @CRLF & _
   "Replace your password with *PW*.   For example:" & @CRLF & _
   "Provider=ASEOLEDB;User ID=-USERID-;Password=*PW*;Data Source=-SERVER-:-PORT-;Initial Catalog=-DatabaseName-" & @CRLF & _
   "If you need assistance with your specific connection string try using:     https://www.connectionstrings.com/", 5, 70, 740, 60)
GUICtrlSetResizing(-1, 802)
Local $cstring = GUICtrlCreateInput("", 10, 125, 725, 20)
GUICtrlSetResizing(-1, 802)
Local $pwl = GUICtrlCreateLabel("Password:", 10, 165)
GUICtrlSetResizing(-1, 802)
Local $pw = GUICtrlCreateInput("", 65, 161, 340, 20, BitOR($ES_PASSWORD, $ES_AUTOHSCROLL))
GUICtrlSetResizing(-1, 802)
Local $savc = GUICtrlCreateButton("Save", 480, 161, 100, 20)
GUICtrlSetResizing(-1, 802)
Local $canc = GUICtrlCreateButton("Cancel", 600, 161, 100, 20)
GUICtrlSetResizing(-1, 802)

Guimod("SETTINGS", $GUI_HIDE+1)
GuiMod('SETTINGS-ENTRY', $GUI_DISABLE)
$selC = $selConn
GuiMod('RELOAD-CON')

Local $hSelAll = GUICtrlCreateDummy()
Dim $AccelKeys[1][2] = [["^a", $hSelAll]]
GUISetAccelerators($AccelKeys, $GUI)

GUISetState(@SW_SHOW)

Local $nMsg
While 1
   $nMsg = GUIGetMsg()
   Switch $nMsg               ;Checks if a button has been pressed
   Case $querysel
      $selQ[1] = GUICtrlRead($querysel)
      If $selQ[0] <> $selQ[1] Then QuerySelect()
      GUICtrlSetState($qtab, $GUI_SHOW)
   Case $queryopt
      Switch GUICtrlRead($queryopt)
      Case "Save to File"
         $fh = FileOpen(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\" & GuiCtrlRead($querysel) & ".txt", 2)
         FileWrite($fh, GuiCtrlRead($query))
         $Qlist[_ArraySearch($Qlist, GuiCtrlRead($querysel), 0, 0, 0, 0, 1, 0)][1] = GuiCtrlRead($query)
         $Qlist[_ArraySearch($Qlist, GuiCtrlRead($querysel), 0, 0, 0, 0, 1, 0)][2] = False
         GUICtrlSetData($qtab, "            Query            ")
      Case "New"
         $temp = InputBox("New Query Name","Enter a name for your new query." & @CRLF & "Please be aware only letters, numbers, underscores, and spaces are allowed." & @CRLF & "Symbols will be removed.", "", "", Default, Default, Default, Default, 0, $GUI)
         $temp = StringRegExpReplace($temp, "[^A-Za-z0-9 _]", "")
         If $temp <> "" And _ArraySearch($Qlist, $temp, 0, 0, 0, 0, 1, 0) = -1 Then
            FileWriteLine(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\" & $temp & ".txt", "--First Line Placeholder")
            QueryLoad()
            GUICtrlSetData($querysel, "")
            GUICtrlSetData($querysel, $QList[0][1], $temp)
            $selQ[1] = GUICtrlRead($querysel)
            If $selQ[0] <> $selQ[1] Then QuerySelect()
         Else
            MsgBox(0,'Invalid Input','Invalid name entered. (Symbols and Duplicate Names are not allowed)', 0, $GUI)
         EndIf
      Case "Edit Name"
         $temp = InputBox("Edit Query name","Enter a new name for your query." & @CRLF & "Please be aware only letters, numbers, underscores, and spaces are allowed." & @CRLF & "Symbols will be removed.", GuiCtrlRead($querysel))
         $temp = StringRegExpReplace($temp, "[^A-Za-z0-9 _]", "")
         If $temp <> "" And _ArraySearch($Qlist, $temp, 0, 0, 0, 0, 1, 0) = -1 Then
            FileMove(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\" & GuiCtrlRead($querysel) & ".txt", @AppDataDir & "\SQL-Query-Tool\Saved-Queries\" & $temp & ".txt")
            $selQ[0] = $temp
            QueryLoad()
            GUICtrlSetData($querysel, "")
            GUICtrlSetData($querysel, $QList[0][1], $temp)
            $selQ[1] = GUICtrlRead($querysel)
            If $selQ[0] <> $selQ[1] Then QuerySelect()
         Else
            MsgBox(0,'Invalid Input','Invalid name entered. (Symbols and Duplicate Names are not allowed)', 0, $GUI)
         EndIf
      Case "Delete"
         If UBound($Qlist) = 2 Then
            MsgBox(0,'Deletion Error','You may not delete the only saved query', 0, $GUI)
         Else
            If MsgBox(4, 'Confirm Deletion', "Are you sure you want to delete the query named: " & @CRLF & GUICtrlRead($querysel), 0, $GUI) = 6 Then
                  FileDelete(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\" & GuiCtrlRead($querysel) & ".txt")
               QueryLoad()
               GUICtrlSetData($querysel, "")
               GUICtrlSetData($querysel, $QList[0][1], $Qlist[1][0])
               $selQ[1] = GUICtrlRead($querysel)
               $selQ[0] = $selQ[1]
               GUICtrlSetData($query, $Qlist[1][1])
            EndIf
         EndIf
      Case "Open Query Folder"
         ShellExecute(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\")
      EndSwitch
      GUICtrlSetData($queryopt, 'Query Options', 'Query Options')
   Case $run
      GuiMod("RUNNING", $GUI_DISABLE)
      ToolTip("Executing SQL query, please wait...")
      $sqlCon = ObjCreate("ADODB.Connection")
      $sqlCon.Mode = 16  ; shared
      $sqlCon.CursorLocation = 3 ; client side cursor
      Local $connString = StringReplace(GuiCtrlRead($cstring), "*PW*", GuiCtrlRead($pw))
      $sqlCon.Open($connString)
      If @error Then
         MsgBox(0, "Fail", "Failed to connect to the database")
      Else
         $sqlCon.CommandTimeout = 60
         GUICtrlSetData($result, ProcessQuery(GuiCtrlRead($query)))
         GUICtrlSetState($rtab , $GUI_SHOW)
         GuiCtrlSetData($status, "Last Ran: " & GuiCtrlRead($querysel) & " - " & StringRight(_NowCalc(), 8))
      EndIf
      ToolTip("")
      GuiMod("RUNNING", $GUI_ENABLE)
   Case $settings
      ToolTip("Loading...")
      $selC = $selConn
      GuiMod('MAIN', $GUI_HIDE)
      GuiMod('RELOAD-CON')
      GuiMod('SETTINGS', $GUI_SHOW)
      ToolTip("")
   Case $consel
      If GUICtrlRead($consel) <> $selC Then
         $selC = _ArraySearch($conName, GUICtrlRead($consel), 0, 0, 0, 0, 1, 1)
         GUICtrlSetData($cname, $conName[$selC][1])
         GUICtrlSetData($cString, $conStr[$selC][1])
         GUICtrlSetData($pw, StringEncrypt(False, $conPW[$selC][1]))
         $selC = GUICtrlRead($consel)
      EndIf
   Case $newc
      GuiMod('SETTINGS-SELECT', $GUI_DISABLE)
      GuiMod('SETTINGS-ENTRY', $GUI_ENABLE)
      GuiCtrlSetData($consel, '*New-Connection-Entry*', '*New-Connection-Entry*')
      GuiCtrlSetData($cname, '')
      GuiCtrlSetData($cString, '')
      GuiCtrlSetData($pw, '')
      $selC = -1
   Case $edic
      GuiMod('SETTINGS-SELECT', $GUI_DISABLE)
      GuiMod('SETTINGS-ENTRY', $GUI_ENABLE)
      $selC = GUICtrlRead($consel)
      _ArrayDisplay($conName, "2")
      $selC = _ArraySearch($conName, $selC, 0, 0, 0, 0, 1, 1)
      GuiCtrlSetData($cname, $conName[$selC][1])
      $conName[$selC][1] &= "*"
      GuiCtrlSetData($cString, $conStr[$selC][1])
      GuiCtrlSetData($pw, StringEncrypt(False, $conPW[$selC][1]))
   Case $delc
      If UBound($conName) = 2 Then
         MsgBox(0,'Error','You can not delete the only connection setting.', 0, $GUI)
      Else
         $selC = GuiCtrlRead($consel)
         If MsgBox(4, 'Confirm Deletion', 'Are you sure you want to delete the connection: ' & $selC, 0, $GUI) = 6 Then
            ToolTip('Loading...')
            $selC = _ArraySearch($conName, $selC, 0, 0, 0, 0, 1, 1)
            $conName[$selC][1] = '*!*BLANK*!*'
            $conStr[$selC][1] = '*!*BLANK*!*'
            $conPW[$selC][1] = '*!*BLANK*!*'
            IniWriteSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Name", $conName)
            IniWriteSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-String", $conStr)
            IniWriteSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Password", $conPW)
            GuiMod('RELOAD-CON')
            ToolTip('')
         EndIf
      EndIf
   Case $savc
      If GuiCtrlRead($cname) = "" Or _ArraySearch($conName, GuiCtrlRead($cname), 0, 0, 0, 0, 1, 1) > -1 Then
         MsgBox(0,'Invalid Input',"You can't have a blank connection name or two connections with the same name", 0, $GUI)
      Else
         ToolTip("Loading...")
         If $selC = -1 Then
            For $i = 1 To UBound($conName)-1
               If $conName[$i][1] = '*!*BLANK*!*' Then
                  $selC = $i
                  $conName[$i][1] = GuiCtrlRead($cname)
                  $conStr[$i][1] = GuiCtrlRead($cstring)
                  $conPW[$i][1] = StringEncrypt(True, GuiCtrlRead($pw))
                  ExitLoop
               ElseIf $i = UBound($conName)-1 Then
                  $selC = UBound($conName)
                  _ArrayAdd($conName, UBound($conName) & "|" & GuiCtrlRead($cname))
                  _ArrayAdd($conStr, UBound($conStr) & "|" & GuiCtrlRead($cstring))
                  _ArrayAdd($conPW, UBound($conPW) & "|" & StringEncrypt(True, GuiCtrlRead($pw)))
               EndIf
            Next
         Else
            $conName[$selC][1] = GuiCtrlRead($cname)
            $conStr[$selC][1] = GuiCtrlRead($cstring)
            $conPW[$selC][1] = StringEncrypt(True, GuiCtrlRead($pw))
         EndIf
         IniWriteSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Name", $conName)
         IniWriteSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-String", $conStr)
         IniWriteSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Password", $conPW)
         GuiMod('SETTINGS-SELECT', $GUI_ENABLE)
         GuiMod('SETTINGS-ENTRY', $GUI_DISABLE)
         GuiMod('RELOAD-CON')
         ToolTip('')
      EndIf
   Case $canc
      ToolTip("Loading...")
      GuiMod('SETTINGS-SELECT', $GUI_ENABLE)
      GuiMod('SETTINGS-ENTRY', $GUI_DISABLE)
      GuiMod('RELOAD-CON')
      ToolTip('')
   Case $test
      $sqlCon = ObjCreate("ADODB.Connection")
      $sqlCon.Mode = 16  ; shared
      $sqlCon.CursorLocation = 3 ; client side cursor
      Local $connString = StringReplace(GuiCtrlRead($cstring), "*PW*", GuiCtrlRead($pw))
      $sqlCon.Open($connString)
      If @error Then
         MsgBox(0, "Fail", "Failed to connect to the database")
      Else
         MsgBox(0, "Success", "Successfully connected to the database")
      EndIf
   Case $return
      $selConn = _ArraySearch($conName, $selC, 0, 0, 0, 0, 1, 1)
      IniWrite(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Selected", "Number", $selConn)
      GuiMod('SETTINGS', $GUI_HIDE)
      GuiMod('MAIN', $GUI_SHOW)
   Case $hSelAll
      _SelectAllTextInEdit()
   Case $GUI_EVENT_CLOSE
      Exit
   EndSwitch
   If $fResized Then
      GuiMod('RESIZE')
      $fResized = False
   EndIf
   If $guics[0] = True Then _Input_Check($cname)
WEnd

Func QuerySelect()
   Local $oldp = _ArraySearch($Qlist, $selQ[0], 0, 0, 0, 0, 1, 0)
   Local $old = GuiCtrlRead($query)
   If $Qlist[$oldp][1] <> $old Then
      $Qlist[$oldp][1] = $old
      $Qlist[$oldp][2] = True
   EndIf
   Local $newp = _ArraySearch($Qlist, $selQ[1], 0, 0, 0, 0, 1, 0)
   GUICtrlSetData($query, $Qlist[$newp][1])
   $selQ[0] = $selQ[1]
   If $Qlist[$newp][2] = False Then
      GUICtrlSetData($qtab, "            Query            ")
   Else
      GUICtrlSetData($qtab, "    Query (unsaved)   ")
   EndIf
EndFunc

Func QueryLoad($reload = True)
   Local $qFiles = _FileListToArray(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\")
   Local $tQlist[UBound($qFiles)][3]
   Local $selT
   $tQlist[0][0] = $qFiles[0]
   $tQlist[0][1] = ""
   For $i = 1 To $tQlist[0][0]
      $tQlist[$i][0] = StringTrimRight($qFiles[$i], 4)
      If $reload Then
         $selT = _ArraySearch($Qlist, $tQlist[$i][0], 0, 0, 0, 0, 1, 0)
         If $selT > -1 Then
            $tQlist[$i][1] = $Qlist[$selT][1]
            $tQlist[$i][2] = $Qlist[$selT][2]
         Else
            $tQlist[$i][1] = FileRead(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\" & $qFiles[$i])
            $tQlist[$i][2] = False
         EndIf
      Else
         $tQlist[$i][1] = FileRead(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\" & $qFiles[$i])
         $tQlist[$i][2] = False
      EndIf
      $tQlist[0][1] &= StringTrimRight($qFiles[$i], 4)
      If $i < $tQlist[0][0] Then $tQlist[0][1] &= "|"
   Next
   $Qlist = $tQlist
EndFunc

Func ProcessQuery($input)
   Local $resultO = $sqlCon.Execute($input)
   If Not @error Then
      Local $resultT = ""
      Local $recordE = 0
      Local $resultA[2][0]
      Local $i, $z
      While $recordE = 0 and IsObj($resultO)
         Redim $resultA[2][0]
         For $oField In $resultO.Fields
            Redim $resultA[2][UBound($resultA, 2)+1]
            $resultA[1][UBound($resultA, 2)-1] = $oField.Name
         Next
         ReDim $resultA[UBound($resultA)+1][UBound($resultA, 2)]
         While Not $resultO.EOF
            ReDim $resultA[UBound($resultA)+1][UBound($resultA, 2)]
            $i = 0
            For $oField in $resultO.Fields
               $resultA[UBound($resultA)-1][$i] = StringStripWS(StringStripWS($oField.Value, 1), 2)
               $i += 1
            Next
            $resultO.MoveNext
         WEnd
         For $i = 0 To UBound($resultA, 2)-1
            $z = 0
            For $x = 1 To UBound($resultA)-1
               If StringLen($resultA[$x][$i]) > $z Then $z = StringLen($resultA[$x][$i])
            Next
            $resultA[0][$i] = $z
         Next
         For $i = 0 To UBound($resultA, 2)-1
            $z = ""
            For $x = 1 To $resultA[0][$i] + 4
               $z &= "-"
            Next
            $resultA[2][$i] = $z
         Next
         For $x = 1 To UBound($resultA)-1
            For $i = 0 To UBound($resultA, 2)-1
               $resultT &= $resultA[$x][$i]
               For $z = StringLen($resultA[$x][$i]) To $resultA[0][$i] + 4
                  $resultT &= " "
               Next
            Next
            $resultT &= @CRLF
         Next
         $resultT &= @CRLF & "Total Lines Returned: " & UBound($resultA)-3 & @CRLF
         $resultT &= "__________________________________________________________" & @CRLF & @CRLF & @CRLF
         $resultO = $resultO.NextRecordset
         $recordE = @error
      WEnd
      $resultO.Close
   Else
      Local $resultT = $sqlErr
   EndIf
   Return $resultT
EndFunc

Func GuiMod($action, $toggle = 0)
   Switch $action
      Case 'RESIZE'
         If ($GUIs[2] < 266 or $GUIs[3] < 306) And $guics[0] = False Then WinMove($GUI, '', $GUIs[0], $GUIs[1], 276, 336)
         GUICtrlSetPos($tab, 5, 35, $GUIs[2] - 25, $GUIs[3] - 106)
         GUICtrlSetPos($query, 10, 60, $GUIs[2] - 36, $GUIs[3] - 136)
         GUICtrlSetPos($result, 10, 80, $GUIs[2] - 36, $GUIs[3] - 156)
         GUICtrlSetPos($result, 10, 80)
      Case 'MAIN'
         GUICtrlSetState($querysel, $toggle)
         GUICtrlSetState($queryopt, $toggle)
         GUICtrlSetState($tab, $toggle)
         GUICtrlSetState($query, $toggle)
         GUICtrlSetState($result, $toggle)
         GUICtrlSetState($status, $toggle)
         GUICtrlSetState($run, $toggle)
         GUICtrlSetState($settings, $toggle)
      Case 'SETTINGS'
         If $toggle = $GUI_SHOW Then
            GUISetStyle(BitOR($WS_MINIMIZEBOX, $WS_CAPTION, $WS_SYSMENU))
            $GUIs = WinGetPos($GUI)
            $guics[0] = True
            $guics[1] = $GUIs[2]
            $guics[2] = $GUIs[3]
            $guics[3] = $GUIs[0]
            WinMove($GUI, '', 25, $GUIs[1], 750, 221)
         ElseIf $toggle = $GUI_HIDE Then
            GUISetStyle(BitOR($WS_MINIMIZEBOX, $WS_CAPTION, $WS_SYSMENU, $WS_SIZEBOX))
            $guics[0] = False
            WinMove($GUI, '', $guics[3], Default, $guics[1], $guics[2])
         ElseIf $toggle = $GUI_HIDE+1 Then
            $toggle -= 1
         EndIf
         GUICtrlSetState($consel, $toggle)
         GUICtrlSetState($newc, $toggle)
         GUICtrlSetState($edic, $toggle)
         GUICtrlSetState($delc, $toggle)
         GUICtrlSetState($test, $toggle)
         GUICtrlSetState($return, $toggle)
         GUICtrlSetState($divider, $toggle)
         GUICtrlSetState($cnamel, $toggle)
         GUICtrlSetState($cname, $toggle)
         GUICtrlSetState($cstringl, $toggle)
         GUICtrlSetState($cstring, $toggle)
         GUICtrlSetState($pwl, $toggle)
         GUICtrlSetState($pw, $toggle)
         GUICtrlSetState($savc, $toggle)
         GUICtrlSetState($canc, $toggle)
      Case 'SETTINGS-SELECT'
         GUICtrlSetState($consel, $toggle)
         GUICtrlSetState($newc, $toggle)
         GUICtrlSetState($edic, $toggle)
         GUICtrlSetState($delc, $toggle)
         GUICtrlSetState($test, $toggle)
         GUICtrlSetState($return, $toggle)
      Case 'SETTINGS-ENTRY'
         GUICtrlSetState($cnamel, $toggle)
         GUICtrlSetState($cname, $toggle)
         GUICtrlSetState($cstringl, $toggle)
         GUICtrlSetState($cstring, $toggle)
         GUICtrlSetState($pwl, $toggle)
         GUICtrlSetState($pw, $toggle)
         GUICtrlSetState($savc, $toggle)
         GUICtrlSetState($canc, $toggle)
      Case 'RUNNING'
         GUICtrlSetState($querysel, $toggle)
         GUICtrlSetState($queryopt, $toggle)
         GUICtrlSetState($query, $toggle)
         GUICtrlSetState($tab, $toggle)
         GUICtrlSetState($result, $toggle)
         GUICtrlSetState($run, $toggle)
         GUICtrlSetState($settings, $toggle)
      Case 'RELOAD-CON'
         $conName = IniReadSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Name")
         $conStr = IniReadSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-String")
         $conPW = IniReadSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Password")
         Local $sC = $conName
         _ArraySort($sC, 0, 1, 0, 1)
         GUICtrlSetData($consel, "")
         If $conName[$selC][1] <> "*!*BLANK*!*" Then
            GuiCtrlSetData($cname, $conName[$selC][1])
            GuiCtrlSetData($cString, $conStr[$selC][1])
            GuiCtrlSetData($pw, StringEncrypt(False, $conPW[$selC][1]))
            $selC = $conName[$selC][1]
         Else
            For $i = 1 To UBound($sC)-1
               If $sC[$i][1] <> '*!*BLANK*!*' Then
                  GuiCtrlSetData($cname, $sC[$i][1])
                  GuiCtrlSetData($cString, $conStr[$sC[$i][0]][1])
                  GuiCtrlSetData($pw, StringEncrypt(False, $conPW[$sC[$i][0]][1]))
                  $selC = $sC[$i][1]
                  ExitLoop
               EndIf
            Next
         EndIf
         For $i = 1 To UBound($sC)-1
            If $sC[$i][1] <> '*!*BLANK*!*' Then GUICtrlSetData($consel, $sC[$i][1], $selC)
         Next
   EndSwitch
EndFunc

Func MY_WM_SIZE($hWnd, $Msg, $wParam, $lParam)
   $GUIs = WinGetPos($GUI)
   $fResized = True
   Return $GUI_RUNDEFMSG
EndFunc  ;==>MY_WM_SIZE

Func StringEncrypt($bEncrypt, $sData, $sPassword = 'SQL')
   If $sData = "" Then Return ''
    _Crypt_Startup() ; Start the Crypt library.
    Local $sReturn = ''
    If $bEncrypt Then ; If the flag is set to True then encrypt, otherwise decrypt.
        $sReturn = _Crypt_EncryptData($sData, $sPassword, $CALG_RC4)
    Else
        $sReturn = BinaryToString(_Crypt_DecryptData($sData, $sPassword, $CALG_RC4))
    EndIf
    _Crypt_Shutdown() ; Shutdown the Crypt library.
    Return $sReturn
EndFunc   ;==>StringEncrypt

Func _Input_Check($hInput)
   Local $sText = GUICtrlRead($hInput)
   If StringRegExp($sText, "[^A-Za-z0-9 _]") Then GUICtrlSetData($hInput, StringRegExpReplace($sText, "[^A-Za-z0-9 _]", ""))
EndFunc

Func _SelectAllTextInEdit();will make select all text in any focused edit
    Local $theHandle = _WinAPI_GetFocus()
    Local $TheClass = _WinAPI_GetClassName($theHandle)
    If $TheClass = 'Edit' Then _GUICtrlEdit_SetSel($theHandle, 0, -1)
EndFunc

Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  $sqlErr = "We intercepted a COM Error !" & @CRLF & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext
  SetError(1)  ; to check for after this function returns
Endfunc

 

Edited by Aphotic

Share this post


Link to post
Share on other sites



Hi.

Interesting.

here is my list what you can do:

1.

do not post user name and host to forum

2.

make bigger GuiCtrlCreateEdit

 

3.

use:

#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7
#Tidy_Parameters=/sort_funcs /reel

at top of your script

and fix this 

Z:\TOOLs\Macro\FORUM\___FORUM -- z forum\Simple_SQL_Query_Tool.au3 - 0 error(s), 12 warning(s)

4.

What you are checking with this error ?

Are you sure here about this comment in MsgBox ?

$sqlCon.Open($connString)
            $sqlCon.CommandTimeout = 60
            If @error Then
                MsgBox(0, "Fail", "Failed to connect to the database")

5.

look in my signature or just click here:

specially to the attached _sql.au3

 

6.

search on the forum all my post about SQL and ADO.

 

7.

if you have some spare time look also here:

Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * How to post code on the forum * UDF-Spec Questions

 

Best regards,

mLipok

 


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 - BETA * 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 * Best coding practices * 

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: 2017-06-04

Share this post


Link to post
Share on other sites

... and beware of SQL attacks and injections!

1 person likes this

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

#4 ·  Posted (edited)

Hi.

Interesting.

here is my list what you can do:

1.

do not post user name and host to forum  Removed, thank you

2.

make bigger GuiCtrlCreateEdit   The window is re-sizable, it launches small because I will be automating the tool further in another script

 

3.

use:

#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7
#Tidy_Parameters=/sort_funcs /reel

at top of your script

and fix this  Not sure what this refers to but I get the concept and will search around and do so, thank you

4.

What you are checking with this error ?

Are you sure here about this comment in MsgBox ? Meant to check for a failed connection, I moved the Timeoutsetting to only if it connects successfully, thank you

$sqlCon.Open($connString)
            $sqlCon.CommandTimeout = 60
            If @error Then
                MsgBox(0, "Fail", "Failed to connect to the database")

5.

look in my signature or just click here: Will follow-up when I have more time, thank you

specially to the attached _sql.au3

 

6.

search on the forum all my post about SQL and ADO. Will follow-up when I have more time, thank you

 

7.

if you have some spare time look also here: Will follow-up when I have more time, thank you

Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * How to post code on the forum * UDF-Spec Questions

 

Best regards,

mLipok

 

 

... and beware of SQL attacks and injections! I have very limited SQL experience, I'll need to research and follow-up, thank you

 

 

 

Responses in bold, I'm out for now but will definitely look into those points that I wasn't able to, thanks a bunch!

 

Edited by Aphotic

Share this post


Link to post
Share on other sites

You are welcome.

mLipok

 


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 - BETA * 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 * Best coding practices * 

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: 2017-06-04

Share this post


Link to post
Share on other sites

#6 ·  Posted

hi,

I was wondering how this script works if dataset contains "blob" data? I have blob data in the mysql db and i was how i could show that data back to the user? The data stored is jpg, png, gif.

did you ever address the points above from the other folks? If so, how?

thanks! 

 

Share this post


Link to post
Share on other sites

#7 ·  Posted

You might get ideas from this post, even it uses SQLite as the engine, the display of BLOBs pictures can be transposed into your framework.

 


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

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

    • kawliga751
      By kawliga751
      I have a workplace GUI that has a search field function that pulls up customer records to allow changes. I am attempting to 1)LOGIN 2)Enter Search field, enter a number and then {ENTER} 3)change specific field once customer record loads. I have very limited experience in GUI manipulation but I am able to login using the below:
      #include <IE.au3> Const $navOpenInNewTab = 0x0800 Dim $oIE = _IECreate('http://asag.xxxxxxx.com/ASAG/index.htm') Call ("RacksheetSignIn") Func RacksheetSignIn () Local $username = _IEGetObjByName ($oIE,"uname") Local $password = _IEGetObjByName ($oIE,"pword") $oIE.Navigate2('http://asag.xxxxxxx.com/apps/RackSheets/LogInSAG/login.asp',2048) _IEFormElementSetValue ($username, "xxxxxxx") _IEFormElementSetValue ($password, "xxxxxxx") Send('{Enter}') EndFunc However I can't seem to use the _IEGetObjByName or Mouseclick to focus on the search field to allow me to enter my search criteria. When I "inspect element" in the Search field I get the attached:
       
      Any help would be MUCH appreciated
       
       
       

    • Jibberish
      By Jibberish
      I am having difficulties getting updated results from GUICtrlCreateInput. I wrote a small script to demonstrate what I am trying to do.
      This script reads a text file into an array and displays the Name and Number from the text file.
      The user can check a name and change the number, and the results are displayed in _ArrayDisplay.
      Only the Checked names are displayed in their original position. This is necessary for future
      plumbing.
      The problem with this script is the original number is returned, not the changed number. I cannot
      figure out how to get the updated number to be displayed.
      ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ; ; This script reads a text file into an array and displays the Name and Number from the text file. ; The user can check a name and change the number, and the results are displayed in _ArrayDisplay. ; Only the Checked names are displayed in their original position. This is necessary for future ; plumbing. ; ; The problem with this script is the original number is returned, not the changed number. I cannot ; figure out how to get the updated number to be displayed. ; ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ #include-once #include <MsgBoxConstants.au3> #include <array.au3> #include <Date.au3> #include <WinAPIFiles.au3> #include <AutoItConstants.au3> #include <Misc.au3> #include <File.au3> #include <GUIConstantsEx.au3> #include <StaticConstants.au3> Local $iMax = 6 ;Default number of Videos - 1 for the array counter Dim $aTextFile[$iMax][2] ; [start with 5 entries][Name, Number] Don't get confused! [Row][Column] Local $i = 0 Local $iLeft = 30 Local $iTop = 30 Local $sName Local $sNumber Local $aArray Local $iMaxCol = 5 Local $iRow = 0 Local $iCol = 0 Local $aNumberCount[$iMaxCol][2] $aArray1 = ReadFile() $aNamesNumbers = DisplayNames($aArray1) _ArrayDisplay($aNamesNumbers) ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ; ; ReadFile() places the contents of the NameNumber.txt file in an array ; ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Func ReadFile() Local $sFileData = @ScriptDir & "\NameNumber.txt" Local $iStrReturn = 0 Local $aArrayFile Local $aTextFile[$iMaxCol][2] Local $aName Local $sCheckBox1 Const $iL = 10 ; Count from Left for GUI so all buttons line up Local $sFill Local $iA = 0, $iN = 0 ; Put the Names into an Array _FileReadToArray($sFileData, $aArrayFile) For $iA = 0 To $aArrayFile[0] ; Step through the array looking for Names If StringInStr($aArrayFile[$iA], "Name:") Then $aName = StringSplit($aArrayFile[$iA],":") $sFill = $aName[2] If UBound($aTextFile) <= $iRow Then ; Resize the array when $iRow is equal to the element count in the array to prevent subscript error ReDim $aTextFile[UBound($aTextFile) + 1][$iMaxCol] EndIf $aTextFile[$iRow][$iCol] = $sFill $iRow += 1 EndIf Next $iCol = 1 $iRow = 0 $sFill = "" For $iA = 1 To $aArrayFile[0] ; Step through the array looking for Numbers If StringInStr($aArrayFile[$iA], "Number:") Then $aName = StringSplit($aArrayFile[$iA],":") $sFill = $aName[2] If UBound($aTextFile) <= $iRow Then ; Resize the array when $iRow is equal to the element count in the array to prevent subscript error ReDim $aTextFile[UBound($aTextFile) + 1][$iMaxCol] EndIf $aTextFile[$iRow][$iCol] = $sFill $iRow += 1 EndIf Next Return $aTextFile EndFunc ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ; ; Display the Name and Number ; ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Func DisplayNames($aArray1) Local $aName[$iMax] Local $aNumbers[$iMax] $iMMCount = UBound($aArray1) $iMMCount -=1 Local $iWidth = 300 Local $iLength = 300;$iMMCount * 30 GUISetFont(12) $hGUI = GUICreate("Edit / Update Number Test", $iWidth, $iLength, -1, -1) GUICtrlCreateLabel(" Name Number",1,5) ;$iTop += 30 ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ; ; Display Name with a Checkbox. Only Checked Names should be saved. ; ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ For $i = 0 to $iMMCount Step 1 $sNameText = $aArray1[$i][0] $iNameLength = StringLen($sNameText) $aName[$i] = GUICtrlCreateCheckbox($sNameText,$iLeft, $iTop) $iTop += 30 Next ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ; ; Display the number from the text file below each name. Allow the user to change the number and display the ; new number. ; ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ $iLeft = 60 GUIStartGroup() $iTop = 28 For $i = 0 to $iMMCount Step 1 $sNumberText = $aArray1[$i][1] $aNumberCount[$i][1] = $sNumberText $aNumberCount[$i][0] = GUICtrlCreateInput($sNumberText,$iLeft, $iTop, 50,18, $GUI_DOCKAUTO) GUICtrlSetPos($aNumberCount[$i][0],200) $iTop += 30 Next ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ; ; Read the checked names and (possibly updated) number ; ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ $iMMCount = UBound($aNumberCount) $iMMNewCount = $iMMCount - 1 Local $aGUICheckbox[$iMMCount] Local $aCheckedNameNumber[$iMMCount][2] $iLeft = 30 Local $idCloseGUI = GUICtrlCreateButton("Close",$iLeft, $iTop) GUISetState(@SW_SHOW) While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $idCloseGUI For $i = 0 To $iMMNewCount Step 1 Switch GUICtrlRead($aName[$i]) Case $GUI_CHECKED $aCheckedNameNumber[$i][0] = $aArray1[$i][0] Case $GUI_UNCHECKED EndSwitch Switch GUICtrlRead($aNumberCount[$i][0]) Case $aCheckedNameNumber[$i][1] = GUICtrlRead($aNumberCount[$i][1]) EndSwitch Next ExitLoop EndSwitch WEnd For $i = 0 to $iMMNewCount step 1 GUICtrlRead($aNumberCount[$i][0]) If $aCheckedNameNumber[$i][0] <> "" Then $aCheckedNameNumber[$i][1] = $aNumberCount[$i][1] EndIf Next GUIDelete($hGUI) Return $aCheckedNameNumber EndFunc This is the text file I am reading. If you want to try this out put the NameNumber.txt file in your script directory. It's attached to the post.
      * This is a dummy file with a Name and Number * The only purpose of this file is to read the updated Number. Name:Taggart Number:916 Name:Mongo Number:90 Name:Hedley Lamarr Number:22 Name:Bart Number:9999 Name:The Waco Kid Number:2244 If I change the number, the original number is displayed at the end, not the updated/modified number. I need the modified number to be displayed.
      Thanks in advance for any assistance!
      Jibberish
      NameNumber.txt
    • FMS
      By FMS
      Hello,
      As I was searching this forum I came along something intresting what was new to me.
      CUI/GUI hybrid.
      Whish sounds very usefull to me to compile it all into 1 script.
      So i was fideling around whit it but couldn't get it to work.
      In the real script (not this simplified version) i got a lot of functions whish i can use whan talking to it by commandline and switches.
      I could not get this to work and hope somebody could give me some pointers on how to make it work.
      Also I was hoping iff somebody could help me whit the /? function?
      (I don't know how to output the help text back to the commandline or the function that can help me do it)
      please advice and thanks in advanced.
      #AutoIt3Wrapper_UseUpx=n #AutoIt3Wrapper_UseX64=n #AutoIt3Wrapper_Change2CUI=y #include <Process.au3> #include <Array.au3> If $CmdLine[0] = 0 Then If _ProcessGetName(ProcessGetParent(@AutoItPID)) = "cmd.exe" Then ;ConsoleWrite("Program was started in CMD" & @CRLF) If Mod($CmdLine[0], 2) <> 0 Then Exit MsgBox(16, $sTitle, "Invalid number of parameters! has to be a multiple of 2!") If $CmdLine[0] = 0 Then Exit MsgBox(16, $sTitle, "No parameters passed!") For $i = 1 To $CmdLine[0] Step 2 If $CmdLine[$i] = "/switch" Then MsgBox(64, "Passed Parameters", "/switch is " & $CmdLine[$i+1]) If $CmdLine[$i] = "/?" Then MsgBox(64, "Passed Parameters", "/? " ) Next _ArrayDisplay($CmdLine) Else #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> $Form1 = GUICreate("Form1", 239, 65, 192, 124) $Button1 = GUICtrlCreateButton("Button1", 32, 24, 75, 25) $Button2 = GUICtrlCreateButton("Button2", 120, 24, 75, 25) GUISetState(@SW_SHOW) While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button1 button1() Case $button2 button2() EndSwitch WEnd EndIf EndIf Exit Func button1() MsgBox(16, "title", "button1()!") EndFunc Func button2() MsgBox(16, "title", "button1()!") EndFunc Func ProcessGetParent($i_PID) ;get PID from parent process done by SmOke_N Local $TH32CS_SNAPPROCESS = 0x00000002 Local $a_tool_help = DllCall("Kernel32.dll", "long", "CreateToolhelp32Snapshot", "int", $TH32CS_SNAPPROCESS, "int", 0) If IsArray($a_tool_help) = 0 Or $a_tool_help[0] = -1 Then Return SetError(1, 0, $i_PID) Local $tagPROCESSENTRY32 = _ DllStructCreate( _ "dword dwsize;" & _ "dword cntUsage;" & _ "dword th32ProcessID;" & _ "uint th32DefaultHeapID;" & _ "dword th32ModuleID;" & _ "dword cntThreads;" & _ "dword th32ParentProcessID;" & _ "long pcPriClassBase;" & _ "dword dwFlags;" & _ "char szExeFile[260]" _ ) DllStructSetData($tagPROCESSENTRY32, 1, DllStructGetSize($tagPROCESSENTRY32)) Local $p_PROCESSENTRY32 = DllStructGetPtr($tagPROCESSENTRY32) Local $a_pfirst = DllCall("Kernel32.dll", "int", "Process32First", "long", $a_tool_help[0], "ptr", $p_PROCESSENTRY32) If IsArray($a_pfirst) = 0 Then Return SetError(2, 0, $i_PID) Local $a_pnext, $i_return = 0 If DllStructGetData($tagPROCESSENTRY32, "th32ProcessID") = $i_PID Then $i_return = DllStructGetData($tagPROCESSENTRY32, "th32ParentProcessID") DllCall("Kernel32.dll", "int", "CloseHandle", "long", $a_tool_help[0]) If $i_return Then Return $i_return Return $i_PID EndIf While @error = 0 $a_pnext = DllCall("Kernel32.dll", "int", "Process32Next", "long", $a_tool_help[0], "ptr", $p_PROCESSENTRY32) If DllStructGetData($tagPROCESSENTRY32, "th32ProcessID") = $i_PID Then $i_return = DllStructGetData($tagPROCESSENTRY32, "th32ParentProcessID") If $i_return Then ExitLoop $i_return = $i_PID ExitLoop EndIf WEnd DllCall("Kernel32.dll", "int", "CloseHandle", "long", $a_tool_help[0]) Return $i_return EndFunc ;==>ProcessGetParent  
    • Jibberish
      By Jibberish
      Hello,
      I have used this forum to get help many times. I thought it was time to (hopefully) help others.
      I created a script that reads a text file with a list of videos, displays the videos in a GUI with checkboxes next to the names, and displays the selected videos. This will become a part of a larger script I am creating to test a video player. The tough part for me was creating the GUI and Dynamic list of videos. I had a lot of trouble finding samples to help me, but finally found one  written by Melba23. The link is in the code, so he gets credit for helping! I also have not used arrays much and they are very picky about looping through the arrays without getting the dreaded error " Array variable has incorrect number of subscripts or subscript dimension range exceeded."  However diligence paid off!
      To run this code, take the video names commented below and create a videos.txt file in your script execution directory. You can put however many video names in this list. Thus the dynamic features of the code.
      Cheers!
      Jibberish
      #include <MsgBoxConstants.au3> #include <StringConstants.au3> #include <array.au3> #include <File.au3> #include <GUIConstantsEx.au3> Local $sMediaFile = @ScriptDir & "\videos.txt" ;~ Videos in videos.txt are: ;~ bbb_1080_60s.mp4 ;~ bbb_1080_60s_1.mp4 ;~ bbb_1080_60s_2.mp4 ;~ tos_4K_60s_HEVC.mp4 ;~ tos_4K_60s_HEVC_1.mp4 ;~ tos_4K_60s_HEVC_2.mp4 ;~ ;~ Additional videos can be added to this list. The functions are Dynamic. Dim $aMediaManifest Local $aArrayFile Local $aVideos Local $sVideoName Local $i ; MAIN ; Put the Video File Names into an Array _FileReadToArray($sMediaFile, $aArrayFile) Local $iVideoCount = UBound($aArrayFile) -1 ; Get the number of videos - 1 to prevent errors _ArrayDelete($aArrayFile, 0) ;Counter just gets in the way ; Move backwards through the array deleting the blank lines For $i = $iVideoCount - 1 To 0 Step -1 If $aArrayFile[$i] = "" Then _ArrayDelete($aArrayFile, $i) EndIf Next $aVideos = DisplayVideos($aArrayFile) $iVideoCount = UBound($aArrayFile) -1 _ArrayDisplay($aVideos) ; Display the checked videos ;~ End of MAIN ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ; ; GUI to display Videos in checkboxes ; ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Func DisplayVideos($aArrayFile) Local $iTop = -1 Local $iLeft = -1 Local $iWidth Local $iHeight = $iVideoCount * 30 Local $iL = 10 Local $iT = 10 Local $iRow = 0 Local $aVideo Local $iA = 0, $iB = 0 Local $sFill = "" $iMMCount = UBound($aArrayFile) $iMMNewCount = $iMMCount - 1 Local $aGUICheckbox[$iMMCount] Local $aCheckedVideos[$iMMCount] ; Put the Video File Names into an Array $hGUI = GUICreate("Video Checkbox", $iLeft, $iTop, $iWidth, $iHeight) GUICtrlCreateLabel("Videos", 180, $iT) $iT = $iT + 30 ; This is a great example of using arrays to create GUI check boxes or radio buttons For $i = 0 To $iMMNewCount Step 1 $sMP4Text = $aArrayFile[$i] $aGUICheckbox[$i] = GUICtrlCreateCheckbox($sMP4Text, 30, $iT) $iT += 30 Next $idClose1 = GUICtrlCreateButton("Start", $iL, $iT) GUISetState(@SW_SHOW) ; This section reads the checkboxes and puts the video names in an array in their original position ; in case this is important (as it is to me) ; This was the toughest part to code, and I found no samples online until I saw Melba23's sample here: ; https://www.autoitscript.com/forum/topic/119843-dynamic-gui-problem/#comment-832672 ; I got this working with only a little modification. THANK YOU MELBA23 While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $idClose1 For $i = 0 To $iMMNewCount Step 1 Switch GUICtrlRead($aGUICheckbox[$i]) Case $GUI_CHECKED $aCheckedVideos[$i] = $aArrayFile[$i] Case $GUI_UNCHECKED EndSwitch Next ExitLoop EndSwitch WEnd GUIDelete($hGUI) Return $aCheckedVideos EndFunc ;==>DisplayVideos  
    • thoms
      By thoms
      Hello Forum,
      As said in the title, I'm searching a way to disable all tabstops in a GUI. My GUI includes 15 buttons and 3 edit controls.
      My questions:
      - is there a way to disable all tabstops at a time
      - is there a way to list all controls of a GUI and then disable tabstops one by one
      - or should I use the following line after each button creation:
      _WinAPI_SetWindowLong(GUICtrlGetHandle(-1), $GWL_STYLE, BitAND(_WinAPI_GetWindowLong(GUICtrlGetHandle(-1), $GWL_STYLE), BitNOT($WS_TABSTOP))) Thanks in advance for your valuable help!
      Thoms
      Edit: or maybe disable TAB key? I don't remember how to do!