Jump to content
Sign in to follow this  
Skysnake

[solved] sqlite rowid to combobox

Recommended Posts

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

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

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

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
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By dmob
      So I am trying to implement an archive system of sorts for my (SQLite) DB app. I wrote a function to attach a separate (archive) DB and
      sync the columns with main DB. If archive DB file does not exist, create file with _SQLiteOpen then close the file (and thus connection) with SQLite_Close.
      This works as intended, however, after the create operation, all subsequent _SQLite_* functions returned a "Library misuse error".
      After a little digging I found the problem in the _SQLite_Close function: it clears the "last opened database" handle even when there still is a live
      DB connection open. All other functions then "think" there is no DB connection active. I hacked two functions in the UDF for a quick fix:

      In _SQLite_Close: Change
      ... $__g_hDB_SQLite = 0 __SQLite_hDel($__g_ahDBs_SQLite, $hDB) Return $iRval[0] to:
      $__g_hDB_SQLite = __SQLite_hDel($__g_ahDBs_SQLite, $hDB) Return $iRval[0] and in Func __SQLite_hDel changed
      Func __SQLite_hDel(ByRef $ahLists, $hGeneric) Local $iElement = _ArraySearch($ahLists, $hGeneric) If $iElement > 0 Then _ArrayDelete($ahLists, $iElement) EndFunc ;==>__SQLite_hDel to:
      Func __SQLite_hDel(ByRef $ahLists, $hGeneric) Local $iElement = _ArraySearch($ahLists, $hGeneric) If $iElement > 0 Then _ArrayDelete($ahLists, $iElement) Return $ahLists[UBound($ahLists)-1] ; Return last opened db EndIf Return 0 EndFunc ;==>__SQLite_hDel so it preserves last opened DB again.
       
      My archive function now works great
      I'm not sure if this should be classified as a bug, but I believe so...
      Hope this helps someone before
    • By argumentum
      #include <SQLite.au3> ;-- When SQLite is compiled with the JSON1 extensions it provides builtin tools ;-- for manipulating JSON data stored in the database. ;-- This is a gist showing SQLite return query as a JSON object. ;-- https://www.sqlite.org/json1.html Example() Func Example() _SQLite_Startup() ; "<your path>\sqlite3.dll", False, 1) ; https://www.autoitscript.com/autoit3/docs/libfunctions/_SQLite_Startup.htm _SQLite_Open() ; ...if you can not run this due to errors, get the latest DLL from https://www.sqlite.org/ If _SQLite_Exec(-1, "CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, full_name TEXT NOT NULL, email TEXT NOT NULL, created DATE NOT NULL );") Then Return 4 If _SQLite_Exec(-1, 'INSERT INTO users VALUES ' & _ '(1, "Bob McFett", "bmcfett@hunters.com", "32-01-01"),' & _ '(2, "Angus O''Vader","angus.o@destroyers.com", "02-03-04"),' & _ '(3, "Imperator Colin", "c@c.c", "01-01-01");') Then Return 5 ; -- Get query data as a JSON object using the ; -- json_group_object() [1] and json_object() [2] functions. _SQLite_GetTable2d_ArrayToConsole("SELECT" & _ " json_group_object(" & _ " email," & _ " json_object('full_name', full_name, 'created', created)" & _ " ) AS json_result" & _ " FROM (SELECT * FROM users WHERE created > ""02-01-01"");") ; {"bmcfett@hunters.com":{"full_name":"Bob McFett","created":"32-01-01"},"angus.o@destroyers.com":{"full_name":"Angus O'Vader","created":"02-03-04"}} ; -- Get query data as a JSON object using the ; -- json_group_array() function to maintain order. _SQLite_GetTable2d_ArrayToConsole("SELECT" & _ " json_group_array(" & _ " json_object('full_name', full_name, 'created', created)" & _ " ) AS my_json_result_OrAnythingReally" & _ " FROM (SELECT * FROM users ORDER BY created);") ; [{"full_name":"Imperator Colin","created":"01-01-01"},{"full_name":"Angus O'Vader","created":"02-03-04"},{"full_name":"Bob McFett","created":"32-01-01"}] ;-- Links ;-- [1] https://www.sqlite.org/json1.html#jgroupobject ;-- [2] https://www.sqlite.org/json1.html#jobj ; example found at https://gist.github.com/akehrer/481a38477dd0518ec0086ac66e38e0e2 EndFunc ;==>Example Func _SQLite_GetTable2d_ArrayToConsole($sSQL, $hDB = -1) Local $aResult, $iRows, $iColumns If _SQLite_GetTable2d($hDB, $sSQL, $aResult, $iRows, $iColumns) Then ConsoleWrite("! SQLite Error: " & _SQLite_ErrCode($hDB) & @CRLF & "! " & _SQLite_ErrMsg($hDB) & @CRLF) Else _SQLite_Display2DResult($aResult) EndIf ConsoleWrite(@CRLF) EndFunc ;==>_SQLite_GetTable2d_ArrayToConsole Based on this example, you can build your own query. 
      The code has all the explanations.
      Enjoy  
    • By argumentum
      #include <SQLite.au3> ;~ #include <SQLite.dll.au3> Local $hQuery, $aRow, $aNames _SQLite_Startup() ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF) _SQLite_Open() ; open :memory: Database _SQLite_Exec(-1, "CREATE TABLE aTest (A,B int not null unique ,C text);") _SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('c','2','World');") _SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('b','3',' ');") _SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('a','1','Hello');") _SQLite_Query(-1, "SELECT _ROWID_,* FROM aTest ORDER BY a;", $hQuery) _SQLite_FetchTypes($hQuery, $aNames) ; Read out Column Types ConsoleWrite(StringFormat(" %-10s %-10s %-10s %-10s ", $aNames[0], $aNames[1], $aNames[2], $aNames[3]) & @CRLF) _SQLite_FetchNames($hQuery, $aNames) ; Read out Column Names ConsoleWrite(StringFormat(" %-10s %-10s %-10s %-10s ", $aNames[0], $aNames[1], $aNames[2], $aNames[3]) & @CRLF) While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK ConsoleWrite(StringFormat(" %-10s %-10s %-10s %-10s ", $aRow[0], $aRow[1], $aRow[2], $aRow[3]) & @CRLF) WEnd _SQLite_Exec(-1, "DROP TABLE aTest;") _SQLite_Close() _SQLite_Shutdown() ; Output: ; INTEGER int text ; rowid A B C ; 3 a 1 Hello ; 2 b 3 ; 1 c 2 World Func _SQLite_FetchTypes($hQuery, ByRef $aTypes) Dim $aTypes[1] If __SQLite_hChk($hQuery, 3, False) Then Return SetError(@error, 0, $SQLITE_MISUSE) Local $avDataCnt = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_column_count", "ptr", $hQuery) If @error Then Return SetError(1, @error, $SQLITE_MISUSE) ; DllCall error If $avDataCnt[0] <= 0 Then Return SetError(-1, 0, $SQLITE_DONE) ReDim $aTypes[$avDataCnt[0]] Local $avColName For $iCnt = 0 To $avDataCnt[0] - 1 $avColName = DllCall($__g_hDll_SQLite, "wstr:cdecl", "sqlite3_column_decltype16", "ptr", $hQuery, "int", $iCnt) If @error Then Return SetError(2, @error, $SQLITE_MISUSE) ; DllCall error $aTypes[$iCnt] = $avColName[0] Next Return $SQLITE_OK EndFunc ;==>_SQLite_FetchTypes  If you wanna build a proper JSON string, you may want to know if is {"int":123} or {"text":"123"}
      and for that, this can help, obviously only when declared in the SQLite table.
    • By argumentum
      I was thinking but I don't have the experience, so you may have the experience.
      I was thinking to chop a DB with 100.000 ( 20 columns ) in 10.000 DB chunks ( 10 DBs ) and query concurrently all of them to speed up a search, then add the results. Is that a sound idea ? Or will I run in trouble down the road. Should a DB better be keep in one piece ?
      The DB is now in MySQL. I wanna do all this chopping and use SQLite. 
      Thanks
      [solved]
    • By Colduction
      Hi, i have a source code of "Danny35d" and i have 2 main question that are:
       How to extract this function's results (Datas are as Array) to combo-box without set number of datas?  When i type 1, 2 in _SystemUsers($AccountType = 0) to get only Local or Domain users, it just give me both of them, it's old problem of this function, please share us debugged code :)❤ #include <Array.au3> $Users = _ArrayToString(_SystemUsers(), "|", 1) ConsoleWrite($Users & @CRLF) #cs =============================================================================== Function: _SystemUsers($AccountType = 0) Description: Return an array with the local or domain username Parameter(s): $AccountType: Local, domain or both username 0 = Local and Domain usernames 1 = Local usernames only 2 = Domain usernames only Returns: An array with the list of usernames - Succeeded @error 1 - Didn't query any username @error 2 - Failed to create Win32_SystemUsers object @error 3 - Invalid $AccountType Author(s): Danny35d #ce =============================================================================== Func _SystemUsers($AccountType = 0) Local $aSystemUsers Local $wbemFlagReturnImmediately = 0x10, $wbemFlagForwardOnly = 0x20 Local $colItems = "", $strComputer = "localhost" If Not StringRegExp($AccountType, '[012]') Then Return SetError(3, 3, '') $objWMIService = ObjGet("winmgmts:\\" & $strComputer & "\root\CIMV2") $colItems = $objWMIService.ExecQuery("SELECT * FROM Win32_SystemUsers", "WQL", _ $wbemFlagReturnImmediately + $wbemFlagForwardOnly) If IsObj($colItems) Then For $objItem In $colItems $Output = StringSplit($objItem.PartComponent, ',') If IsArray($Output) Then $Temp = StringReplace(StringTrimLeft($Output[2], StringInStr($Output[2], '=', 0, -1)), '"', '') If $AccountType = 0 Or ($AccountType = 1 And @ComputerName = $Temp) Then $aSystemUsers &= StringReplace(StringTrimLeft($Output[1], StringInStr($Output[1], '=', 0, -1)), '"', '') & '|' ElseIf $AccountType = 2 And @ComputerName <> $Temp Then $aSystemUsers &= StringReplace(StringTrimLeft($Output[1], StringInStr($Output[1], '=', 0, -1)), '"', '') & '|' EndIf EndIf Next $aSystemUsers = StringTrimRight($aSystemUsers, 1) If $aSystemUsers = '' Then Return(SetError(1, 1, $aSystemUsers)) Return(SetError(0, 0, StringSplit($aSystemUsers, '|'))) Else $aSystemUsers = '' Return(SetError(2, 2, $aSystemUsers)) EndIf EndFunc ;==>_SystemUsers Thanks to your best Team.
×
×
  • Create New...