Android Question Need help with updating records using DBUtils

Discussion in 'Android Questions' started by Subatanx86, Mar 13, 2019 at 7:03 PM.

  1. Subatanx86

    Subatanx86 Member Licensed User

    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.
     
  2. Erel

    Erel Administrator Staff Member Licensed User

    You can update records with DBUtils.UpdateRecord.
     
  3. Subatanx86

    Subatanx86 Member Licensed User

    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.
     
  4. OliverA

    OliverA Well-Known Member Licensed User

    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
    Code:
    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.
     
  5. Subatanx86

    Subatanx86 Member Licensed User

    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.
     
  6. OliverA

    OliverA Well-Known Member Licensed User

    The issue here is that internally, UpdateRecord creates a parameterized query** and that may interfere will Null values. You may need to play with
    Code:
    Dim WhereFields As Map
    WhereFields.Initialize
    WhereFields.Put(
    "TimeOut"Null)
    DBUtils.UpdateRecord(
    SQL"Soldiers""TimeOut"DateTime.Time(DateTime.Now), WhereFields)
    or
    Code:
    Dim WhereFields As Map
    WhereFields.Initialize
    WhereFields.Put(
    "TimeOut""Null")
    DBUtils.UpdateRecord(
    SQL"Soldiers""TimeOut"DateTime.Time(DateTime.Now), WhereFields)
    or
    Code:
    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).
     
  7. Subatanx86

    Subatanx86 Member Licensed User

    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)
     
  8. Erel

    Erel Administrator Staff Member Licensed User

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

    Tip:
    Code:
    Dim WhereFields As Map
    WhereFields.Initialize
    WhereFields.Put(
    "Event", edtEvent.Text)
    Equivalent to:
    Code:
    Dim WhereFields As Map = CreateMap("Event": edtEvent.Text)
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice