Jump to content
Sign in to follow this  
Followers 0
John117

Any example of updating SQLite

By John117, in AutoIt General Help and Support

Recommended Posts

John117    1

John117
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

Share this post

Link to post
Share on other sites

John117    1

John117
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

Share this post

Link to post
Share on other sites

John117    1

John117
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

Share this post

Link to post
Share on other sites

PsaltyDS    27

PsaltyDS
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

Share this post

Link to post
Share on other sites

John117    1

John117
Posted

I SWEAR I tried that already! :)

Testing new code now!

Share this post

Link to post
Share on other sites

John117    1

John117
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! :)

Share this post

Link to post
Share on other sites

PsaltyDS    27

PsaltyDS
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

Share this post

Link to post
Share on other sites

John117    1

John117
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

Share this post

Link to post
Share on other sites

PsaltyDS    27

PsaltyDS
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

Share this post

Link to post
Share on other sites

John117    1

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

Share this post

Link to post
Share on other sites

PsaltyDS    27

PsaltyDS
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

Share this post

Link to post
Share on other sites

John117    1

John117
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

Share this post

Link to post
Share on other sites

PsaltyDS    27

PsaltyDS
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

Share this post

Link to post
Share on other sites

John117    1

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

Share this post

Link to post
Share on other sites

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
Sign in to follow this  
Followers 0
Go To Topic Listing AutoIt General Help and Support
×