If DELETE FROM removes unique RowID from Row

Databases
1

I successfully got the DELETE FROM to delete a Row but lost the RowID Number with it. Is there a trick to restore the missing RowID and reassign the RowID numbers in the Rows below the Deleted Row. Do I have to create an INSERT with a increment loop to reassign the RowID Numbers from the missing one. I don’t care if they get changed since I’m using a Popup Menu.

[code]
Dim d As New MessageDialog
Dim b As MessageDialogButton

RowID =PopUpMenu.ListIndex
d.ActionButton.Caption=“Ok”
d.CancelButton.Visible = True
d.Message=“Do You Want To Delete This Row?”

If RowID <> -1 Or PopUpMenu.Text <> “” Then
b=d.ShowModal
Select Case b
Case d.ActionButton
Dim sql As String
sql = "DELETE FROM MyTable WHERE ID = " + str( RowID )

  DB.SQLExecute(sql)
  
  If DB.Error Then
    MsgBox"Delete not successful!"
  Else
    MsgBox "Delete Successful"
    mFrtBrdFng.LoadFrtBrdPresets
  End If
Case d.AlternateActionButton
  b=d.CancelButton
End Select

Else
MsgBox"No Row Selected"
End If[/code]

2

It would be better to store the rowid in the RowTag and use that instead of the ListIndex.

3

What Tim said. What you’re asking to do is highly unusual.

4

Sorry if it’s not clear trying to communicate my problem. I’m a little tired too, maybe I have to take a break. It’s unusual because I have to create it in my own way if I can’t find a source example to do it a better way. The unique ID Column number gets deleted with the Row when I delete a record creating missing sequence number in the ID Column. That is why I asked is there a better way

I’m still trying to grasp RowID in the RowTag instead of listIndex. Tim do you mean

RowID =PopUpMenu.RowTag(PopUpMenu.ListIndex) //from doc example, instead of below
RowID =PopUpMenu.ListIndex

I goto take a break and come back to this
Thanks for offering your help

5

[quote=310502:@Jeffrey Cox]I’m still trying to grasp RowID in the RowTag instead of listIndex. Tim do you mean

RowID =PopUpMenu.RowTag(PopUpMenu.ListIndex) //from doc example, instead of below
RowID =PopUpMenu.ListIndex[/quote]

Yes, that’s what he means.

6

Using unique IDs in Databases is an important thing to identify entries.
To keep them unique, deleted IDs should never be used again.
Just let the db do that work. Add an column like “ID INTEGER PRIMARY KEY AUTOINCREMENT” to each table you are creating.
This column is incremented with each insert automatically. Rowid is set to this value.
When doing your Select, set listbox.rowtag to that id.

That is important, because of foreign keys, reinserting backups, etc. In that cases, reused ids could mix your data…

7

It’s supposed to do that. Unique ID’s should remain unique.

Yes, that’s exactly what I mean.

8

With the RowTag code example RowID integer returns 0 when selecting am 4 Item in the PopupMenu when debugging

9

It is very easy, just klick in the listbox:

https://xojo.io/5ad22815cb63

10

Are you setting the RowTag when you populate the menu?

11

Are you specifically selecting RowID in your SQLSelect statement? It isn’t included in “select * from …” You have to ask for it by name.

Select RowID, * from …

That said, you might consider using your own INTEGER PRIMARY KEY AUTOINCREMENT column, which is safer.

12

Hey Guys
Sorry, for the delay. I got sidetracked to go to work. RowID is a integer property for ID column it’s not a Table.

sql = "SELECT * FROM MyDBTable WHERE ID = " + str( RowID )

// Example of first 2 Columns of MyDBtable the 2nd column is the PopupMenu Items
ID    PopUpMnuPresetNm
0      Name1
1      Name2
2      Name3
13

So just do:

while not rs.eof
popupmenu.addrow(rs.field(“PopUpMnuPresetNm”).stringvalue)
popupmenu.rowtag(popupmenu.listcount-1) = rs.field("ID).integervalue
rs.movenext
wend

now all rows have a rowtag with db-field-value of ID