FrancescoDiMuro

SQLite Query for populating combobox

7 posts in this topic

#1 ·  Posted

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 :) 


Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites



#2 ·  Posted

Hello. Use _GUICtrlComboBox_AddString is faster than GUICtrlSetData

 

Saludos

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

@FrancescoDiMuro,

Remove BEGIN and COMMIT. You don't need to enclose a single select (or any other single SQL statement BTW) in a transaction. SQLite will do that for you under the hood.

Now why is selecting distinct values so slow? Do you have duplicates in your table? If yes you should remove extra copies. If you don't have duplicate rows but only several rows for the same "Comune" column, then the design is flawed: you shouldn't duplicate data like that. Use a separate table and foreign keys for that.

To make you understand what I mean, imagine you have a table of all streets in a country. A bad design would be:
Id, Region, City, Street
where Region and City will be repeated for every street.

A better design will use a Regions table, a Cities Table and a Streets table.

Regions:
RegionId, RegionName (Unique)

Cities:
CityId, CityName (Unique in a given region)

Streets:
Id, RegionId, CityId, StreetName
where RegionId is a foreign key to the Regions table and CityId a foreign key to the Cities table.

Of course this is just a simplified example to fix ideas. This process of avoiding data duplication is called DB normalization.


Besides these considerations, you can also use the power of SQL to return you a single string with all the data and delimiters in place, for instance:

_SQLite_QuerySingleRow($objDatabase, "select group_concat(Comune, '|') FROM (select distinct comune from LISTA_COMUNI) group by null;", $Row)

On my table of 75377 zip codes for 32 countries, this request takes 300ms on my very slow PC. This way you can feed $Row to the combobox all in once. Yet, I personally suspect that a combobox with 8000+ entries is definitely unpractical, but that is up to you.

Edited by jchd

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

Good morning @jchd:)

The table I have does not contain more than a couple of duplicated rows...

What I'd like to have, is retrieve all the data from "Comune", and display them in a combobox, in order to let the user select what he wants to.

Do you have any pratical suggestion on how to display so many rows, and let select just one of them, in order to manage the data selected?

For SQLite: I can try with what you did suggest to me... But then, I have to split data, am I right? :)

Thank you a lot :) 


Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites

#5 ·  Posted

I find it irritating when I'm offered a choice in some overpopulated, endless and boring listbox of combobox.

Restricting the choices to the few which loosely match user input seems preferable. I've made available an SQLite extension DLL named "Unifuzz" which offers a number of Unicode string and collations functions, among which a typos() function returning the Damerau-Levenshtein distance between two strings. I often use it this way, loosely adapted to what I understand of your context:

select distinct comune from comuni where typos(comune, $input) < 3 order by comune;

This returns all distinct comune that are 4 character change away from the string in variable $input. For instance, searching cities in Belgium,
select distinct ville from codespostaux where paysiso = 'BE' and typos(ville, 'böan') < 3 order by ville;
returns:

Ville
BAAL
BOHAN
BOOM
BOST
BRA
BRAS
BRAY
DOHAN
MEAN
MOEN
ON


You can see that allowing as little as two typos already grabs a fair but manageable number of plausible choices. This function works by first folding case and accents on the whole BMP unicode range. Sorry I don't have a list of italian comuni to examplify.


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

#7 ·  Posted

