I am new to using Gambas and am thrilled with it. However, I have run into a problem. I have written a program that contains a textbox into which one enters a unique licence number  (ASLNo) eg. 00001. Once the textbox (txtASLNo.Text) loses focus a SQL statement looks for the record associated with the number in a table and loads the returned data into other textboxes etc.
My problem comes when a number is entered and no corresponding record can be found. I want a message to appear stating "record not found" and for the cursor then to return to the ASLNo textbox for a new number to be inserted. To do this I have used the following code:

Dim sSQL as String
Dim sASLNo as string
Dim $rSQLData as Result
sASLNo=txtASLNo.text

sSQL="Select * from Aircraft where ASLNo =" & "'" & sASLNo & "'"
$rSQLData = $hConn.Exec(sSQL)
If $rSQLData.index = -1 then
Message("Record not found")
txtASLNo.setfocus
EndIf

Of course there is more code before the above to test whether the number entered is 5 digits long and after the If-EndIf statement, if the record is found the rest of the code processes. All works well when a record is found but when no record is found, the "Record not found" message is generated but then, instead of the cursor going back to the ASLNo textbox the program goes to the CATCH and ERROR message at the end of the program and hangs the program.

Can anyone suggest a way to solve this problem.

 Hi Bushbug & welcome to the wonderful world of Gambas.

I can't see what the problem is from the code snippet you have posted.

Can you determine the error condition that you are getting by single stepping through your code?

Could you attach your zipped project so we can download and see the whole thing?

 Thank you for the prompt reply Stevedee.
Here is the full coding for the particular event. (txtASLNo_LostFocu())

Public Sub txtASLNo_LostFocus()

  Dim sASLNo As String
  Dim sSQLData As String
  Dim $rSQLData As Result
  Dim iLen As Integer
  Dim sAppName As String
  Dim sASLType As String
  Dim sOpsName As String
  Dim sBoxNo As String
  Dim sStreetNo As String
  Dim sStreetName As String
  Dim sTown As String
  Dim sContact As String
  Dim SContNo As String
  Dim sBase1 As String
  Dim sBase2 As String
  Dim sBase3 As String
  Dim sTrafficType As String
  Dim sArea As String
  Dim dIssue As String
  Dim sErrorResult As String
  Dim iValid As Integer
  Dim dExpire As String
  Dim dNoticeDt As Date
  Dim dRenewDt As Date
  Dim dProcDt As Date
  Dim bASLValid As Boolean
  Dim iIndex As Integer
  
  sASLNo = txtASLNo.Text
    
  If txtASLNo.text = "" Or Len(txtASLNo.text) <> 5 Then
  Message("You have not entered a valid ASL number!")
  iLen = Len(txtASLNo.Text)
  txtASLNo.Select(0, iLen)
  txtASLNo.SetFocus
  
  Else
  
  sSQLData = "Select *, ExpireDt, RenewDt, NoticeDt from ASLHolder,ASLDates Where ASLHolder.ASLNo = " & "'" & sASLNo & "'" & "And ASLDates.ASLNo =" & "'" & sASLNo & "'"
  
  $rSQLData = $hConn.Exec(sSQLData)
  
  iIndex = $rSQLData.Index
  
  If iIndex = -1 Then
    Message.Error("Record not found! Please enter a valid ASL number")
    txtASLNo.setfocus
  Endif
  
  sAppName = $rSQLData!HolderName
  sASLType = $rSQLData!ASLType
  sOpsName = $rSQLData!OpsName
  sBoxNo = $rSQLData!BoxNo
  sStreetNo = $rSQLData!StreetNo
  sStreetName = $rSQLData!StreetName
  sTown = $rSQLData!Town
  sContact = $rSQLData!ContactName
  sContNo = $rSQLData!ContactNo
  sTrafficType = $rSQLData!TrafficType
  sBase1 = $rSQLData!Base1
  sBase2 = $rSQLData!Base2
  sBase3 = $rSQLData!Base3
  sArea = $rSQLData!Area
  dIssue = $rSQLData!InitialDate
  dProcDt = $rSQLData!ProcDate
  iValid = $rSQLData!Validity
  bASLValid = $rSQLData!ASLValid
  
  txtAppName.text = sAppName
  cmbASLType.text = sASLType
  txtOpsName.text = sOpsName
  txtStName.Text = sStreetName
  txtBoxNo.text = sBoxNo
  txtStNo.text = sStreetNo
  txtTown.text = sTown
  txtContact.text = sContact
  txtConFone.text = sContNo
  txtTraffic.text = sTrafficType
  txtBase1.text = sBase1
  txtBase2.text = sBase2
  txtBase3.text = sBase3
  txtArea.text = sArea
  dtbIssue.Value = dIssue
  txtValid.text = iValid
  chkASLValid.Value = bASLValid
 
   dExpire = $rSQLData!ExpireDt
   dRenewDt = $rSQLData!RenewDt
   dNoticeDt = $rSQLData!NoticeDt
  
   dtbNoticeDt.Value = dNoticeDt
   dtbRenewalDt.Value = dRenewDt
   dtbExpire.Value = dExpire
   
   If dtbExpire.value > Date(Now) Then
      chkASLValid.value = True
      Else
       chkASLValid.value = False
       Message.Warning("ASL Invalid")
   Endif
   Catch

   Error(DConv(Error.Text))
   Endif
   End

