Record not found problem

Post your Gambas programming questions here.
Post Reply
Bushbug
Posts: 6
Joined: Thursday 8th November 2018 7:01pm

Record not found problem

Post by Bushbug »

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.
User avatar
stevedee
Posts: 518
Joined: Monday 20th March 2017 6:06pm

Re: Record not found problem

Post by stevedee »

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?
Bushbug
Posts: 6
Joined: Thursday 8th November 2018 7:01pm

Re: Record not found problem

Post by Bushbug »

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
Bushbug
Posts: 6
Joined: Thursday 8th November 2018 7:01pm

Re: Record not found problem

Post by Bushbug »

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
User avatar
stevedee
Posts: 518
Joined: Monday 20th March 2017 6:06pm

Re: Record not found problem

Post by stevedee »

Bushbug wrote: Friday 9th November 2018 2:38pm ...Thank you for your interest and inspiration...
It was nothing...well done!
User avatar
gbWilly
Posts: 68
Joined: Friday 23rd September 2016 11:41am
Location: Netherlands
Contact:

Re: Record not found problem

Post by gbWilly »

Have you considered this:
If $rSQLData.Available Then
  'Do the stuff  you need to do when a key is found
Else
   'Set focus to the input
  Message.Error("Record not found! Please enter a valid ASL number")
  txtASLNo.setfocus
Endif
gbWilly
- Dutch translation for Gambas3
- Gambas wiki content contributer


... there is always a Catch if things go wrong!
User avatar
Quincunxian
Posts: 171
Joined: Sunday 25th June 2017 12:14am
Location: Western Australia

Re: Record not found problem

Post by Quincunxian »

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:
If (Not IsNull($rSQLData)) And $rSQLData.Available then 
{process data}
EndIf
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 ?
Cheers - Quin.
I code therefore I am
User avatar
Got2BeFree
Posts: 91
Joined: Saturday 26th November 2016 2:52am
Location: Lost

Re: Record not found problem

Post by Got2BeFree »

Quincunxian wrote: Sunday 18th August 2019 11:39pm 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:
If (Not IsNull($rSQLData)) And $rSQLData.Available then 
{process data}
EndIf
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...
iCount = hResData!Number
    If iCount > 0 Then
        ...
    Endif
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.
sholzy

I'm wondering around lost in the past, not knowing where the present is.
Post Reply