Changing database row after user input.....

[DT2000]

New and in need of advice and a push in the right direction.

I'll try to explain what I am attempting to do....

I have been trying many different ways to accomplish my goal but have not figured out how to make this work yet.

My database contains sport player information. Once the program is run the user inputs a players jersey number or their name into the string gadget and the fields are populated. This part works fine.

Once the fields are populated a user can enter another players name or number and it populates the new data without problems.

However I also have a "Next" and "Previous" button available for use once an initial look up has been populated. The issue I am having is that once the initial data is populated in the fields and the user then uses the "Next" or "Prev" button then program starts to populate the fields from the first entry RecNo 1 in the database table, and not from the current players number column.

I am trying to write the code to go to the next or previous players number value and not the RowID. Of course the players jersey numbers are as an example 1, 2, 6, 10, 15, 3, 18.... where they do not follow a strict sequential pattern. And I cannot use the RowID for increment or decrement because during a season there are deletions and additions of players during trades and acquisitions. So a player #6 maybe deleted from the current RowID and weeks later a new player with the same jersey number entered, but it will have a new RowID at the end of the table, and there is no way to move the player into the correct position in the table.
So I am looking for a way to write the procedure to get the value of the the jersey number, then increment or decrement to the next known value... which is not always an increment by the value of 1 as I mentioned.

Any help and hints would be helpful.

Thanks in advance.

[netmaestro]

I was going to make a suggestion or two but then I started playing with it and oh what the hell. The first code traverses by what order the records went into the database, the second traverses by jersey #:

Code: Select all

