Jump to content

Recommended Posts

Posted (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 by Hatcheda
Posted

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

Posted

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

Posted

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:

$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!

:)

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
Posted

And in an attempt to reclaim my sanity, I copied the line over the one in script2. It didnt work! :P 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! :)

Posted

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"

:P

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
Posted (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 by Hatcheda
Posted (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! :P

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

$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 by PsaltyDS
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
Posted

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!

Posted

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!

:)

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
Posted (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 :P)

Edited by Hatcheda
Posted

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.

:)

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
Posted

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!

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...