Jump to content
Skysnake

[solved] sqlite rowid to combobox

Recommended Posts

Skysnake

Hi all

I am comfortable with SQL and doing okay with Lists, ListViews and Comboboxes.  What I need to do is this: READ row from SQL, place SOME of this row into a combo list, MODIFY data in the Combo, READ the Combo and WRITE back to SQL as an update.

So, I want to modify existing SQL data in a combo box and then update the SQL to reflect the combo changes.

My specific problem, is that I do not know how to handle the rowid in the combo environment.  I do not want to show it to the user.

I have another project where the ID is in fact displayed on screen in the combo, so there it is easy, read the value and run an update.  Now, I need to hide the ID from the user, but not lose it.

Help? Suggestions? Examples?  I have been searching for some time now, but can't find any related posts.

Thanks for reading

 

Edited by Skysnake

Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites
kylomas

Skysnake,

Is this anything at all like what you are looking for?

#include <GUIConstantsEx.au3>
#include <array.au3>
#include <sqlite.au3>
#include <GuiComboBox.au3>

#AutoIt3Wrapper_Add_Constants=n

_SQLite_Startup()
_SQLite_Open()

Local $sql, $aRows, $iCols, $iRows, $LastSelected
$sql = 'create table T1 (Fname, MI, Lname);'
$sql &= 'insert into T1 values("Thomas","R","Jones");'
$sql &= 'insert into T1 values("William","X","Smith");'
$sql &= 'insert into T1 values("James","T","Kirk");'
$sql &= 'insert into T1 values("Rastus","P","Badass");'
_SQLite_Exec(-1, $sql)

Local $gui010 = GUICreate('SQLite _rowid_ Example')
GUICtrlCreateLabel('Middle Initial', 140, 15, 100, 20)
Local $cMI = GUICtrlCreateCombo('', 140, 30, 100, 50)
Local $dummyenter = GUICtrlCreateDummy()
GUICtrlCreateLabel('Hit [ENTER] to update DB entry', 110, 70, 200, 20)

_pop_combo()

GUISetState()

Local $aAcKeys[1][2] = [["{Enter}", $dummyenter]]
GUISetAccelerators($aAcKeys)

While 1
    Switch GUIGetMsg()
        Case $gui_event_close
            Exit
        Case $cMI
            $LastSelected = _GUICtrlComboBox_GetCurSel($cMI)
        Case $dummyenter
            If $LastSelected = '' Then ContinueLoop
            ConsoleWrite($aRows[$LastSelected][0] & @CRLF)
            _SQLite_Exec(-1, 'update T1 set MI = ' & _SQLite_FastEscape(GUICtrlRead($cMI)) & ' where _rowid_ = ' & $aRows[$LastSelected+1][0] & ';')
            _pop_combo()
    EndSwitch
WEnd

Func _pop_combo()

    _SQLite_GetTable2d(-1, 'select _rowid_, MI from T1', $aRows, $iRows, $iCols)

    _arraydisplay($aRows)

    Local $str = '|'

    For $i = 1 To UBound($aRows) - 1
        $str &= $aRows[$i][1] & '|'
    Next
    GUICtrlSetData($cMI, $str, $aRows[1][1])

    $LastSelected = ''

EndFunc   ;==>_pop_combo

kylomas 

Edit: Original posting deleted, corrected version posted (_rowid_ used incorrectly).

 

Edited by kylomas
Corrected code
  • Like 1

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
Skysnake

beautiful, thank you. 


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites
Skysnake

a slight modification. 

#include <GUIConstantsEx.au3>
#include <array.au3>
#include <sqlite.au3>
#include <GuiComboBox.au3>

#AutoIt3Wrapper_Add_Constants=n

_SQLite_Startup()
_SQLite_Open("sql2combo.db3")

