SQL search> listview > select function

[plarmuseau]

so i wanted to create a code that starts with a MYSQL query($sQuery) in a database ($DB) with a searchterm

and as the user doubleclicks the listview grid, he gets the result.

so in the code you simply have to call the function like this..

$resultaat=SQLselectLV($DB,$sQuery,$zoekterm)

Consolewrite($resultaat)

Can i have any suggestion to make this better ?

expandcollapse popup

#include <GuiConstantsEx.au3>
#include <GuiListView.au3>
#include <WindowsConstants.au3>

Global $sListView,$DB,$selectedID,$selectedINFO
Dim $conn,$sQuery,$Query,$rs,$tableField_Names,$data,$resultaat

    $zoekterm="meproba"
    $sQuery="select * from (select stf_volgnummer nr,stf_datumontvangen datum, stf_prd_primkey prim,stf_omschrijving naam,0 AP,stf_cnk CNK from grondstofregister where stf_omschrijving like '"&$zoekterm&"%' UNION select 0000 nr,bit_datechanged datum,bit_prd_primkey prim,bit_omschrijving naam,bit_aankoopprijs ap,bit_bestelcode CNK FROm bestelitems,product where bit_omschrijving like '"&$zoekterm&"%' and bit_prd_primkey=prd_primkey and prd_codefiscaalforfait=32 ) as foo order by datum desc "
    $server="10.10.10.100"
    $sqldb="database"
    $UID="administratororuser"
    $PWD="paswordfromadmin"
    $guiw=800
    $guih=300
    $DB="PROVIDER=SQLOLEDB;SERVER="&$server&";DATABASE="&$sqldb&";UID="&$UID&";PWD="&$PWD&";"

;~ ---------------------------------------------
;~  $DB= database connection
;~  $sQuery= query in database
;~  $zoekterm = search term in query, word or number that is used in the query to search in database
;~  $resultaat= array of fields in the query that is returned as result of the selection
;~  
;~ -----------------------------------------------  
    $resultaat=SQLselectLV($DB,$sQuery,$zoekterm)
    Consolewrite($resultaat)
    
Func SQLselectLV($DB,$sQuery,$zoekterm)
    
    $GUI = GUICreate(" ListView Create", $guiw, $guih)
    $selectie=f_dbselect($sQuery,$DB)

    $headers=$tableField_Names
    $sListView = GUICtrlCreateListView($headers, 10, 35, $guiw - 3, $guih - 3)  
     $cheaders=stringsplit($tablefield_names,"|")
    _GUICtrlListView_DeleteAllItems($sListView)
    $itemcount=_GUICtrlListView_GetItemCount($sListView)    
    $leeg=""
    For $j = 0 To ubound($cheaders)-3
        $leeg=$leeg&"|"
    Next
        for $i = $itemcount to ubound($selectie)-1
            GUICtrlCreateListViewItem($leeg, $sListView)
        Next
    
    for $i = 0 to ubound($selectie)-1
        For $j = 0 To ubound($cheaders)-3
        _GUICtrlListView_SetItem($sListView, $selectie[$i][$j], $i,$j)
        _GUICtrlListView_SetColumnWidth($sListview, $j, $LVSCW_AUTOSIZE)        
        Next
    next
    GUICtrlSendMsg($sListView, $LVM_SETEXTENDEDLISTVIEWSTYLE, $LVS_EX_GRIDLINES, $LVS_EX_GRIDLINES)
    GUICtrlSendMsg($sListView, $LVM_SETEXTENDEDLISTVIEWSTYLE, $LVS_EX_FULLROWSELECT, $LVS_EX_FULLROWSELECT)
    _GUICtrlListView_SetExtendedListViewStyle($sListView, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT, $LVS_EX_SUBITEMIMAGES))
    GUISetState()
    
     GUIRegisterMsg($WM_NOTIFY, "WM_NOTIFY")

    While 1
    $msg = GUIGetMsg()

    Switch $msg
            Case $GUI_EVENT_CLOSE
            return $selectedINFO
            Exit
    EndSwitch
    WEnd
    GUIDelete()
    
EndFunc


Func WM_NOTIFY($hWnd, $iMsg, $iwParam, $ilParam)
    #forceref $hWnd, $iMsg, $iwParam
    Local $hWndFrom, $iIDFrom, $iCode, $tNMHDR, $hWndListView, $tInfo
;~     Local $tBuffer
    $hWndListView = $sListView
    If Not IsHWnd($sListView) Then $hWndListView = GUICtrlGetHandle($sListView)

    $tNMHDR = DllStructCreate($tagNMHDR, $ilParam)
    $hWndFrom = HWnd(DllStructGetData($tNMHDR, "hWndFrom"))
    $iIDFrom = DllStructGetData($tNMHDR, "IDFrom")
    $iCode = DllStructGetData($tNMHDR, "Code")
    Switch $hWndFrom
        Case $hWndListView
            Switch $iCode
                Case $NM_DBLCLK ; Sent by a list-view control when the user double-clicks an item with the left mouse button
                    $tInfo = DllStructCreate($tagNMITEMACTIVATE, $ilParam)
;~                     MsgBox(0,"Doubleclick","Index " & DllStructGetData($tInfo, "Index") & @CRLF & "Subitem " & DllStructGetData($tInfo, "SubItem"))
                    $selectedID=DllStructGetData($tInfo, "Index")
                        $selectedINFO=""
                        For $j = 0 To _GUICtrlListView_GetColumnCount($sListView)
                            $selectedINFO=$selectedINFO&"|"&_GUICtrlListView_GetItemText($sListView, $selectedID,$j)
                        Next
                    send ("{ESC}")
                    Return $GUI_RUNDEFMSG
                EndSwitch
    EndSwitch
    Return $GUI_RUNDEFMSG
EndFunc   ;==>WM_NOTIFY

Func f_dbselect($query1,$DB)
    $conn=ObjCreate("ADODB.Connection")
    $conn.Open($DB)
    $conn.CursorLocation = 3
    $rs = $conn.Execute ($query1)
    $tableField_Names=""
    For $tablefield in $rs.fields
        $tableField_Names &= $tablefield.name & "|"
    next
    if $rs.EOF Then
        $getRows_Data=$rs
    Else
        $getRows_Data=$rs.GetRows()
    EndIf
    $rs.close
    $conn.close()
    return $getRows_Data ;=>_createDBlistView()
endfunc