Android Question Need help with updating records using DBUtils

Subatanx86 · Mar 13, 2019

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 · Mar 14, 2019

You can update records with DBUtils.UpdateRecord.

Subatanx86 · Mar 14, 2019

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 · Mar 14, 2019

Subatanx86 said:
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

B4X:

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 · Mar 14, 2019

OliverA said:
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

B4X:

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 · Mar 14, 2019

Subatanx86 said:
"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

B4X:

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

or

B4X:

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

or

B4X:

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 · Mar 14, 2019

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 · Mar 15, 2019

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

Tip:

B4X:

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

Equivalent to:

B4X:

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

Android Question Need help with updating records using DBUtils

Subatanx86

Member

Erel

B4X founder

Subatanx86

Member

OliverA

Expert

Subatanx86

Member

OliverA

Expert

Subatanx86

Member

Erel

B4X founder

Similar Threads

Android Question Need help with updating records using DBUtils

Member

B4X founder

Member

Expert

Member

Expert

Member

B4X founder

Similar Threads

Privacy & Transparency

Privacy & Transparency