SQL select and spinner with multiple field

[stefanoa]

hi i need to display 2 field (surname, name) in spinner "text" contactsSpinner:

DBUtils.ExecuteSpinner(SQLEvents, "SELECT surname, name FROM contacts", Null, 0, contactsSpinner)
contactsSpinner_ItemClick(1, contactsSpinner.GetItem(0))

>>> result: "SMITH"

i've tried:

DBUtils.ExecuteSpinner(SQLEvents, "SELECT surname + ' ' + name FROM contacts", Null, 0, contactsSpinner)
contactsSpinner_ItemClick(1, contactsSpinner.GetItem(0))

>>> result: "0" (and not "SMITH JOHN")

help..

 

[Erel]

Try "SELECT surname || ' ' || name..."

 

[stefanoa]

Now it's ok!

perfect, I solved with:

DBUtils.ExecuteSpinner(SQLEvents, "SELECT surname || ' ' || name FROM contacts", Null, 0, contactsSpinner)

thanks!! :sign0098:

 

[cirollo]

how to get to work

Hello!

tried this:

DBUtils.ExecuteSpinner(SQL, "SELECT IdCli ||' '|| Ragsoc FROM Clienti where Iniziale= " & "'" & SpLettere.SelectedItem &"'"& "order by Ragsoc", Null, 0, SpRagsoc)

SpRagsoc_ItemClick(0, SpRagsoc.GetItem(0))

but when I have this istructions:

Sub SpRagsoc_ItemClick (Position As Int, Value As Object)
   Dim m As Map
   m = DBUtils.ExecuteMap(SQL, "SELECT IdCli, ragsoc, indirizzo, cap FROM Clienti WHERE IdCli = ?", _
      Array As String(value))
   If m = Null Then 'Null will return if there is no match
      TxtRagSoc.Text = ""
      TxtIndirizzo.Text = ""
   Else
      TxtRagsoc.Text = m.Get("ragsoc")
      TxtIndirizzo.Text = m.Get("indirizzo")
   End If
   'Get the tests for this specific student (currently it is all tests).
'   DBUtils.ExecuteSpinner(SQL, "SELECT test FROM Grades WHERE id = ?", _
'      Array As String(Value), 0, spnrTests)
'   spnrTests.SelectedIndex = 0
'   spnrTests_ItemClick(0, spnrTests.GetItem(0))
'   FindFailedTests(Value)
End Sub

I get the error on this: TxtRagsoc.Text = m.Get("ragsoc")

I would like to show two fields in spinner but using the 1st when I raise the Item_click

 

[Erel]

What is the error message?

 

[cirollo]

the error is

on the device:

an error has occurred in sub:clienti_spragsoc_itemclick (B4A line:59) txtragsoc.text=m.get("ragsoc")
java.lang.runtimeexception: object should first be initialized (map).
continue?

if i choose yes it continues but appears again when i choose a record from the spinner!

note that if i use only one field
DBUtils.ExecuteSpinner(SQL, "SELECT IdCli FROM Clienti where Iniziale= " & "'" & SpLettere.SelectedItem &"'"& "order by Ragsoc", Null, 0, SpRagsoc)
it works but i need to show the description field also in the spinner.

thanks

 

[Erel]

DBUtils.ExecuteMap returns an uninitialized Map if no results found.
You should change the test to:

If m.IsInitialized = False

 

[cirollo]

cannot understand

DBUtils.ExecuteMap returns an uninitialized Map if no results found.
You should change the test to:

If m.IsInitialized = False

sorry Erel but i don't understand....
in the spinner i can see the records.....it's when i click on one of these that the error is raised (or when the layout opens, assuming it is on the 1st record of the spinner)
so, where i have to put your istructions?

 

[cirollo]

works but

ok Erel, tried to do your suggestions,

If m.IsInitialized = False Then
TxtRagSoc.Text = ""
TxtIndirizzo.Text = ""
Else
TxtRagsoc.Text = m.Get("ragsoc")
TxtIndirizzo.Text = m.Get("indirizzo")
End If

it works but no value is returned with:

TxtRagsoc.Text = m.Get("ragsoc")
TxtIndirizzo.Text = m.Get("indirizzo")

I need to assign the values (i see these in the spinner!)

 

[Erel]

No value is returned because no value was found.

 

[cirollo]

yes, you're right

i discovered that the 2 fields where treated like a unique string, so no match in the db was found!

no way to select which field of the spinner use for matching?

ad es. spinner id+description

using id for sql select.