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

[rleiman]

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:

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.

 

[rleiman]

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:

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

 

[bees]

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

 

[devlei]

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:

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

....

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

 

[rleiman]

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?

 

[devlei]

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

 

[timo]

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:

...
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 '='

 

[timo]

Sory, that was for strings.