#include #include #include #Include #Include #include opt("GUIOnEventMode", 1) opt("WinTitleMatchMode", 2) ;1=start, 2=subStr, 3=exact, 4=advanced opt("MouseCoordMode", 0) ;1=absolute, 0=relative, 2=client ; Initialize SvenP 's error handler $oMyError = ObjEvent("AutoIt.Error", "MyErrFunc") ;Set up the database object Global $oDb $oDb = ObjCreate( "LiteX.LiteConnection") Global $ExcelLV Global $CurTree Global $TreeVDB Global $DBPath Global $RecordGUI Global $FieldNames Global $New Global $InputBoxes Global $CurTable Global $BeginnerMode Global $TableNames[15] Global $CurListIndex Global $FieldGUI Global $FieldGUIwidth Global $FieldInputBox Global $UpdatedLabel Global $rgAllowDefaults global $UseExcelLV Global $LastNumOfHeadings = 1 global $LastNumOfRows = 1 global $StatusLabel Dim $Integrity, $TempStore, $PageSize, $DefaultCacheSize, $AutoVacuum, $Synchrone ; PTREX ;MsgBox(0, "Feedback", "Hello from SQLite version" & $oDb.Version & "!") #region GUI CREATION ;GUI variables $xBorderL = 9;distance from left side of gui $xBorderR = 9;distance from right side of gui $yBorderTier1 = 1;Tier 1 distance from top of gui $yBorderTier2 = 415 ;Tier 2 distance from top of gui $yBorderTier3 = 440 ;Tier 3 distance from top of gui $yBorderTier4 = 735 $xSpacing = 5;distance between controls in the x dimension $ySpacing = 5;distance between controls in the y dimension $GUIWidth = 1000 $GUIHeight = 775 $GUIText = "SQLite GUI - v4.5" $Main = GUICreate($GUIText, $GUIWidth, $guiheight, (@DesktopWidth - $GUIWidth) / 2, (@DesktopHeight - $GUIHeight) / 2,$ws_visible+$ws_clipsiblings+$WS_OVERLAPPEDWINDOW) GUISetOnEvent($GUI_EVENT_CLOSE, "GUIevent") ;This line tells the GUI to close when the "X" is clicked $Helpmenu = GUICtrlCreateMenu("File") $Helpitem = GUICtrlCreateMenuItem("Open/Create Database", $Helpmenu) guictrlsetonevent(-1,"ButtonOpenDB") $Infoitem = GUICtrlCreateMenuItem("Close Database", $Helpmenu) guictrlsetonevent(-1,"CloseDB") $InputDBheight = 20 $InputDBwidth = 250 $InputDBPath = GUICtrlCreateInput("", $xBorderL, $yBorderTier1, $InputDBwidth, $InputDBheight) GUICtrlSetState(-1, $gui_hide) ; PTREX $InfoDB = GUICtrlCreateLabel("", $xBorderL, $yBorderTier1, $InputDBwidth, $InputDBheight,$SS_SUNKEN) ; PTREX $TreeDBwidth = $InputDBwidth $TreeDBheight = 375 $TreeDB = GUICtrlCreateTreeView($xBorderL, $yBorderTier1 + $InputDBheight + $ySpacing, $TreeDBwidth, $TreeDBheight) GUICtrlSetImage (-1, "objsel.dll",2) ; PTREX $ListVDBxPos = $InputDBwidth + $xBorderL + $xSpacing $UseExcelLV = true if $UseExcelLV = false Then $ListVDB = GUICtrlCreateListView("Field Names...", $ListVDBxPos, $yBorderTier1, $GUIWidth - $ListVDBxPos - $xBorderR, $TreeDBheight + $ySpacing + $InputDBheight, $LVS_SHOWSELALWAYS + $LVS_SINGLESEL, $LVS_EX_GRIDLINES + $LVS_EX_FULLROWSELECT) Else $ListVDB = _GUIctrlcreatelistviewenhanced("Field Names...", $ListVDBxPos, $yBorderTier2, $GUIWidth - $ListVDBxPos - $xBorderR, $TreeDBheight + $ySpacing + $InputDBheight) EndIf $StatusLabel = guictrlcreatelabel("",$xborderl-3,$ybordertier4,250,18,$SS_SUNKEN) ; PTREX changed size $DatabasePathLabel = guictrlcreatelabel("",$xborderl-3+500+$xspacing,$ybordertier4,$guiwidth-2*$xborderl-600,18,$SS_SUNKEN) $PragmaLabel = guictrlcreatelabel("",$xborderl-3+252,$ybordertier4,250,18,$SS_SUNKEN) ; PTREX Added $VersionLabel = guictrlcreatelabel("SQLite v" & $odb.version,$xborderl-3+500+2*$xspacing+$guiwidth-2*$xborderl-600,$ybordertier4,95,18,$SS_SUNKEN) $TabHeight = $GUIHeight - $yBorderTier2 - 23 - 18 $Tab1 = GUICtrlCreateTab(5, $ybordertier2, $GUIWidth - 10,$TabHeight) GUICtrlSetResizing($Tab1,$gui_dockauto) $Tab_Database = GUICtrlCreateTabItem(" Database Control ") GUICtrlSetState(-1, $GUI_SHOW) #region ;Database controls $Group0Ystart = $yBorderTier3 $Group0Xstart = $xBorderL $Group0width = 90 $Group0Height = $TabHeight -37 GUICtrlCreateGroup("Database", $Group0Xstart, $Group0Ystart, $Group0width, $Group0Height ) $bNewDwidth = 80 $bNewDHeight = 40 $bNewDxStart = $group0xstart+ ($Group0width - $bNewDwidth) / 2 $bNewDyStart = $Group0Ystart + 15 $bNewD = GUICtrlCreateButton("Open/Create Database", $bNewDxStart, $bNewDyStart, $bNewDwidth, $bNewDHeight, $BS_MULTILINE) GUICtrlSetOnEvent($bNewD, "ButtonOpenDB") $bRefreshD = GUICtrlCreateButton("Refresh Database", $bNewDxStart, $bNewDyStart + $ySpacing + $bNewDHeight, $bNewDwidth, $bNewDHeight, $BS_MULTILINE) GUICtrlSetOnEvent(-1, "RefreshDatabase") $bVacuumD = GUICtrlCreateButton("Vacuum Database", $bNewDxStart, $bNewDyStart + 6 * $ySpacing + 2 * $bNewDHeight, $bNewDwidth, $bNewDHeight, $BS_MULTILINE) GUICtrlSetOnEvent(-1, "VacuumDatabase") GUICtrlCreateGroup("", -99, -99, 1, 1) ;Table Controls $Group1Ystart = $yBorderTier3 $Group1Xstart = $Group0Xstart + $Group0width + $xSpacing + 20 $Group1width = 210 GUICtrlCreateGroup("Create New Table", $Group1Xstart, $Group1Ystart, $Group1width, $Group0Height) $Group1ItemYStart = $Group1Ystart + 20 $Group1ItemXStart = $Group1Xstart + 5 $NewTableLabelHeight = 20 GUICtrlCreateLabel("Table Name:", $Group1Xstart + $xSpacing, $Group1ItemYStart, 100, $NewTableLabelHeight) $NewTableInputHeight = 21 $NewTableInput = GUICtrlCreateInput("", $Group1ItemXStart, $Group1ItemYStart + $NewTableLabelHeight - 5, 200, $NewTableInputHeight) $NewFieldButtonHeight = 23 $NewFieldButton = GUICtrlCreateButton("New FIELD", $Group1ItemXStart, $Group1ItemYStart + $NewTableLabelHeight + $NewTableInputHeight, 100, $NewFieldButtonHeight) GUICtrlSetOnEvent($NewFieldButton, "NewFieldNewTable") $NewFieldsLVHeight = 140 $NewFieldsLV = GUICtrlCreateListView("Name|Type", $Group1ItemXStart, $Group1ItemYStart + $NewTableLabelHeight + $NewTableInputHeight + $NewFieldButtonHeight+$ySpacing, $Group1width-10, $NewFieldsLVHeight) $NewTableButton = GUICtrlCreateButton("Create TABLE", $Group1ItemXStart, $Group1ItemYStart + $NewTableLabelHeight + $NewTableInputHeight + $NewFieldButtonHeight+$ySpacing+$NewFieldsLVHeight, 100, 23) GUICtrlSetOnEvent(-1, "NewTable") GUICtrlCreateGroup("", -99, -99, 1, 1) $Group2Ystart = $yBorderTier3 $Group2Xstart = $Group1Xstart + $Group1width + $xSpacing $Group2width = 125 $Group2Height = $Group0Height GUICtrlCreateGroup("Modify Selected Table", $Group2Xstart, $Group2Ystart, $Group2width, $Group2Height) $bTableRYstart = $Group1ItemYStart $bTableRwidth = $Group2width - 10 $bTableRheight = 25 $bTableRxStart = $group2xstart+($Group2width - $bTableRwidth) / 2 $bTableR = GUICtrlCreateButton("Rename Table", $bTableRxStart, $bTableRYstart, $bTableRwidth, $bTableRheight) GUICtrlSetOnEvent(-1, "RenameTable") $bTableDyStart = $bTableRYstart + $bTableRheight + $ySpacing $bTableD = GUICtrlCreateButton("Delete Table", $bTableRxStart, $bTableDyStart, $bTableRwidth, $bTableRheight) GUICtrlSetOnEvent(-1, "DeleteTable") $bFieldAyStart = $bTableDyStart + $bTableRheight + 5 * $ySpacing $bFieldA = GUICtrlCreateButton("Insert Field", $bTableRxStart, $bFieldAyStart, $bTableRwidth, $bTableRheight) guictrlsetonevent(-1,"NewFieldExistingTable") GUICtrlSetState(-1, $gui_disable) $cFieldComboWidth = $Group2width - 25 $cFieldComboXStart = $group2xstart+ ($Group2width - $cFieldComboWidth) / 2 $cFieldComboYStart = $bFieldAyStart + $bTableRheight + 2*$ySpacing $cFieldCombo = GUICtrlCreateCombo("", $bTableRxStart, $cFieldComboYStart, $bTableRwidth, $bTableRheight) GUICtrlSetState(-1, $gui_disable) $bFieldRyStart = $cFieldComboYStart + $bTableRheight + $ySpacing $bFieldR = GUICtrlCreateButton("Edit Field Name", $bTableRxStart, $bFieldRyStart, $bTableRwidth, $bTableRheight) GUICtrlSetState(-1, $gui_disable) $bFieldTyStart = $bFieldRyStart + $bTableRheight + $ySpacing $bFieldT = GUICtrlCreateButton("Edit Field Type", $bTableRxStart, $bFieldTyStart, $bTableRwidth, $bTableRheight) GUICtrlSetState(-1, $gui_disable) $bFieldNyStart = $bFieldTyStart + $bTableRheight + $ySpacing $bFieldN = GUICtrlCreateButton("Delete Field", $bTableRxStart, $bFieldNyStart, $bTableRwidth, $bTableRheight) guictrlsetstate(-1, $gui_disable) GUICtrlCreateGroup("", -99, -99, 1, 1) ;Record Controls $Group3Ystart = $yBorderTier3 $Group3Xstart = $Group2Xstart + $Group2width + $xSpacing + 20 $Group3width = 115 $Group3Height = $Group0Height GUICtrlCreateGroup("Modify Records", $Group3Xstart, $Group3Ystart, $Group3width, $Group3Height) $bNewRWidth = $Group3Width - 10 $bNewRHeight = 25 $bNewRxStart = $group3xstart+($Group3width - $bNewRWidth) / 2 $bNewRyStart = $Group1ItemYStart $bNewR = GUICtrlCreateButton("New Record", $bNewRxStart, $bNewRyStart, $bNewRWidth, $bNewRHeight) GUICtrlSetOnEvent($bNewR, "AddRecord") $bEditRHeight = $bNewRHeight + 11 $bEditR = GUICtrlCreateButton("Edit Selected Record", $bNewRxStart, $bNewRyStart + $bNewRHeight + $ySpacing, $bNewRWidth, $bEditRHeight, $BS_MULTILINE) GUICtrlSetOnEvent($bEditR, "EditRecord") $bDeleteR = GUICtrlCreateButton("Delete Record", $bNewRxStart, $bNewRyStart + $bNewRHeight + $bEditRHeight + 2 * $ySpacing, $bNewRWidth, $bNewRHeight) GUICtrlSetOnEvent(-1, "DeleteRecord") GUICtrlCreateGroup("", -99, -99, 1, 1) ;Index Controls $Group4Ystart = $yBorderTier3 $Group4Xstart = $Group3Xstart + $Group3width + $xSpacing + 20 $Group4width = 125 $Group4Height = $Group0Height GUICtrlCreateGroup("Modify Indexes", $Group4Xstart, $Group4Ystart, $Group4width, $Group4Height) $cIndexComboWidth = $Group4width - 10 $cIndexComboXStart = $group4xstart+ ($Group4width - $cIndexComboWidth) / 2 $iLabelHeight = 20 GUICtrlCreateLabel("Choose Fields to index:",$group4xstart+ ($Group4width - $cIndexComboWidth) / 2,$Group4Ystart + 20,$Group4width - 15,$iLabelHeight) $cIndexComboYStart = $Group4Ystart + 15 + $iLabelHeight $cIndexCombo = GUICtrlCreateInput("", $cIndexComboXStart, $cIndexComboYStart, $cIndexComboWidth, 20) $RadioGroup1xStart = $cIndexComboXStart $RadioGroup1yStart = $cIndexComboYStart + 3 * $ySpacing $RadioGroup1Width = $Group4width - 10 GUICtrlCreateGroup("", $RadioGroup1xStart, $RadioGroup1yStart, $RadioGroup1Width, 55) $DescendRadio = GUICtrlCreateRadio("Descend (A-Z)", $RadioGroup1xStart + 5, $RadioGroup1yStart + 10, $RadioGroup1Width-15, 20) guictrlsetstate(-1, $GUI_CHecked) $AscendRadio = GUICtrlCreateRadio("Ascending (Z-A)", $RadioGroup1xStart + 5, $RadioGroup1yStart + 20 + $ySpacing+3, $RadioGroup1Width-15, 25) GUICtrlCreateGroup("", -99, -99, 1, 1) $RadioGroup2xStart = $cIndexComboXStart $RadioGroup2yStart = $RadioGroup1yStart + 52 GUICtrlCreateGroup("", $RadioGroup2xStart, $RadioGroup2yStart, $RadioGroup1Width, 55) $UniqueRadio = GUICtrlCreateRadio("Unique", $RadioGroup2xStart + 5, $RadioGroup2yStart + 10, $RadioGroup1Width-15, 20) GUICtrlSetState(-1, $GUI_CHecked) $NUniqueRadio = GUICtrlCreateRadio("Not Unique", $RadioGroup2xStart + 5, $RadioGroup2yStart + 20 + $ySpacing, $RadioGroup1Width-15, 25) GUICtrlCreateGroup("", -99, -99, 1, 1) $bCreateIndexYstart = $RadioGroup2yStart + 55 + $ySpacing $bCreateIndex = GUICtrlCreateButton("Create Index", $cIndexComboXStart + 5, $bCreateIndexYstart, $Group4width - 25, 23) GUICtrlSetOnEvent(-1, "CreateIndex") $bDeleteIndex = GUICtrlCreateButton("Delete Index", $cIndexComboXStart + 5, $bCreateIndexYstart + 23 + 3 * $ySpacing, $Group4width - 25, 23) guictrlsetonevent(-1,"DeleteIndex") GUICtrlCreateGroup("", -99, -99, 1, 1) ;Misc Controls $Group5Ystart = $yBorderTier3 $Group5Xstart = $Group4Xstart + $Group4width + $xSpacing + 20 $Group5width = 110 $Group5Height = $Group0Height GUICtrlCreateGroup("Misc", $Group5Xstart, $Group5Ystart, $Group5width, $Group5Height) $bExcelInWidth = $Group5width-10 $bExcelInHeight = 30 $bExcelInxStart = $group5xstart+ ($Group5width - $bExcelInWidth) / 2 $bExcelInyStart = $Group5Ystart + 20 $bExcelIn = GUICtrlCreateButton("Excel Import New", $bExcelInxStart, $bExcelInyStart, $bExcelInWidth, $bExcelInHeight) GUICtrlSetOnEvent(-1,"XLSImportNew") $bExcelIn2 = GUICtrlCreateButton("Excel Import Existing", $bExcelInxStart, $bExcelInyStart+30 +$ySpacing, $bExcelInWidth, $bExcelInHeight) GUICtrlSetOnEvent(-1,"XLSImportExisting") $bExcelOutHeight = $bExcelInHeight $bExcelOut = GUICtrlCreateButton("CSV Export", $bExcelInxStart, $bExcelInyStart + 2*$bExcelInHeight + 2*$ySpacing, $bExcelInWidth, $bExcelOutHeight, $BS_MULTILINE) GUICtrlSetState(-1, $gui_disable) $cBeginner = GUICtrlCreateCheckbox("Beginner Mode", $bExcelInxStart, $bExcelInyStart + 3 * $bExcelInHeight + 3 * $ySpacing, $bExcelInWidth, 25) GUICtrlSetOnEvent(-1, "Beginner") GUICtrlCreateGroup("", -99, -99, 1, 1) #endregion GUICtrlCreateTabItem("") $SQL_View = GUICtrlCreateTabItem(" SQL Entry ") #region ;SQL View Tab $SQLinsertY = $yBorderTier2 + 30 $SQLlabelHeight = 20 $SQLinsertLabel = GUICtrlCreateLabel("Enter SQL Commands:", $xBorderL + 10, $SQLinsertY, 500, $SQLlabelHeight) $SQLInsert = GUICtrlCreateInput("", $xBorderL + 10, $SQLinsertY + $SQLlabelHeight, 500, 100,$ES_MULTILINE+$ES_WANTRETURN) $SQLcommit = GUICtrlCreateButton("Process SQL", $xBorderL + 10, $SQLinsertY + $SQLlabelHeight + 110, 90, 24) GUICtrlSetOnEvent(-1, "CommitSQL") HotKeySet("{F9}", "CommitSQL") ;PTREX HotKeySet("^{TAB}","_Tab_Switch") ;PTREX #endregion $Tab_View = GUICtrlCreateTabItem(" Advanced View ") ;Tier 3------------------------------------------ $TreeWidth = 250 $TreeHeight = 350 $ComboHeight = 20 $ApplyChanges = GUICtrlCreateButton("Apply Changes", $xBorderL+500 , $SQLinsertY+10, 90, 24) GUICtrlSetOnEvent(-1, "_Pragma_info") ; Memory Setting $bRefreshD = GUICtrlCreateButton("Refresh Database", $bNewDxStart+5, $bNewDyStart + $ySpacing + $bNewDHeight+120, $bNewDwidth, $bNewDHeight, $BS_MULTILINE) GUICtrlSetOnEvent(-1, "RefreshDatabase") GUICtrlCreateGroup("DB Memory Settings", $Group0Xstart, $Group1Ystart, 150, $Group0Height ) $CacheSizeLabel = GUICtrlCreateLabel("Enter DB Cache Size :", $xBorderL+10, $SQLinsertY+20, 500, $SQLlabelHeight) $CacheSizeInput = GUICtrlCreateInput("2000", $xBorderL+10, $SQLinsertY+40, 80, 20,$ES_MULTILINE+$ES_WANTRETURN) GUICtrlSetTip(-1,"# of Pages, 1 page = 1,5 K ; Default = 2000") $TempStoreLabel = GUICtrlCreateLabel("Temp Store Settings :", $xBorderL + 10, $SQLinsertY+70, 500, $SQLlabelHeight) $RadioTempStore0 = GUICtrlCreateRadio("Default",$xBorderL+10, $SQLinsertY +90, 120, 20,$WS_GROUP) $RadioTempStore1 = GUICtrlCreateRadio ("Temp File",$xBorderL+10, $SQLinsertY+ 110, 120, 20) $RadioTempStore2 = GUICtrlCreateRadio ("In Memory",$xBorderL+10, $SQLinsertY+ 130, 120, 20) GUICtrlSetState(-1,$GUI_CHECKED ) ; Misc. Settings GUICtrlCreateGroup("DB advanced Settings I", $Group0Xstart+160, $Group1Ystart, 150, $Group0Height ) $CountChangeLabel= GUICtrlCreateLabel("Count Changes : ", $xBorderL+170, $SQLinsertY+20, 500, $SQLlabelHeight) $RadioCount0 = GUICtrlCreateRadio("Off",$xBorderL+170, $SQLinsertY+35, 40, 20,$WS_GROUP) $RadioCount1 = GUICtrlCreateRadio ("On",$xBorderL+220, $SQLinsertY+35, 40, 20) GUICtrlSetState(-1,$GUI_CHECKED ) $CaseSensitiveLabel= GUICtrlCreateLabel("Case Sensitive : ", $xBorderL+170, $SQLinsertY+60, 500, $SQLlabelHeight) $RadioCaseSens0 = GUICtrlCreateRadio("Off",$xBorderL+170, $SQLinsertY +75, 40, 20,$WS_GROUP) $RadioCaseSens1 = GUICtrlCreateRadio ("On",$xBorderL+220, $SQLinsertY+75, 40, 20) GUICtrlSetState(-1,$GUI_CHECKED ) $ParserTraceLabel= GUICtrlCreateLabel("Parser Trace : ", $xBorderL+170, $SQLinsertY+100, 500, $SQLlabelHeight) $RadioParser0 = GUICtrlCreateRadio("Off",$xBorderL+170, $SQLinsertY +115, 40, 20,$WS_GROUP) GUICtrlSetState(-1,$GUI_CHECKED ) $RadioParser1 = GUICtrlCreateRadio ("On",$xBorderL+220, $SQLinsertY+115, 40, 20) $VDBETraceLabel= GUICtrlCreateLabel("VDBE Trace : ", $xBorderL+170, $SQLinsertY+140, 500, $SQLlabelHeight) $RadioVDBETrace0 = GUICtrlCreateRadio("Off",$xBorderL+170, $SQLinsertY +155, 40, 20,$WS_GROUP) GUICtrlSetState(-1,$GUI_CHECKED ) $RadioVDBETrace1 = GUICtrlCreateRadio ("On",$xBorderL+220, $SQLinsertY+155, 40, 20) $VDBEListingLabel= GUICtrlCreateLabel("VDBE Listing : ", $xBorderL+170, $SQLinsertY+180, 500, $SQLlabelHeight) $RadioVDBEList0 = GUICtrlCreateRadio("Off",$xBorderL+170, $SQLinsertY +195, 40, 20,$WS_GROUP) GUICtrlSetState(-1,$GUI_CHECKED ) $RadioVDBEList1 = GUICtrlCreateRadio ("On",$xBorderL+220, $SQLinsertY+195, 40, 20) $EncodingLabel= GUICtrlCreateLabel("Encodig : ", $xBorderL+170, $SQLinsertY+220, 500, $SQLlabelHeight) $ComboEncoding = GUICtrlCreateCombo ("",$xBorderL+170, $SQLinsertY+240, 100, $SQLlabelHeight) GUICtrlSetData(-1,"UTF-8|UTF-16|UTF-16le|UTF-16be","UTF-8") GUICtrlSetTip(-1,"Can only be changed if the DB has not been created, Default = UTF-8") ; Advance Info GUICtrlCreateGroup("DB advanced Settings II", $Group0Xstart+320, $Group1Ystart, 150, $Group0Height) $FullColumnLabel= GUICtrlCreateLabel("Full Column Names : ", $xBorderL+330, $SQLinsertY+20, 500, $SQLlabelHeight) $RadioFullColumn0 = GUICtrlCreateRadio("Off",$xBorderL+330, $SQLinsertY +35, 40, 20,$WS_GROUP) GUICtrlSetState(-1,$GUI_CHECKED ) $RadioFullColumn1 = GUICtrlCreateRadio ("On",$xBorderL+380, $SQLinsertY+35, 40, 20) $ShortColumnLabel= GUICtrlCreateLabel("Short Column Names : ", $xBorderL+330, $SQLinsertY+60, 500, $SQLlabelHeight) $RadioShortColumn0 = GUICtrlCreateRadio("Off",$xBorderL+330, $SQLinsertY+75, 40, 20,$WS_GROUP) $RadioShortColumn1 = GUICtrlCreateRadio ("On",$xBorderL+380, $SQLinsertY+75, 40, 20) GUICtrlSetState(-1,$GUI_CHECKED ) $SchemaVersionLabel= GUICtrlCreateLabel("Schema Version : ", $xBorderL+330, $SQLinsertY+200, 500, $SQLlabelHeight) $UserVersionLabel= GUICtrlCreateLabel("User Version : ", $xBorderL+330, $SQLinsertY+220, 500, $SQLlabelHeight) $UserVersionInput= GUICtrlCreateInput("0", $xBorderL+330, $SQLinsertY+240, 100, $SQLlabelHeight) GUICtrlSetTip(-1,"This value can be set as a user defined variable, Default = 0") ;End Tier GUICtrlSetState($ExcelLV,$GUI_FOCUS) ;Set focus on the Excel Object GUISetState(@SW_SHOW, $Main) $tmp = WinGetPos($GUIText) MouseClick("left", $tmp[0] + 275, $tmp[1] + 30,1,1) While 1 Sleep(8000) WEnd Exit #endregion - GUI Creation Func OpenDatabase($DBPath1) Dim $oStmt Dim $GetIndices Dim $GetFieldNames Dim $GetTableNames Dim $TableNames[15] ;Close the database connection so you know it isn't open $odb.close (); GUICtrlSetState($bRefreshD, $GUI_ENABLE) ;If the database is being refreshed you dont ask for a path If $DBPath1 = "" Then $DBPath = FileOpenDialog("New or Existing Database...", "C:\Kevin\AutoItScripts\PartsDatabase", "DB (*.db;db3)|All (*.*)") ;Ensure the .db extension is applied If StringRight($DBPath, 3) <> ".db" and $dbpath <> "" Then $DBPath = $DBPath & ".db" EndIf ElseIf $DBPath1 <> "" Then $DBPath = $DBPath1 EndIf if $dbpath <> "" Then ;Put database path into the path inputbox GUICtrlSetData($InputDBPath, $DBPath);PTREX GUICtrlSetData($DatabasePathLabel,"Current Database: " & $DBPath) ;Reset Tree _GUICtrlTreeViewDeleteAllItems ($TreeDB) ;Open the database file (creates it if it does not exist) $oDb.Open ($DBPath) ;Find all Tables and columns and put them on the Tree Control for easy reference $SQL = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name" $GetTableNames = $odb.prepare ($SQL) If $BeginnerMode = True Then MsgBox(0, "Gather CREATE statements", $SQL) EndIf $Data = "" ;NameCount is a count of all tables... used to expand the TableNames array if it exceeds 15 $NameCount = 0 $HaveLog = false While $GetTableNames.step () = 0 ;Put the table name in $Data $Data = $GetTableNames.ColumnValue (0) if $data = "z_SQL_Log" Then $HaveLog = True EndIf ;Put the table name in the table name array If $NameCount > 14 Then _ArrayInsert($TableNames, UBound($TableNames)) EndIf $TableNames[$NameCount] = $Data $NameCount = $NameCount + 1 ;Create TreeView item for table $CurTree = GUICtrlCreateTreeViewItem($Data, $TreeDB) GUICtrlSetImage (-1, "mmcndmgr.dll",13) ; PTREX GUICtrlSetOnEvent($CurTree, "TreeDBTableClick") ;Getting the field names from the current table $SQL = "SELECT * FROM " & $Data $GetFieldNames = $oDb.Prepare ($SQL) $ColumnCount = $GetFieldNames.columncount dim $TempFieldNames[$columncount+1] for $ColCount = 0 to $columncount-1 $TempFieldNames[$ColCount] = $getfieldnames.columnname($colcount) Next ;Getting the field type requires parsing the original CREATE statement of the table located in the sql field $GetFieldType = $odb.prepare ("SELECT name,sql FROM sqlite_master WHERE tbl_name='" & $Data & "'") ;FirstIndex tells the program that this is the first index for a given table. This way ;only one index tree item is created for each table $FirstIndex = True $FirstMiscIndex = True ;This section grabs the CREATE statement for the current table or index and puts it in the treeview While $GetFieldType.step () = 0 $Type = $getfieldtype.columnvalue (0) $Type1 = $getfieldtype.columnvalue(1) ;Each statement has two "columns", one is the tablename and the other is the CREATE statement. We dont want the name part. ;There are three types of CREATES, TABLE and INDEX and Misc (Misc catches things I dont know about!) If StringInStr($Type1, "TABLE", 1) Then $Junk = stringinstr($type1,"(",1,1) $TempType = stringtrimleft($type1,$junk) $TempArray = stringsplit($TempType,",") ;Ok now that you have the data available (Field Name and Type), time to go back and put it together For $Count = 0 To $ColumnCount - 1 ;Column Name $Data1 = $GetFieldNames.columnname ($Count) ;Parse Column Type $ColType = StringStripWS($TempArray[$count+1], 3) If StringRight($ColType, 1) = ")" Then $ColType = StringTrimRight($ColType, 1) ;Put Column name and Column Type in the Tree underneath table item GUICtrlCreateTreeViewItem($ColType, $CurTree) $Data1 = "" Next ElseIf StringInStr($Type1, "INDEX", 1) Then ;If it's the first index for the table you need to create an INDEX item If $FirstIndex = True Then $IndexItem = GUICtrlCreateTreeViewItem("Indexes for " & $Data, $CurTree) $FirstIndex = False EndIf ;I may parse the index down a bit... for now I'm not! If StringInStr($Type, "UNIQUE", 1) Then GUICtrlCreateTreeViewItem($Type1, $IndexItem) ControlFocus("Parts Database", "", $InputDBPath) ;PTREX Else GUICtrlCreateTreeViewItem($Type1, $IndexItem) ControlFocus("Parts Database", "", $InputDBPath) ;PTREX EndIf Else ;If it's the first Misc for the table you need to create an INDEX item If $FirstMiscIndex = True Then $MiscItem = GUICtrlCreateTreeViewItem("Misc", $CurTree) $FirstMiscIndex = False EndIf If $Type = "0" Then EndIf GUICtrlCreateTreeViewItem($Type, $MiscItem) EndIf WEnd WEnd if $HaveLog = false Then $SQL = "CREATE TABLE z_SQL_Log(TimeStamp TEXT DEFAULT CURRENT_TIMESTAMP , Type TEXT , Comment TEXT , SQLEntry TEXT)" $odb.execute("BEGIN TRANSACTION") $odb.execute($SQL) $odb.execute("INSERT INTO z_SQL_Log(SQLEntry, Type, Comment) VALUES('" & $SQL & "' , 'CREATE TABLE' , 'Initialize Log Table')") $ODB.execute("COMMIT TRANSACTION") EndIf _Pragma_Info() EndIf GUICtrlSetData($Pragmalabel,"DB integrity = " & $Integrity & " Page Size = " & $PageSize & " Bytes") ; PTREX EndFunc ;==>OpenDatabase Func TreeDBTableClick() Dim $GetFieldNames Dim $Data1 = "" GUISetState(@SW_LOCK) ;Get the current selected table from the TreeView $ItemID = _GUICtrlTreeViewGetTree ($TreeDB, "|") $TempArray = StringSplit($ItemID, "|") ;Store the Current Table for future reference If $TempArray[1] <> "" Then $CurTable = $TempArray[1] ;Get field names so you can put them at the top of the ListView control $GetFieldNames = $oDb.Prepare ("SELECT * FROM " & $CurTable) $ColumnCount = $GetFieldNames.columncount ;Now you need to put the Field Name and Type in an array to save for ; other functions' reference ;Get the FieldNames array set for entering in field data If $ColumnCount = 1 Then Dim $FieldNames[$ColumnCount][$ColumnCount + 1] Else Dim $FieldNames[$ColumnCount][$ColumnCount] EndIf ;Get the CREATE statement for this table so you can get the FieldTypes $SQL = "SELECT sql FROM sqlite_master WHERE tbl_name='" & $CurTable & "'" $GetFieldType = $odb.prepare ($SQL) If $BeginnerMode = True Then MsgBox(0, "Get the CREATE statements from table", $SQL) EndIf ;This grabs the table CREATE statement (there are different types, INDEX for example) While $getfieldtype.step () = 0 For $NewColCount = 0 To $GetFieldType.columncount - 1 $TempString = $getfieldtype.columnvalue ($NewColCount) If StringInStr($TempString, "CREATE TABLE", 1) Then $Type = $GetFieldType.columnvalue ($NewColCount) EndIf Next WEnd If $BeginnerMode = True Then MsgBox(0, "A Sample CREATE statement", $Type) EndIf ;We want the first column of the listview to be the interanal SQLite rowid $Data1 = "RowID*" ;Generate the new header columns for the ListView For $Count = 0 To $ColumnCount - 1 ;Column Name If StringInStr($Type, "TABLE", 1) Then $Data1 = $Data1 & "|" & $GetFieldNames.columnname ($Count) $FieldNames[$Count][0] = $getfieldnames.columnname ($Count) $Junk = stringinstr($type,"(",1,1) $TempType = stringtrimleft($type,$junk) $TempArray = stringsplit($TempType,",") ;Parse Column Type $ColType = StringStripWS($TempArray[$Count+1], 3) If StringRight($ColType, 1) = ")" Then $ColType = StringTrimRight($ColType, 1) $FieldNames[$Count][1] = $ColType If StringInStr($ColType, "PRIMARY KEY", 1) <> 0 Then $Data1 = $Data1 & "*" EndIf EndIf Next ;Reset the ListView control and put in all data from the selected table ;Regen listview... if $UseExcelLV = false Then GUICtrlDelete($ListVDB) GUISwitch($Main, $Tab_Database) $ListVDB = GUICtrlCreateListView($Data1, $ListVDBxPos, $yBorderTier2, $GUIWidth - $ListVDBxPos - $xBorderR, $TreeDBheight + $ySpacing + $InputDBheight, $LVS_SHOWSELALWAYS + $LVS_SINGLESEL, $LVS_EX_GRIDLINES + $LVS_EX_FULLROWSELECT) GUICtrlSetState($Tab_Database, $GUI_SHOW) Else with $ExcelLV For $Headercount = 1 to $LastNumOfHeadings .activewindow.columnheadings($HeaderCount).caption = $headercount Next $TempColHeadings = stringsplit($data1,"|",1) ;_arraydisplay($tempcolheadings,"") if $lastnumofrows = 0 Then $lastnumofrows = 1 .ActiveSheet.Range("A1:IV" & $lastnumofrows).Clear $LastNumOfHeadings = $tempcolheadings[0] for $HeaderCount = 1 to $TempColHeadings[0] .activewindow.columnheadings($HeaderCount).caption = $TempColHeadings[$HeaderCount] .columns($HeaderCount).autofit Next EndWith EndIf ;guisetstate(@sw_unlock) ;guisetstate(@sw_lock) $begin = TimerInit() ;Grab all data from database table and display in ListView $SQL = "SELECT rowid,* FROM " & $CurTable & " ORDER BY rowid" $TableData = $oDb.Prepare ($SQL) $dif = TimerDiff($begin) If $BeginnerMode = True Then MsgBox(0, "Select all data, order by rowid", $SQL) EndIf ;Read data into table... lock listview so it doesn't flicker if $UseExcelLV = false Then ;GUISetState(@SW_LOCK) While $tabledata.step () = 0 $Data = "" For $Count = 0 To $ColumnCount $Data = $Data & "|" & $tabledata.ColumnValue ($Count) Next GUICtrlCreateListViewItem($Data, $ListVDB) $Data = StringTrimLeft($Data, 1) WEnd ;GUISetState(@SW_UNLOCK) Else $SSRow = 0 dim $ExcelArray[$ColumnCount+2][5000] $MaxRow = 4999 While $tabledata.step () = 0 ;guisetstate(@sw_unlock) guictrlsetdata($StatusLabel,"Records " & $SSRow+1 & " in " & Round($dif,4) & " milliseconds") ; PTREX ;guisetstate(@sw_lock) if $SSRow > $MaxRow then $GoOn = msgbox(4,"Lots of records...","You are importing a lot of records. Continue?") if $GoOn = 6 Then redim $ExcelArray[$ColumnCount+2][$SSRow + 5000] $MaxRow = $MaxRow + 5000 Elseif $goon = 7 Then ;guisetstate(@sw_unlock) guictrlsetstate($StatusLabel,"Putting data in grid.") ;guisetstate(@sw_lock) ExitLoop EndIf EndIf for $SSCol = 0 to $columncount If $tabledata.ColumnValue ($SSCol) = "" Then $excelarray[$SSCOl][$SSRow] = " " Else $excelarray[$SSCOl][$SSRow] =$tabledata.ColumnValue ($SSCol) EndIf Next $SSRow = $SSRow+1 WEnd $Range = "A1:" & ColumnIntConv($columncount+1) & $SSRow+1 $ExcelLV.range($range).value = $ExcelArray for $ColCOunt = 1 to $columncount+1 $ExcelLV.columns($colcount).autofit Next $LastNumOfRows = $SSRow EndIf $TableData.Close (); EndIf guisetstate(@SW_UNLOCK) EndFunc ;==>TreeDBTableClick Func NewTable() ;This function creates a new table. It assumes the user has already filled out the necessary data in the GUI. $FieldParamStr = "" $Refresh = False If Not FileExists($DBPath) Then MsgBox(0, "DB does not exist", "There is no database to add that table to.") ElseIf GUICtrlRead($NewTableInput) = "" Then MsgBox(0, "No Table Name", "Please insert a tablename.") Else $Proceed = False $TableName = GUICtrlRead($NewTableInput) $CheckEnd = UBound($TableNames) - 1 ;Loop through table names, only proceed if name is not in list While $Proceed = False For $Check = 0 To $CheckEnd If $TableNames[$Check] = $TableName Then $TableName = InputBox("Duplicate Table", "You need to choose a different table name... the one you used is already used.", $TableName) $Proceed = False ExitLoop ElseIf $Check = $CheckEnd Then $Proceed = True EndIf Next WEnd If $Proceed = True Then ;Get the field name and type data from the field listview... put it together for SQL if _GUICtrlListViewGetItemCount($NewFieldsLV) > 0 Then For $LVcount = 0 To _GUICtrlListViewGetItemCount ($NewFieldsLV) - 1 $LVdata = _GUICtrlListViewGetItemText ($NewFieldsLV, $LVcount) $TempArray = StringSplit($LVdata, "|") $FieldParamStr = $FieldParamStr & "," & " " & $TempArray[1] & " " & $TempArray[2] Next $FieldParamStr = StringTrimLeft($FieldParamStr, 2) $odb.execute("BEGIN TRANSACTION") $SQL = "CREATE TABLE " & $TableName & " (" & $FieldParamStr & ")" $oDb.Execute ($SQL) $SQL = stringreplace($SQL,"'","''") $odb.execute("INSERT INTO z_SQL_Log(SQLEntry, Type, Comment) VALUES('" & $SQL & "','CREATE TABLE','Table Create')") $odb.execute("COMMIT TRANSACTION") If $BeginnerMode = True Then MsgBox(0, "Create the table", $SQL) EndIf OpenDatabase($DBPath) Else msgbox(0,"Add Fields","You need to add fields using the Add Fields button.") EndIf EndIf EndIf EndFunc ;==>NewTable Func NewFieldNewTable() CreateNewField(False) EndFunc Func NewFieldExistingTable() if FileExists($DBPath) = 0 Then msgbox(0,"Open Database","You need to open a database first.") ElseIf not IsArray($fieldnames) Then msgbox(0,"Choose Table","You need to choose a table from the tree first.") Else CreateNewField(True) EndIf EndFunc Func CreateNewField($AddToExisting) ;This function starts the creation of a new field for a new table. It creates the GUI for creation. If FileExists($DBPath) = 0 Then MsgBox(0, "Open a Database", "Please open a database.") Else $FieldGUIwidth = 550 $FieldGUI = GUICreate("Put Field together", $FieldGUIwidth, 390) GUISetOnEvent($GUI_EVENT_CLOSE, "FieldGUIClose") ;This line tells the GUI to close when the "X" is clicked $Shpiel = "Insert the new field name and it's parameters. Insert parameters, then modifiers, then Defaults. ONLY the first parameter, any modifiers, and the first DEFAULT will be used. For example: FName::Param1,Modifier1,Modifier2,Default1" & @CR & @CR & _ "Parameters:" & @CR & _ "1 = TEXT" & @CR & _ "2 = NUMERIC" & @CR & _ "3 = VARCHAR(xx) x=Lenght " & @CR & _ "4 = INTEGER" & @CR & _ "5 = FLOAT" & @CR & _ "6 = BLOB" & @CR & _ "7 = BOOLEAN" & @CR & @CR & _ "Some Modifiers:" & @CR & _ "P = PRIMARY KEY" & @CR & _ "U = UNIQUE (not needed if PRIMARY KEY is used)" & @CR & _ "C = COLLATE NOCASE" & @CR & @CR & _ "Column Default Values:" & @CR & _ "T = CURRENT_TIME [HH:MM:SS]" & @CR & _ "D = CURRENT_DATE [YYYY-MM-DD]" & @CR & _ "S = CURRENT_TIMESTAMP [YYYY-MM-DD HH:MM:SS]" $NewFieldLabel = GUICtrlCreateLabel($Shpiel, 5, 5, $FieldGUIwidth - 10, 300) $UpdatedLabel = GUICtrlCreateLabel("Preview Appears Here...", 5, 315, $FieldGUIwidth - 10, 20) $FieldInputBox = GUICtrlCreateInput("", 5, 335, $FieldGUIwidth - 10, 20) $PreviewButton = GUICtrlCreateButton("Preview", 50, 360, 75, 25) GUICtrlSetOnEvent($FieldInputBox, "FieldInputEvent") $AddFieldButton = GUICtrlCreateButton("Add Field", 170, 360, 75, 25) if $AddToExisting = false Then GUICtrlSetOnEvent($AddFieldButton, "NewFieldName") elseif $addtoexisting = True Then GUICtrlSetOnEvent($AddFieldButton, "AddFieldToExisting") EndIf GUISetState(@SW_SHOW, $FieldGUI) EndIf EndFunc ;==>CreateNewField Func AddFieldToExisting() $FieldText = GUICtrlRead($FieldInputBox) $ParsedFieldText = InputFieldParse($FieldText,2) $TempArray = StringSplit($FieldText, "::", 1) If $FieldText <> "" And IsArray($TempArray) Then $SQL = "ALTER TABLE " & $curtable & " COLUMN " & $ParsedFieldText if $BeginnerMode = True Then msgbox(0,"Add Index",$SQL) EndIf $odb.execute("BEGIN TRANSACTION") $odb.execute($SQL) $odb.execute("COMMIT TRANSACTION") guidelete($FieldGUI) TreeDBTableClick() Else MsgBox(0, "Incorrect entry", "Please reenter the data. Don't forget the :: delimiter.") EndIf EndFunc Func FieldInputEvent() ;This function is the function called when you click on a new field... it is deleted! $FieldText = InputFieldParse(GUICtrlRead($FieldInputBox)) GUICtrlSetData($UpdatedLabel, $FieldText) EndFunc ;==>FieldInputEvent Func FieldGUIClose() GUIDelete($FieldGUI) EndFunc ;==>FieldGUIClose Func NewFieldName() ;This is the function that is called when the user presses the Add Field button on the new field GUI. It just adds the field info to ; the new field listview $FieldText = GUICtrlRead($FieldInputBox) $ParsedFieldText = InputFieldParse($FieldText) $TempArray = StringSplit($FieldText, "::", 1) If $FieldText <> "" And IsArray($TempArray) Then $Item = $TempArray[1] & "|" & StringReplace($ParsedFieldText, $TempArray[1] & " ", "", 1) $NewFieldItem = GUICtrlCreateListViewItem($Item, $NewFieldsLV) ;Set the GUI to automatically delete the new field if it is clicked GUICtrlSetOnEvent($NewFieldItem, "DeleteField") GUIDelete($FieldGUI) Else MsgBox(0, "Incorrect entry", "Please reenter the data. Don't forget the :: delimiter.") EndIf EndFunc ;==>NewFieldName Func InputFieldParse($iString,$AddExisting=1) If $iString <> "" Then $TempArray = StringSplit($iString, "::", 1) If $TempArray[0] > 1 Then $NewFName = StringStripWS($TempArray[1], 8) $Parameters = StringSplit(StringStripWS($TempArray[2], 8), ",") ;These three booleans tell whether the first datatype, Unique/Pri Key identifier, or Default field have been reached $param = False $Key = False $Default = False For $PCount = 1 To $Parameters[0] If $Parameters[$PCount] = "1" And $param = False Then $Parameters[$PCount] = "TEXT" $param = True ElseIf $Parameters[$PCount] = "2" And $param = False Then $Parameters[$PCount] = "NUMERIC" $param = True ElseIf $Parameters[$PCount] = "3" And $param = False Then $Parameters[$PCount] = "VARCHAR"&"("&" "&")" $param = True ElseIf $Parameters[$PCount] = "4" And $param = False Then $Parameters[$PCount] = "INTEGER" $param = True ElseIf $Parameters[$PCount] = "5" And $param = False Then $Parameters[$PCount] = "FLOAT" $param = True ElseIf $Parameters[$PCount] = "6" And $param = False Then $Parameters[$PCount] = "BLOB" $param = True ElseIf $Parameters[$PCount] = "7" And $param = False Then $Parameters[$PCount] = "BOOLEAN" $param = True ElseIf $Parameters[$PCount] = "P" And $Key = False and $AddExisting = 1 Then $Parameters[$PCount] = "PRIMARY KEY" $Key = True ElseIf $Parameters[$PCount] = "U" And $Key = False and $AddExisting = 1 Then $Parameters[$PCount] = "UNIQUE" $Key = True ElseIf $Parameters[$PCount] = "C" Then $Parameters[$PCount] = "COLLATE NOCASE" ElseIf $Parameters[$PCount] = "T" And $Default = False and $AddExisting = 1 Then $Parameters[$PCount] = "DEFAULT CURRENT_TIME" $Default = True ElseIf $Parameters[$PCount] = "D" And $Default = False and $AddExisting = 1 Then $Parameters[$PCount] = "DEFAULT CURRENT_DATE" $Default = True ElseIf $Parameters[$PCount] = "S" And $Default = False and $AddExisting = 1 Then $Parameters[$PCount] = "DEFAULT CURRENT_TIMESTAMP" $Default = True Else ;If the user has typed in their own parameters... we assume they know what they are doing! If IsNumber(Number($Parameters[$PCount])) Then $Parameters[$PCount] = "" Else $Parameters[$PCount] = StringUpper($Parameters[$PCount]) EndIf EndIf If $Parameters[$PCount] <> "" Then $NewFName = $NewFName & " " & $Parameters[$PCount] EndIf Next Return $NewFName Else return "Don't forget the :: delimiter!!" EndIf EndIf EndFunc ;==>InputFieldParse Func DeleteField() _GUICtrlListViewDeleteItemsSelected ($NewFieldsLV) EndFunc ;==>DeleteField Func EditRecord() ;This function starts the process to edit a record. It gets the current listview selection and then calls the ; function to create the edit GUI. When you click 'edit' in the GUI you run RecordEdit. dim $CurRecord[2] If FileExists($DBPath) = 0 Then MsgBox(0, "Open Database", "You Need to open a valid database.") ElseIf Not IsArray($FieldNames) Then MsgBox(0, "Select Table", "You need to select a table before choosing this action.") Else if $UseExcelLV = false Then if _GUICtrlListViewGetSelectedCount ($ListVDB) < 1 Then MsgBox(0, "Select Record", "You need to select a record before choosing this action.") Else $CurRecord = _GUICtrlListViewGetItemTextArray ($ListVDB) $CurListIndex = $CurRecord[1] EndIf Else dim $CurRecord[$LastNumOfHeadings] $CurRow = $ExcelLV.selection.row $curlistindex = $ExcelLV.cells($currow,1).value if $currow < 1 then msgbox(0, "Select Record", "You need to select a record before choosing this action.") Else ;Pull record from database so that numbers aren't screwed up by Excel $Record = $odb.prepare("SELECT * FROM " & $CurTable & " WHERE rowid=" & $CurListIndex) while $Record.step() = 0 for $cCount = 0 to $Record.columncount-1 $CurRecord[$cCount+1] = $Record.columnvalue($cCount) Next WEnd EndIf EndIf DisplayRecGUI($CurRecord) EndIf EndFunc ;==>EditRecord Func RecordEdit() ;After you have input the changes you now have to update the database! $ColumnNames = "" $ColumnData = "" $KeyFound = False For $Count = 0 To UBound($FieldNames, 1) - 1 If StringInStr($FieldNames[$Count][1], "PRIMARY") = 0 Then $TempData = GUICtrlRead($InputBoxes[$Count]) $Numerical = number($tempdata) if stringlen($Numerical) = stringlen($tempdata) Then if $tempdata <> "0" and $numerical = 0 Then $tempdata = "'" & $TempData & "'" Else if stringleft($tempdata,1) == 0 Then $tempdata = "''" & $tempdata EndIf Else if stringleft($tempdata,1) == 0 Then $tempdata = "''" & $tempdata $TempData = stringreplace($tempdata,"'","''") $TempData = "'" & $TempData & "'" EndIf $ColumnData = $ColumnData & " , " & $FieldNames[$Count][0] & "=" & $TempData Else $PriKey = $FieldNames[$Count][0] $PriNum = $CurListIndex $KeyFound = True EndIf Next If $KeyFound = False Then $PriNum = $CurListIndex $PriKey = "rowid" EndIf $ColumnData = StringTrimLeft($ColumnData, 3) $SQL = "UPDATE " & $CurTable & " SET " & $ColumnData & " WHERE " & $PriKey & "= " & $PriNum If $BeginnerMode = True Then MsgBox(0, "Update the record", $SQL) EndIf $odb.execute ("BEGIN TRANSACTION") $odb.execute ($SQL) $odb.execute ("COMMIT TRANSACTION") GUIDelete($RecordGUI) TreeDBTableClick() EndFunc ;==>RecordEdit Func AddRecord() ;This function starts the insert new record process. It launches the GUI. Records are actually inserted ; in InsertRecord. If FileExists($DBPath) = 0 Then MsgBox(0, "", "You need to open a valid database.") ElseIf Not IsArray($FieldNames) Then MsgBox(0, "Select a Table", "Please select a table first.") Else DisplayRecGUI("") EndIf EndFunc ;==>AddRecord Func InsertRecord() ;This function inserts the new record into the database. $NewRecord = "" Dim $ColumnNames = "" Dim $ColumnData = "" $oDb.Execute ("BEGIN TRANSACTION") ;Build the list of columns and the list of values for the SQL statment For $FieldCount = 0 To UBound($InputBoxes) - 1 ;If the field is a PRIMARY KEY it won't get updated and doesn't need to be in the SQL Statement. If StringInStr($FieldNames[$FieldCount][1], "PRIMARY KEY") Then $ColumnNames = $ColumnNames & "," & $FieldNames[$FieldCount][0] $ColumnData = $ColumnData & "," & "null" ;If the user has left the inputs empty, either there is no information or the field has a default value ; If there is no information add a space so that nothing appears. If it is a default, do nothing. ElseIf GUICtrlRead($InputBoxes[$FieldCount]) = "" Then If StringInStr($FieldNames[$FieldCount][1], "DEFAULT") = 0 Then $ColumnNames = $ColumnNames & "," & $FieldNames[$FieldCount][0] $ColumnData = $ColumnData & ",' '" EndIf ;A normal field gets updated with the user input text. Else $TempData = GUICtrlRead($InputBoxes[$FieldCount]) if $TempData == 0 then if number($tempdata) <> "" Then $columndata = $columndata & " , " & 0 Else $columndata = $columndata & " ,' ' " EndIf Else $Numerical = number($tempdata) if stringlen($Numerical) = stringlen($tempdata) Then if $tempdata <> "0" and $numerical = 0 Then $columndata = $columndata & " , '" & $TempData & "'" Else if stringleft($tempdata,1) == 0 Then $tempdata = "''" & $tempdata $columndata = $columndata & " , " & $TempData EndIf Else if stringleft($tempdata,1) == 0 Then $tempdata = "''" & $tempdata $TempData = stringreplace($tempdata,"'","''") $columndata = $columndata & " , '" & $TempData & "'" EndIf EndIf $ColumnNames = $ColumnNames & "," & $FieldNames[$FieldCount][0] EndIf Next ;Clean up the column names and values $ColumnNames = StringTrimLeft($ColumnNames, 1) While StringLeft($ColumnData, 3) = " , " $ColumnData = StringTrimLeft($ColumnData, 3) WEnd ;Insert the data into the database $SQL= "INSERT INTO " & $CurTable & "(" & $ColumnNames & ") VALUES( " & $ColumnData & ")" $odb.execute ($SQL) $oDb.Execute ("COMMIT TRANSACTION") If $BeginnerMode = true Then MsgBox(0, "Insert Record", $SQL) EndIf GUIDelete($RecordGUI) TreeDBTableClick() EndFunc ;==>InsertRecord Func DeleteRecord() ;Deletes the currently selected record If FileExists($DBPath) = 0 Then MsgBox(0, "Open Database", "You Need to open a valid database.") ElseIf Not IsArray($FieldNames) Then MsgBox(0, "Select Table", "You need to select a table before choosing this action.") Else if $UseExcelLV = false Then $CurRecord = _GUICtrlListViewGetItemTextArray ($ListVDB) $DBIndex = $CurRecord[1] Else $DBIndex = $ExcelLV.selection.row $dbindex = $ExcelLV.cells($dbindex,1).value EndIf $Confirm = MsgBox(1, "Delete Record?", "Are you sure you want to delete Record# " & $DBIndex & "? All data will be lost!") If $Confirm = 1 Then $SQL = "DELETE FROM " & $CurTable & " WHERE rowid=" & $DBIndex $odb.execute ("BEGIN TRANSACTION") $odb.execute ($SQL) $odb.execute ("COMMIT TRANSACTION") If $BeginnerMode = True Then MsgBox(0, "Delete record", $SQL) EndIf TreeDBTableClick() EndIf EndIf EndFunc ;==>DeleteRecord Func AddPart() EndFunc ;==>AddPart Func RefreshDatabase() If FileExists($DBPath) Then OpenDatabase($DBPath) _Pragma_Info() Else MsgBox(0, "No Database open", "You have not yet opened a database... you can't refresh it.") EndIf EndFunc ;==>RefreshDatabase Func RenameTable() If FileExists($DBPath) = 0 Then MsgBox(0, "No Database open", "You have not yet opened a database... you can't rename a table.") ElseIf Not IsArray($FieldNames) Then MsgBox(0, "Choose Table", "You need to choose a table for this action.") Else $CheckEnd = UBound($TableNames) - 1 $NewName = InputBox("Give a new name", "Please enter the new name you would like to give the table.", $CurTable) If $NewName <> "" And @error = 0 Then $Proceed = False ;Loop through table names, only proceed if name is not in list While $Proceed = False For $Check = 0 To $CheckEnd If $TableNames[$Check] = $NewName Then $NewName = InputBox("Duplicate Table", "You need to choose a different table name... the one you used is already used.", $NewName) $Proceed = False ExitLoop ElseIf $Check = $CheckEnd Then $Proceed = True EndIf Next WEnd If $Proceed = True Then $SQL = "ALTER TABLE " & $CurTable & " RENAME TO " & $NewName $odb.execute ("BEGIN TRANSACTION") $odb.execute ($SQL) $odb.execute("INSERT INTO z_SQL_Log(SQLEntry, Type,Comment) VALUES('" & $SQL & "','RENAME TABLE','Table Rename')") $odb.execute ("COMMIT TRANSACTION") If $BeginnerMode = True Then MsgBox(0, "Rename the table", $SQL) EndIf OpenDatabase($DBPath) EndIf EndIf EndIf EndFunc ;==>RenameTable Func DeleteTable() If FileExists($DBPath) = 0 Then MsgBox(0, "No Database open", "You have not yet opened a database... you can't refresh it.") ElseIf Not IsArray($FieldNames) Then MsgBox(0, "Choose Table", "You need to choose a table for this action.") elseif $curtable = "z_SQL_Log" Then msgbox(0,"","Please don't delete this table...") Else $Confirm = MsgBox(1, "Delete Table?", "If you delete this table >< " & $CurTable & " >< all information will be lost. Continue?") If $Confirm = 1 Then $SQL = "DROP TABLE " & $CurTable $odb.execute ("BEGIN TRANSACTION") $odb.execute ($SQL) $odb.execute("INSERT INTO z_SQL_Log(SQLEntry, Type,Comment) VALUES('" & $SQL & "','DELETE TABLE','Table Delete')") $odb.execute ("COMMIT TRANSACTION") If $BeginnerMode = True Then MsgBox(0, "Delete Table", $SQL) EndIf OpenDatabase($DBPath) EndIf EndIf EndFunc ;==>DeleteTable Func VacuumDatabase() If GUICtrlRead($InputDBPath) <> "" Then $SQL = "VACUUM" $odb.execute ($SQL) $odb.execute("INSERT INTO z_SQL_Log(SQLEntry, Type, Comment) VALUES('" & $SQL & "','VACUUM','Database Vacuum')") OpenDatabase($DBPath) Else MsgBox(0, "No Database open", "You have not yet opened a database... you can't refresh it.") EndIf EndFunc ;==>VacuumDatabase Func CommitSQL() Dim $DataOut If GUICtrlRead($InputDBPath) <> "" Then If StringInStr(GUICtrlRead($SQLInsert), "PRAGMA") Then ;PTREX $DataOut = $odb.execute (GUICtrlRead($SQLInsert)) MsgBox(0, " ",$DataOut & " PRAGMA Test ") EndIf If IsArray ($DataOut) Then ReDim $DataOut[10] For $i = 0 to UBound( $DataOut ) - 1 MsgBox(0, " ",$DataOut[$i] & " PRAGMA Test ") Next MsgBox(0," ", "" & $Dataout) Endif If StringInStr(GUICtrlRead($SQLInsert), "ANALYZE") Then $DataOut = $odb.execute (GUICtrlRead($SQLInsert)) MsgBox(0, " ",$DataOut & " PRAGMA Test ") ;PTREX EndIf If StringInStr(GUICtrlRead($SQLInsert), "SELECT") Then $odb.execute("INSERT INTO z_SQL_Log(SQLEntry, Type, Comment) VALUES('" & stringreplace(guictrlread($SQLInsert),"'","''") & "','SELECT', 'User SELECT')") $ColString = "" $DataOut = $odb.prepare (StringStripWS(GUICtrlRead($SQLInsert), 3)) $ColumnCount = $DataOut.columncount - 1 If $ColumnCount <> "" And IsObj($DataOut) Then with $ExcelLV ;Clear Headings For $Headercount = 1 to $LastNumOfHeadings+1 .activewindow.columnheadings($HeaderCount).caption = $headercount Next if $lastnumofrows = 0 Then $lastnumofrows = 1 .ActiveSheet.Range("A1:IV" & $lastnumofrows).Clear $LastNumOfHeadings = $columncount for $HeaderCount = 0 to $columncount $ColName = $dataout.columnname($headercount) .activewindow.columnheadings($HeaderCount+1).caption = $ColName .columns($HeaderCount+1).autofit Next For $ColCount = 0 To $ColumnCount $ColString = $ColString & "|" & $ColCount Next EndWith $ColString = StringTrimLeft($ColString, 1) if $UseExcelLV = false Then GUICtrlDelete($ListVDB) GUISwitch($Main, $SQL_View) $ListVDB = GUICtrlCreateListView($ColString, $xBorderL + 10, $SQLinsertY + $SQLlabelHeight + 140, $GUIWidth - 40, $guiheight- ($SQLinsertY + $SQLlabelHeight + 180)) GUICtrlSetState($SQL_View, $GUI_SHOW) GUISetState(@SW_LOCK) While $DataOut.step () = 0 $Data = "" For $Count = 0 To $ColumnCount $Data = $Data & "|" & $dataout.ColumnValue ($Count) Next $Data = StringTrimLeft($Data, 1) GUICtrlCreateListViewItem($Data, $ListVDB) WEnd _GUICtrlListViewSetColumnWidth ($ListVDB, 0, $LVSCW_AUTOSIZE) GUISetState(@SW_UNLOCK) Else guisetstate(@sw_lock) $SSRow = 0 dim $ExcelArray[$ColumnCount+2][5000] $MaxRow = 4999 While $dataout.step () = 0 if $SSRow > $MaxRow then redim $ExcelArray[$ColumnCount+2][$MaxRow + 5000] $MaxRow = $MaxRow + 5000 EndIf for $SSCol = 0 to $columncount If $DataOut.ColumnValue ($SSCol) = "" Then $excelarray[$SSCOl][$SSRow] = " " Else $excelarray[$SSCOl][$SSRow] =$DataOut.ColumnValue ($SSCol) EndIf Next $SSRow = $SSRow+1 WEnd $lastnumofrows = $SSRow+1 $Range = "A1:" & ColumnIntConv($columncount+1) & $SSRow+1 $ExcelLV.range($range).value = $ExcelArray for $ColCOunt = 1 to $columncount+1 $ExcelLV.columns($colcount).autofit Next guisetstate(@sw_unlock) EndIf EndIf ELSEIF StringInStr(GUICtrlRead($SQLInsert), "CREATE VIEW") Then $odb.execute ("BEGIN TRANSACTION") $odb.execute (GUICtrlRead($SQLInsert)) $odb.execute("INSERT INTO z_SQL_LOG(SQLEntry, Type, Comment) VALUES('" & stringreplace(guictrlread($SQLInsert),"'","''") & "','VIEW','User VIEW')") $odb.execute ("COMMIT TRANSACTION") ;Else ; $odb.execute ("BEGIN TRANSACTION") ; $odb.execute (GUICtrlRead($SQLInsert)) ; $odb.execute("INSERT INTO z_SQL_Log(SQLEntry, Type, Comment) VALUES('" & stringreplace(guictrlread($SQLInsert),"'","''") & "','User Change')") ; $odb.execute ("COMMIT TRANSACTION") EndIf Else MsgBox(0, "No Database open", "You have not yet opened a database...") EndIf EndFunc ;==>CommitSQL Func ChildGUIDelete() GUIDelete($RecordGUI) EndFunc ;==>ChildGUIDelete Func GUIevent() Exit EndFunc ;==>GUIevent Func DisplayRecGUI($rgRecord = "") $rgXborder = 5 $rgYborder = 20 $LabelMaxWidth = 0 $FieldCount = 0 Dim $InputBoxes[UBound($FieldNames, 1) ] ;Find the max number of characters in a label, this will help determine the width of ; the GUI For $FieldCount = 0 To UBound($FieldNames, 1) - 1 If $LabelMaxWidth < StringLen($FieldNames[$FieldCount][0]) Then $LabelMaxWidth = StringLen($FieldNames[$FieldCount][0]) EndIf Next ;pixels per character seems about right for width $LabelMaxWidth = $LabelMaxWidth * 6 ;Set up and make the GUI $RecordGUIheight = UBound($FieldNames, 1) * 25 + 75 $RecordGUIwidth = (Ceiling(UBound($FieldNames, 1) / 15)) * ($LabelMaxWidth + 150 + 20) $RecordGUI = GUICreate("Edit/New Record", $RecordGUIwidth, $RecordGUIheight) GUISetOnEvent($GUI_EVENT_CLOSE, "ChildGUIDelete") If IsArray($rgRecord) Then $InsertText = "Update" Else $InsertText = "Insert" EndIf $rgInsert = GUICtrlCreateButton($InsertText, $RecordGUIwidth / 2 + 5, $RecordGUIheight - 25, 80, 20) If IsArray($rgRecord) Then GUICtrlSetOnEvent($rgInsert, "RecordEdit") Else GUICtrlSetOnEvent($rgInsert, "InsertRecord") EndIf $rgClose = GUICtrlCreateButton("Close", $RecordGUIwidth / 2 - 85, $RecordGUIheight - 25, 80, 20) GUICtrlSetOnEvent(-1, "ChildGUIDelete") $rgAllowDefaults = GUICtrlCreateCheckbox("Allow changing DEFAULT fields", $rgXborder, $RecordGUIheight - 50, $RecordGUIwidth, 20) GUICtrlSetOnEvent(-1, "AllowDefaults") $LabelXStart = $rgXborder $InputXStart = $rgXborder + $LabelMaxWidth + 10 For $FieldCount = 0 To UBound($FieldNames, 1) - 1 if $fieldcount = 16 Then $LabelXStart = $rgXborder + $LabelMaxWidth + 150 + 3*$xspacing $InputXStart = $rgXborder + 2* $LabelMaxWidth + 150 + 2*$xspacing +10 $rgYborder = 20 EndIf GUICtrlCreateLabel($FieldNames[$FieldCount][0], $LabelXStart, $rgYborder+2, $LabelMaxWidth, 20,$SS_RIGHT ) If IsArray($rgRecord) Then If $rgRecord[$FieldCount + 1] <> "" Then $Text = $rgRecord[$FieldCount + 1] Else $Text = "" EndIf Else $Text = "" EndIf $InputBoxes[$FieldCount] = GUICtrlCreateInput($Text,$InputXStart , $rgYborder, 150, 20) If StringInStr($FieldNames[$FieldCount][1], "PRIMARY") Then GUICtrlSetState($InputBoxes[$FieldCount], $gui_disable) EndIf If StringInStr($FieldNames[$FieldCount][1], "DEFAULT") Then GUICtrlSetState($InputBoxes[$FieldCount], $gui_disable) EndIf $rgYborder = $rgYborder + 25 Next GUISetState(@SW_SHOW, $RecordGUI) EndFunc ;==>DisplayRecGUI Func Beginner() $Checked = GUICtrlRead($cBeginner) If $Checked = $GUI_CHecked Then $BeginnerMode = True ElseIf $Checked = $gui_unchecked Then $BeginnerMode = False EndIf EndFunc ;==>Beginner Func AllowDefaults() $Checked = GUICtrlRead($rgAllowDefaults) If $Checked = $GUI_CHecked Then for $count = 0 to ubound($InputBoxes)-1 if StringInStr($FieldNames[$count][1],"PRIMARY KEY",1) = 0 Then GUICtrlSetState($inputboxes[$count],$GUI_ENABLE) EndIf Next ElseIf $Checked = $gui_unchecked Then for $count = 0 to ubound($InputBoxes)-1 if StringInStr($FieldNames[$count][1],"PRIMARY KEY",1) = 0 and StringInStr($fieldnames[$count][1],"DEFAULT",1) <> 0 Then GUICtrlSetState($inputboxes[$count],$GUI_DISABLE) EndIf Next EndIf EndFunc ;==>AllowDefaults Func CreateIndex() If FileExists($DBPath) = 0 Then MsgBox(0, "No Database open", "You have not yet opened a database... you can't rename a table.") ElseIf Not IsArray($FieldNames) Then MsgBox(0, "Choose Table", "You need to choose a table for this action.") Else $IndexName = InputBox("Insert Index Name", "Please insert a name for the index.") $ColumnData = "" If $IndexName <> "" And @error = 0 Then $Fields = GUICtrlRead($cIndexCombo) $TempArray = StringSplit($Fields, ",") ;_ArrayDisplay($TempArray, "") $found = False $FieldCount = UBound($FieldNames, 1) - 1 If GUICtrlRead($UniqueRadio) = $GUI_CHecked Then $Unique = "UNIQUE " Else $Unique = "" EndIf If GUICtrlRead($AscendRadio) = $GUI_CHecked Then $Direction = " ASC" Else $Direction = " DESC" EndIf If $TempArray[0] = 1 Then For $Count = 0 To $FieldCount If $TempArray[1] = $FieldNames[$Count][0] Then $found = True $ColumnData = $TempArray[1] & $Direction ExitLoop EndIf Next Else For $InCount = 1 To $TempArray[0] $found = False For $Count = 0 To $FieldCount If $TempArray[$InCount] = $FieldNames[$Count][0] Then $found = True $ColumnData = $ColumnData & " , " & $TempArray[$InCount] & $Direction ExitLoop EndIf Next If $found = False Then MsgBox(0, "Invalid Field Names", "You're using an invalid field name somewhere.") ExitLoop EndIf Next EndIf If $found = True Then If StringLeft($ColumnData, 3) = " , " Then $ColumnData = StringTrimLeft($ColumnData, 3) $SQL = "CREATE " & $Unique & "INDEX " & $IndexName & " ON " & $CurTable & "(" & $ColumnData & ")" if $BeginnerMode = true Then MsgBox(0, "Create Index", $SQL) EndIf $odb.execute ("BEGIN TRANSACTION") $odb.execute ($SQL) $odb.execute("INSERT INTO z_SQL_Log(SQLEntry, Type,Comment) VALUES('" & $SQL & "','CREATE INDEX','Index Create')") $odb.execute ("COMMIT TRANSACTION") TreeDBTableClick() EndIf EndIf EndIf EndFunc ;==>CreateIndex Func DeleteIndex() $IndexName = inputbox("Delete Index","Type the name of an index from this table. Index names can be found in the Tree on the left.") if $IndexName <> "" and @error = 0 Then $SQL = "DROP INDEX " & $IndexName $odb.execute("BEGIN TRANSACTION") $odb.execute($SQL) $odb.execute("INSERT INTO z_SQL_Log(SQLEntry, Type, Comment) VALUES('" & $SQL & "','DELETE INDEX','Index Delete')") $odb.execute("COMMIT TRANSACTION") if $BeginnerMode = true Then MsgBox(0, "Delete Index", $SQL) EndIf EndIf EndFunc Func ButtonOpenDB() OpenDatabase("") EndFunc ;==>ButtonOpenDB Func XLSImportNew() if FileExists($dbpath) = 0 Then msgbox(0,"Open a Database","You need to open a database.") Else $TableName = StringStripWS(inputbox("Table Name","Give the New Table a Name"),8) $Proceed = false $CheckEnd = ubound($tablenames)-1 ;Loop through table names, only proceed if name is not in list While $Proceed = False For $Check = 0 To $CheckEnd If $TableNames[$Check] = $TableName Then $TableName = StringStripWS(InputBox("Duplicate Table", "You need to choose a different table name... the one you used is already used.", $TableName),8) $Proceed = False ExitLoop ElseIf $Check = $CheckEnd Then $Proceed = True EndIf Next WEnd if $Proceed = true and $TableName <> "" then $ImportRange = InputBox("Import Range","Insert what range you want the import to cover. A blank entry means import the WHOLE Excel file. Format is StartRow:EndRow.","") if $ImportRange <> "" Then $TempArray = stringsplit($ImportRange,":") $StartRow = $TempArray[1] if $TempArray[0] >1 Then $EndRow = $TempArray[2] EndIf if $EndRow <> "" Then XLSImport(True,$TableName,$StartRow,$EndRow) Else XLSImport(True,$TableName,$StartRow) EndIf Else XLSImport(True,$TableName) EndIf EndIf EndIf EndFunc Func XLSImportExisting() if FileExists($dbpath) = 0 Then msgbox(0,"Open a Database","You need to open a database.") elseif isarray($FieldNames) = 0 Then msgbox(0,"Select A Table","Select a table you want to import into.") Else $TableName = $CurTable $ImportRange = InputBox("Import Range","Insert what range you want the import to cover. A blank entry means import the WHOLE Excel file. Format is StartRow:EndRow.","") if $ImportRange <> "" Then $TempArray = stringsplit($ImportRange,":") $StartRow = $TempArray[1] if $TempArray[0] >1 Then $EndRow = $TempArray[2] EndIf if $EndRow <> "" Then XLSImport(false,$TableName,$StartRow,$EndRow) Else XLSImport(False,$TableName,$StartRow) EndIf Else XLSImport(False,$TableName) EndIf EndIf EndFunc Func XLSImport($NewTable,$TableName,$StartRow = 1,$Endrow = 0 ) $path = FileOpenDialog("Excel File...", "C:\Kevin\AutoItScripts\PartsDatabase", "XLS (*.xls)") if fileexists($path) Then guictrlsetdata($StatusLabel,"Setting up Excel object...") $oexcel = ObjGet ($Path) ; Get an Excel Object from an existing filename if @error Then msgbox(0,"","Not an object") EndIf $oexcel.Worksheets.count if $NewTable = true Then $xlRow = 1 $xlCol = 1 $LastCol = false $Data = "" ;Read top row to get Field Parameters and number of fields dim $TempFieldNames[1] dim $TempFieldTypes[1] guictrlsetdata($StatusLabel,"Getting data from file...") with $oexcel while $LastCol = false $TempData = $oexcel.activesheet.cells($xlRow,$xlCol).value if $TempData = 0 Then $TempData = $oexcel.activesheet.cells($xlRow,$xlCol).formula EndIf if $TempData <> "" Then $TempData = InputFieldParse($TempData) $Data = $Data & " , " & $TempData $TempArray = stringsplit($TempData," ") _ArrayAdd($TempFieldNames,$TempArray[1]) _arrayadd($TempFieldTypes,$TempData) $xlCol = $xlCol+1 Else $LastCol = True $xlCol = $xlCol - 1 $Data = stringtrimleft($data,3) EndIf wend EndWith $SQL = "CREATE TABLE " & $TableName & "(" & $data & ")" $odb.execute("BEGIN TRANSACTION") $odb.execute($SQL) $SQL=stringreplace($SQL,"'","''") $odb.execute("INSERT INTO z_SQL_Log(SQLEntry, Comment) VALUES('" & $SQL & "','Record Insert')") $odb.execute("COMMIT TRANSACTION") $ColumnData = "" for $SCol = 1 to $xlCol $ColumnData = $ColumnData & ", " & $TempFieldNames[$scol] Next $ColumnData = Stringtrimleft($ColumnData,2) Else $xlCol = ubound($fieldnames,1) $ColumnData = "" for $SCol = 0 to $xlCol-1 $ColumnData = $ColumnData & ", " & $FieldNames[$scol][0] Next $ColumnData = Stringtrimleft($ColumnData,2) EndIf if $EndRow = 0 Then $rEnd = 1 $xlup = -4162 ;Find last occupied row For $Col = 1 To $xlCol $TempEnd = $oexcel.activesheet.Cells(65536, $Col).End($xlup).Row If $TempEnd > $rEnd Then $rEnd = $TempEnd EndIf Next Else $rEnd = $EndRow EndIf If $Startrow = 1 then $Startrow = 2 $LastRowText = " of " & $rEnd ;Read in the records from Excel $odb.execute("BEGIN TRANSACTION") for $SRow = number($StartRow) to number($rEnd) $ValueData = "" guictrlsetdata($StatusLabel,"Record " & $Srow & $LastRowText) for $SCol = 1 to $xlCol $TempData = $oexcel.activesheet.cells($srow,$scol).value if $TempData == 0 then $TempData = $oexcel.activesheet.cells($srow,$scol).formula if number($tempdata) <> "" Then $ValueData = $ValueData & " , " & 0 Else $ValueData = $ValueData & " ,' ' " EndIf Else $Numerical = number($tempdata) if stringlen($Numerical) = stringlen($tempdata) Then if $tempdata <> "0" and $numerical = 0 Then $ValueData = $ValueData & " , '" & $TempData & "'" Else if stringleft($tempdata,1) == 0 Then $tempdata = "''" & $tempdata $ValueData = $ValueData & " , " & $TempData EndIf Else if stringleft($tempdata,1) == 0 Then $tempdata = "''" & $tempdata $TempData = stringreplace($tempdata,"'","''") $ValueData = $ValueData & " , '" & $TempData & "'" EndIf EndIf Next $ValueData= stringtrimleft($ValueData,3) $SQL= "INSERT INTO " & $TableName & "(" & $ColumnData & ") VALUES( " & $ValueData & ")" msgbox(0,"",$SQL) $odb.execute ($SQL) Next $oDb.Execute ("COMMIT TRANSACTION") $oexcel.close OpenDatabase($dbpath) EndIf EndFunc ; This is SvenP's custom error handler Func MyErrFunc() $HexNumber = Hex($oMyError.number, 8) MsgBox(0, "AutoItCOM Test", "We intercepted a COM Error !" & @CRLF & @CRLF & _ "err.description is: " & @TAB & $oMyError.description & @CRLF & _ "err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _ "err.number is: " & @TAB & $HexNumber & @CRLF & _ "err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _ "err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _ "err.source is: " & @TAB & $oMyError.source & @CRLF & _ "err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _ "err.helpcontext is: " & @TAB & $oMyError.helpcontext _ ) SetError(1) ; to check for after this function returns EndFunc ;==>MyErrFunc func _GUICtrlCreateListViewEnhanced($col_names, $x_pos,$y_pos,$width,$height) ;Create Spreadsheet object. if RegRead("HKCR\OWC9.spreadsheet","") then $ExcelLV = ObjCreate("OWC9.spreadsheet") if RegRead("HKCR\OWC10.spreadsheet","") then $ExcelLV = ObjCreate("OWC10.spreadsheet") if RegRead("HKCR\OWC11.spreadsheet","") then $ExcelLV = ObjCreate("OWC11.spreadsheet") ;$ExcelLV = ObjCreate("OWC10.spreadsheet"); Office XP ;If not IsObj($ExcelLV) Then ;$ExcelLV = ObjCreate("OWC9.spreadsheet"); Office 2000 ;EndIf ;if not isobj($ExcelLV) then ; $ExcelLV = ObjCreate("OWC11.spreadsheet"); Office 2003 ;EndIf If IsObj($ExcelLV) Then with $ExcelLV .AllowPropertyToolbox = True .DisplayOfficeLogo = false .DisplayPropertyToolbox = False .DisplayTitleBar = False .DisplayToolbar = False ;.ViewOnlyMode = True ;.autofit = True ;.Maxwidth = 1000 ;.Maxheight = 1000 EndWith With $ExcelLV.ActiveWindow .DisplayGridlines = True .DisplayHorizontalScrollBar = True .DisplayVerticalScrollBar = True .DisplayColumnHeadings = True .DisplayRowHeadings = True .DisplayWorkbookTabs = False .EnableResize = false EndWith $owcWbook = $ExcelLV.ActiveWorkbook $owcWSheet=$owcWbook.ActiveSheet $GUI_ActiveX = GUICtrlCreateObj($ExcelLV,265,1,730,415) guictrlsetstyle(-1,$ws_visible) GUICtrlSetResizing($GUI_ActiveX,$gui_dockAuto) ;$ExcelLV.activesheet.rows(2).Select ;$ExcelLV.ActiveWindow.FreezePanes = True $owcWSheet.cells(1,1).Select MouseClick("Left") ;Solves the Excel Object to jump when maximizing. $TempHeadingArray = stringsplit($col_names,"|") with $ExcelLV for $i = 1 to $tempheadingarray[0] ;.cells(1,$i).value = $tempheadingarray[1] .activewindow.columnheadings($i).caption = $Tempheadingarray[$i] .columns($i).autofit next EndWith ;~ With $owcWSheet.Protection() ;~ .AllowDeletingColumns = 0 ;~ .AllowDeletingRows = 0 ;~ .AllowInsertingColumns = 0 ;~ .AllowInsertingRows = 0 ;~ .AllowSorting = 1 ;~ .Enabled = 1 ;~ ;~ EndWith Else MsgBox(0,"Reply","You don't appear to have the office web components installed. If you are using Office 2000, the components do not support spreadsheets. Sorry.",4) EndIf EndFunc func ColumnIntConv($String1) $String1 = number($String1) If $String1 > 26 Then $FirstDigit = Chr(floor($String1 / 26) + 64) ;integer divide!!! $CharDivide = $String1 / 26 If isint($CharDivide) and $chardivide < 10 Then $CharDivide = $CharDivide - 1 Else $CharDivide = floor($String1 / 26) EndIf $SecondDigit = Chr($CharDivide * ($String1 / ($CharDivide) - 26) + 64) $ColumnIntConv = $FirstDigit & $SecondDigit ElseIf $String1 > 0 And $String1 < 27 Then ;msgbox(0,"","Your in") $ColumnIntConv = chr($String1 + 64) Else $ColumnIntConv = $String1 EndIf return $ColumnIntConv EndFunc Func _Tab_Switch() ; PTREX Local $Tabcount = _GUICtrlTabGetItemCount($Tab1) Local $State1 = GUICtrlRead ($Tab1) If WinActive($Main) Then If GUICtrlRead ($Tab1) = $Tabcount -1 Then $State1 = 0 _GUICtrlTabSetCurFocus ($Tab1, $State1) Else _GUICtrlTabSetCurFocus ($Tab1, $State1+1) EndIf EndIf EndFunc Func _Pragma_Info() ; PTREX If FileExists($DBPath) = 0 Then MsgBox(0, "No Database open", "You have not yet opened a database... you can't rename a table.") Else $Integrity = $odb.execute ("PRAGMA Integrity_check") If GUICtrlRead($RadioTempStore2)=1 Then $TempStore = $odb.execute ("PRAGMA temp_store='Memory'") ElseIf GUICtrlRead($RadioTempStore0)=1 Then $TempStore = $odb.execute ("PRAGMA temp_store='Default'") Else $TempStore = $odb.execute ("PRAGMA temp_store='File'") EndIf $TempStore = $odb.execute ("PRAGMA temp_store") GUICtrlSetData($TempStoreLabel,"Temp Store Settings : " & $Tempstore) $TempStoreDir = $odb.execute ("PRAGMA temp_store_directory") ;$PageSize = $odb.execute ("PRAGMA page_size='8192") ; Can only be set at create DB event. $PageSize = $odb.execute ("PRAGMA page_size") $DefaultCacheSize = $odb.execute ("PRAGMA default_cache_size") ; Defaults stored per DB $CacheSize = $odb.execute ("PRAGMA cache_size=" & GUICtrlRead($CacheSizeInput)) ; Defaults stored per Session $CacheSize = $odb.execute ("PRAGMA cache_size") $AutoVacuum = $odb.execute ("PRAGMA auto_vacuum") $Synchrone = $odb.execute ("PRAGMA synchronous") If GUICtrlRead($RadioCount1)=1 Then $CountChange = $odb.execute ("PRAGMA count_changes=1") Else $CountChange = $odb.execute ("PRAGMA count_changes=0") EndIf $CountChange = $odb.execute ("PRAGMA count_changes") GUICtrlSetData($CountChangeLabel,"Count Changes : " & $Countchange) If GUICtrlRead($RadioCaseSens1)=1 Then $CaseSensitive = $odb.execute ("PRAGMA case_sensitive_like=1") Else $CaseSensitive = $odb.execute ("PRAGMA case_sensitive_like=0") EndIf $CaseSensitive = $odb.execute ("PRAGMA case_sensitive_like") GUICtrlSetData($CaseSensitiveLabel,"Case Sensitive : " & $CaseSensitive) IF GUICtrlRead($RadioParser1)=1 Then $ParserTrace = $odb.execute ("PRAGMA parser_trace=1") Else $ParserTrace = $odb.execute ("PRAGMA parser_trace=0") EndIf $ParserTrace = $odb.execute ("PRAGMA parser_trace") GUICtrlSetData($ParserTraceLabel,"Parser Trace : " & $ParserTrace) If GUICtrlRead($RadioVDBETrace1)=1 Then $VDBETrace = $odb.execute ("PRAGMA vdbe_trace=1") Else $VDBETrace = $odb.execute ("PRAGMA vdbe_trace=0") EndIf $VDBETrace = $odb.execute ("PRAGMA vdbe_trace") GUICtrlSetData($VDBETraceLabel,"VDBE Trace : " & $VDBETrace) If GUICtrlRead($RadioVDBEList1)=1 Then $VDBEListing = $odb.execute ("PRAGMA vdbe_listing=1") Else $VDBEListing = $odb.execute ("PRAGMA vdbe_listing=0") EndIf $VDBEListing = $odb.execute ("PRAGMA vdbe_listing") GUICtrlSetData($VDBEListingLabel,"VDBE Listing : " & $VDBEListing) If GUICtrlRead($RadioFullColumn1)=1 Then GUICtrlSetState($RadioShortColumn0,$GUI_CHECKED ) $FullColumn = $odb.execute ("PRAGMA full_column_names=1") Else GUICtrlSetState($RadioShortColumn1,$GUI_CHECKED ) $FullColumn = $odb.execute ("PRAGMA full_column_names=0") EndIf $FullColumn = $odb.execute ("PRAGMA full_column_names") GUICtrlSetData($FullColumnLabel,"Full Column Names : " & $FullColumn) If GUICtrlRead($RadioShortColumn1)=1 Then GUICtrlSetState($RadioFullColumn0,$GUI_CHECKED ) $ShortColumn = $odb.execute ("PRAGMA short_column_names=1") Else GUICtrlSetState($RadioFullColumn1,$GUI_CHECKED ) $ShortColumn = $odb.execute ("PRAGMA short_column_names=0") Endif $ShortColumn = $odb.execute ("PRAGMA short_column_names") GUICtrlSetData($ShortColumnLabel,"Short Column Names : " & $ShortColumn) If StringIsInt(GUICtrlRead($UserVersionInput))=1 Then $TempString = GUICtrlRead($UserVersionInput) $UserVersion = $odb.execute ("PRAGMA user_version="&$Tempstring) Else Msgbox(0,"Error","Value must be an Integer ") EndIf $UserVersion = $odb.execute ("PRAGMA user_version") GUICtrlSetData($UserVersionLabel,"User Version : " & $UserVersion) $SchemaVersion = $odb.execute ("PRAGMA schema_version") GUICtrlSetData($SchemaVersionLabel,"Schema Version : " & $SchemaVersion) $Encoding = $odb.execute ("PRAGMA encoding") GUICtrlSetData($EncodingLabel,"Encoding : " & $Encoding) $DatabaseList = $odb.execute ("PRAGMA database_list") $TableInfo = $odb.execute ("PRAGMA table_info") $ForeignKey = $odb.execute ("PRAGMA foreign_key_list") $IndexInfo = $odb.execute ("PRAGMA index_info") $IndexList = $odb.execute ("PRAGMA index_list") GUICtrlSetData($InfoDB, " Cache Size = " & $CacheSize & " AutoVacuum = " & $AutoVacuum & " Sync = " & $Synchrone ) Endif EndFunc Func CloseDB() $odb.close () _GUICtrlTreeViewDeleteAllItems($TreeDB) GUICtrlSetState($bRefreshD, $gui_disable) EndFunc