AddRow to Multiple Joined Tables

Databases
1

Okay, so this works as far as putting the information I need into two separate tables (and it needs to be multiple tables, because there can be multiple addresses for each student). Currently when I make a record in _StudentInformation it adds an internal ID that I use to do my joins, which works great for pulling information, BUT, I have no way at the moment to add a record that I can pull back out, because, while the ID gets created for _StudentInformation I have no way of knowing what that ID is for _StudentAddress.

Is there a way to do this? (It’s not the “StudentID” field - That’s something that the user can enter and won’t be reliable for joins).

var StudentRS as new DatabaseRow
var AddressRS as new DatabaseRow

//Update Information in _StudentInformation table
StudentRS.Column("StudentID")=StudentIDField.text
StudentRS.Column("GradYear") = GradYearField.text
StudentRS.Column("FirstName")= FirstNameField.text
StudentRS.Column("MiddleInitial")= MiddleInitialField.text
StudentRS.Column("LastName")= LastNameField.text
StudentRS.Column("DOB")= DOBField.text

try
  Session.db.AddRow("_StudentInformation", StudentRS)  
end try


//Update Information in _StudentAddress table

AddressRS.Column("Address") = HomeAddress1Field.text
AddressRS.Column("Address2") = HomeAddress2Field.text
AddressRS.Column("City") = CityField.text
AddressRS.Column("State") = StateField.text
AddressRS.Column("PostalCode") = ZipField.text
AddressRS.Column("Plus4") = PlusFourField.text

try
  Session.db.AddRow("_StudentAddress", AddressRS)
end try
2

This is where something like ActiveRecord and ARGen can really come in handy. Events like AfterCreate allow you to chain together related items like this. The plain-vanilla way would be to use SQLite’s LastRowID function after inserting the Student record to get the ID for that record.

3

Thank you for that, I will check into it (although I have a current budget of $0, LOL).

If I go the plain-vanilla way, just so I’m clear on this:
Insert into _StudentInformation, new SQL query using LastRowID for that table, insert into _StudentAddress using the generated ID?

4

if it is a sqlite database you can use lastrowid,
for other databases it depends there are ways to have a lastrowid too using the right sql command.

5

[quote=495642:@Eric Kringle]If I go the plain-vanilla way, just so I’m clear on this:
Insert into _StudentInformation, new SQL query using LastRowID for that table, insert into _StudentAddress using the generated ID?[/quote]
You won’t write the query, you would just use your SQLiteDatabase object:

dim iStudentID as Integer
try
  Session.db.AddRow("_StudentInformation", StudentRS)
  iStudentID = Session.db.LastRowID
end try
6

Perfect, thank you for your help! I’ll give this a shot.