Check exsistance before add to SQL

[John117]

I have the following code courtesy of PsaltyDS and would like to make a change that I need help with.

I would like to have the ;add users section query to see of the $avSampleData username exists as a record before adding a new one. If it does exist, I would like it to update instead of add new record. If it doesnt exist, then it would add as normal.

The match would be based on USERID. All other fields would be updated with new information.

Can anyone help with this? I can see from this how to query, and add, but not update. Any update demo should work.

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

;users are added here from the Source Section.

; 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

;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[1] & ", " & $aRow[2] & ", " & $aRow[3] & ", " & $aRow[4] &@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

Edited July 27, 2007 by Hatcheda

[PsaltyDS]

Try this change:

; Add users
        For $n = 0 To UBound($avSampleData) - 1
            $avSplit = StringSplit($avSampleData[$n], ",")
            $sQuery = "SELECT * FROM Users WHERE User_Name='" & $avSplit[1] & "'"
            If _SQLite_Exec(-1, $sQuery) = $SQLITE_OK Then
                MsgBox(16, "Error", "User_Name already exists: " & $avSplit[1])
            Else
                $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

Almost everything is done by running a Query. Try to Select something, and if it succeeds (returns $SQLITE_OK) it existed already.

Start you query with 'UPDATE' to change values, etc.

The format of the various commands you can put in $sQuery before doing _SQLite_Exec() is on this page: SQL As Understood By SQLite

P.S. Your code is getting somewhat annoying to read. How about dropping those long blocks of white space, and running Tidy (Ctrl-t) on it in SciTE before posting? Thanks.

[John117]

I was thinking the blocks made it easier for new users to read

[PsaltyDS]

I was thinking the blocks made it easier for new users to read

Wrong.

[John117]

Um-k

It does bock duplicate entries for matching USERID

For updating I have tried this . . .

CODE

$sQuery = "SELECT * FROM Users WHERE User_Name='" & $avSplit[1] & "'" ;new section

If _SQLite_Exec(-1, $sQuery) = $SQLITE_OK Then ;New Section

$sQuery = "Update Users(LogOn) VALUES('" & $avSplit[8] & "') WHERE User_Name='" & $avSplit[1] & "'"

;MsgBox(16, "Error", "User_Name already exists: " & $avSplit[1]) ;New Section

Else

not sure that is what you mean by update.

[PsaltyDS]

Um-k

It does bock duplicate entries for matching USERID

For updating I have tried this . . .

CODE

$sQuery = "SELECT * FROM Users WHERE User_Name='" & $avSplit[1] & "'" ;new section

If _SQLite_Exec(-1, $sQuery) = $SQLITE_OK Then ;New Section

$sQuery = "Update Users(LogOn) VALUES('" & $avSplit[8] & "') WHERE User_Name='" & $avSplit[1] & "'"

;MsgBox(16, "Error", "User_Name already exists: " & $avSplit[1]) ;New Section

Else

not sure that is what you mean by update.

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] & "'"

To learn AutoIt you have to spend time with the AutoIt help file. To learn SQLite, you have to spend time on the SQLite help site.

[John117]

Thanks for the links, I used the SQl site. The end result is very close to mine when you replace the new value with the avSplit.

Neither of the below are changing the record.

$sQuery = "UPDATE Department='" & $avSplit[3] & "' FROM Users WHERE User_Name='" & $avSplit[1] & "'"

$sQuery = "UPDATE Department='Marketing' FROM Users WHERE User_Name='" & $avSplit[1] & "'"

Edit: FYI for anyone reading, it seams you first have to comment out the UPDATE section back to the origional post and run the code. This creates the database with some info. Then you can use the update section after you manually change the value of department under $avSampleData[1] but I am yet to create an actual database change after runing the code again. It will just show the initial add. (meaning update work = )

Edited July 25, 2007 by Hatcheda

[John117]

@all

Please see the following post for a working answer provided by PsaltyDS

http://www.autoitscript.com/forum/index.ph...mp;#entry378588