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
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
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…
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
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