When updating to prepared statements I’ve run into a hitch. I’ve been using the LastRowID to get an internal number I can use across tables. It used to work just fine with the following code:
var AddressRS as new DatabaseRow //Update Information in _StudentAddress AddressRS.Column("InternalStudentID") = Session.pCurrentStudentID AddressRS.Column("Address") = StudentBasicInfoContainer1.HomeAddress1Field.text AddressRS.Column("Address2") = StudentBasicInfoContainer1.HomeAddress2Field.text AddressRS.Column("City") = StudentBasicInfoContainer1.CityField.text AddressRS.Column("State") = StudentBasicInfoContainer1.StateField.text AddressRS.Column("PostalCode") = StudentBasicInfoContainer1.ZipField.text AddressRS.Column("Plus4") = StudentBasicInfoContainer1.PlusFourField.text try Session.db.AddRow("_StudentAddress", AddressRS) end try
Now I’m trying to update everything and future-proof my code a bit, but my updated code no longer works. Everything binds except Session.pCurrentStudentID, so the majority of the information goes into the database, but the common key isn’t there.
My assumption is that, since pCurrentStudentID is an Int64 (because the program I’m using to maintain my database was spitting out huge auto incremented numbers) and the SQLite column is an Integer that it doesn’t want to play nice. But the large number is coming from SQLite in the first place. Xojo is the one that didn’t want to accept the number as an integer.
Here is the almost-working code:
session.pCurrentInternalStudentID=Session.db.LastRowID MessageBox (session.pCurrentInternalStudentID.tostring) //I only used this to make sure the value was being properly set. It is. try //_STUDENTADDRESSTABLE var sql as string = "INSERT INTO _StudentAddress (InternalStudentID, AddressType, Address, City, State, PostalCode, Plus4, Country, Address2) VALUES (?,?,?,?,?,?,?,?,?)" var ps as SQLitePreparedStatement = session.db.prepare(sql) ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)//This is where it doesn't bind properly. ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER) ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT) ps.BindType(3, SQLitePreparedStatement.SQLITE_TEXT) ps.BindType(4, SQLitePreparedStatement.SQLITE_TEXT) ps.BindType(5, SQLitePreparedStatement.SQLITE_TEXT) ps.BindType(6, SQLitePreparedStatement.SQLITE_TEXT) ps.BindType(7, SQLitePreparedStatement.SQLITE_TEXT) ps.BindType(8, SQLitePreparedStatement.SQLITE_TEXT) ps.bind(0, session.pCurrentStudentID)// I'm assuming it's because session.pCurrentInternalStudentID is an Int64, but I can't figure out how to convert it. ps.bind(1, 1) ps.bind(2, StudentBasicInfoContainer1.HomeAddress1Field.Value) ps.bind(3, StudentBasicInfoContainer1.CityField.Value) ps.bind(4, StudentBasicInfoContainer1.StatePopup.SelectedRowValue) ps.bind(5, StudentBasicInfoContainer1.ZipField.Value) ps.bind(6, StudentBasicInfoContainer1.PlusFourField.Value) ps.bind(7, "United States") ps.bind(8, StudentBasicInfoContainer1.HomeAddress2Field.Value) ps.SQLExecute end try
Any help would be greatly appreciated.