Local $sql, $aRows, $iCols, $iRows, $LastSelected
$sql = 'create table T1 (Fname, MI, Lname);'
$sql &= 'insert into T1 values("Thomas","R","Jones");'
$sql &= 'insert into T1 values("William","X","Smith");'
$sql &= 'insert into T1 values("James","T","Kirk");'
$sql &= 'insert into T1 values("Rastus","P","Badass");'
_SQLite_Exec(-1, $sql)

Local $gui010 = GUICreate('SQLite _rowid_ Example')
GUICtrlCreateLabel('Middle Initial', 140, 15, 100, 20)
Local $cMI = GUICtrlCreateCombo('', 140, 30, 100, 50)
Local $dummyenter = GUICtrlCreateDummy()
GUICtrlCreateLabel('Hit [ENTER] to update DB entry', 110, 70, 200, 20)

_pop_combo()

GUISetState()

Local $aAcKeys[1][2] = [["{Enter}", $dummyenter]]
GUISetAccelerators($aAcKeys)

While 1
    Switch GUIGetMsg()
        Case $gui_event_close
            Exit
        Case $cMI
            ; add one to combo for SQL rowid
            $LastSelected = _GUICtrlComboBox_GetCurSel($cMI)+1
            ConsoleWrite("$LastSelected: " & $LastSelected & @CRLF)
            ;Local $showSelected=$LastSelected+1
        Case $dummyenter
            If $LastSelected = '' Then ContinueLoop
            ConsoleWrite("SQL Row: " & $aRows[$LastSelected][0] & @CRLF)
            _SQLite_Exec(-1, 'update T1 set MI = ' & _SQLite_FastEscape(GUICtrlRead($cMI)) & ' where _rowid_ = ' & $aRows[$LastSelected][0] & ';')
            _pop_combo()
    EndSwitch
WEnd

Func _pop_combo()

    _SQLite_GetTable2d(-1, 'select _rowid_, MI from T1', $aRows, $iRows, $iCols)

    _arraydisplay($aRows)

    Local $str = '|'

    For $i = 1 To UBound($aRows) - 1
        $str &= $aRows[$i][1] & '|'
    Next
    GUICtrlSetData($cMI, $str, $aRows[1][1])

    $LastSelected = ''

EndFunc   ;==>_pop_combo

 

Edit:

I have added this to the Wiki

https://www.autoitscript.com/wiki/Snippets_(_Combo_)

 

 

 

 

Edited by Skysnake
Added to the Wiki

Skysnake

