Skysnake

[solved] sqlite rowid to combobox

4 posts in this topic

#1 ·  Posted (edited)

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



#2 ·  Posted (edited)

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
1 person likes this

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

beautiful, thank you. 


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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

    • FrancescoDiMuro
      By FrancescoDiMuro
      Good morning
      I'm working on a little project, and I was wondering if there's a way ( sure there is ) to insert data to a ComboBox control everytime the user insert some text in the ComboBox which is not in the ComboBox data. I'll try to explain with an example:
      - Form with some data ( ComboBox is empty );
      - User prompt something in the ComboBox: if what the user prompts is not in the ComboBox values ( at the moment empty ), then insert the value in the ComboBox values, in order to have, next time the user prompts the form, the value that he entered.
      More pratical example:
      - First call of the form, ComboBox empty;
      - I prompt "A" in the ComboBox;
      - Second call, in the ComboBox I should see "A";
      - I prompt "B" in the ComboBox;
      - Third call, I should see "A"
                                                 "B";
      - And so on...
      Everytime the form is called, I re-create it, so I think I can't use _GUICtrlComboBox_AddStrings().
      I tried with
      Global $strCboStrings = "" ; When the user prompt the form, the code below is executed. ; I.E. : User prompt "A", after the if I should have "A"; ; In the second call, If I write "B", I should see "A" ; "B"; ; If I write "A" again, in the combobox values should not be any changes. If Not StringInStr($strCboStrings, GUICtrlRead($cboVoiceCategory)) <> 0 Then $strCboStrings &= $strCboStrings & "|" & GUICtrlRead($cboVoiceCategory) EndIf Any suggestion?
      Thanks
    • kstriyhon
      By kstriyhon
      hello Comunity i am new to autoit and i bump into a pitfall regarding the creation of objects in a dinamyc way but more especific  creating combobox at runtime
      i need many comboboxes and sofar i createdthem manually 
      like this 
      $idCombo_1 = GUICtrlCreateCombo("", 500, 40, 130, 21)     $idCombo_2 = GUICtrlCreateCombo("", 500, 70, 130, 21)     $idCombo_3 = GUICtrlCreateCombo("", 500, 100, 130, 21)     $idCombo_4 = GUICtrlCreateCombo("", 500, 130, 130, 21)     $idCombo_5 = GUICtrlCreateCombo("", 500, 160, 130, 21)     $idCombo_6 = GUICtrlCreateCombo("", 500, 190, 130, 21)     $idCombo_7 = GUICtrlCreateCombo("", 500, 220, 130, 21)     $idCombo_8 = GUICtrlCreateCombo("", 500, 250, 130, 21)     $idCombo_9 = GUICtrlCreateCombo("", 500, 280, 130, 21)     GUICtrlSetData($idCombo_1, "Item1|Item2|Item3|Item4|Item5")     GUICtrlSetData($idCombo_2, "Item1|Item2|Item3|Item4|Item5")     GUICtrlSetData($idCombo_3, "Item1|Item2|Item3|Item4|Item5")     GUICtrlSetData($idCombo_4, "Item1|Item2|Item3|Item4|Item5")     GUICtrlSetData($idCombo_5, "Item1|Item2|Item3|Item4|Item5")     GUICtrlSetData($idCombo_6, "Item1|Item2|Item3|Item4|Item5")     GUICtrlSetData($idCombo_7, "Item1|Item2|Item3|Item4|Item5")     GUICtrlSetData($idCombo_8, "Item1|Item2|Item3|Item4|Item5")     GUICtrlSetData($idCombo_9, "Item1|Item2|Item3|Item4|Item5") now i need this example but create them at runtime as there are more than 9 combos what i need thanks in advance kind regards
    • FrancescoDiMuro
      By FrancescoDiMuro
      Good evening guys
      I am working on a little project, in which I have to retrieve 8000+ rows of data from a table, from a SQLite database, to populate a combobox 
      This is what I tried 'til now, but it still takes about 12 seconds to populate the combobox.
      _SQLite_Exec($objDatabase, "BEGIN TRANSACTION;") If _SQLite_GetTable($objDatabase, "SELECT DISTINCT Comune FROM LISTA_COMUNI;", $arrRisultatoQuery, $intRighe, $intColonne) = $SQLITE_OK Then For $i = 2 To UBound($arrRisultatoQuery) - 1 If $i < UBound($arrRisultatoQuery) - 1 Then GUICtrlSetData($cbo_ComuneNascita, $arrRisultatoQuery[$i] & "|") Else GUICtrlSetData($cbo_ComuneNascita, $arrRisultatoQuery[$i]) EndIf Next _SQLite_Exec($objDatabase, "COMMIT;") Are there any other solution to retrieve 8000+ records from a SQLite database?
      Thank you very much  
    • FrancescoDiMuro
      By FrancescoDiMuro
      Good morning community!
      I am working on a script which read from a text file ( .txt ) and should import all the content in a SQLite3 DB, in order to execute some queries that should be difficult to execute on a text file.
      So, I was looking for something very very fast, because the file could be very large ( I don't know exaclty how much can became big, but I know a lot of rows, it's a log file ... )
      I found the "Import method", but I don't know If I can implement it in a query ( @jchd, it's your turn!  )
      Do you know some methods that I can implement in my script to have a very very fast import of thousands and thousands rows in a SQLite3 DB?
      Thanks a lot
      Francesco