Android Question Need help with updating records using DBUtils

[Subatanx86]

First I apologize I'm just learning B4A and I'm not a programmer. In any event I'm using a mobile computer with an integrated barcode scanner to scan a military ID badge. The scanner is parsing out the useful information First Name, Last Name, and Rank. The user would select an event and scan the badge then a button is clicked to add that data and the current Date and Time.

My problem comes with checking out. They would like to scan the badge again in a similar screen except the button on this screen would just edit the record but adding the current time.

They also want to be able to check out everyone signed in with one click of the button. For this all records would need the current time added.

 

[Erel]

You can update records with DBUtils.UpdateRecord.

 

[Subatanx86]

Thank you Erel!

Dim WhereFields As Map
WhereFields.Initialize
WhereFields.Put("First", edtFirstOut.Text)
WhereFields.Put("Last", edtLastOut.Text)
WhereFields.Put("Rank", edtRankOut.Text)
DBUtils.UpdateRecord(SQL, "Soldiers", "TimeOut", DateTime.Time(DateTime.Now), WhereFields)

The above code worked for updating a single record. Can I update all records the same way? I thought I read in one of the posts that UpgradeRecord could not doe multiple records.

 

[OliverA]

I thought I read in one of the posts that UpgradeRecord could not doe multiple records.

If multiple entries can be identified by a common field, UpdateRecord will update multiple records. For example, the "Soldiers" table would have an "Event" column you could do something like this

Dim WhereFields As Map
WhereFields.Initialize
WhereFields.Put("Event", edtEvent.Text)
DBUtils.UpdateRecord(SQL, "Soldiers", "TimeOut", DateTime.Time(DateTime.Now), WhereFields)

and then every record in "Soldiers" that has the same value in the "Event" column will be updated.

 

[Subatanx86]

If multiple entries can be identified by a common field, UpdateRecord will update multiple records. For example, the "Soldiers" table would have an "Event" column you could do something like this

Dim WhereFields As Map
WhereFields.Initialize
WhereFields.Put("Event", edtEvent.Text)
DBUtils.UpdateRecord(SQL, "Soldiers", "TimeOut", DateTime.Time(DateTime.Now), WhereFields)

and then every record in "Soldiers" that has the same value in the "Event" column will be updated.

Ok I can give that a shot. I was trying to do that with the "TimeOut" field being "Null" then just updating the DateTime.Time(DateTime.Now)

The log says it updates, but it still shows as null.

I will check your solution.

 

[OliverA]

"TimeOut" field being "Null" t

The issue here is that internally, UpdateRecord creates a parameterized query** and that may interfere will Null values. You may need to play with

Dim WhereFields As Map
WhereFields.Initialize
WhereFields.Put("TimeOut", Null)
DBUtils.UpdateRecord(SQL, "Soldiers", "TimeOut", DateTime.Time(DateTime.Now), WhereFields)

or

Dim WhereFields As Map
WhereFields.Initialize
WhereFields.Put("TimeOut", "Null")
DBUtils.UpdateRecord(SQL, "Soldiers", "TimeOut", DateTime.Time(DateTime.Now), WhereFields)

or

Dim WhereFields As Map
WhereFields.Initialize
WhereFields.Put("TimeOut", "'Null'")
DBUtils.UpdateRecord(SQL, "Soldiers", "TimeOut", DateTime.Time(DateTime.Now), WhereFields)

And see if one of them works. Another way would be to use a Date that you know is invalid as a type of unscheduled flag. For example use 1/1/1970 as a default entry. When you see that date, you know that a person has not checked out yet.

**Please note, I'm not knocking parameterized queries! They are a good thing. Null's are special though and UpdateRecord's internal query generation may interfere with it (may).

 

[Subatanx86]

OliverA, that is a good idea using an invalid date. Since there are no overnight activities I ended up using the Date field captured during the check in process.

Dim WhereFields As Map
WhereFields.Initialize
WhereFields.Put("Date", DateTime.Date(DateTime.Now))
DBUtils.UpdateRecord(SQL, "Soldiers", "TimeOut", DateTime.Time(DateTime.Now), WhereFields)

 

[Erel]

Please use [code]code here...[/code] tags when posting code.

Tip:

Dim WhereFields As Map
WhereFields.Initialize
WhereFields.Put("Event", edtEvent.Text)

Equivalent to:

Dim WhereFields As Map = CreateMap("Event": edtEvent.Text)