SQLite, how to delete correctly a record?

[ciginfo]

Hello,
With SQLite, impossible to delete a record without disturbing the table created.
Records from my table appear in a listview but when I delete a recording appli fails when I reopen the table to watch a row of the list.
Can someone look at the small joined program and tell me what I need to change.
Thank you very much.

 

[Mahares]

See code below. I commented the line that was giving you trouble and replaced it with the line below it.

Sub Listing_ItemClick (Position As Int, Value As Object)
Dim Cursor2 As Cursor
   Cursor2 = SQL1.ExecQuery("SELECT ID, Champ1, Champ2, Champ3 FROM mytable")
   
   
'   Cursor2.Position = Value-1
   Cursor2.Position = Position  '8/3/12  by Mahares

You should be able to delete any record without problems.
Bonne Journee.

 

[mc73]

See code below. I commented the line that was giving you trouble and replaced it with the line below it.

Sub Listing_ItemClick (Position As Int, Value As Object)
Dim Cursor2 As Cursor
    Cursor2 = SQL1.ExecQuery("SELECT ID, Champ1, Champ2, Champ3 FROM mytable")
    
    
'    Cursor2.Position = Value-1
    Cursor2.Position = Position  '8/3/12  by Mahares

You should be able to delete any record without problems.
Bonne Journee.

careful with this, my friend, Mahares. Even if this in case it can be correct, since I guess our friend is using serial Ids, in general, he should use the value object returned, from what I saw in his code.

 

[Mahares]

@mc73: I do not know if you had a chance to run his code to see why using the value yielded the error in his execution. Please confirm if you have an alternate or a more secure solution. It will be refreshing to know.

 

[mc73]

@mc73: I do not know if you had a chance to run his code to see why using the value yielded the error in his execution. Please confirm if you have an alternate or a more secure solution. It will be refreshing to know.

Sure. The following replacements have to be performed

1.

Sub Listing_ItemClick (Position As Int, Value As Object)
    Dim cursor2 As Cursor 
'replacements -----    
cursor2 = SQL1.ExecQuery2("SELECT ID, Champ1, Champ2, Champ3 FROM mytable WHERE ID=?",Array As String (Value) )
    
    cursor2.Position =0
'------------------

and 2.

Sub BtnSupprimer_Click
    If LblTexte.Text = "" Then
 ToastMessageShow("Pas de ligne selectionnée", False)
 Else
    Dim Answ As Int
    Answ = Msgbox2("Voulez vous vraiment supprimer cet enregistrement","A T T E N T I O N","Yes","","No",Null)
    If Answ = DialogResponse.POSITIVE Then
'replacement -----            
SQL1.ExecNonQuery2("DELETE FROM mytable WHERE ID = ?", Array As Object(T_Index))
'------------
    End If
    AFFICHE_LISTE
 End If
End Sub

 

[ciginfo]

Fantastic. The two solutions work fine. But if I have understood, i have to take mc73's solution, more complexe but more secure.
I don't understand the possible problem with mahares's solution.
Thank you both.

 

[mc73]

Fantastic. The two solutions work fine. But if I have understood, i have to take mc73's solution, more complexe but more secure.
I don't understand the possible problem with mahares's solution.
Thank you both.

Let me explain the problem to both of you. Suppose we have 3 items, with ids 1,2,3, ok? Then, let's delete item 2. Mahares' solution will work fine. Then, when we load again the table we will have 2 items, with ids 1 and 3. The position in the list, will be 1,2. So, if we try to delete item 2 (in the list), we will not find item with id 3, if we choose its position.

EDIT: Oops, nope! Now that I see Mahares' code better, and since you'll be always setting a clone of the whole table to the list, it will also work well, without a problem. Anyway, if you have a really large database, then quering the whole table while you're just searching for a simple record, would take a bit more time, so still I think that you should query by ID as I wrote earlier.