I have a program that requires retrieving a date from SQLite3 and placing the value into a datebox. When I run the Select statement "SELECT PDate FROM TableName WHERE Registration = "PJA NA" in SQLite itself (Execute SQL), it returns the correct date as shown in the specific field. However, If I put the same SQL Statement in my Gambas3 program to retrieve the PDate from the Table it returns a completely incorrect date including a time.
Here is the actual statement: "Select PDate, VMake, VMiles from Vehicles Where Vreg = " & "'" & sVeh & "'"
The date returned should be 12/04/2019 but what is actually returned is 20/04/1912 00:00:00
Can anyone shed some light on the problem please?
Retrieving dates from SQLIte
- Quincunxian
- Posts: 174
- Joined: Sunday 25th June 2017 12:14am
- Location: Western Australia
Re: Retrieving dates from SQLIte
Hi BushBug,
I'll make the assumption that you are running the latest Gambas 3 version.
What date 'Mode' do you have set for the control ?
Can you show the code where you are applying the returned value to the control ?
MyDataBox.Value = ?
If you are using a variable for this, how is it declared ?
ie: passing the value back from a subroutine or function.
or
Are you doing this directly after the query ?
MyDateBox.Value = MyResult!PDATE
Minor thing and nothing to do with the issue you are having.
Always put your WHERE clauses in brackets : Where(Vreg = " & "'" & sVeh & "'")
where you have more than one comparison - get into the habit now as with more
complex queries it can lead to some perplexing errors especially when you are doing multiple joins.
I'll make the assumption that you are running the latest Gambas 3 version.
What date 'Mode' do you have set for the control ?
Can you show the code where you are applying the returned value to the control ?
MyDataBox.Value = ?
If you are using a variable for this, how is it declared ?
ie: passing the value back from a subroutine or function.
or
Are you doing this directly after the query ?
MyDateBox.Value = MyResult!PDATE
Minor thing and nothing to do with the issue you are having.
Always put your WHERE clauses in brackets : Where(Vreg = " & "'" & sVeh & "'")
where you have more than one comparison - get into the habit now as with more
complex queries it can lead to some perplexing errors especially when you are doing multiple joins.
Cheers - Quin.
I code therefore I am
I code therefore I am
Re: Retrieving dates from SQLIte
Thank you very much for your response Quincunxian. In reply to your questions:
1. The mode of the Datebox is set to Dateonly.
2. I am using a variable "dPurchase" which is declared as a Date. (Dim dPurchase As Date)
3. I am applying the returned value directly from the query. (dPurchase = $rData1!PDate)
4. This is the code for applying the value to the control.
sSQL3 = "Select PDate, VMake, VMiles from Vehicles Where Vreg = " & "'" & sVeh & "'"
dtbPurDt.ReadOnly = False
dtbPurDt.Value = dPurchase
It puts a value of 20/04/1912 in the control instead of 12/04/2019.
_______________________________________________________________________________________________________
Interestingly, in another part of the program (different form) I have a similar query and it works just fine.
sSQL = "Select TransDate, Odometer from VehicleData Where VehType =" & "'" & sReg & "'" & "Order by Odometer Desc"
$sRes = MODMain.$Con.Exec(sSQL)
dDate = $sRes!TransDate
dtbTransDate.ReadOnly = False
lblTransDate.text = "Last transaction date"
dtbTransDate.Value = dDate
1. The mode of the Datebox is set to Dateonly.
2. I am using a variable "dPurchase" which is declared as a Date. (Dim dPurchase As Date)
3. I am applying the returned value directly from the query. (dPurchase = $rData1!PDate)
4. This is the code for applying the value to the control.
sSQL3 = "Select PDate, VMake, VMiles from Vehicles Where Vreg = " & "'" & sVeh & "'"
dtbPurDt.ReadOnly = False
dtbPurDt.Value = dPurchase
It puts a value of 20/04/1912 in the control instead of 12/04/2019.
_______________________________________________________________________________________________________
Interestingly, in another part of the program (different form) I have a similar query and it works just fine.
sSQL = "Select TransDate, Odometer from VehicleData Where VehType =" & "'" & sReg & "'" & "Order by Odometer Desc"
$sRes = MODMain.$Con.Exec(sSQL)
dDate = $sRes!TransDate
dtbTransDate.ReadOnly = False
lblTransDate.text = "Last transaction date"
dtbTransDate.Value = dDate
- Quincunxian
- Posts: 174
- Joined: Sunday 25th June 2017 12:14am
- Location: Western Australia
Re: Retrieving dates from SQLIte
Can you copy the second query where it works, to populate the datebox that has the error ?
My thinking is around trying to determine if the error is from the underlying data in the SQL database.
Everything else, from what I can see is ok.
My thinking is around trying to determine if the error is from the underlying data in the SQL database.
Everything else, from what I can see is ok.
Cheers - Quin.
I code therefore I am
I code therefore I am
Re: Retrieving dates from SQLIte
Good morning Quincunxian. Thank you for pointing me in the right direction. I deleted some records in the database table and re-saved them. Then the program worked perfectly. Obviously some date data in the specific table had somehow become corrupted.
Your assistance is greatly appreciated. Now I just want to try and "publish" the program so that I can send it to a friend to use. Let's see if I can do it successfully.
Best regards.
Your assistance is greatly appreciated. Now I just want to try and "publish" the program so that I can send it to a friend to use. Let's see if I can do it successfully.
Best regards.