How do you populate values from a query into multiple text boxes?

rleiman

Well-Known Member
Licensed User
Longtime User
Hi Everyone,

I have about 10 text boxes I would like to populate with a query from the database but don't know how to do it in a single statement.

In Oracle / plsql I would use something like this using 3 table columns as an example:

B4X:
TheFirstName Varchar2(15);
TheLastName Varchar2(15);
TheId Number;

TheIdValue = 550

SELECT Id, FirstName, LastName
  INTO TheId, TheFirstName, TheLastName
  FROM MyTable
 WHERE Id = TheIdValue;

Can you show me the coding I need to do the same thing in Basic4Android?

Thanks.
 
Last edited:

rleiman

Well-Known Member
Licensed User
Longtime User
I found out from the DBUtils example how to do it but I get a "Null pointer exception" on the line with "Get". I also used a Toast to make sure that intCurrentId actually did have a value.

Here's the code I used:

B4X:
Sub ListViewPeopleEventHandler_ItemClick (Position As Int, Value As Object)

   ' Update the details area.
   '-------------------------
   Dim valuesFromTheListView() As String
   valuesFromTheListView = Value
   
   intCurrentId = valuesFromTheListView(0)
   
   Dim mapOfTableFields As Map
   mapOfTableFields = DBUtils.ExecuteMap(SQL, "SELECT Id, FirstName, LastName FROM PeopleToVisit WHERE id = ?", _
      Array As String(intCurrentId))
      
   ToastMessageShow(intCurrentId, False)   
      
        ' I get the error on this next line.
        '-------------------------------------
   EditTextFirstName.Text = mapOfTableFields.Get("FirstName")
   
        EditTextFirstName.RequestFocus
   EditTextFirstName.SelectAll
   EditTextFirstName.Color = Colors.Cyan

   EditTextLastName.Text = mapOfTableFields.Get("LastName")
   EditTextLastName.RequestFocus
   EditTextLastName.Color = Colors.Cyan

   tableMode = "Edit"
   Activity.Title = "Maintenance - Result Of Visit *** EDIT ***"
End Sub
 
Upvote 0

bees

Member
Licensed User
Longtime User
B4X:
TheFirstName Varchar2(15);
TheLastName Varchar2(15);
TheId Number;

TheIdValue = 550

SELECT Id, FirstName, LastName
  INTO TheId, TheFirstName, TheLastName
  FROM MyTable
 WHERE Id = TheIdValue;

I have not used the DBUitils, but do the following

Dim oConnect as Sql
Dim oRecordSet as Cursor
Dim cQuery as String
Dim cLastName as String

cQuery = "Select Id, FirstName,Lastname From MyTable Where Id = " & TheIdValue

'Think Sqlite has an 'AS' Clause so you can try this
cQuery = "Select Id, FirstName as TheFirstname,Lastname as theLastName From MyTable Where Id = " & TheIdValue

'Database should not have NULL Values but in case they do
cQuery = "Select Id, IfNull(FirstName,'') as TheFirstname,IfNull(Lastname,'') as theLastName From MyTable Where Id = " & TheIdValue

oConnect.Initialize("yourdir","yourdb",False)
oRecordSet = oConnect.ExecQuery(cQuery)
If oRecordSet.RowCount > 0 Then
oRecordSet.Position = 0
cLastName = oRecordSet.Get("YourColomnName")
End If

HTH
 
Upvote 0

devlei

Active Member
Licensed User
Longtime User
I agree with Bees you should familiarize yourself with Sqlite - you will benefit greatly in the long run.

To add to what Bees suggested you put data from the recordset/cursor into your textboxes with:

B4X:
TextBox1.Text = oRecordSet.GetString("YourColomnName1")
TextBox2.Text = oRecordSet.GetString("YourColomnName2")
....

If data not a string use relevant GetLong, GetDouble etc.
 
Upvote 0

rleiman

Well-Known Member
Licensed User
Longtime User
Thanks for all the replies and help.

Your code worked but I'm not sure why the DBUtils way did not work. At least for now I will use the way you taught me to get data into the text boxes and it will help me learn SQLite as well.

Is there a beginner B4A SQLite tutorial?
 
Upvote 0

devlei

Active Member
Licensed User
Longtime User
Yes, the Beginners Guide (Ch 12.4, P143) will help you greatly. It also has a reference to the W3Schools website, which is a great reference while using sql.

SQL Introduction
 
Upvote 0

timo

Active Member
Licensed User
Longtime User
I found out from the DBUtils example how to do it but I get a "Null pointer exception" on the line with "Get". I also used a Toast to make sure that intCurrentId actually did have a value.

Here's the code I used:

B4X:
...
mapOfTableFields = DBUtils.ExecuteMap(SQL, "SELECT Id, FirstName, LastName FROM PeopleToVisit WHERE id = ?", _
      Array As String(intCurrentId))
...   
      
        ' I get the error on this next line.
        '-------------------------------------
...

Not shure, but I think that in SQLite 'WHERE' requiers 'LIKE', not '='
 
Upvote 0
Top