_ArrayToString is a possibility but using the power of fast SQL function will always reveal faster than UDF code.


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

    • odaylton
      By odaylton
      I even understand that the Handle generated by _WinAPI_LoadCursor is not an Icon but how to display the image in the Combobox
      Here is the listing and please tell me how to make the current user current cursor image uncertainty
      #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> Example() Func Example() Local $hGUI, $hImage, $hCombo Local $idListview, $hImage Local $sWow64 = "" Local $Pasta="C:\WINDOWS\Cursors\3dgarro.cur" $hGUI = GUICreate("ImageList Mouse Icons", 400, 300) $hCombo = _GUICtrlComboBoxEx_Create($hGUI, "", 2, 2, 394, 100) $idListview = GUICtrlCreateListView("", 2, 104, 394, 200, BitOR($LVS_SHOWSELALWAYS, $LVS_NOSORTHEADER, $LVS_REPORT)) _GUICtrlListView_SetExtendedListViewStyle($idListview, BitOR($LVS_EX_FULLROWSELECT, $LVS_EX_GRIDLINES, $LVS_EX_DOUBLEBUFFER)) GUISetState(@SW_SHOW) ; Load images $hImage = _GUIImageList_Create(16, 16) ;teste _GUIImageList_AddIcon($hImage, $Pasta) ;<<<<<<<<<<<<<<<<<<<<<this $hIcon= _WinAPI_CopyCursor(_WinAPI_LoadCursor(0,$IDC_HAND));32649 ; Hand cursor _GUIImageList_AddIcon($hImage, $hIcon);<<<<<<<<<<<<<<<<<< erro for $i=1 to 15 $hIcon= _WinAPI_CopyCursor(_WinAPI_LoadCursor(0, $i));<<<<<<<<<<<<<<<<< _GUIImageList_AddIcon($hImage, $hIcon);<<<<<<<<<<<< Next _GUIImageList_AddIcon($hImage, $Pasta) _GUICtrlListView_SetImageList($idListview, $hImage, 1) _GUICtrlComboBoxEx_SetImageList($hCombo, $hImage) ; Add columns _GUICtrlListView_AddColumn($idListview, "Items", 120) ; Add items $a=StringSplit("testeIcone|HAND|APPSTARTING|ARROW|CROSS|HELP|IBEAM|ICON|NO|SIZE|SIZEALL|SIZENESW|SIZENS|SIZENWSE|SIZEWE|UPARROW|WAIT|testefim","|") For $i=1 to $a[0] _GUICtrlListView_AddItem($idListview, $a[$i], $i-1) _GUICtrlComboBoxEx_AddString($hCombo, $a[$i], $i-1) Next ; Loop until the user exits. Do Until GUIGetMsg() = $GUI_EVENT_CLOSE GUIDelete() EndFunc ;==>Example Note that only the initial image is uncertain because it comes from a file
      And the one that would be the last enters as the second
      And the background is black how to transform into transparent
      imagem de cursor.bmp
    • Ian_Mac
      By Ian_Mac
      hello, i just wanted to ask if how........
       I have a gui combo box with a list and i wanted it to remember the last item that i selected, that when i close the gui and then open the gui again, my last selected item will be automatically show in the combobox as selected item already.
      thank you in advance.
    • argumentum
      By argumentum
      #include <GuiComboBox.au3> #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> #include <WinAPI.au3> #include <Array.au3> #include <WinAPITheme.au3> #include <GDIPlus.au3> #Region GUICtrlComboSetColors UDF Global $g__aWM_CTLCOLORLISTBOX[1][16] = [[0, 0, 0]] ; init. the Global array ; #FUNCTION# ==================================================================================================================== ; Name...........: GUICtrlComboSetColors ; Description ...: Change the colors and position/size of a ComboBox ; Syntax.........: GUICtrlComboSetColors ( $idCombo [, $iBgColor = Default] [, $iFgColor = Default] [, $iExtendLeft = Default] ) ; Parameters ....: $idCombo - GUICtrlCreateCombo() ControlID / [ ArrayIndex ] ; $iBgColor - Background RGB color ; - or "-1" to use prior color declared ; - or to remove a control by ControlID, "-2" ; - or to remove a control by ArrayIndex, "-3" ; $iFgColor - Foreground RGB color ; - or "-1" to use prior color declared ; - or "-2" to use sytem color and leave theme default ; $iExtendLeft - pixels to extend the dropdown list ; - or "-1" to use prior width declared ; - or "1" auto size, extending left ( see Remarks/AutoSize ) ; - or "2" auto size, extending right ( see Remarks/AutoSize ) ; Return values .: Success - index position in the array ; Failure - 0 ; @error - 1 : Control handle = 0 ; - 2 : GetComboBoxInfo failed ; - 3 : Control for removal not found ; @extended - 2 : Success on Control removal ; Author ........: argumentum ; Modified.......: v0.0.0.5 ; Remarks .......: this UDF is in its a work in progress, will expand if needed. ; AutoSize...: use the pertinent parameters from GUICtrlComboSetColors_SetAutoSize() ; minus the CtrlID as semicolon separated to initialize. Ex: "2;Arial;8.5;0" ; Related .......: ; Link ..........: https://www.autoitscript.com/forum/topic/191035-combobox-set-dropdownlist-bgcolor/ ; Example .......: Yes, at the end of the file ; =============================================================================================================================== Func GUICtrlComboSetColors($idCombo = 0, $iBgColor = Default, $iFgColor = Default, $iExtendLeft = Default) If Not $idCombo Then Return SetError(1, 0, 0) Local $n, $tInfo, $i = 0 If $iBgColor = -2 Or $iBgColor = -3 Then Local $m For $n = 1 To $g__aWM_CTLCOLORLISTBOX[0][0] If ($g__aWM_CTLCOLORLISTBOX[$n][0] = $idCombo And $iBgColor = -2) Or ($g__aWM_CTLCOLORLISTBOX[$n][9] = $idCombo And $iBgColor = -3) Then _ArrayDelete($g__aWM_CTLCOLORLISTBOX, $n) $g__aWM_CTLCOLORLISTBOX[0][0] -= 1 Return SetError(0, 2, $n) EndIf Next Return SetError(3, 0, 0) EndIf For $n = 1 To $g__aWM_CTLCOLORLISTBOX[0][0] If $g__aWM_CTLCOLORLISTBOX[$n][0] = $idCombo Then $i = $n ExitLoop EndIf Next If Not $i Then $g__aWM_CTLCOLORLISTBOX[0][0] += 1 $i = $g__aWM_CTLCOLORLISTBOX[0][0] ; If $i >= UBound($g__aWM_CTLCOLORLISTBOX) Then ReDim $g__aWM_CTLCOLORLISTBOX[$i + 1][16] ; add extra "slots" EndIf EndIf Local $sStr = GUICtrlRead($idCombo) Local $iSetWindowTheme = 1 If $iBgColor = Default And $iFgColor = Default Then $iSetWindowTheme = 0 If $iBgColor = Default Then $iBgColor = _WinAPI_GetSysColor($COLOR_WINDOW) If $iFgColor = Default Then $iFgColor = _WinAPI_GetSysColor($COLOR_WINDOWTEXT) If $iBgColor = -1 Then $iBgColor = $g__aWM_CTLCOLORLISTBOX[$i][10] If $iFgColor = -1 Then $iFgColor = $g__aWM_CTLCOLORLISTBOX[$i][11] $g__aWM_CTLCOLORLISTBOX[$i][11] = $iFgColor $g__aWM_CTLCOLORLISTBOX[$i][10] = $iBgColor If $iExtendLeft = Default Then $iExtendLeft = 0 $g__aWM_CTLCOLORLISTBOX[$i][12] = 0 EndIf If $iExtendLeft = -1 Then $iExtendLeft = $g__aWM_CTLCOLORLISTBOX[$i][8] ElseIf Int($iExtendLeft) = 1 Then $g__aWM_CTLCOLORLISTBOX[$i][12] = 1 ElseIf Int($iExtendLeft) = 2 Then $g__aWM_CTLCOLORLISTBOX[$i][12] = 2 Else $g__aWM_CTLCOLORLISTBOX[$i][12] = 0 EndIf $g__aWM_CTLCOLORLISTBOX[$i][8] = Int($iExtendLeft) $g__aWM_CTLCOLORLISTBOX[$i][0] = $idCombo $g__aWM_CTLCOLORLISTBOX[$i][1] = GUICtrlGetHandle($idCombo) $g__aWM_CTLCOLORLISTBOX[$i][13] = "Arial" ; default $sFont $g__aWM_CTLCOLORLISTBOX[$i][14] = 8.5 ; default $fSize $g__aWM_CTLCOLORLISTBOX[$i][15] = 0 ; default $iStyle If $g__aWM_CTLCOLORLISTBOX[$i][12] Then $f = StringSplit($iExtendLeft, ";") If UBound($f) > 1 Then $g__aWM_CTLCOLORLISTBOX[$i][8] = Int($f[1]) If UBound($f) > 2 Then $g__aWM_CTLCOLORLISTBOX[$i][13] = $f[2] If UBound($f) > 3 Then $g__aWM_CTLCOLORLISTBOX[$i][14] = Int($f[3]) If UBound($f) > 4 Then $g__aWM_CTLCOLORLISTBOX[$i][15] = Int($f[4]) $t = TimerInit() GUICtrlComboSetColors_SetAutoSize(Int("-" & $i), $g__aWM_CTLCOLORLISTBOX[$i][12], $g__aWM_CTLCOLORLISTBOX[$i][13], $g__aWM_CTLCOLORLISTBOX[$i][14], $g__aWM_CTLCOLORLISTBOX[$i][15]) ConsoleWrite(TimerDiff($t) & @CRLF) EndIf If _GUICtrlComboBox_GetComboBoxInfo($idCombo, $tInfo) Then If $iSetWindowTheme Then If $g__aWM_CTLCOLORLISTBOX[$i][11] <> -2 Then _WinAPI_SetWindowTheme($g__aWM_CTLCOLORLISTBOX[$i][1], "", "") If $g__aWM_CTLCOLORLISTBOX[$i][11] <> -2 Then GUICtrlSetColor($g__aWM_CTLCOLORLISTBOX[$i][0], $iFgColor) GUICtrlSetBkColor($g__aWM_CTLCOLORLISTBOX[$i][0], $iBgColor) Else GUICtrlSetBkColor($g__aWM_CTLCOLORLISTBOX[$i][0], _WinAPI_GetSysColor($COLOR_HOTLIGHT)) _WinAPI_SetWindowTheme($g__aWM_CTLCOLORLISTBOX[$i][1], 0, 0) EndIf $g__aWM_CTLCOLORLISTBOX[$i][2] = DllStructGetData($tInfo, "hCombo") $g__aWM_CTLCOLORLISTBOX[$i][3] = DllStructGetData($tInfo, "hEdit") $g__aWM_CTLCOLORLISTBOX[$i][4] = DllStructGetData($tInfo, "hList") ; this is what is colored Else $g__aWM_CTLCOLORLISTBOX[0][0] -= 1 Return SetError(2, 0, 0) EndIf If Int($g__aWM_CTLCOLORLISTBOX[$i][5]) Then _WinAPI_DeleteObject($g__aWM_CTLCOLORLISTBOX[$i][5]) $g__aWM_CTLCOLORLISTBOX[$i][5] = 0 ; holder for "_WinAPI_CreateSolidBrush()" return value $g__aWM_CTLCOLORLISTBOX[$i][6] = BitOR(BitAND($iBgColor, 0x00FF00), BitShift(BitAND($iBgColor, 0x0000FF), -16), BitShift(BitAND($iBgColor, 0xFF0000), 16)) If $g__aWM_CTLCOLORLISTBOX[$i][11] = -2 Then $iFgColor = _WinAPI_GetSysColor($COLOR_WINDOWTEXT) $g__aWM_CTLCOLORLISTBOX[$i][7] = BitOR(BitAND($iFgColor, 0x00FF00), BitShift(BitAND($iFgColor, 0x0000FF), -16), BitShift(BitAND($iFgColor, 0xFF0000), 16)) If Not $g__aWM_CTLCOLORLISTBOX[0][1] Then If $g__aWM_CTLCOLORLISTBOX[$i][4] Then $g__aWM_CTLCOLORLISTBOX[0][1] = GUIRegisterMsg($WM_CTLCOLORLISTBOX, "UDF_WM_CTLCOLORLISTBOX") If $g__aWM_CTLCOLORLISTBOX[0][1] Then OnAutoItExitRegister("OnAutoItExit_UDF_WM_CTLCOLORLISTBOX") EndIf EndIf $g__aWM_CTLCOLORLISTBOX[0][2] += 1 $g__aWM_CTLCOLORLISTBOX[$i][9] = $g__aWM_CTLCOLORLISTBOX[0][2] ; internal ID $g__aWM_CTLCOLORLISTBOX[0][3] = TimerInit() ; to use in UDF_WM_CTLCOLORLISTBOX() $g__aWM_CTLCOLORLISTBOX[0][4] = 0 ; to use in UDF_WM_CTLCOLORLISTBOX() If $sStr Then GUICtrlSetData($idCombo, $sStr) Return SetError(0, 0, $g__aWM_CTLCOLORLISTBOX[0][2]) EndFunc ;==>GUICtrlComboSetColors Func UDF_WM_CTLCOLORLISTBOX($hWnd, $Msg, $wParam, $lParam) ConsoleWrite('+ Func UDF_WM_CTLCOLORLISTBOX(' & $hWnd & ', ' & $Msg & ', ' & $wParam & ', ' & $lParam & ')' & @CRLF) For $i = 1 To $g__aWM_CTLCOLORLISTBOX[0][0] If $g__aWM_CTLCOLORLISTBOX[$i][4] = $lParam Then If TimerDiff($g__aWM_CTLCOLORLISTBOX[0][3]) > 500 Or $g__aWM_CTLCOLORLISTBOX[0][4] <> $lParam Then If $g__aWM_CTLCOLORLISTBOX[$i][12] Then GUICtrlComboSetColors_SetAutoSize("-" & $i) EndIf $g__aWM_CTLCOLORLISTBOX[0][3] = TimerInit() $g__aWM_CTLCOLORLISTBOX[0][4] = $lParam If $g__aWM_CTLCOLORLISTBOX[$i][8] > 0 Then Local $aWPos = WinGetPos($g__aWM_CTLCOLORLISTBOX[$i][2]) WinMove($lParam, "", $aWPos[0] - $g__aWM_CTLCOLORLISTBOX[$i][8], $aWPos[1] + $aWPos[3], $aWPos[2] + $g__aWM_CTLCOLORLISTBOX[$i][8]) ElseIf $g__aWM_CTLCOLORLISTBOX[$i][8] < 0 Then Local $aWPos = WinGetPos($g__aWM_CTLCOLORLISTBOX[$i][2]) WinMove($lParam, "", $aWPos[0], $aWPos[1] + $aWPos[3], $aWPos[2] - $g__aWM_CTLCOLORLISTBOX[$i][8]) EndIf If $g__aWM_CTLCOLORLISTBOX[$i][7] >= 0 Then _WinAPI_SetTextColor($wParam, $g__aWM_CTLCOLORLISTBOX[$i][7]) EndIf If $g__aWM_CTLCOLORLISTBOX[$i][6] >= 0 Then _WinAPI_SetBkColor($wParam, $g__aWM_CTLCOLORLISTBOX[$i][6]) If Not $g__aWM_CTLCOLORLISTBOX[$i][5] Then $g__aWM_CTLCOLORLISTBOX[$i][5] = _WinAPI_CreateSolidBrush($g__aWM_CTLCOLORLISTBOX[$i][6]) Return $g__aWM_CTLCOLORLISTBOX[$i][5] EndIf Return 0 EndIf Next EndFunc ;==>UDF_WM_CTLCOLORLISTBOX ; #FUNCTION# ==================================================================================================================== ; Name...........: GUICtrlComboSetColors_SetAutoSize ; Description ...: Set autosize for a ComboBox initialized in GUICtrlComboSetColors() ; Syntax.........: GUICtrlComboSetColors ( $idCombo [, $iExtendLeft = Default] [, $sFont = Default] [, $fSize = Default] [, $iStyle = Default] ) ; Parameters ....: $idCombo - GUICtrlCreateCombo() ControlID / [ ArrayIndex ] ; $iExtendLeft - 1 = Left, 2 = Right, 0 = disable auto-sizing ; $sFont - Font name ; $fSize - Font size ; $iStyle - Font style ; Return values .: Success - widthest string in pixels ; Failure - -1 ; @error - look at the comments in the function ; Author ........: argumentum ; Modified.......: v0.0.0.5 ; Remarks .......: this UDF is in its a work in progress, will expand if needed. ; Related .......: GUICtrlComboSetColors() ; Link ..........: https://www.autoitscript.com/forum/topic/191035-combobox-set-dropdownlist-bgcolor/ ; Example .......: Yes, at the end of the file ; =============================================================================================================================== Func GUICtrlComboSetColors_SetAutoSize($idCombo, $iExtendLeft = Default, $sFont = Default, $fSize = Default, $iStyle = Default) ConsoleWrite('+ Func GUICtrlComboSetColors_AutoSizeSet("' & $idCombo & '", "' & $iExtendLeft & '", "' & $sFont & '", "' & $fSize & '", "' & $iStyle & '")' & @CRLF) $idCombo = Int($idCombo) ; just in case the value is a string Local $n, $iArrayIndex = 0, $iCtrl = 0 If $idCombo > 0 Then For $n = 1 To $g__aWM_CTLCOLORLISTBOX[0][0] If $g__aWM_CTLCOLORLISTBOX[$n][0] = $idCombo Then ; the expected value, is the ControlID $iArrayIndex = $n ExitLoop EndIf Next Return SetError(4, 0, -1) ; $iArrayIndex not found ElseIf $idCombo < 0 Then ; the expected value, is a negative of array's index .. $iArrayIndex = Int(StringTrimLeft(StringStripWS($idCombo, 8), 1)) ; .. so now is a positive value .. If $iArrayIndex < 1 Then Return SetError(3, 0, -1) ; .. else, error .. If $iArrayIndex > $g__aWM_CTLCOLORLISTBOX[0][0] Then Return SetError(2, 0, -1) ; .. as long as is not greater than expected Else Return SetError(1, 0, -1) ; could not find a usable value EndIf Switch $iExtendLeft Case 0, 1, 2 $g__aWM_CTLCOLORLISTBOX[$iArrayIndex][12] = $iExtendLeft EndSwitch Local $aCtrlPos = WinGetPos($g__aWM_CTLCOLORLISTBOX[$iArrayIndex][1]) If UBound($aCtrlPos) <> 4 Then Return SetError(5, 0, -1) ; could not get a usable value Local $sString = StringReplace(_GUICtrlComboBox_GetList($g__aWM_CTLCOLORLISTBOX[$iArrayIndex][0]), "|", @CRLF) Local $aStrWidth = _GDIPlus_MeasureString($sString, $g__aWM_CTLCOLORLISTBOX[$iArrayIndex][13], $g__aWM_CTLCOLORLISTBOX[$iArrayIndex][14], $g__aWM_CTLCOLORLISTBOX[$iArrayIndex][15]) If UBound($aStrWidth) <> 2 Then Return SetError(6, 0, -1) ; could not get a usable value If $aStrWidth[0] < $aCtrlPos[2] Then $g__aWM_CTLCOLORLISTBOX[$iArrayIndex][8] = 0 Else $g__aWM_CTLCOLORLISTBOX[$iArrayIndex][8] = $aStrWidth[0] - $aCtrlPos[2] If $g__aWM_CTLCOLORLISTBOX[$iArrayIndex][12] = 2 Then $g__aWM_CTLCOLORLISTBOX[$iArrayIndex][8] = Int("-" & $aStrWidth[0] - $aCtrlPos[2]) EndIf Return $aStrWidth[0] EndFunc ;==>GUICtrlComboSetColors_SetAutoSize Func _GDIPlus_MeasureString($sString, $sFont = "Arial", $fSize = 12, $iStyle = 0, $bRound = True) ConsoleWrite('Func _GDIPlus_MeasureString("' & $sString & '", "' & $sFont & '", "' & $fSize & '", "' & $iStyle & '", "' & $bRound & '")' & @CRLF) ; original code @ https://www.autoitscript.com/forum/topic/150736-gdi-wrapping-text/?do=findComment&comment=1077210 If Not $__g_iGDIPRef Then _GDIPlus_Startup() ; added by argumentum for this UDF's implementation ( AutoIt v3.3.14 ) due to the way the function is written ;~ Func _GDIPlus_Startup($sGDIPDLL = Default, $bRetDllHandle = False) ;~ $__g_iGDIPRef += 1 <-- I believe this aspect should be coded differently in "GDIPlus.au3" ;~ If $__g_iGDIPRef > 1 Then Return True Local $aSize[2] Local Const $hFamily = _GDIPlus_FontFamilyCreate($sFont) If Not $hFamily Then Return SetError(1, 0, $aSize) Local Const $hFormat = _GDIPlus_StringFormatCreate() Local Const $hFont = _GDIPlus_FontCreate($hFamily, $fSize, $iStyle) Local Const $tLayout = _GDIPlus_RectFCreate(0, 0, 0, 0) Local Const $hGraphic = _GDIPlus_GraphicsCreateFromHWND(0) Local $aInfo = _GDIPlus_GraphicsMeasureString($hGraphic, $sString, $hFont, $tLayout, $hFormat) $aSize[0] = $bRound ? Round($aInfo[0].Width, 0) : $aInfo[0].Width $aSize[1] = $bRound ? Round($aInfo[0].Height, 0) : $aInfo[0].Height _GDIPlus_FontDispose($hFont) _GDIPlus_FontFamilyDispose($hFamily) _GDIPlus_StringFormatDispose($hFormat) _GDIPlus_GraphicsDispose($hGraphic) Return $aSize EndFunc ;==>_GDIPlus_MeasureString Func OnAutoItExit_UDF_WM_CTLCOLORLISTBOX() For $i = 1 To $g__aWM_CTLCOLORLISTBOX[0][0] If Int($g__aWM_CTLCOLORLISTBOX[$i][5]) Then _WinAPI_DeleteObject($g__aWM_CTLCOLORLISTBOX[$i][5]) Next If $__g_iGDIPRef Then _GDIPlus_Shutdown() EndFunc ;==>OnAutoItExit_UDF_WM_CTLCOLORLISTBOX #EndRegion GUICtrlComboSetColors UDF Example() Func Example() ; Create GUI GUICreate("ComboBox Set DROPDOWNLIST BgColor", 640, 300) Local $a_idCombo[7] = [6] $a_idCombo[1] = GUICtrlCreateCombo("", 2, 2, 390, 296, BitOR($CBS_DROPDOWNLIST, $WS_HSCROLL, $WS_VSCROLL)) GUICtrlComboSetColors($a_idCombo[1], 0xEEEEEE, -2, Default) Example_FillTheCombo($a_idCombo[1]) GUICtrlCreateLabel("<<< change BG color, default theme && size ", 400, 4, 396, 296) $a_idCombo[2] = GUICtrlCreateCombo("", 2, 32, 390, 296, BitOR($CBS_DROPDOWNLIST, $WS_HSCROLL, $WS_VSCROLL)) GUICtrlComboSetColors($a_idCombo[2], 0x0000FF, 0xFFFF00, 0) Example_FillTheCombo($a_idCombo[2]) GUICtrlCreateLabel("<<< change colors", 400, 34, 396, 296) $a_idCombo[3] = GUICtrlCreateCombo("", 2, 62, 390, 296, BitOR($CBS_DROPDOWNLIST, $WS_HSCROLL, $WS_VSCROLL)) GUICtrlComboSetColors($a_idCombo[3], 0xdddddd, Default, 100) Example_FillTheCombo($a_idCombo[3]) GUICtrlCreateLabel("<<< change BG color, resize 100px. left", 400, 64, 396, 296) $a_idCombo[4] = GUICtrlCreateCombo("", 2, 92, 390, 296, BitOR($CBS_DROPDOWNLIST, $WS_HSCROLL, $WS_VSCROLL)) GUICtrlComboSetColors($a_idCombo[4], Default, 0x0000FF, -100) Example_FillTheCombo($a_idCombo[4]) GUICtrlCreateLabel("<<< change FG color, resize 100px. right", 400, 94, 396, 296) $a_idCombo[5] = GUICtrlCreateCombo("", 2, 122, 390, 296, BitOR($CBS_DROPDOWNLIST, $WS_HSCROLL, $WS_VSCROLL)) GUICtrlComboSetColors($a_idCombo[5], 0x00FFFF, 0x0000FF, 1) Example_FillTheCombo($a_idCombo[5]) GUICtrlCreateLabel("<<< change colors, resize auto left", 400, 124, 396, 296) $a_idCombo[6] = GUICtrlCreateCombo("", 2, 152, 390, 296, BitOR($CBS_DROPDOWNLIST, $WS_HSCROLL, $WS_VSCROLL)) GUICtrlSetFont($a_idCombo[6], 10, 400, 0, "Courier New") GUICtrlComboSetColors($a_idCombo[6], Default, Default, "2;Courier New;10") Example_FillTheCombo($a_idCombo[6]) GUICtrlCreateLabel("<<< default colors, resize auto right", 400, 154, 396, 296) Local $bttnArrayShow = GUICtrlCreateButton("Show array", 2, 296 - 27, 75, 25) Local $bttnStrMore = GUICtrlCreateButton("Longer str.", 102, 296 - 27, 75, 25) Local $idLorem = GUICtrlCreateLabel("", 195, 296 - 27, 50, 25) Local $bttnStrLess = GUICtrlCreateButton("Shorter str.", 252, 296 - 27, 75, 25) GUISetState(@SW_SHOW) WinActivate("ComboBox Set DROPDOWNLIST BgColor") ;~ Sleep(3500) ; you can reassign colors, size, or restore default ;~ GUICtrlComboSetColors($idCombo5, Default, Default, 300) ; this resets the Control back to default and changes $iExtendLeft ;~ GUICtrlComboSetColors($idCombo5, 0x0000FF, 0x00FFFF, -1) ; this changes the colors and keeps $iExtendLeft as it was ;~ GUICtrlComboSetColors($idCombo5, -1, -1, 300) ; using "-1" will keep the existing colors ;~ ; so in this case, only the $iExtendLeft is declared ;~ Example_FillTheCombo($idCombo5) ;~ Sleep(500) ; after removal, it will not repaint "hList", but then again, you're deleteing the control ;~ GUICtrlComboSetColors($idColors, -3) ;~ GUICtrlDelete($idCombo2) Local $iLorem = 5, $sLorem = "" Example_LoremStr($iLorem, $sLorem, $a_idCombo, $idLorem) ; Loop until the user exits. While 1 Switch GUIGetMsg() Case $GUI_EVENT_CLOSE GUIDelete() Return Case $bttnArrayShow _ArrayDisplay($g__aWM_CTLCOLORLISTBOX, "$g__aWM_CTLCOLORLISTBOX") Case $bttnStrMore $iLorem += 5 Example_LoremStr($iLorem, $sLorem, $a_idCombo, $idLorem) Case $bttnStrLess $iLorem -= 5 Example_LoremStr($iLorem, $sLorem, $a_idCombo, $idLorem) EndSwitch WEnd EndFunc ;==>Example Func Example_FillTheCombo(ByRef $idComboCtrl) GUICtrlSetData($idComboCtrl, "") _GUICtrlComboBox_AddString($idComboCtrl, "something") _GUICtrlComboBox_AddString($idComboCtrl, "something else") _GUICtrlComboBox_AddString($idComboCtrl, "blah, blah, blah, blah") _GUICtrlComboBox_AddString($idComboCtrl, "Lorem will change") Local $a = _GUICtrlComboBox_GetListArray($idComboCtrl) GUICtrlSetData($idComboCtrl, $a[1]) EndFunc ;==>Example_FillTheCombo Func Example_LoremStr(ByRef $iLorem, ByRef $sLorem, ByRef $a_idCombo, ByRef $idLorem) Local Static $s = "Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat." $s &= " Ut wisi enim ad minim veniam, quis nostrud exerci tation ullamcorper suscipit lobortis nisl ut aliquip ex ea commodo consequat." Local Static $i = 5, $a = StringSplit($s, " ") If $iLorem < 1 Then $iLorem = 1 If $iLorem > $a[0] Then $iLorem = $a[0] Local $x, $iLastEntry $sLorem = "" GUICtrlSetData($idLorem, $iLorem & ' words') For $x = 1 To $iLorem $sLorem &= $a[$x] & " " Next For $x = 1 To $a_idCombo[0] $iLastEntry = _GUICtrlComboBox_GetCount($a_idCombo[$x]) - 1 _GUICtrlComboBox_DeleteString($a_idCombo[$x], $iLastEntry) _GUICtrlComboBox_AddString($a_idCombo[$x], $sLorem) Next EndFunc ;==>Example_LoremStr new in v0.0.0.5: auto size the dropdown.
    • kcvinu
      By kcvinu
      Hi all,
      How can i get notified when user clicks on a combo box's edit area ? This is my code so far. 
      #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> #include <Constants.au3> #include <ComboConstants.au3> #include <ListBoxConstants.au3> Global $btn ; creates a window Global $Window_0 = GUICreate("My Window", 800, 500, -1, -1, BitOR($WS_SIZEBOX, $WS_MINIMIZEBOX, $WS_MAXIMIZEBOX)) $btn = GUICtrlCreateButton("Click Me", 50, 50, 120, 50) Global $cmb = GUICtrlCreateCombo("Sample", 50, 150, 300, 50) GUICtrlSetFont(-1,12,400) Global $lstbx = GUICtrlCreateList("FirstItem", 400,50, 200, 300) GUICtrlSetFont(-1,12,400) GUIRegisterMsg($WM_COMMAND, "MyEventCallback") GUIRegisterMsg($WM_SIZE, "MyEventCallback") GUIRegisterMsg($WM_NOTIFY, "NotifyManager") GUISetState(@SW_SHOW) GUICtrlSetData($cmb, "Item 2|Item 3", "Item 2") GUICtrlSetData($lstbx, "Apple|Orange|Pineapple|Grape|Lemon") Do $Event = GUIGetMsg( ) Until $Event = $GUI_EVENT_CLOSE Func MyEventCallback($hwnd, $message, $wParam, $lParam) Select Case $message = $WM_COMMAND ;---------------------------------------------------------------- If LoWord($wParam) = $cmb Then ; if control id is combox's Then Local $Notification = HiWord($wParam) Select Case $Notification = $CBN_DROPDOWN ; here we check the notification code. ConsoleWrite("$CBN_DROPDOWN Worked " & @MIN & ":" & @SEC & @CRLF) Case $Notification = $CBN_EDITCHANGE ; here we check the notification code. ConsoleWrite("$CBN_EDITCHANGE Worked " & @MIN & ":" & @SEC & @CRLF) ; **** Here i want add the code for combo box clicking. EndSelect ;--------------------------------------------------------------------- ElseIf LoWord($wParam) = $lstbx Then Local $Notification = HiWord($wParam) Select Case $Notification = $LBN_SELCHANGE ConsoleWrite("$LBN_SELCHANGE" & @CRLF) EndSelect EndIf ;------------------------------------------------------------------ Case $message = $WM_SIZE EndSelect Return $GUI_RUNDEFMSG EndFunc ;==>ProGUI_EventCallback Func NotifyManager($hWnd, $iMsg, $wParam, $lParam) #forceref $hWnd, $iMsg, $wParam ;Local $hWndFrom, $iIDFrom, $iCode, $tNMHDR Local $tagNMHDR, $event, $hwndFrom, $code $tagNMHDR = DllStructCreate("int;int;int", $lParam) If DllStructGetData($tagNMHDR, 3) = $NM_LDOWN Then ConsoleWrite("Notify Worked" & @CRLF) EndIf Return $GUI_RUNDEFMSG EndFunc Func LoWord($Variable) Return BitAND($Variable, 0xFFFF) EndFunc Func HiWord($Variable) Return BitShift($Variable, 16) EndFunc  
    • 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