Hi all and Happy new year 2012, i wrote a script to browse any MS-SQL DB table and them value in 1 / Display all Table in a listview1 2 / Create a listview2 with the column of the table selected in listview1 3 / Display all values in listview2 i have a problem with the second loop my value does not appear in the ligne & column and i don't know where is my error, if someone can help me and my noob level. sql.au3 is an include (see attached file) and my code : #include <sql.au3> #include <File.au3> #include <array.au3> #include <GuiMenu.au3> #include <GUIListBox.au3> #include <GuiTreeView.au3> #include <GuiListView.au3> #include <GuiStatusBar.au3> #include <EditConstants.au3> #include <GuiConstantsEx.au3> #include <ButtonConstants.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #include <TreeViewConstants.au3> Global $OU, $hGui, $hListView, $treeOne, $treeItem, $iIndex, $i, $main, $tableselected, $ListeTable1, $colonnes, $username, $servername, $databasename, $GO #Region ### START Koda GUI section ### Form= $main = GUICreate("Form1", 1024, 742, 200, 114) $input1 = GUICtrlCreateInput(@UserName, 88, 8, 141, 21) $input2 = GUICtrlCreateInput("servername", 88, 32, 141, 21) $input3 = GUICtrlCreateInput("SMS_CODE", 88, 56, 141, 21) $Input4 = GUICtrlCreateInput("", 88, 80, 121, 21) $Input5 = GUICtrlCreateInput("", 88, 104, 121, 21) $Input6 = GUICtrlCreateInput("", 88, 128, 121, 21) $Label1 = GUICtrlCreateLabel("Username", 8, 16, 56, 17) $Label2 = GUICtrlCreateLabel("SERVER", 8, 40, 59, 17) $Label3 = GUICtrlCreateLabel("DB", 8, 64, 56, 17) $Label4 = GUICtrlCreateLabel("Label4", 8, 88, 46, 17) $Label5 = GUICtrlCreateLabel("Label5", 8, 112, 46, 17) $Label6 = GUICtrlCreateLabel("Label6", 8, 136, 46, 17) ;~ $List1 = GUICtrlCreateListView("", 8, 160, 249, 565) ;~ $List2 = GUICtrlCreateListView("", 264, 160, 545, 565) $Label7 = GUICtrlCreateLabel("-", 288, 5, 321, 21) $Label8 = GUICtrlCreateLabel("-", 288, 20, 321, 21) $Label9 = GUICtrlCreateLabel("-", 288, 35, 321, 21) $Label10 = GUICtrlCreateLabel("-", 288, 50, 321, 21) $Label11 = GUICtrlCreateLabel("-", 288, 65, 321, 21) $Label12 = GUICtrlCreateLabel("-", 288, 80, 321, 21) Global $ListView1 = _GUICtrlListView_Create($main, "", 8, 160, 249, 565) Global $ListView2 = _GUICtrlListView_Create($main, "", 264, 160, 745, 565) _GUICtrlListView_SetExtendedListViewStyle($hListView, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT, $LVS_EX_SUBITEMIMAGES)) $Button1 = GUICtrlCreateButton("GO", 216, 128, 75, 25, $WS_GROUP) ;~ $Button2 = GUICtrlCreateButton("Button2", 296, 128, 75, 25, $WS_GROUP) $fFlagCG = False $fFlagCG_listview = False $fFlagTL_listview = False GUISetState(@SW_SHOW) #EndRegion ### END Koda GUI section ### GUIRegisterMsg($WM_NOTIFY, "_WM_NOTIFY") While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button1 Go() EndSwitch If $fFlagCG_listview Then $fFlagCG_listview = False ListeColumn($tableselected) EndIf If $fFlagTL_listview Then $fFlagTL_listview = False ;~ MsgBox(64, '', 'coucou') ;~ listtable2($tableselected) EndIf WEnd Func Go() $servername = GUICtrlRead($input2) $databasename = GUICtrlRead($input3) MsgBox(64,'informations', 'U will connect to : ' & $servername & ' on ' & $databasename & ' DB with this : ' & @UserName) listtable() EndFunc ;==>Go Func listtable() _GUICtrlListView_InsertColumn($ListView1, 0, "Tables", '350', -1, -1, False) $sqlCon = ObjCreate("ADODB.Connection") $sqlCon.Open("DRIVER={SQL Server};SERVER=" & $servername & ";DATABASE=" & $databasename & ";UID=" & @UserName & ";Trusted_Connection=Yes;") $NameList = "" If @error Then MsgBox(0, "ERROR", "Failed to connect to the database") Exit Else ;~ MsgBox(0, "Success!", "Connection to database successful!") EndIf $result0 = _SQLQuery($sqlCon, "select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES") $totali = 0 With $result0 While Not .EOF $ligne0 = .Fields("TABLE_NAME" ).value ;~ $ligneOK0 = StringMid($ligne0, 1, 2) ;~ If $ligneOK0 = 'v_' Then ;~ $ligneOK20 = StringMid($ligne0, 1, 13) ;~ If $ligneOK20 = 'v_CM_RES_COLL' Then ;~ Else $totali = $totali + 1 ;~ EndIf ;~ Else ;~ EndIf .MoveNext WEnd EndWith ;~ MsgBox(64, '', $totali + 1) $result = _SQLQuery($sqlCon, "select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES") Dim $ListeTable1[$totali + 1] $i = 0 With $result While Not .EOF $ligne = .Fields("TABLE_NAME" ).value ;~ $ligneOK = StringMid($ligne, 1, 2) ;~ If $ligneOK = 'v_' Then ;~ $ligneOK2 = StringMid($ligne, 1, 13) ;~ If $ligneOK2 = 'v_CM_RES_COLL' Then ;~ Else $i = $i + 1 _GUICtrlListView_AddItem($ListView1, $ligne, $i) $ListeTable1[$i] = $ligne ;~ EndIf ;~ Else ;~ EndIf .MoveNext WEnd EndWith Return $ListeTable1 EndFunc ;==>listtable Func ListeColumn($tableselected) _GUICtrlListView_Destroy($ListView2) $ListView2 = _GUICtrlListView_Create($main, "", 264, 160, 745, 565) _GUICtrlListView_DeleteAllItems($ListView2) $sqlCon = ObjCreate("ADODB.Connection") $sqlCon.Open("DRIVER={SQL Server};SERVER=" & $servername & ";DATABASE=" & $databasename & ";UID=" & @UserName & ";Trusted_Connection=Yes;") $NameList = "" $colonnes = "" $y = 0 If @error Then MsgBox(0, "ERROR", "Failed to connect to the database") Exit Else ;~ MsgBox(0, "Success!", "Connection to NEW database successful!") EndIf $result0 = _SQLQuery($sqlCon, "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='" & $tableselected & "'") $total = 0 With $result0 While Not .EOF $total = $total + 1 .MoveNext WEnd EndWith Sleep(50) msgbox(64,'',$total) Dim $colonneliste[$total] $queryfile = FileOpen(@ScriptDir & '\queryfull.txt', 2) $result0 = _SQLQuery($sqlCon, "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='" & $tableselected & "'") With $result0 While Not .EOF $y = $y + 1 GUICtrlSetData($Label3, $y) $ligne = .Fields("COLUMN_NAME" ).value If $y = 0 Then $colonneliste[$y] = $total Else If $y = ($total - 1) Then FileWrite($queryfile, $ligne) ExitLoop Else $colonneliste[$y] = $ligne FileWrite($queryfile, $ligne & ', ') EndIf EndIf ;~ $colonnes &= $ligne & ',' _GUICtrlListView_InsertColumn($ListView2, $y, $ligne, '75', -1, -1, False) .MoveNext WEnd EndWith FileClose($queryfile) ;~ $queryfile = FileOpen(@ScriptDir & '\queryfull.txt', 0) ;~ $listecols = FileReadLine($queryfile, 1) ;~ MsgBox(64, 'listecols', $total & ' ' & $tableselected & ' ' & $listecols) $result2 = _SQLQuery($sqlCon, 'Select * from ' & $tableselected) $ii = 1 With $result2 While Not .EOF $test = IsArray($colonneliste) If $test = 1 Then ;~ _ArrayDisplay($colonneliste) If $ii > ($total - 2) Then ExitLoop Else ;~ $valueii = StringUpper($colonneliste[$ii]) $newligne = .Fields($colonneliste[$ii]).value _GUICtrlListView_AddItem($ListView2, $newligne) For $dropcols = 1 to ($total - 2) $newligne2 = .Fields($colonneliste[$dropcols]).value ;~ MsgBox(64, '$ii & $newligne2 & $dropcols', $ii & ' ' & $newligne2 & ' ' & $dropcols) _GUICtrlListView_AddSubItem($ListView2, $ii, $newligne2, $dropcols) ;~ $valuedropcols = StringUpper($colonneliste[$dropcols]) ;~ _GUICtrlListView_AddItem($hListView, "Row 1: Col 1", 0) ;~ _GUICtrlListView_AddSubItem($hListView, 0, "Row 1: Col 2", 1) ;~ _GUICtrlListView_AddSubItem($hListView, 0, "Row 1: Col 3", 2) Next EndIf Else MsgBox(64, '$test', 'is not an array') EndIf $ii = $ii + 1 .MoveNext WEnd EndWith $fFlagTL_listview = True Return $colonnes EndFunc ;==>ListeColumn Func listtable2($tableselected) ;~ $sqlCon = ObjCreate("ADODB.Connection") ;~ $sqlCon.Open("DRIVER={SQL Server};SERVER=" & $servername & ";DATABASE=" & $databasename & ";UID=" & @UserName & ";Trusted_Connection=Yes;") ;~ $NameList = "" ;~ If @error Then ;~ MsgBox(0, "ERROR", "Failed to connect to the database") ;~ Exit ;~ Else ;~ MsgBox(0, "Success!", "Connection to database successful!") ;~ EndIf ;~ Return $ListeTable1 EndFunc ;==>listtable2 Func _WM_NOTIFY($hWnd, $iMsg, $wParam, $lParam) #forceref $hWnd, $iMsg, $wParam Local $tNMHDR, $hWndFrom, $iCode $tNMHDR = DllStructCreate($tagNMHDR, $lParam) $hWndFrom = HWnd(DllStructGetData($tNMHDR, "hWndFrom")) $iCode = DllStructGetData($tNMHDR, "Code") Local $IDFrom = DllStructGetData($tNMHDR, "IDFrom") Local $tStruct = DllStructCreate("hwnd;uint_ptr;int_ptr;int;int", $lParam) ; Increase the struct size to get the column <<<<<<<<<<<<<<<< If @error Then Return Switch DllStructGetData($tStruct, 3) ;~ Menu listview des users = Struct4 (0x00741A46) ;~ Menu Treeview OU = Struct4 (0x0058040E) Case $NM_RCLICK ; determine if right click on an item Case $NM_DBLCLK ; Look for the double click <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Case $NM_CLICK ; determine if Left click on an item GUICtrlSetData($Label7, 'DllStructGetData($tStruct, 0) = ' & DllStructGetData($tStruct, 0)) GUICtrlSetData($Label8, 'DllStructGetData($tStruct, 1) = ' & DllStructGetData($tStruct, 1)) GUICtrlSetData($Label9, 'DllStructGetData($tStruct, 2) = ' & DllStructGetData($tStruct, 2)) GUICtrlSetData($Label10, 'DllStructGetData($tStruct, 3) = ' & DllStructGetData($tStruct, 3)) GUICtrlSetData($Label11, 'DllStructGetData($tStruct, 4) = ' & DllStructGetData($tStruct, 4)) GUICtrlSetData($Label12, 'DllStructGetData($tStruct, 5) = ' & DllStructGetData($tStruct, 5)) If DllStructGetData($tStruct, 2) = 10001 Then $fFlagCG = True EndIf If DllStructGetData($tStruct, 2) = 10000 Then $fFlagCG_listview = True $tableselected = $ListeTable1[DllStructGetData($tStruct, 4) + 1] msgbox(64,'$tableselected = ',$tableselected) Return $tableselected EndIf Return 0 EndSwitch Return $GUI_RUNDEFMSG EndFunc ;==>_WM_NOTIFY sql.au3