John117 Posted July 24, 2007 Share Posted July 24, 2007 (edited) I have the following code. One is the end section of a script, the second is the beginning of another script. The first section is a query that pulls data to a msgbox The second section pulls data froma text file. I would like to replace the text file with the data pulled from the query and cutout the msgbox. Please help if you can (point being to merge the scripts) Section one: CODE$sQuery = 'SELECT User_Name 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] &@CRLF 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() Section two: CODEDim $aRecords If Not _FileReadToArray( @ScriptDir & "\Add New Contacts Here.txt",$aRecords) Then MsgBox(4096,"Error", " Error reading log to Array error:" & @error) Exit EndIf Dim $aButtons[uBound($aRecords)] $aHeight = (UBound($aRecords) * 25) + 60 Full scripts below if needed. Script two depends on a text file in the same directory Format is: Add New Contacts here.txt UserId1 UserId2 Userid3 and so on . . . This will need to be in the same directory as the script file. Full script 1: 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 update where needed. 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() 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 ; Add users ; For $n = 0 To UBound($avSampleData) -1 ; $avSplit = StringSplit($avSampleData[$n], ",") ; $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]) ; Next $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[1] & ", " & $aRow[2] & ", " & $aRow[3] & ", " & $aRow[4] &@CRLF 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 Full script 2: CODE#include <GuiConstants.au3> #include <file.au3> Dim $aRecords If Not _FileReadToArray( @ScriptDir & "\Add New Contacts Here.txt",$aRecords) Then MsgBox(4096,"Error", " Error reading log to Array error:" & @error) Exit EndIf Dim $aButtons[uBound($aRecords)] $aHeight = (UBound($aRecords) * 25) + 60 ; 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) ;$CurrentUserName = GUICtrlCreateLabel("My Messenger", 90, 10) ; INPUT $Edit1 = GUICtrlCreateEdit("Type Message Here", 10, 30, 230, 60, $ES_WANTRETURN) ;$input = GUICtrlCreateInput("Type Message Here", 10, 30, 230, 60, $ES_WANTRETURN) ;BUILD BUTTONS $aTop = 100 $aLeft = 10 For $x = 1 to $aRecords[0] $aButtons[$x] = GUICtrlCreateButton( $aRecords[$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 edit: first code box was edited to only pull userid Edited July 24, 2007 by Hatcheda Link to comment Share on other sites More sharing options...
John117 Posted July 24, 2007 Author Share Posted July 24, 2007 This is what I am now working with. Kinda getting there, but I have a break somewhere feeding, or handeling the array. 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 ;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 update where needed. 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() 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 ; Add users For $n = 0 To UBound($avSampleData) -1 $avSplit = StringSplit($avSampleData[$n], ",") $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]) Next $sQuery = 'SELECT User_Name 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] &@CRLF Else ExitLoop EndIf WEnd ;MsgBox(64, "Results", $sMsg) Dim $aButtons[uBound($aRow)] Else MsgBox(16, "Error", "Error executing query: " & $sQuery) EndIf ; Shut it down _SQLite_Close($hDB) _SQLite_Shutdown() EndIf EndIf ;Dim $aRecords ;If Not _FileReadToArray( @ScriptDir & "\Add New Contacts Here.txt",$aRecords) Then ;MsgBox(4096,"Error", " Error reading log to Array error:" & @error) ; Exit ;EndIf ;Dim $aButtons[uBound($aRecords)] $aHeight = (UBound($aRow) * 25) + 60 ; 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) ;$CurrentUserName = GUICtrlCreateLabel("My Messenger", 90, 10) ; INPUT $Edit1 = GUICtrlCreateEdit("Type Message Here", 10, 30, 230, 60, $ES_WANTRETURN) ;$input = GUICtrlCreateInput("Type Message Here", 10, 30, 230, 60, $ES_WANTRETURN) ;BUILD BUTTONS $aTop = 100 $aLeft = 10 For $x = 1 to UBound($aRow) -1 $aButtons[$x] = GUICtrlCreateButton( $aRow[$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 UBound($aRow) -1 If $msg = $aButtons[$x] Then Run("net send " & $aRow[$x] & " Mesage From " & @UserName & ": " & StringReplace(GUICtrlRead($Edit1), @CRLF, " "), "", @SW_HIDE) MsgBox(0, "Message Sent", "message to "&$aRow[$x]&": "& StringReplace(GUICtrlRead($Edit1), @CRLF, " ")) EndIf Next WEnd Link to comment Share on other sites More sharing options...
PsaltyDS Posted July 24, 2007 Share Posted July 24, 2007 This cleans it up some. I think you missed the fact that $aRow is only the temporary result of each FetchData, and the final list came out as a string in $sMsg. I changed it to give you an array called $aRecords instead, and inserted a debug display so you could see the results. I also commented out the Run() stuff because I don't want it running on my computer while debugging: expandcollapse popup#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() 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 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 ; Add users For $n = 0 To UBound($avSampleData) - 1 $avSplit = StringSplit($avSampleData[$n], ",") $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]) Next $sQuery = 'SELECT User_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] & "," Else ExitLoop EndIf WEnd If StringRight($sMsg, 1) = "," Then $sMsg = StringTrimRight($sMsg, 1) $aRecords = StringSplit($sMsg, ",") Else MsgBox(16, "Error", "Error executing query: " & $sQuery) EndIf ; Shut it down _SQLite_Close($hDB) _SQLite_Shutdown() EndIf EndIf ; Debug check of data _ArrayDisplay($aRecords, "Debug: $aRecords") ;MsgBox(64, "Results", $sMsg) Dim $aButtons[$aRecords[0] + 1] ;Dim $aButtons[UBound($aRecords)] $aHeight = (($aRecords[0] + 1) * 25) + 60 ; 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 $aRecords[0] $aButtons[$x] = GUICtrlCreateButton($aRecords[$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 Didn't test any button functionality. Good luck with it. 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 Link to comment Share on other sites More sharing options...
John117 Posted July 24, 2007 Author Share Posted July 24, 2007 PsaltyDS -Thanks, This works just as needed. I change: aHeight = (($aRecords[0] + 1) * 25) + 60 to +100 to pickup single, and odd numbered users. (this was a prop with the second script before. Thanks for all the help Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now