Database insert and update

Getting Started
1

Hello, I am writing my first code maintenance program in Xojo. I could not locate a CRUD example so I am taking my best shot. Here is code I am using to insert a new record, and to update an existing record. This seems like a lot of essentially duplicate coding to me, so my question is - is there an easier and better way to do this?

dim status as String = “”
try
if NewRec = True then
Dim row As New DatabaseRecord
row.Column(“company”) = Company
row.Column(“location”)=ML_CODE.Text
row.Column(“bhrrate1”) = ML_B01.Text
row.Column(“bhrrate2”) = ML_B02.Text
row.Column(“bhrrate3”) = ML_B03.Text
row.Column(“bhrrate4”) = ML_B04.Text
row.Column(“bhrrate5”) = ML_B05.Text
row.Column(“bhrrate6”) = ML_B06.Text
row.Column(“bhrrate7”) = ML_B07.Text
row.Column(“bhrrate8”) = ML_B08.Text
row.Column(“bhrrate9”) = ML_B09.Text
row.Column(“bhrrate10”) = ML_B10.Text
row.Column(“mhrrate1”) = ML_M01.Text
row.Column(“mhrrate2”) = ML_M02.Text
row.Column(“mhrrate3”) = ML_M03.Text
row.Column(“mhrrate4”) = ML_M04.Text
row.Column(“mhrrate5”) = ML_M05.Text
row.Column(“mhrrate6”) = ML_M06.Text
row.Column(“mhrrate7”) = ML_M07.Text
row.Column(“mhrrate8”) = ML_M08.Text
row.Column(“mhrrate9”) = ML_M09.Text
row.Column(“mhrrate10”) = ML_M10.Text
db.InsertRecord(“helperrates”, row)
if db.Error then
status = "Unable to save this information. Error code " + str(db.ErrorCode) + " " + db.ErrorMessage
msgbox(status)
end if
else
rs.Edit
rs.field(“bhrrate1”).StringValue=ML_B01.Text
rs.field(“bhrrate2”).StringValue=ML_B02.Text
rs.field(“bhrrate3”).StringValue=ML_B03.Text
rs.field(“bhrrate4”).StringValue=ML_B04.Text
rs.field(“bhrrate5”).StringValue=ML_B05.Text
rs.field(“bhrrate6”).StringValue=ML_B06.Text
rs.field(“bhrrate7”).StringValue=ML_B07.Text
rs.field(“bhrrate8”).StringValue=ML_B08.Text
rs.field(“bhrrate9”).StringValue=ML_B09.Text
rs.field(“bhrrate10”).StringValue=ML_B10.Text
rs.field(“mhrrate1”).StringValue=ML_M01.Text
rs.field(“mhrrate2”).StringValue=ML_M02.Text
rs.field(“mhrrate3”).StringValue=ML_M03.Text
rs.field(“mhrrate4”).StringValue=ML_M04.Text
rs.field(“mhrrate5”).StringValue=ML_M05.Text
rs.field(“mhrrate6”).StringValue=ML_M06.Text
rs.field(“mhrrate7”).StringValue=ML_M07.Text
rs.field(“mhrrate8”).StringValue=ML_M08.Text
rs.field(“mhrrate9”).StringValue=ML_M09.Text
rs.field(“mhrrate10”).StringValue=ML_M10.Text
rs.Update
end if

catch err
status = "Unable to save this information. Error code " + str(err.ErrorNumber) + " " + err.Message
msgbox(status)
end try

if status = “” then
db.Commit
if db.Error then
status = "Unable to save this information. Error code " + str(db.ErrorCode) + " " + db.ErrorMessage
msgbox(status)
end if
AskCode
end if

2

Often to prevent write the same code for insert/update, people will just do a basic insert of a blank record, and put all of the actual code in the updating. This way you can update existing records, or you can create and blank record and then update it to make a new record.

3

Hi Tom. I could do that, but that means more trips to the database. In a simple code maintenance program that might not be much, but in a batch update the performance hit would probably be noticeable.

4

Right, that is the drawback of that method, so it’s up to you. Alternatively you could create a class that will update to the database based on introspection, so the insert and update commands are automatically generated for you based on the class’s variables. I’ve done something similar recently and it works well.

5

The following works and it is a bit less coding than my original example. It may not be the best way to do this and I will do some more thinking to see if I can make it less cumbersome.

dim i as Integer
dim data(22) as string
data(1) = Company
data(2) = ML_CODE.Text
data(3) = ML_B01.Text
data(4) = ML_B02.Text
data(5) = ML_B03.Text
data(6) =ML_B04.Text
data(7) =ML_B05.Text
data(8) =ML_B06.Text
data(9) =ML_B07.Text
data(10) = ML_B08.Text
data(11) = ML_B09.Text
data(12) = ML_B10.Text
data(13) = ML_M01.Text
data(14) = ML_M02.Text
data(15) = ML_M03.Text
data(16) = ML_M04.Text
data(17) = ML_M05.Text
data(18) = ML_M06.Text
data(19) = ML_M07.Text
data(20) = ML_M08.Text
data(21) = ML_M09.Text
data(22) = ML_M10.Text
if NewRec = False then
  rs.edit
  for i = 3 to rs.FieldCount
    rs.idxfield(i).StringValue = data(i)
  next i
  rs.Update
else
  Dim row As New DatabaseRecord
  row.Column("company") = Company
  row.Column("location")=ML_CODE.Text
  for i = 3 to rs.FieldCount
    row.Column(rs.idxfield(i).Name) = data(i)
  next i
  db.InsertRecord("helperrates", row)
  if db.Error then
    status = "Unable to save this information.  Error code " + str(db.ErrorCode) + " " + db.ErrorMessage
    msgbox(status)
  end if
end if
6

You can also create a control set so you have an array of text fields that you can loop through so that you don’t need to individually list each field.

7

Thanks for that idea. The user interface guide devotes only one brief page to control sets so it is difficult to visualize how that would work. I’ll see if I can dig up some examples to help me understand it.

8

Basically you just select the control set you’d like each field to be a part of. Set up the indexes for each, and then when loading/saving use a for loop to put/get data.

Just make sure you either make the indexes sequential, or you check that the field<>nil incase you have a missing index.

9

Thanks again Tom. I checked out control sets and decided not to go that route. It would have worked well for this simple program, but for more complex ones with lots of fields, tab pages, validation, etc. I felt I had to go a different direction. I decided to write an external method to write a record to a database and to let that method do the record set edit or insert a new row.

In my maintenance program I copy the fields to an array, and then pass it to my external write method.

dim data(22) as string
data(1) = Company
data(2) = ML_CODE.Text
data(3) = ML_B01.Text
…
data(22) = ML_M10.Text
WriteDB(db, rs, NewRec, data(), “helperrates”, true)
if db.Error then
dim status as String = "Unable to save this information. Error code " + str(db.ErrorCode) + " " + db.ErrorMessage
msgbox(status)
return
end if

My external method:

Sub WriteDB(byref db as Database, byref rs as RecordSet, NewRec as Boolean, data() as String, tablename as String,
optional commit as Boolean = False)

dim i as Integer
if NewRec = False then
rs.edit
for i = 1 to rs.FieldCount
rs.idxfield(i).StringValue = data(i)
next i
rs.Update
else
Dim row As New DatabaseRecord
for i = 1 to rs.FieldCount
row.Column(rs.idxfield(i).Name) = data(i)
next i
db.InsertRecord(tablename, row)
end if

if not(db.Error) and commit = True then
db.Commit
end if
End Sub