UseSQLiteDatabase()
db = OpenDatabase(0, ":memory:", "", "", #PB_Database_SQLite)
If db
  sql$ = "Create table records (id integer primary key, jersey integer, name text)"
  If DatabaseUpdate(0, sql$)
    sql$ = "insert into records (jersey, name) values ('1', 'Davis')"
    DatabaseUpdate(0, sql$)
    sql$ = "insert into records (jersey, name) values ('6', 'Evans')"
    DatabaseUpdate(0, sql$)
    sql$ = "insert into records (jersey, name) values ('11', 'Fallon')"
    DatabaseUpdate(0, sql$)
    sql$ = "insert into records (jersey, name) values ('3', 'Green')"
    DatabaseUpdate(0, sql$)
    sql$ = "insert into records (jersey, name) values ('5', 'Humboldt')"
    DatabaseUpdate(0, sql$)
    sql$ = "insert into records (jersey, name) values ('14', 'Jennings')"
    DatabaseUpdate(0, sql$)
    sql$ = "insert into records (jersey, name) values ('16', 'Kellog')"
    DatabaseUpdate(0, sql$)
  EndIf
EndIf

OpenWindow(0,0,0,320,120,"",#PB_Window_ScreenCentered|#PB_Window_SystemMenu)

TextGadget(0, 100,20,60,20,"Name:")
TextGadget(1, 100,50,60,20,"Jersey:")
TextGadget(2, 160,20,200,20,"")
TextGadget(3, 160,50,200,20,"")
ButtonGadget(4, 60,90,80,20,"Search Jersey:")
StringGadget(5, 140,90,60,20,"")
ButtonGadget(6, 200,90,20,20,"<")
ButtonGadget(7, 220,90,20,20,">")
SetActiveGadget(5)

currentrecord = 0

Repeat
  ev=WaitWindowEvent()
  Select ev
    Case #PB_Event_Gadget
      Select EventGadget()
        Case 4
          sql$ = "select * from records where jersey='"+GetGadgetText(5)+"'"
          If DatabaseQuery(0, sql$)
            FirstDatabaseRow(0)
            currentrecord = GetDatabaseLong(0,0)
            SetGadgetText(2, GetDatabaseString(0,2))
            SetGadgetText(3, GetDatabaseString(0,1))
            FinishDatabaseQuery(0)
          EndIf
          
        Case 6
          sql$ = "select * from records where id<'"+Str(currentrecord)+"' order by -id limit 1"
          If DatabaseQuery(0, sql$)
            FirstDatabaseRow(0)
            thisrecord = GetDatabaseLong(0,0)
            If thisrecord
              currentrecord = thisrecord
              SetGadgetText(2, GetDatabaseString(0,2))
              SetGadgetText(3, GetDatabaseString(0,1))
            EndIf
            FinishDatabaseQuery(0)       
          EndIf
          
        Case 7
          sql$ = "select * from records where id>'"+Str(currentrecord)+"' order by id limit 1"
          If DatabaseQuery(0, sql$)
            FirstDatabaseRow(0)
            thisrecord = GetDatabaseLong(0,0)
            If thisrecord
              currentrecord = thisrecord
              SetGadgetText(2, GetDatabaseString(0,2))
              SetGadgetText(3, GetDatabaseString(0,1))
            EndIf
            FinishDatabaseQuery(0)       
          EndIf
      EndSelect
  EndSelect
  
Until ev=#PB_Event_CloseWindow

I'm using an autoincrement id field for the order but you can use some other column for the order and arrange it any way you like, this code will traverse it up and down in that order. For example, if what you really want is to order by jersey number (I really can't tell from your description) then use that for gadgets 6 and 7's code. Here is the code to traverse by jersey #:

Code: Select all

UseSQLiteDatabase()
db = OpenDatabase(0, ":memory:", "", "", #PB_Database_SQLite)
If db
  sql$ = "Create table records (id integer primary key, jersey integer, name text)"
  If DatabaseUpdate(0, sql$)
    sql$ = "insert into records (jersey, name) values ('1', 'Davis')"
    DatabaseUpdate(0, sql$)
    sql$ = "insert into records (jersey, name) values ('6', 'Evans')"
    DatabaseUpdate(0, sql$)
    sql$ = "insert into records (jersey, name) values ('11', 'Fallon')"
    DatabaseUpdate(0, sql$)
    sql$ = "insert into records (jersey, name) values ('3', 'Green')"
    DatabaseUpdate(0, sql$)
    sql$ = "insert into records (jersey, name) values ('5', 'Humboldt')"
    DatabaseUpdate(0, sql$)
    sql$ = "insert into records (jersey, name) values ('14', 'Jennings')"
    DatabaseUpdate(0, sql$)
    sql$ = "insert into records (jersey, name) values ('16', 'Kellog')"
    DatabaseUpdate(0, sql$)
  EndIf
EndIf

OpenWindow(0,0,0,320,120,"",#PB_Window_ScreenCentered|#PB_Window_SystemMenu)

TextGadget(0, 100,20,60,20,"Name:")
TextGadget(1, 100,50,60,20,"Jersey:")
TextGadget(2, 160,20,200,20,"")
TextGadget(3, 160,50,200,20,"")
ButtonGadget(4, 60,90,80,20,"Search Jersey:")
StringGadget(5, 140,90,60,20,"")
ButtonGadget(6, 200,90,20,20,"<")
ButtonGadget(7, 220,90,20,20,">")
SetActiveGadget(5)

currentrecord = 0

Repeat
  ev=WaitWindowEvent()
  Select ev
    Case #PB_Event_Gadget
      Select EventGadget()
        Case 4
          sql$ = "select * from records where jersey='"+GetGadgetText(5)+"'"
          If DatabaseQuery(0, sql$)
            FirstDatabaseRow(0)
            currentrecord = GetDatabaseLong(0,1)
            SetGadgetText(2, GetDatabaseString(0,2))
            SetGadgetText(3, GetDatabaseString(0,1))
            FinishDatabaseQuery(0)
          EndIf
          
        Case 6
          sql$ = "select * from records where jersey<'"+Str(currentrecord)+"' order by -jersey limit 1"
          If DatabaseQuery(0, sql$)
            FirstDatabaseRow(0)
            thisrecord = GetDatabaseLong(0,1)
            If thisrecord
              currentrecord = thisrecord
              SetGadgetText(2, GetDatabaseString(0,2))
              SetGadgetText(3, GetDatabaseString(0,1))
            EndIf
            FinishDatabaseQuery(0)       
          EndIf
          
        Case 7
          sql$ = "select * from records where jersey>'"+Str(currentrecord)+"' order by jersey limit 1"
          If DatabaseQuery(0, sql$)
            FirstDatabaseRow(0)
            thisrecord = GetDatabaseLong(0,1)
            If thisrecord
              currentrecord = thisrecord
              SetGadgetText(2, GetDatabaseString(0,2))
              SetGadgetText(3, GetDatabaseString(0,1))
            EndIf
            FinishDatabaseQuery(0)       
          EndIf
      EndSelect
  EndSelect
  
Until ev=#PB_Event_CloseWindow