Record not found problem

Post Reply
Bushbug
Posts: 3
Joined: Thursday 08th November 2018 7:01pm

Record not found problem

Post by Bushbug » Thursday 08th November 2018 7:56pm

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.

stevedee
Posts: 85
Joined: Monday 20th March 2017 6:06pm

Re: Record not found problem

Post by stevedee » Friday 09th November 2018 10:10am

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: 3
Joined: Thursday 08th November 2018 7:01pm

Re: Record not found problem

Post by Bushbug » Friday 09th November 2018 11:55am

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: 3
Joined: Thursday 08th November 2018 7:01pm

Re: Record not found problem

Post by Bushbug » Friday 09th November 2018 2:38pm

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

stevedee
Posts: 85
Joined: Monday 20th March 2017 6:06pm

Re: Record not found problem

Post by stevedee » Friday 09th November 2018 5:16pm

Bushbug wrote:
Friday 09th November 2018 2:38pm
...Thank you for your interest and inspiration...
It was nothing...well done!

Post Reply