Android Question Strange behaviour

[Pelky]

I don't quite know how to explain this - Perhaps if I just show results and you can help.

'---- * Setup Select

    Dim SelectFrom As Int
    Dim SelectTo     As Int

    Log("DateFromShow.Text = "&DateFromShow.Text)
    Log("DateToShow.Text      = "&DateToShow.Text)
    SelectFrom  = DateFromShow.Text.Replace(".","")
    SelectTo   = DateToShow.Text.Replace(".","")
    Log("SelectFrom = "&SelectFrom)
    Log("SelectTo = "&SelectTo)
    
'----*
    Cursor = DB.ExecQuery("Select * from LogRecord WHERE  LogWeekNo='"&WeekNo&"' ORDER BY LogDate ASC")
'    Cursor = DB.ExecQuery("Select * from LogRecord WHERE  LogDate=>'"&SelectFrom&"' and LogDate=<'"&SelectTo&"' ORDER BY LogDate ASC")

If I run this code as shown I get the log showing:-

DateFromShow.Text = 06.11.2023
DateToShow.Text.. = 12.11.2023
SelectFrom = 6112023
SelectTo = 12112023

If I switch the comment to the first Cursor and use second Cursor selection I get:-

DateFromShow.Text = 06.11.2023
DateToShow.Text.. = 12.11.2023
SelectFrom = 6112023
SelectTo = 1.2112023E7

You will notice that the SelectTo is different - Includes '.' and 'E7'

No other changes made to code only the switching of the comment line

I am sure I have done something crazy but cant see it. I am hoping that you good people could assist.

 

[aeric]

My comment:

Use String instead.

Dim SelectFrom As String
Dim SelectTo As String

 

[DonManfred]

Other note:
- You should always use Parametrized Queries

 

[Mahares]

I am hoping that you good people could assist.

Your code should be like this:

  cursor = DB.ExecQuery2("SELECT * FROM LogRecord WHERE  LogDate BETWEEN ? AND ? ORDER BY LogDate", Array As String(DateFromShow.Text, DateToShow.Text))

Use BETWEEN Do not use >= and <=. You do not need the variables. You do not need to replace the dots.

 

[Pelky]

Thank You For The Response - Much appreciated. Given that nifty work around, can you work out why the replace worked in one instance and
not the other - very strange

Once again thank you

 

[aeric]

You need to understand the difference between the data types and the concept. B4X and other programming languages like Java will convert the data type when you assign from one to another data types.

 

[Mahares]

Given that nifty work around, can you work out why the replace worked in one instance and
not the other - very strange

If you are talking about my code, it is not a workaround. It is the proper way to write the syntax of the query in your situation.
There is no way to compare your first code to your second code, because you do not specify what the variable WeekNo is in your first query.
While you are at it, please change the title of the thread to something more meaningful.
Added a couple of minutes later: If you want to know, post a simple project depicting the problem and attach it to the forum. You will get your answer and see that there is nothing strange happening.

 

[DonManfred]

It is the proper way to write the syntax of the query in your situation

But it only works if the datecolumn is of type DATE. With varchar you´ll not get that kind of sorting working (given that the dates are stored like in #1 shown (probably in varchar))

 

[Mahares]

But it only works if the datecolumn is of type DATE

If he stores the dates as TEXT in the SQLite table, the sorting will be good even if you have dates like: 12.11.2023 or 06.11.2023

 

[Pelky]

Once again thank you so very much for your assistance... it is working properly now

 

[Pelky]

My comment:

Use String instead.

Dim SelectFrom As String
Dim SelectTo As String

Thank you.. I have followed your advice and changed the select format..