John117 1 Posted July 25, 2007 (edited) Can someone please post any working example of any update to a sqlite database record? As long as it is not an add(insert), but is an update to an exsisting record, it will work. I have been struggeling with this concept for a couple days and have found nothing on line to show a working example. Edited July 25, 2007 by Hatcheda Share this post Link to post Share on other sites
John117 1 Posted July 25, 2007 This is the shortest version I have. It is not working $sQuery = "UPDATE SET Department='Marketing' FROM Users WHERE User_Name='HATCHEDA'" It should update the department with Marketing -geting no errors. just doesn't update Share this post Link to post Share on other sites
John117 1 Posted July 25, 2007 The easiest way to understand is to run the following code. When you run script one, it will create a db. the department for the record will be "NotUpdated" Then run the second script. The Update should change the Department to "Updated". -but I can't get it to work. Can anyone help with this . . .Please Script1: CODE#include <SQLite.au3> #include <SQLite.dll.au3> ;This is the declaration section $UserName = StringUpper(@UserName) Global $hDB, $hQuery, $aRow, $sMsg, $aNames ;This is the Source section -This info will be pulled from the 'Info Tab' on the GUI. ;It will check to see if the USERID already in the database, and aid in the update where needed Global $avSampleData[1] = [$UserName & "," & "David Hatcher,NotUpdated,FE,BEGINING,BM,SG,ON,OFF"] ;This is the command to start the SQL.DB Global $SqlLiteDll = _SQLite_Startup() If @error Then MsgBox(16, "Error", "Failed to start up SQLite, @error = " & @error) Else $hDB = _SQLite_Open(@ScriptDir & "\TestData.db") ; Opens a database file to read/write ;$hDB = _SQLite_Open() ; Opens a database in memory if a file is not used If $hDB = 0 Or @error Then MsgBox(16, "Error", "Error opening database in memory, @error = " & @error & ", @extended = " & @extended) Else ; Create a database table _SQLite_Exec(-1, "CREATE TABLE Users (User_ID INTEGER PRIMARY KEY, User_Name, Full_Name, Department, Group1, Group2, Group3, Group4, LogOn, LogOff);") ; Create Users table ;users are added here from the Source Section $avSampleData[1] ; Add users For $n = 0 To UBound($avSampleData) - 1 $avSplit = StringSplit($avSampleData[$n], ",") ;$sQuery = "SELECT * FROM Users WHERE User_Name='" & $avSplit[1] & "'" ;new section ;If _SQLite_Exec(-1, $sQuery) = $SQLITE_OK Then ;New Section ;$sQuery = "UPDATE Department='Marketing' FROM Users WHERE User_Name='" & $UserName & "'" ;MsgBox(16, "Error", "User_Name already exists: " & $avSplit[1]) ;New Section ;Else ;New Section $sQuery = "INSERT INTO Users(User_Name, Full_Name, Department, Group1, Group2, Group3, Group4, LogOn, LogOff) VALUES('" & $avSplit[1] & "','" & $avSplit[2] & "','" & $avSplit[3] & "','" & $avSplit[4] & "','" & $avSplit[5] & "','" & $avSplit[6] & "','" & $avSplit[7] & "','" & $avSplit[8] & "','" & $avSplit[9] & "');" If _SQLite_Exec(-1, $sQuery) <> $SQLITE_OK Then MsgBox(16, "Error", "Error adding user to database: " & $avSplit[1]) ;EndIf Next ;query section that poduces information from the database that was written by "; Add Users" $sQuery = 'SELECT User_Name,Full_Name,Department,Group1,Group2,Group3,Group4,LogOn,LogOff FROM Users WHERE User_Name="' & $UserName & '"' If _SQLite_Query(-1, $sQuery, $hQuery) = $SQLITE_OK Then ; Read data out of the query $sMsg = "users:" & @LF While 1 $RET = _SQLite_FetchData($hQuery, $aRow) If $RET = $SQLITE_OK Then _ArrayDisplay($aRow, "Debug: $aRow") $sMsg &= $aRow[0] & ", " & $aRow[2] & @CRLF ;add more here to show more. ex $aRow[5] would show "Group3" or "BM" Else ExitLoop EndIf WEnd MsgBox(64, "Results", $sMsg) Else MsgBox(16, "Error", "Error executing query: " & $sQuery) EndIf ; Shut it down _SQLite_Close($hDB) _SQLite_Shutdown() EndIf EndIf Script2: CODE#include <SQLite.au3> #include <SQLite.dll.au3> ;This is the declaration section $UserName = StringUpper(@UserName) Global $hDB, $hQuery, $aRow, $sMsg, $aNames ;This is the Source section -This info will be pulled from the 'Info Tab' on the GUI. ;It will check to see if the USERID already in the database, and aid in the update where needed Global $avSampleData[1] = [$UserName & "," & "David Hatcher,NotUpdated,FE,BEGINING,BM,SG,ON,OFF"] ;This is the command to start the SQL.DB Global $SqlLiteDll = _SQLite_Startup() If @error Then MsgBox(16, "Error", "Failed to start up SQLite, @error = " & @error) Else $hDB = _SQLite_Open(@ScriptDir & "\TestData.db") ; Opens a database file to read/write ;$hDB = _SQLite_Open() ; Opens a database in memory if a file is not used If $hDB = 0 Or @error Then MsgBox(16, "Error", "Error opening database in memory, @error = " & @error & ", @extended = " & @extended) Else ; Create a database table _SQLite_Exec(-1, "CREATE TABLE Users (User_ID INTEGER PRIMARY KEY, User_Name, Full_Name, Department, Group1, Group2, Group3, Group4, LogOn, LogOff);") ; Create Users table ;users are added here from the Source Section $avSampleData[1] ;For $n = 0 To UBound($avSampleData) - 1 ;$avSplit = StringSplit($avSampleData[$n], ",") ;$sQuery = "SELECT * FROM Users WHERE User_Name='" & $avSplit[1] & "'" ;new section ;If _SQLite_Exec(-1, $sQuery) = $SQLITE_OK Then ;New Section $sQuery = "UPDATE Department='Marketing' FROM Users WHERE User_Name='" & $UserName & "'" ;MsgBox(16, "Error", "User_Name already exists: " & $avSplit[1]) ;New Section ;Else ;New Section ;$sQuery = "INSERT INTO Users(User_Name, Full_Name, Department, Group1, Group2, Group3, Group4, LogOn, LogOff) VALUES('" & $avSplit[1] & "','" & $avSplit[2] & "','" & $avSplit[3] & "','" & $avSplit[4] & "','" & $avSplit[5] & "','" & $avSplit[6] & "','" & $avSplit[7] & "','" & $avSplit[8] & "','" & $avSplit[9] & "');" ;If _SQLite_Exec(-1, $sQuery) <> $SQLITE_OK Then MsgBox(16, "Error", "Error adding user to database: " & $avSplit[1]) ;EndIf ;Next ;query section that poduces information from the database that was written by "; Add Users" $sQuery = 'SELECT User_Name,Full_Name,Department,Group1,Group2,Group3,Group4,LogOn,LogOff FROM Users WHERE User_Name="' & $UserName & '"' If _SQLite_Query(-1, $sQuery, $hQuery) = $SQLITE_OK Then ; Read data out of the query $sMsg = "users:" & @LF While 1 $RET = _SQLite_FetchData($hQuery, $aRow) If $RET = $SQLITE_OK Then _ArrayDisplay($aRow, "Debug: $aRow") $sMsg &= $aRow[0] & ", " & $aRow[2] & @CRLF ;add more here to show more. ex $aRow[5] would show "Group3" or "BM" Else ExitLoop EndIf WEnd MsgBox(64, "Results", $sMsg) Else MsgBox(16, "Error", "Error executing query: " & $sQuery) EndIf ; Shut it down _SQLite_Close($hDB) _SQLite_Shutdown() EndIf EndIf Share this post Link to post Share on other sites
PsaltyDS 38 Posted July 25, 2007 OK, first I have to more careful to take my own advice: UPDATE Bookmark It By update, I meant changing existing columns, like changing the Department: expandcollapse popup$sQuery = "UPDATE Department='Marketing' FROM Users WHERE User_Name='" & $avSplit[1] & "'"oÝ÷ Ù:%yªçëh"ܨº¯zÚ,¥éݶ)Â+a¶ºÚ¶¥¥ø¥y:%yªçIâµì¨º¯zÚ,¥éݶ)¢{azêåoÝ÷ Ø¥²ë©Â+aiÉ^j{÷(Ö¢ç]zj¢¸jëh×6#include <SQLite.au3> #include <SQLite.dll.au3> Global $hDB, $hQuery, $aRow, $sMsg, $aNames Global $avSampleData[4] = ["coolj, Joe Cool, Marketing", "pattyp, Peppermint Patty, Maintenance", "brownc, Charlie Brown, Accounting", "vanpeltl, Lucy VanPelt, Marketing"] Global $SqlLiteDll = _SQLite_Startup() $hDB = _SQLite_Open() ; Opens a database in memory ; Create a database table $sExec = "CREATE TABLE Users (User_ID INTEGER PRIMARY KEY,User_Name,Full_Name,Department);" _SQLite_Exec($hDB, $sExec) ; Add users For $n = 0 To 3 $avSplit = StringSplit($avSampleData[$n], ",") ; Strip leading and trailing whitespace before using entries For $i = 1 To $avSplit[0] $avSplit[$i] = StringStripWS($avSplit[$i], 1 + 2) Next $sExec = "INSERT INTO Users(User_Name,Full_Name,Department) VALUES('" & $avSplit[1] & "','" & $avSplit[2] & "','" & $avSplit[3] & "');" If _SQLite_Exec($hDB, $sExec) = $SQLITE_OK Then ConsoleWrite("Debug: Successfully added to Users table: " & $avSplit[2] & @LF) Next ; Show current data _QueryMarketing() ; Change a record $sExec = "UPDATE Users SET Department='Marketing' WHERE User_Name='pattyp'" If _SQLite_Exec($hDB, $sExec) = $SQLITE_OK Then ConsoleWrite("Debug: Successfully updated a record." & @LF) ; Show data again _QueryMarketing() ; Shut it down _SQLite_Close() _SQLite_Shutdown() Func _QueryMarketing() ; Query for users in Marketing $sQuery = "SELECT * FROM Users WHERE Department='Marketing'" If _SQLite_Query($hDB, $sQuery, $hQuery) = $SQLITE_OK Then ; Read data out of the query $sMsg = "Marketing users:" & @LF While 1 $RET = _SQLite_FetchData($hQuery, $aRow) If $RET = $SQLITE_OK Then ConsoleWrite("Debug: FetchData loop got one row of data..." & @LF) $sMsg &= $aRow[2] & @CRLF Else ConsoleWrite("Debug: Exit FetchData loop, $RET = " & $RET & @LF) ExitLoop EndIf WEnd MsgBox(64, "Results", $sMsg) Else MsgBox(16, "Error", "Error executing query: " & $sQuery) EndIf EndFunc ;==>_QueryMarketing Hope that helps! Hide PsaltyDS's signature Hide all signatures Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Share this post Link to post Share on other sites
John117 1 Posted July 25, 2007 I SWEAR I tried that already! Testing new code now! Share this post Link to post Share on other sites
John117 1 Posted July 25, 2007 And in an attempt to reclaim my sanity, I copied the line over the one in script2. It didnt work! meaning I did try that and the prob was somewhere else! On the other hand, the new code and structure do work and are better! So I will stop beating my head now! Thanks a ton. I have spent many, many hours on this now! Share this post Link to post Share on other sites
PsaltyDS 38 Posted July 25, 2007 And in an attempt to reclaim my sanity, I copied the line over the one in script2. It didnt work! meaning I did try that and the prob was somewhere else!On the other hand, the new code and structure do work and are better! So I will stop beating my head now! Thanks a ton. I have spent many, many hours on this now! Watch out for my mistake: In every place you give data or column names, etc., don't let extra leading spaces sneak in! "User_Name, Full_Name, Department" <> "User_Name,Full_Name,Department" Hide PsaltyDS's signature Hide all signatures Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Share this post Link to post Share on other sites
John117 1 Posted July 25, 2007 (edited) OK, here's my attempt to convert the first script to yours. (I didn't notice anything to stop duplicate entries based on User_Name) and yours happended to be working on the update side! this specific section should contain the issue CODE ; Add users For $n = 0 To UBound($avSampleData) - 1 $avSplit = StringSplit($avSampleData[$n], ",") ; Strip leading and trailing whitespace before using entries For $i = 1 To $avSplit[0] $avSplit[$i] = StringStripWS($avSplit[$i], 1 + 2) Next $sExec = "SELECT User_Name FROM Users WHERE User_Name='" & $UserName & "'" If _SQLite_Exec($hDB, $sExec) <> $SQLITE_OK Then $sExec = "INSERT INTO Users(User_Name,Full_Name,Department,Group1,Group2,Group3,Group4,LogOn,LogOff) VALUES('" & $avSplit[1] & "','" & $avSplit[2] & "','" & $avSplit[3] & "','" & $avSplit[4] & "','" & $avSplit[5] & "','" & $avSplit[6] & "','" & $avSplit[7] & "','" & $avSplit[8] & "','" & $avSplit[9] & "');" EndIf If _SQLite_Exec($hDB, $sExec) = $SQLITE_OK Then ConsoleWrite("Debug: Successfully added to Users table: " & $avSplit[2] & @LF) Next I was of the thought that the CODE $sExec = "SELECT User_Name FROM Users WHERE User_Name='" & $UserName & "'" If _SQLite_Exec($hDB, $sExec) <> $SQLITE_OK Then Should return <> if no matching User_Name exsists and then add a record. (IE a clean DB or first time user access) as it stands the record is not added with <> set to = a new record is added each time. Full code posted below CODE#include <SQLite.au3> #include <SQLite.dll.au3> #include <GuiConstants.au3> #include <file.au3> ;This is the declaration section $UserName = StringUpper(@UserName) Global $hDB, $hQuery, $aRow, $sMsg, $aNames Global $avSampleData[1] = [$UserName & "," & "David Hatcher,SIM,FE,BE,BM,SG,ON,OFF"] ;This is the command to start the SQL.DB Global $SqlLiteDll = _SQLite_Startup() $hDB = _SQLite_Open() ; Opens a database in memory If @error Then MsgBox(16, "Error", "Failed to start up SQLite, @error = " & @error) Else $hDB = _SQLite_Open(@ScriptDir & "\TestData.db") ; Opens a database file to read/write ;$hDB = _SQLite_Open() ; Opens a database in memory If $hDB = 0 Or @error Then MsgBox(16, "Error", "Error opening database in memory, @error = " & @error & ", @extended = " & @extended) Else ; Create a database table $sExec = "CREATE TABLE Users (User_ID INTEGER PRIMARY KEY,User_Name,Full_Name,Department,Group1,Group2,Group3,Group4,LogOn,LogOff);" ; Create Users table _SQLite_Exec($hDB, $sExec) ; Add users For $n = 0 To UBound($avSampleData) - 1 $avSplit = StringSplit($avSampleData[$n], ",") ; Strip leading and trailing whitespace before using entries For $i = 1 To $avSplit[0] $avSplit[$i] = StringStripWS($avSplit[$i], 1 + 2) Next $sExec = "SELECT User_Name FROM Users WHERE User_Name='" & $UserName & "'" If _SQLite_Exec($hDB, $sExec) <> $SQLITE_OK Then $sExec = "INSERT INTO Users(User_Name,Full_Name,Department,Group1,Group2,Group3,Group4,LogOn,LogOff) VALUES('" & $avSplit[1] & "','" & $avSplit[2] & "','" & $avSplit[3] & "','" & $avSplit[4] & "','" & $avSplit[5] & "','" & $avSplit[6] & "','" & $avSplit[7] & "','" & $avSplit[8] & "','" & $avSplit[9] & "');" EndIf If _SQLite_Exec($hDB, $sExec) = $SQLITE_OK Then ConsoleWrite("Debug: Successfully added to Users table: " & $avSplit[2] & @LF) Next ; Show current data _QueryMarketing() ; Change a record $sExec = "UPDATE Users SET Department='Marketing' WHERE User_Name='" & $UserName & "'" If _SQLite_Exec($hDB, $sExec) = $SQLITE_OK Then ConsoleWrite("Debug: Successfully updated a record." & @LF) ; Show data again _QueryMarketing() ; Shut it down _SQLite_Close() _SQLite_Shutdown() EndIf EndIf Func _QueryMarketing() ; Query for users in Marketing $sQuery = "SELECT * FROM Users WHERE Department='Marketing'" If _SQLite_Query($hDB, $sQuery, $hQuery) = $SQLITE_OK Then ; Read data out of the query $sMsg = "Marketing users:" & @LF While 1 $RET = _SQLite_FetchData($hQuery, $aRow) If $RET = $SQLITE_OK Then ConsoleWrite("Debug: FetchData loop got one row of data..." & @LF) $sMsg &= $aRow[2] & @CRLF Else ConsoleWrite("Debug: Exit FetchData loop, $RET = " & $RET & @LF) ExitLoop EndIf WEnd MsgBox(64, "Results", $sMsg) Else MsgBox(16, "Error", "Error executing query: " & $sQuery) EndIf EndFunc ;==>_QueryMarketing Edited July 25, 2007 by Hatcheda Share this post Link to post Share on other sites
PsaltyDS 38 Posted July 26, 2007 (edited) OK, here's my attempt to convert the first script to yours. (I didn't notice anything to stop duplicate entries based on User_Name) and yours happended to be working on the update side! this specific section should contain the issue CODE ; Add users For $n = 0 To UBound($avSampleData) - 1 $avSplit = StringSplit($avSampleData[$n], ",") ; Strip leading and trailing whitespace before using entries For $i = 1 To $avSplit[0] $avSplit[$i] = StringStripWS($avSplit[$i], 1 + 2) Next $sExec = "SELECT User_Name FROM Users WHERE User_Name='" & $UserName & "'" If _SQLite_Exec($hDB, $sExec) <> $SQLITE_OK Then $sExec = "INSERT INTO Users(User_Name,Full_Name,Department,Group1,Group2,Group3,Group4,LogOn,LogOff) VALUES('" & $avSplit[1] & "','" & $avSplit[2] & "','" & $avSplit[3] & "','" & $avSplit[4] & "','" & $avSplit[5] & "','" & $avSplit[6] & "','" & $avSplit[7] & "','" & $avSplit[8] & "','" & $avSplit[9] & "');" EndIf If _SQLite_Exec($hDB, $sExec) = $SQLITE_OK Then ConsoleWrite("Debug: Successfully added to Users table: " & $avSplit[2] & @LF) NextI was of the thought that the $sExec = "SELECT User_Name FROM Users WHERE User_Name='" & $UserName & "'" If _SQLite_Exec($hDB, $sExec) <> $SQLITE_OK Then Should return <> if no matching User_Name exsists and then add a record. (IE a clean DB or first time user access) as it stands the record is not added with <> set to = a new record is added each time. You are executing a query outside of the If/EndIf: If _SQLite_Exec($hDB, $sExec) = $SQLITE_OK Then ConsoleWrite("Debug: Successfully added to Users table: " & $avSplit[2] & @LF) Try it like this: ; Add users For $n = 0 To UBound($avSampleData) - 1 $avSplit = StringSplit($avSampleData[$n], ",") ; Strip leading and trailing whitespace before using entries For $i = 1 To $avSplit[0] $avSplit[$i] = StringStripWS($avSplit[$i], 1 + 2) Next $sQuery = "SELECT User_Name FROM Users WHERE User_Name='" & $UserName & "'" If _SQLite_Query($hDB, $sQuery, $hQuery) = $SQLITE_OK Then ConsoleWrite("Debug: Skipped adding user, already exists: " & $UserName & @LF) Else $sExec = "INSERT INTO Users(User_Name,Full_Name,Department,Group1,Group2,Group3,Group4,LogOn,LogOff) VALUES('" & _ $avSplit[1] & "','" & $avSplit[2] & "','" & $avSplit[3] & "','" & $avSplit[4] & "','" & $avSplit[5] & "','" & _ $avSplit[6] & "','" & $avSplit[7] & "','" & $avSplit[8] & "','" & $avSplit[9] & "');" If _SQLite_Exec($hDB, $sExec) = $SQLITE_OK Then ConsoleWrite("Debug: Successfully added to Users table: " & $avSplit[2] & @LF) EndIf Next Edit: Added missing $hQuery parameter. Edited July 26, 2007 by PsaltyDS Hide PsaltyDS's signature Hide all signatures Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Share this post Link to post Share on other sites
John117 1 Posted July 26, 2007 Hey, I tried that for about an hour last night and couldn't get around this line If _SQLite_Query($hDB, $sQuery) = $SQLITE_OK Then If _^ ERROR Error: Incorrect number of parameters in function call I swaped it out with a few other possibilities based on previous working versions and haven't found the solution I have the full code below incase something is affecting it. I have tried your last post on the full code I will post below and your demo Full: CODE#include <SQLite.au3> #include <SQLite.dll.au3> #include <GuiConstants.au3> #include <file.au3> ;************************************************************************ ;Start ;************************************************************************ $UserName = StringUpper(@UserName) Dim $UpdateMsg, $sMsg2, $bRecords Global $hDB, $hQuery, $aRow, $sMsg, $aNames Global $avSampleData[1] = [$UserName & "," & "David Hatcher,Yoda,FE,BE,BM,SG,ON,OFF"] ;************************************************************************ ;START OR CREATE SQL.DB ;************************************************************************ Global $SqlLiteDll = _SQLite_Startup() If @error Then MsgBox(16, "Error", "Failed to start up SQLite, @error = " & @error) Else ;$hDB = _SQLite_Open() ; Opens a database in memory $hDB = _SQLite_Open(@ScriptDir & "\TestData.db") ; Opens a database file to read/write If $hDB = 0 Or @error Then MsgBox(16, "Error", "Error opening database in memory, @error = " & @error & ", @extended = " & @extended) Else ; Create a database table $sExec = "CREATE TABLE Users (User_ID INTEGER PRIMARY KEY,User_Name,Full_Name,Department,Group1,Group2,Group3,Group4,LogOn,LogOff);" ; Create Users table _SQLite_Exec($hDB, $sExec) ;************************************************************************ ; Add users ;************************************************************************ For $n = 0 To UBound($avSampleData) - 1 $avSplit = StringSplit($avSampleData[$n], ",") ; Strip leading and trailing whitespace before using entries For $i = 1 To $avSplit[0] $avSplit[$i] = StringStripWS($avSplit[$i], 1 + 2) Next $sQuery = "SELECT User_Name FROM Users WHERE User_Name='" & $UserName & "'" If _SQLite_Query($hDB, $sQuery) = $SQLITE_OK Then ConsoleWrite("Debug: Skipped adding user, already exists: " & $UserName & @LF) Else $sExec = "INSERT INTO Users(User_Name,Full_Name,Department,Group1,Group2,Group3,Group4,LogOn,LogOff) VALUES('" & _ $avSplit[1] & "','" & $avSplit[2] & "','" & $avSplit[3] & "','" & $avSplit[4] & "','" & $avSplit[5] & "','" & _ $avSplit[6] & "','" & $avSplit[7] & "','" & $avSplit[8] & "','" & $avSplit[9] & "');" If _SQLite_Exec($hDB, $sExec) = $SQLITE_OK Then ConsoleWrite("Debug: Successfully added to Users table: " & $avSplit[2] & @LF) EndIf Next ;************************************************************************ ; Make a record change ;************************************************************************ _QueryMarketing() ; Show current data $sExec = "UPDATE Users SET Department='Marketing' WHERE User_Name='" & $UserName & "'" ; Change a record If _SQLite_Exec($hDB, $sExec) = $SQLITE_OK Then ConsoleWrite("Debug: Successfully updated a record." & @LF) _QueryMarketing(); Show data again ;************************************************************************ ;Building of GUI Section ;************************************************************************ ; Query the database to add Users to GUI $sQuery = 'SELECT User_Name,Full_Name FROM Users WHERE User_Name="' & $UserName & '"' If _SQLite_Query(-1, $sQuery, $hQuery) = $SQLITE_OK Then ; Read data out of the query While 1 $RET = _SQLite_FetchData($hQuery, $aRow) If $RET = $SQLITE_OK Then $sMsg &= $aRow[0] & "," $sMsg2 &= $aRow[1] & "," ;$sMsg &= $aRow[0] & ", " & $aRow[2] & @CRLF ;add more here to show more. ex $aRow[5] would show "Group3" or "BM" Else ExitLoop EndIf WEnd If StringRight($sMsg, 1) = "," Then $sMsg = StringTrimRight($sMsg, 1) $aRecords = StringSplit($sMsg, ",") If StringRight($sMsg2, 1) = "," Then $sMsg2 = StringTrimRight($sMsg2, 1) $bRecords = StringSplit($sMsg2, ",") Else MsgBox(16, "Error", "Error executing query: " & $sQuery) EndIf ; Shut it down _SQLite_Close($hDB) _SQLite_Shutdown() EndIf EndIf ; Debug check of data -Shows matching _ArrayDisplay($aRecords, "Debug: $aRecords") _ArrayDisplay($bRecords, "Debug: $bRecords") ;MsgBox(64, "Results", $sMsg) Dim $aButtons[$aRecords[0] + 1] ;Dim $aButtons[uBound($aRecords)] $aHeight = (($aRecords[0] + 1) * 25) + 100 ; START NET SEND RunWait("net start messenger", "", @SW_HIDE) ; GUI $window = GUICreate(@UserName & "'s Messenger", 250, $aHeight) GUISetIcon(@SystemDir & "\mspaint.exe", 0) ; MENU $filemenu = GUICtrlCreateMenu("File") $fileitem = GUICtrlCreateMenuItem("Add Contacts...", $filemenu) $separator1 = GUICtrlCreateMenuItem("", $filemenu) $exititem = GUICtrlCreateMenuItem("Exit", $filemenu) ;LABEL - TO BE ADDED WITH USERID DETAIL $CurrentUserName = GUICtrlCreateLabel("Type Message *Press Button to Send*", 20, 10, 200, 20) ; INPUT $Edit1 = GUICtrlCreateEdit("Type Message Here", 10, 30, 230, 60, $ES_WANTRETURN) ;BUILD BUTTONS $aTop = 100 $aLeft = 10 For $x = 1 To $bRecords[0] $aButtons[$x] = GUICtrlCreateButton($bRecords[$x], $aLeft, $aTop, 100, 30) If $aLeft = 10 Then $aLeft = 140 ContinueLoop Else $aLeft = 10 $aTop = $aTop + 40 EndIf Next ;************************************************************************ ;SET BUTTON FUNCTIONS ;************************************************************************ GUISetState() While 1 $cursorinfo = GUIGetCursorInfo($window) If $cursorinfo[4] = $Edit1 Then If $cursorinfo[2] = 1 Then GUICtrlSetData($Edit1, "") EndIf EndIf $msg = GUIGetMsg() If $msg = $GUI_EVENT_CLOSE Then Exit If $msg = $exititem Then Exit Select Case $msg = $fileitem ShellExecute("Add New Contacts Here.txt", "", @ScriptDir, "edit") EndSelect For $x = 1 To $aRecords[0] If $msg = $aButtons[$x] Then Run("net send " & $aRecords[$x] & " Mesage From " & @UserName & ": " & StringReplace(GUICtrlRead($Edit1), @CRLF, " "), "", @SW_HIDE) MsgBox(0, "Message Sent", "message to " & $aRecords[$x] & ": " & StringReplace(GUICtrlRead($Edit1), @CRLF, " ")) EndIf Next WEnd ;************************************************************************ ;FUNCTIONS ;************************************************************************ Func _QueryMarketing() ; Query for users in Marketing $sQuery = "SELECT * FROM Users WHERE Department='Marketing'" ;To be changed to logon where logon > logoff and logon > (now - x) If _SQLite_Query($hDB, $sQuery, $hQuery) = $SQLITE_OK Then ; Read data out of the query $UpdateMsg = "Marketing users:" & @LF While 1 $RET = _SQLite_FetchData($hQuery, $aRow) If $RET = $SQLITE_OK Then ConsoleWrite("Debug: FetchData loop got one row of data..." & @LF) $UpdateMsg &= $aRow[2] & @CRLF Else ConsoleWrite("Debug: Exit FetchData loop, $RET = " & $RET & @LF) ExitLoop EndIf WEnd MsgBox(64, "Results", $UpdateMsg) Else MsgBox(16, "Error", "Error executing query: " & $sQuery) EndIf EndFunc ;==>_QueryMarketing Also, would you review this code to see if I did anything silly? It is all working now except for the avoid dups part. With all the sections working I will now be able to put the app together with little difficulty. (Assuming I think of a way to refresh the buttons list with out annoying the users.) -Going to study up on that now! Share this post Link to post Share on other sites
PsaltyDS 38 Posted July 26, 2007 Sorry, forgot the parameter for the query handle when I changed it from _SQLite_Exec() to _SQLite_Query(): If _SQLite_Query($hDB, $sQuery, $hQuery) = $SQLITE_OK Then I made only that change, and it looked like your script above was getting close to done! Hide PsaltyDS's signature Hide all signatures Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Share this post Link to post Share on other sites
John117 1 Posted July 26, 2007 (edited) Hey, that took care of the error, but it's skipping adding a new user when you have a clean DB. (First run) Shouldn't it fail and then add a user when the query returns false? Giver a swing and let me know what's happening! Thanks! This is what I am working with. SQL file should be used over memory to insure duplicates do not appear on second, third . . . . run. CODE#include <SQLite.au3> #include <SQLite.dll.au3> #include <GuiConstants.au3> #include <file.au3> ;************************************************************************ ;Start ;************************************************************************ $UserName = StringUpper(@UserName) Dim $UpdateMsg, $sMsg2, $bRecords Global $hDB, $hQuery, $aRow, $sMsg, $aNames Global $avSampleData[1] = [$UserName & "," & "David Hatcher,Yoda,FE,BE,BM,SG,ON,OFF"] ;************************************************************************ ;START OR CREATE SQL.DB ;************************************************************************ Global $SqlLiteDll = _SQLite_Startup() If @error Then MsgBox(16, "Error", "Failed to start up SQLite, @error = " & @error) Else ;$hDB = _SQLite_Open() ; Opens a database in memory $hDB = _SQLite_Open(@ScriptDir & "\TestData.db") ; Opens a database file to read/write If $hDB = 0 Or @error Then MsgBox(16, "Error", "Error opening database in memory, @error = " & @error & ", @extended = " & @extended) Else ; Create a database table $sExec = "CREATE TABLE Users (User_ID INTEGER PRIMARY KEY,User_Name,Full_Name,Department,Group1,Group2,Group3,Group4,LogOn,LogOff);" ; Create Users table _SQLite_Exec($hDB, $sExec) ;************************************************************************ ; Add users ;************************************************************************ For $n = 0 To UBound($avSampleData) - 1 $avSplit = StringSplit($avSampleData[$n], ",") ; Strip leading and trailing whitespace before using entries For $i = 1 To $avSplit[0] $avSplit[$i] = StringStripWS($avSplit[$i], 1 + 2) Next $sQuery = "SELECT User_Name FROM Users WHERE User_Name='" & $UserName & "'" If _SQLite_Query($hDB, $sQuery, $hQuery) = $SQLITE_OK Then ConsoleWrite("Debug: Skipped adding user, already exists: " & $UserName & @LF) Else $sExec = "INSERT INTO Users(User_Name,Full_Name,Department,Group1,Group2,Group3,Group4,LogOn,LogOff) VALUES('" & _ $avSplit[1] & "','" & $avSplit[2] & "','" & $avSplit[3] & "','" & $avSplit[4] & "','" & $avSplit[5] & "','" & _ $avSplit[6] & "','" & $avSplit[7] & "','" & $avSplit[8] & "','" & $avSplit[9] & "');" If _SQLite_Exec($hDB, $sExec) = $SQLITE_OK Then ConsoleWrite("Debug: Successfully added to Users table: " & $avSplit[2] & @LF) EndIf Next ;************************************************************************ ; Make a record change ;************************************************************************ _QueryMarketing() ; Show current data $sExec = "UPDATE Users SET Department='Marketing' WHERE User_Name='" & $UserName & "'" ; Change a record If _SQLite_Exec($hDB, $sExec) = $SQLITE_OK Then ConsoleWrite("Debug: Successfully updated a record." & @LF) _QueryMarketing(); Show data again ;************************************************************************ ;Building of GUI Section ;************************************************************************ ; Query the database to add Users to GUI $sQuery = 'SELECT User_Name,Full_Name FROM Users WHERE User_Name="' & $UserName & '"' If _SQLite_Query(-1, $sQuery, $hQuery) = $SQLITE_OK Then ; Read data out of the query While 1 $RET = _SQLite_FetchData($hQuery, $aRow) If $RET = $SQLITE_OK Then $sMsg &= $aRow[0] & "," $sMsg2 &= $aRow[1] & "," ;$sMsg &= $aRow[0] & ", " & $aRow[2] & @CRLF ;add more here to show more. ex $aRow[5] would show "Group3" or "BM" Else ExitLoop EndIf WEnd If StringRight($sMsg, 1) = "," Then $sMsg = StringTrimRight($sMsg, 1) $aRecords = StringSplit($sMsg, ",") If StringRight($sMsg2, 1) = "," Then $sMsg2 = StringTrimRight($sMsg2, 1) $bRecords = StringSplit($sMsg2, ",") Else MsgBox(16, "Error", "Error executing query: " & $sQuery) EndIf ; Shut it down _SQLite_Close($hDB) _SQLite_Shutdown() EndIf EndIf ; Debug check of data -Shows matching _ArrayDisplay($aRecords, "Debug: $aRecords") _ArrayDisplay($bRecords, "Debug: $bRecords") ;MsgBox(64, "Results", $sMsg) Dim $aButtons[$aRecords[0] + 1] ;Dim $aButtons[uBound($aRecords)] $aHeight = (($aRecords[0] + 1) * 25) + 100 ; START NET SEND RunWait("net start messenger", "", @SW_HIDE) ; GUI $window = GUICreate(@UserName & "'s Messenger", 250, $aHeight) GUISetIcon(@SystemDir & "\mspaint.exe", 0) ; MENU $filemenu = GUICtrlCreateMenu("File") $fileitem = GUICtrlCreateMenuItem("Add Contacts...", $filemenu) $separator1 = GUICtrlCreateMenuItem("", $filemenu) $exititem = GUICtrlCreateMenuItem("Exit", $filemenu) ;LABEL - TO BE ADDED WITH USERID DETAIL $CurrentUserName = GUICtrlCreateLabel("Type Message *Press Button to Send*", 20, 10, 200, 20) ; INPUT $Edit1 = GUICtrlCreateEdit("Type Message Here", 10, 30, 230, 60, $ES_WANTRETURN) ;BUILD BUTTONS $aTop = 100 $aLeft = 10 For $x = 1 To $bRecords[0] $aButtons[$x] = GUICtrlCreateButton($bRecords[$x], $aLeft, $aTop, 100, 30) If $aLeft = 10 Then $aLeft = 140 ContinueLoop Else $aLeft = 10 $aTop = $aTop + 40 EndIf Next ;************************************************************************ ;SET BUTTON FUNCTIONS ;************************************************************************ GUISetState() While 1 $cursorinfo = GUIGetCursorInfo($window) If $cursorinfo[4] = $Edit1 Then If $cursorinfo[2] = 1 Then GUICtrlSetData($Edit1, "") EndIf EndIf $msg = GUIGetMsg() If $msg = $GUI_EVENT_CLOSE Then Exit If $msg = $exititem Then Exit Select Case $msg = $fileitem ShellExecute("Add New Contacts Here.txt", "", @ScriptDir, "edit") EndSelect For $x = 1 To $aRecords[0] If $msg = $aButtons[$x] Then Run("net send " & $aRecords[$x] & " Mesage From " & @UserName & ": " & StringReplace(GUICtrlRead($Edit1), @CRLF, " "), "", @SW_HIDE) MsgBox(0, "Message Sent", "message to " & $aRecords[$x] & ": " & StringReplace(GUICtrlRead($Edit1), @CRLF, " ")) EndIf Next WEnd ;************************************************************************ ;FUNCTIONS ;************************************************************************ Func _QueryMarketing() ; Query for users in Marketing $sQuery = "SELECT * FROM Users WHERE Department='Marketing'" ;To be changed to logon where logon > logoff and logon > (now - x) If _SQLite_Query($hDB, $sQuery, $hQuery) = $SQLITE_OK Then ; Read data out of the query $UpdateMsg = "Marketing users:" & @LF While 1 $RET = _SQLite_FetchData($hQuery, $aRow) If $RET = $SQLITE_OK Then ConsoleWrite("Debug: FetchData loop got one row of data..." & @LF) $UpdateMsg &= $aRow[2] & @CRLF Else ConsoleWrite("Debug: Exit FetchData loop, $RET = " & $RET & @LF) ExitLoop EndIf WEnd MsgBox(64, "Results", $UpdateMsg) Else MsgBox(16, "Error", "Error executing query: " & $sQuery) EndIf EndFunc ;==>_QueryMarketing Thanks for all the help! From here on out I will just be adding GUI stuff like tabs, Menu, maybe study up on sort options Will be happy to send you finished script when I get there . . .(of course I will change the Update part to update something more useful ) Edited July 26, 2007 by Hatcheda Share this post Link to post Share on other sites
PsaltyDS 38 Posted July 26, 2007 Wow, I didn't think it was that hard to get SQLite to tell you something didn't exist. This works for skipping User_Name that already exists, but adding one that doesn't: $sQuery = "SELECT User_Name FROM Users WHERE User_Name='" & $UserName & "'" _SQLite_QuerySingleRow($hDB, $sQuery, $aRow) If $aRow[0] <> "" Then ConsoleWrite("Debug: Skipped adding user, already exists: " & $UserName & @LF) Else $sExec = "INSERT INTO Users(User_Name,Full_Name,Department,Group1,Group2,Group3,Group4,LogOn,LogOff) VALUES('" & _ $avSplit[1] & "','" & $avSplit[2] & "','" & $avSplit[3] & "','" & $avSplit[4] & "','" & $avSplit[5] & "','" & _ $avSplit[6] & "','" & $avSplit[7] & "','" & $avSplit[8] & "','" & $avSplit[9] & "');" If _SQLite_Exec($hDB, $sExec) = $SQLITE_OK Then ConsoleWrite("Debug: Successfully added to Users table: " & $avSplit[2] & @LF) EndIf Using _SQLite_QuerySingleRow() vice just _SQLite_Query() avoids the extra step of doing the _SQLite_FetchData() part. Hide PsaltyDS's signature Hide all signatures Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Share this post Link to post Share on other sites
John117 1 Posted July 26, 2007 Holy Connolly, By George youve Done IT!!! Thank you so much! From here on out, it should be nothing but swaping a few fields, and adding a little nonessential flare! Share this post Link to post Share on other sites
