Sign in to follow this  
Followers 0
Zoldex

SQLite and Listview problem. Help needed!

3 posts in this topic

#1 ·  Posted (edited)

Hi everybody!

I'm trying to use SQLite for the first time into an app with a listview.

I've already succesful managed to create a database, insert and update items, also on the listview! :sweating:

My problem is about deleting records.

I mean i can easily delete a record from a database with

_SQLite_Exec(-1, "DELETE FROM Database WHERE KEY = " & $selected_row_on_listview & ";")

and also from listview with

_GUICtrlListView_DeleteItemsSelected($listview)

but the difference is that if I have 5 records into the Database and I delete 1 in the middle (say KEY=3) I will have KEY values 0,1,2,4

BUT Listview updates its rows 0,1,2,3 so I couldn't use $selected_row_on_listview anymore, since if I click on row 3 it would try to delete a missing record.

Sorry if my question sounds silly but I'm really stuck now.

Thanks for any hints and forgive my poor english.

Also, any good example with SQLite and listview would be greatly appreciated.

Edited by Zoldex

Share this post


Link to post
Share on other sites

#2 ·  Posted (edited)

Zoldex,

I read the data from the SQLite query into an array. Then I have a function that loops through the array to populate the listview.

When I delete a selected row, I delete that row number from the array (and from the database) and then re-call the function to update the listview.

That way the array always has the rows in the same order as the listview, but in the delete statement to the database I can pass the KEY that is held in one of the array columns.

Another method would be to hold the database KEY value in a hidden column in the ListView.

Hope that gives you an idea.

NiVZ

Edited by NiVZ

Share this post


Link to post
Share on other sites

NiVZ,

thanks for your answer.

If I understand what you say, I should read again the whole table from database to update listview, each time I delete a record?

That's what I thought when I had the problem but don't know what would happen with very large databases.

I mean that it would become a slow process if I need to delete several rows...

Another method would be to hold the database KEY value in a hidden column in the ListView.

My KEY value is not displayed on the listview and is used only as a progressive counter, that would serve me to identify the row in the database.

Anyways, even if not displayed on listview, when I delete the row from the database it's really gone forever and creates a "hole" in the counter.

The listview, instead, updates itself all the index rows...

I would need a way to tell SQLite to "reorganize" the rows when a KEY in the middle is missing... like Listview does.

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

  • Similar Content

    • AdamUL
      By AdamUL
      I have been testing AutoIt 3.3.14.3 with SQLite, and the Help File examples.  The examples that I have tested are throwing errors or not doing anything.  I have sqlite3.dll, sqlite3_x64.dll, and sqlite3.exe in the directories with the testing script.  I am copying the examples directly from the help file into a test script for testing with no edits.  The _SQLite_GetTable2d example is returning an error ("Library used incorrectly") with each _SQLite_Exec command.  The _SQLite_FastEncode example returns an empty dialog box.  The _SQLite_Exec example only prints out the SQLite version, and nothing else in the SciTE console.  Currently, I'm still searching for what is causing this issue.  I'm on Windows 7 Enterprise 64-bit.  Is anyone else having this issue?  
       
      Adam
       
    • Burgs
      By Burgs
      Greetings,
        I have SQLite setup within my AutoIT program...I'm trying to accomplish what should be a relatively simple task.  I want to be able to return an array of 'table' names for an established database...I believe this might be possible using the '_SQLite_SQLiteExe' command...since it seems to be able to access SQLite schemas...?  The ".tables" command is one of them...if I am not mistaken that command returns a list of all table names in the active database.
        I am attempting the following:
      #include <SQLite.au3> #include <SQLite.dll.au3> Global $hDb, $sIn, $sOut ... $sIn = ".tables" & @CRLF _SQLite_SQLiteExe($hDb, $sIn, $sOut) if @error == 0 Then ;Show Table (using SQLite3.dll) Else if @error == 2 Then ConsoleWrite("ERROR: Sqlite3.exe file not found" & @CRLF) Else ConsoleWrite("ERROR: @error=" & @error & " when calling _SQLite_SQLiteExe" & @CRLF) EndIf ;@error is "2"...OR NOT... EndIf ;@error is "0"...OR NOT... ...   The error being thrown is "ERROR: Sqlite3.exe file not found" ...
        Am I required to have the Sqlite3.exe installed in my directory (i.e. @ScriptsDir)...???  I do not have it in there at present because I did not believe it was necessary with the 'include' calls to "SQLite.au3" and "SQLite.dll.au3"...any advice appreciated.  Thanks in advance. 
      Regards
       
       
       
       
       
       
       
    • FrancescoDiMuro
      By FrancescoDiMuro
      Good evening guys
      I am working on a little project, in which I have to retrieve 8000+ rows of data from a table, from a SQLite database, to populate a combobox 
      This is what I tried 'til now, but it still takes about 12 seconds to populate the combobox.
      _SQLite_Exec($objDatabase, "BEGIN TRANSACTION;") If _SQLite_GetTable($objDatabase, "SELECT DISTINCT Comune FROM LISTA_COMUNI;", $arrRisultatoQuery, $intRighe, $intColonne) = $SQLITE_OK Then For $i = 2 To UBound($arrRisultatoQuery) - 1 If $i < UBound($arrRisultatoQuery) - 1 Then GUICtrlSetData($cbo_ComuneNascita, $arrRisultatoQuery[$i] & "|") Else GUICtrlSetData($cbo_ComuneNascita, $arrRisultatoQuery[$i]) EndIf Next _SQLite_Exec($objDatabase, "COMMIT;") Are there any other solution to retrieve 8000+ records from a SQLite database?
      Thank you very much  
    • FrancescoDiMuro
      By FrancescoDiMuro
      Good morning community!
      I am working on a script which read from a text file ( .txt ) and should import all the content in a SQLite3 DB, in order to execute some queries that should be difficult to execute on a text file.
      So, I was looking for something very very fast, because the file could be very large ( I don't know exaclty how much can became big, but I know a lot of rows, it's a log file ... )
      I found the "Import method", but I don't know If I can implement it in a query ( @jchd, it's your turn!  )
      Do you know some methods that I can implement in my script to have a very very fast import of thousands and thousands rows in a SQLite3 DB?
      Thanks a lot
      Francesco
    • FrancescoDiMuro
      By FrancescoDiMuro
      Good morning guys
      I was trying to use again SQLite in AutoIt, and I've again an issue: I can't startup SQLite...
      Can you please show me the way to set the enviornment for a SQLIte usage in AutoIt? Step by step, I'll follow your suggestion(s).

      Thanks for everyone will reply!

      Francesco