Why is the snake in the sky?

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

    • Eminence
      By Eminence
      Hello,
      Using SQLite, I was trying to select a specific column with having duplicate entries removed by using the DISTINCT function. 
      Local $sQuery = "SELECT DISTINCT supervisor, COUNT(DISTINCT employee_name) FROM data_db What the above snippet does is just list only one supervisor but with the total count of unique employee names in the whole database when it should be listing out all supervisor entries from the database and list the total count of employees per each supervisors. If I remove the COUNT function, it does list out all unique entries of supervisor names from the list. Attached is a screenshot of an example database as well. 
      Any help will be much appreciated. Thank you!

    • Seminko
      By Seminko
      Hey,
      I have a script that uses SQLite. It worked without a hiccup on my W7 system.
      However, last week I bought a new machine, installed W10 and autoit, and now I'm getting an error during _SQLite_Startup.
      Can anyone advise on how to find what's wrong? Tried checking $__g_hPrintCallback_SQLite but that returns nothing.
       
      Tried downloading the latest version of the dll from the link in the function page and I also replaced the default SQLite.dll.au3 that came with AutoIt with the one that came with the latest SQLite version zip.
       
      Relevant parts of my script:
      #include <SQLite.au3> #include <SQLite.dll.au3> _SQLite_Startup(@ScriptDir & "\Config\sqlite3_x64.dll", False, 1) If @error Then MsgBox(16, "SQLite Error", "SQLite3.dll Can't be Loaded! - " & $__g_hPrintCallback_SQLite & @CRLF & @CRLF & "Exiting application / Zavolej Honzovi") Exit -1 EndIf  
      Thanks
      S.
    • FrancescoDiMuro
      By FrancescoDiMuro
      Good evening everyone
      I am building a management for the company I work with, and I just imported a real amount of rows ( about 29000 ), in my SQLite DB.
      The thing I am not understanding, is the time that the script takes to build this amount of rows in the ListView.
      I didn't measure it, but I think it took 2 minutes or so to create each ListView item...
      It is normal that it takes so much time?
      What can I do to improve the creation of the items?

      Here's the code I am using to query and to create ListView items...
      ; Articles ListView: Global $lvwArticles = GUICtrlCreateListView("ID|Fornitore|Codice|Descrizione|EU|Prezzo|Sconto Applicato|Note", 14, 87, 1507, 660, BitOR($GUI_SS_DEFAULT_LISTVIEW,$LVS_SORTASCENDING,$LVS_SORTDESCENDING), BitOR($WS_EX_CLIENTEDGE,$LVS_EX_GRIDLINES,$LVS_EX_FULLROWSELECT)) ; Query $strQuery = "SELECT * FROM ARTICOLI;" ; Query Execution _SQLite_GetTable2d($objDatabase, $strQuery, $arrResult, $intRows, $intColumns) If @error Then ; Error Handling Else ; Cleaning the ListView _GUICtrlListView_DeleteAllItems($lvwArticles) If @error Then ; Error Handling Else ; No records in the Table If UBound($arrResult) < 2 Then ; Error Handling Else _GUICtrlListView_BeginUpdate($lvwArticles) For $intCounter = 1 To UBound($arrResult) - 1 $strListViewItem = $arrResult[$intCounter][0] & "|" & _ $arrResult[$intCounter][1] & "|" & _ $arrResult[$intCounter][2] & "|" & _ $arrResult[$intCounter][3] & "|" & _ $arrResult[$intCounter][4] & "|" & _ $arrResult[$intCounter][5] & "|" & _ $arrResult[$intCounter][6] & "|" & _ $arrResult[$intCounter][7] $objListViewItem = GUICtrlCreateListViewItem($strListViewItem, $lvwArticles) Next _GUICtrlListView_EndUpdate($lvwArticles) EndIf EndIf EndIf Thanks in advance


      Best Regards.
    • odaylton
      By odaylton
      Hello everyone ,
      Text translated from Portuguese by google - please apologize for any errors

      Once again I need a light from the gurus of this wonderful website
      I am fanatic by combobox for its usability but I am not able to uncertain images within lists ...
      I know uncertain icons, and cursors but I am not able to insert gif or jpg images.
      1) How to solve this ...
      2) will the path is to convert gif into bmp and then uncertain ... how to do that?
      #include <GUIConstantsEx.au3> #include <GuiImageList.au3> #include <GuiListView.au3> #include <WinAPI.au3> #include <WindowsConstants.au3> ;dados de mouse #include <WinAPIRes.au3> ;COMBO #include <GuiComboBoxEx.au3> ;Fonts #include <FontConstants.au3> ;obter imagem de arquivo #include <GDIPlus.au3> #include <WinAPIGdi.au3> Example() Func Example() Local $hGUI, $hImage, $hCombo, $hFont Local $idListview, $hImage Local $sWow64 = "" Local $Pasta = "C:\WINDOWS\Cursors\3dgarro.cur" Local $PastaROMs = @MyDocumentsDir & "\Stella\ROMs\" ;com barra no final $hGUI = GUICreate("ImageList", 500, 310) GUISetFont(14, 400, 0, "Arial") ;<<<<<<<<<<this does not work for _GUICtrlComboBoxEx_Create $hCombo = _GUICtrlComboBoxEx_Create($hGUI, "", 2, 2, 494, 200) ;GUICtrlSetFont($hCombo, 14, 400, 0, "Arial");<<<<<<<<<<It does not work ; ;Create a handle to a font object $hFont = _WinAPI_CreateFont(30, 0, 0, 0, 400, False, False, False, $DEFAULT_CHARSET, $OUT_DEFAULT_PRECIS, $CLIP_DEFAULT_PRECIS, $DEFAULT_QUALITY, 0, 'Arial') ;Pass the handle to _WinAPI_SetFont _WinAPI_SetFont($hCombo, $hFont) $idListview = GUICtrlCreateListView("", 2, 50, 494, 250, BitOR($LVS_SHOWSELALWAYS, $LVS_NOSORTHEADER, $LVS_REPORT)) _GUICtrlListView_SetExtendedListViewStyle($idListview, BitOR($LVS_EX_FULLROWSELECT, $LVS_EX_GRIDLINES, $LVS_EX_DOUBLEBUFFER)) GUISetState(@SW_SHOW) ; Initialize GDI+ library _GDIPlus_Startup() ; Load images $hImage = _GUIImageList_Create(120, 70, 6, 2) ;32,32,5,5) ; $DadosCombo = "Escolha|" & _GUIImageList_Add($hImage, _GUICtrlComboBoxEx_CreateSolidBitMap($hCombo, 0x0000FF, 120, 70)) ;Local $aCursors[] = [32650, 32512, 32515, 32649, 32651, 32513, 32648, 32646, 32643, 32645, 32642, 32644, 32516, 32514] For $i = 1 To 10 $sFile = FileOpenDialog("Please select an image", $PastaROMs, "Image (*.jpg;*.png;*.bmp;*.gif;*.tif)", BitOR($FD_PATHMUSTEXIST, $FD_FILEMUSTEXIST)) If @error Then Exit MsgBox(BitOR($MB_TOPMOST, $MB_ICONERROR), "Error", "No image file has been selected", 30) $a = _GDIPlus_BitmapCreateFromFile ($sFile);<<<<< ok If @error Or Not $hImage Then MsgBox(BitOR($MB_TOPMOST, $MB_ICONERROR), "Error", "This file isn't supported by GDIPlus!") Else $b=_GUIImageList_Add($hImage, $a);<<<<<< Return -1 $DadosCombo &= "|" & $i & "_img_princ.gif""|" & $b EndIf Next _GUICtrlListView_SetImageList($idListview, $hImage, 1) _GUICtrlComboBoxEx_SetImageList($hCombo, $hImage) ; Add columns _GUICtrlListView_AddColumn($idListview, "Icons - Mouses", 350) ; Add items $a = StringSplit($DadosCombo, "|") For $i = 1 To $a[0] Step 2 _GUICtrlListView_AddItem($idListview, $a[$i], $a[$i + 1]) _GUICtrlComboBoxEx_AddString($hCombo, $a[$i], $a[$i + 1], $a[$i + 1]) Next ;AutoIt_Debugger_Command:Disable_Debug ; Loop until the user exits. Do Until GUIGetMsg() = $GUI_EVENT_CLOSE GUIDelete() ;AutoIt_Debugger_Command:Enable_Debug EndFunc ;==>Example tks
    • aiter
      By aiter
      I  possibly misunderstood the value of combo boxes, but I thought you could type a value, press enter and the combo box would store that value.
      The reason for wanting this is to store a history of typed commands into the combo box (accepted by pressing enter).
       
      After hours of trying to trap enter on a combobox I realized that the enter event was not registering.
      I had code like this (which used to work on a input box), but it does not for a combobox (because it does not accept enter)
      unc WM_COMMAND($hWnd, $Msg, $wParam, $lParam) Local $nNotifyCode = BitShift($wParam, 16) Local $nID = BitAND($wParam, 0x0000FFFF) If $hWnd = $CmdForm Then If $nID = $ComboInput Then ; useed to work on a normal input box If _IsPressed('0D') Then ; enter was pressed, respond DoCmd() Anyone able to help?
×