The full project is here:
/home/bushbug/AirServiceLicences-0.0.30.tar.gz

It uses a SQLite3 database. Do you require me to post the DB with a few tables filled with sample data?

When running the program, I select the ASLAmend radio button on the Main Form. This opens a form named Amendments. I enter a number (ASLNo) and if the number is not 5 digits long it returns an error message and goes back to the ASLNo text box, selecting the entered text for replacement.

If the number is correct and represents previously saved records in the ASLHolder Table and ASLDates Table, the SQL query finds the record and places the returned data in the various textboxes and Datechoosers.

However, in the event that the number is correct in terms of length but does not represent a number previously saved in the said Tables, the SQL query returns an error message "Record not found! Please enter a valid ASL number" because the DataResult.Index is -1. Then, instead of going back to the ASLNo textbox the process just continues to then edn and throws out an error.

I hope that the above is clearer.
Kind regards,
Frank

 Hi Stevedee,
I have solved the problem by changing the: If DataResult.Index = -1 to <> -1 and placing the Else part after the process. It then works perfectly.
Thank you for your interest and inspiration.
Kind regards,
Frank

Bushbug said

…Thank you for your interest and inspiration…

It was nothing…well done!

Have you considered this:

There is one case where 'Available' is a bit ambiguous.

If the table exists but is empty ( there are no current records) , 'Available' returns a True but your '$rSQLData' is null as it read no records.
so:

I was thinking of asking if this can be changed in Gambas so that Available returns true if the table exists and does have at least one record of data but not sure if there are any other uses that I have not come across yet where the current implementation is still required.

Anybody have any thoughts on this ?

Quincunxian said

There is one case where 'Available' is a bit ambiguous.

If the table exists but is empty ( there are no current records) , 'Available' returns a True but your '$rSQLData' is null as it read no records.
so:

Code (gambas)

1. If (Not IsNull($rSQLData)) And $rSQLData.Available then
2. \{process data}
3. EndIf
4.  

I was thinking of asking if this can be changed in Gambas so that Available returns true if the table exists and does have at least one record of data but not sure if there are any other uses that I have not come across yet where the current implementation is still required.

Anybody have any thoughts on this ?

I work mainly with SQLite (easiest for my projects) and after Available, I test for rows before processing data like this…

It's been a long time since using MySQL so I can't remember if there is something similar to Number in SQLite.

I hadn't thought of using IsNull to test for rows. I'll have to play with that one.