Trouble with DTPicker and SQL query few gets

[jobsilva]

Hello:

I have two DTPicker controls on a form.
I select the dates where I have data and I only get three or four data.
I obtain this data from a native table of a field called fec_vta type Date()

My query the following:

STORE Thisform.Ole_vtas_i.oBJECT.Value TO fec1 && DTPicker 1 Initial date
STORE Thisform.Ole_vtas_f.obJECT.Value TO fec2 && DTPicker 2 Final date
fec1 = TTOD(fec1)
fec2 = TTOD(fec2)

SELECT a_vtas.n_vta,fec_vta,Art,cant,precu,vend,tot_vta,v_tarj FROM a_vtas ;
WHERE fec_vta Between fec1 AND fec2 INTO CURSOR lcCursor_v READWRITE

SELECT lcCursor_v
IF _tally <> 0
Thisform.list1.RowSource = 'lcCursor_v'
Thisform.list1.RowSourceType = 2
ELSE
MESSAGEBOX("Does not exist data to show with selected dates.",0+64+4096,"Warning!!!")
Thisform.Ole_vtas_i.oBJECT.Value = DATE()-180
Thisform.Ole_vtas_f.obJECT.Value = DATE()
ENDIF

What could I have wrong?

 

[Rajesh Karunakaran]

Dear Jobsilva,

If you simply run the query in command window, do you see all expected records?

Eg:

SELECT a_vtas.n_vta,fec_vta,Art,cant,precu,vend,tot_vta,v_tarj FROM a_vtas ;
WHERE BETWEEN(fec_vta, DATE(year1,month1,date1), DATE(year2,month2,date2))

In above query, replace year1,month1,date1 and year2,month2,date2 with your corresponding date values.

Rajesh


Rajesh

 

[Mike Lewis]

Could you clarify your question. What exactly is not working? Is the SELECT failing to find the correct records? Or are the OLE controls failing to display the correct dates?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

A classic case for using the debugger to find out what's wrong.
Set a breakpoint at the first STORE command.

Then see what actually gets into fec1 and fec2.

If that's right, are there fields fec1 and/or fec2 that differ from the variables fec1 and fec2? You can see the variable values in the Locals window of the debugger. If fields of that name exist, you'll see them by doing ? fec1, fec2 from the command window. If such fields exist and their values differ from the variables, the query will pick up the field values instead of the variable values, and that lets it query what you don't expect. And last not least, even if all that's okay, there could simply be no data in the date range.

You didn't tell us whether the code causes any error message or what's wrong, as Mike also said already. So it's really hard to help you without knowing what goes wrong at all.

Chriss

 

[jobsilva]

Thanks, sorry for delay;

Rajesh Karunakaran, I replace the code year1,month1,date1 and year2,month2,date2 with your corresponding date values, and still is the result.

Mike Lewis, The OLE control working fine, I get in fec1 {20/7/2023} and {31/7/2023} in control 2 with DATE format, but when I run Select I get non-existent values, for example in n_vta I get the number 70 and this value does not exist.
And only get 3 records buy really are 16.

Chris Miller, I use debugger exactly in fec1 buy don't show any error, the querry work fine, but the result is wrong.
fec1 and fec2 I get them from OLE controls with DATE format with time, buy I use:

fec1 = TTOD(fec1)
fec2 = TTOD(fec2)

To extract only DATE, the field is fec_vta with DATE format.
The date range is correct

The truth already drives me half crazy.

The strange thing is that after looking here and there, changing code, it suddenly works correctly, but the next day it gives incorrect values ​​again.

 

[Mike Lewis]

when I run Select I get non-existent values, for example in n_vta I get the number 70 and this value does not exist.

Just to be completely clear about this ....

You are saying that the underlying table (a_vtas) does NOT contain 70 in the n_vta field; but after running the query, lcCursor_v DOES contain 70 in tht field.

Is that correct?

If so, let's ignore the datepicker for the moment and focus on why the query is not working as expected.

The only explantion I can think of for the behaviour you are seeing is that the value (70 in this case) is present in n_vtas but the relevant record has been deleted, and you have SET DELETED ON. Or, alternatively, you have a filter on n_vtas which is excluding the record contianing the 70. In those cases, SELECT will return the hidden record to the cursor. So you will see the 70 in the result set, but not in the underlying table.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

in n_vta I get the number 70 and this value does not exist.

Mike points out the only way that's possible, he just got the ON/OFF wrong. If you SET DELETED OFF, which means you turn off the feature of VFP to automatically suppress records marked as deleted, a query will also select deleted records and put them into a result undeleted.

People, even experts, tend to confuse the ON/OFF meaning to turn deleted records ON/OFF, but that's not the meaning of the setting. It's about whther to take the deletion mark into account (ON) or disregard it (OFF), which means the OFF setting treats all records as undeleted.

Application code should always have SET DELETED ON, to actually take into account the deletion mark of records to mean a deletion, so records marked deleted are not fetched from DBFs, no matter if with a BROWSE or in SQL queries. Otherwise, SQL won't invent values not in a dbf, that's impossible. So being in there deleted would explain it. You have another problem, though.

You say you expect 16 records but only get 3. Well, the whole debate on DELETED would only increase the count of result records, not ever reduce it. What Mike also already mentioned, SET FILTER, will only increase the amount of records fetched by SQL, too, as SQL disregards the SET FILTER and only takes into account the filtering by the WHERE clause.

So there's something else going wrong. I don't see what it could be, the only way to have less records is actually less records fulfilling the where condition. So did you really put in the right condition? When you verify what you expect, do you take into account the year? If SQL only finds 3 records in the date range, there are only 3 records in that range, I don't see how it can fail that condition.

Chriss

PS: Do you have an index on the date field? How about reindexing that? Do you in general use IDX indexes instead of TAGs of a CDX inde file? Then you constantly risk the IDX index to not contain all data indexed, because IDX files only get updated with data changes, when they are explicitly opened together with a DBF. Use CDX instead, always and only.

The point about that is that rushmore optimization may only fetch 3 records by index optimizations, as the index does not have the 13 other records. Records not in the index won't make it into a result set, if an index is used to determine the result records.

This could explain it, but I actually believe you fetch from something else than you look into to verify or you miss the exact range.

 

[Chris Miller]

As far as I get it the DTPicker is no issue, you get the right dates, do you? Look out for the explicit year you get into fec1 and fec2, eventually. it's very easy to overlook you query the wrong year.

Chriss

 

[Chris Miller]

One more reason for less records in a result than you expect is that you have new records buffered and not yet committed and finally stored in the DBF file. Notice, SQL always takes a new workarea to reopen the DBF (or DBFs, if there are joins) so you query from the DBF file, while the SQL query executes. So you don't query buffered records. Not sure, if that plays a role at all, but worth mentioning too.

Chriss

 

[jobsilva]

Hello;

Mike Lewis, Is right!!!
The a_vtas table does not have the number 70 in the field and after the query this number magically appears.
I also verified that the table did not have delete filters.
Even if I enter the start and end date by hand, I get the same result.

Chris Miller, curiously, I get the same behavior if I try to make a Select Top 10.
It only shows me the same 3 records.

The DTPicker is working fine.

I'm mad with this.

 

[Chris Miller]

If I manually query our table I get 84 records with

SELECT a_vtas.n_vta,fec_vta,Art,cant,precu,vend,tot_vta,v_tarj FROM a_vtas ;
WHERE fec_vta Between Date(2023,07,20) and Date(2023,07,31) INTO CURSOR lcCursor_v READWRITE

And n_vta does not include 70.

By the way, your date constants are wrong:

Ambiguous date/datetime constant. Use the format:
{^yyyy-mm-dd[,][hh[:mm[:ss]] [a|p]]}

So when you want the range from 20th of July 2023 to 31st of July 2023 use
{^2023-07-20} and {^2023-07-32}

That can't be the topic of your form error, as you there use variables instead of date constants anyway. So let's look into your forms.

The code you posted is not in your form. In the Aceptar button has:

STORE Thisform.Ole_vtas_i.oBJECT.Value TO fec1
STORE Thisform.Ole_vtas_f.obJECT.Value TO fec2

b1 = VARTYPE(fec1)

fec1 = DTOC(fec1)
fec2 = DTOC(fec2)
...

This will gracefully work, even though f2c1 and fec2 are datetimes, not dates.
I get the same 85 records, if I pick the date range and "completo". Your result also depends on that and the code you posted is not to be found in the form rep_vta_fecha.scx
In the top 10 form you don't SET CENTURY ON. That makes 2023 to 23 and that converts back to 1923 or 2123 or perhaps even the year 23.

Your completely inconsistent in your coding, get this straight. First of all, don't convert dates with DTOC and then back to CTOD just do as you posted, convert the datetime from the DTPicker to date with TTOD(). In the top 10 form you also do that. But then you continue with a DTOC conversion, that you invert with CTOD() in your query.

Program consistently, actually do as you posted, convert with TTOD and then use that in the query clause fec_vta Between fec1 AND fec2.

And then look at exactly what you have there. With other options you ave additional conditions, that shrink the result again, of course. And that'll differ from your expected result. Fantastic. You can't be helped, if you don't see it for yourself.


Chriss

 

[Mike Lewis]

A good way to solve this kind of problem is to build up to it, one step at a time.

So, start by simply doing [tt]SELECT a_vtas.n_vta FROM a_vtas[/tt] on its own. As there is no INTO clause, it will open a Browse windowe. Look there to see if the 70 appears.

Then add the other fields to the SELECT; look again for the 70.

Next add the INTO CURSOR clause. And then the WHERE clause.

By building it up gradually in this way, you should be able to identify the point at which the problem occurs.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

One more thing: Your form doesn't open the a_vtas.dbf, I manually opened it before starting the forms.

If you don't explicitly open the a_vtas.dbf file you want to query, you might query another DBF file. Just being in a different current directory (set with CD or SET DEFAULT) you might query another a_vtas.dbf file, like an older backup of the DBF with less data for July 2023. Anything can go wrong and the inconsistent coding you have doesn't make this stable and reliable, too.

Chriss

 

[mjcmkrsr]

Hi JobSilva,

Below the code I tested with your top_10.scx - and it worked

LOCAL fec1 as Date, fec2 as Date

*!*	The two properties below have been set for readability - I guess you have them set in your settings

SET CENTURY ON
SET DATE DMY

STORE Thisform.dt1.oBJECT.Value TO fec1
STORE Thisform.dt2.obJECT.Value TO fec2

SELECT TOP 10 a_vtas.art, a_vtas.vend, fec_vta, ; 
	SUM(a_vtas.cant) ;
	FROM a_vtas ;
	WHERE FEC_vta BETWEEN fec1 AND fec2 ;
	GROUP BY 1, 2, 3 ;
	ORDER BY 4 DESC ;
	INTO CURSOR lcCursor
  
	IF _tally > 0
		ThisForm.List1.RowSource = "lcCursor"
		
*!*	The RowSourceType = 2 is set in the List.Init method

	ELSE 

		MESSAGEBOX("No hay datos para mostrar en el rango de Fecha Seleccionada.",0+64+4096,"Aviso!!!")
   		
		Thisform.dt1.oBJECT.Value = DATE()
		Thisform.dt2.obJECT.Value = DATE()

	ENDIF 
	
ThisForm.Refresh()

hth

MarK

 

[mjcmkrsr]

...

btw you may want to

SELECT TOP 10 a_vtas.art, UPPER(a_vtas.vend), fec_vta, ; 
...

otherwise you'll get a sum for "Job Silva", one for "JOB SILVA", one for "JOB silva" ...

hth

MarK

 

[Chris Miller]

MarK, the problem is not that any of his code errors.

If I apply this modification to his code in the top 10 form, I also get reslts, that's not the problem.

STORE Thisform.dt1.oBJECT.Value TO fec1
STORE Thisform.dt2.obJECT.Value TO fec2
fec1 = TTOD(fec1)
fec2 = TTOD(fec2)

*fec1 = DTOC(fec1)
*fec2 = DTOC(fec2)
 
  SET ENGINEBEHAVIOR 90
  SELECT TOP 10 a_vtas.art,a_vtas.vend,fec_vta, ;
  SUM(a_vtas.cant);
  FROM a_vtas;
  WHERE FEC_vta BETWEEN fec1 AND fec2 ;
  GROUP BY art,vend,fec_vta;
  ORDER BY 4 DESC ;
  INTO CURSOR lcCursor READWRITE
  
  SELECT lcCursor
  IF _tally <> 0
  	  Thisform.list1.RowSource = 'lcCursor'
	  Thisform.list1.RowSourceType = 2
   ELSE 
   		MESSAGEBOX("No hay datos para mostrar en el rango de Fecha Seleccionada.",0+64+4096,"Aviso!!!")
   		Thisform.dt1.oBJECT.Value = DATE()
   		Thisform.dt2.obJECT.Value = DATE()
   ENDIF

The results do not match his expectations, but as inconsistent his attachment is to his posted code, I don't see how to help other than pointing out you have to be more consistent in your coding and not do wildly different things.

So some points to make is:
If you never convert from the data type regime of datetimmes and dates to character, then you also et no conversion loss. And code works independently of SEET CENTURY ON or OFF, too.
Add the dbf you want to query to the forms datasession to exacctly open tht dbf and not nother one from whatever other directory. In detail, the top 10 form uses a private data session, the a_vtas.dbf used will be whatever VFP finds with current directory, if I first open it manually in the defalt datasession I get the error "file is in use". If I cd into a wrong directory the dbf is not found. If you want your forms to work consistently you can also let the sql query open the dbf file, that's not the fault but then you have to be consistent with the current directory setting.

There are too many inconsistent ways in this code to point them all out and explain them, Id be here tomorrow.

You're going far too many corners, get your code more straight forward and consistent, jobsilva.

Chriss

 

[Chris Miller]

By the way, the DTPIcker seems to consistently put the time portion of the datetimes to midnight, so exactly start of a certain day.

You can do an sql clause

datefield between datetimevalue1 and datetimevalue2

VFP supports this data type mix, that's not a problem, but I'd be cautious what VFP does exactly with the range. In theory, it could mean you don't get data from 31st of July when picking that date as the end date, because midnight is just the start of the date. So it depends on what VFP does exactly to be able to make the comparisons of the data with the between limits. If dates are converted to datetimes with noon instead of midnight, they fall out of the range, if datetimes are converted to dates, that'll work.

It's of course safest to keep the TTOD conversions. But don't do DTOC and go back to CTOD, that's not just nonsensical, that's just a completely unnecessary waste of time.

Chriss

 

[Chris Miller]

One more very general advice: Everytime you put a SET command into your code, first contemplate:

1. Is it a setting I want generally? Then it should go into main.prg
2. Is it a setting I only need locally and temporary? Then don't forget code to a) determine how this setting is currently using the SET() function and b) set it back to that original value when you're done.
3. Be aware that main.prg is not enough for the purpose of point 1, as a) settings have a different default in the IDE than in an EXE and b) settings also have a default in each new datasession, so even setting some things in main.prg does not make them the same for the whole application, whenever a form has a private datasession you need to repeat some settings

There is one base class in the FFC classes coming with VFP that will do a general reset of all setting that this class finds at the time it inits. It's shooting sparrows with cannons to use this, and maybe usage of such a class would need you to learn OOP style programming, which goes over the top of many anyway. So jut do it individually.

I could give much more general advice. This seems to me most appropriate not only regarding the SET CENTURY. There also is no need to SET ENGINEBEHAVIOR 90 in the middle of some form code, that's what you decide to use for the whole application, usually. Many you do just look like probing, whether something helps without thinking at all. If you don't have a full understanding of VFP poking arond and probing won''t get you far.

And that's a big point. VFP is full of hurdles for beginners that simply don't know, because it's not natural to assume some settings are only per datasession and not general settings. VFP seems to attract a lot of developers as easy to use, but it actually has many more woes in the concept known as POLA - the principle of least astonishment. VFP language and differing behavior depending on setting surprises you multiple times in your developer life with VFP. As it's grown in history and has many concepts violating that principle.

So that speaks for you, it means some falts are with the VFP developers to not make the language more consistent and less prone to side conditions, but if you add to that yourself by poking around instead of understanding your own code and why it does things he way it does and not how you expect it. You can't just walk around, ask for code and use it, don't understand it but try modification to it. If you have no true and stp by step understanding of all code you're using, you'll also always fail to modify it to work as you expect your modifications to work.

Nobody can help you with that, you have to get a deeper understanding of how VFP works in many aspects.

Chriss

 

[Chris Miller]

One thing to get you going in your manual verification to get the reference result you can compare to the form result, at least:

1. open the a_vtas table you actually want to query
2. Make this query to it

SELECT a_vtas.n_vta,fec_vta,Art,cant,precu,vend,tot_vta,v_tarj FROM a_vtas ;
WHERE fec_vta Between Date(2023,7,20) AND Date(2023,7,31) INTO CURSOR lcCursor_v READWRITE

That works in both enginebehavior 90 and 80, that works with century on or off, that's a reference you can count on.
Do you really get only 3 or 16 records from that? I doubt it.

Also notice that the queries in your forms differ from that one, not only by parameters insted of an explicitly sepcified date range, you have additional HAVING clauses in your SCXes. They filter the result also based on options you pick in the form. You don't seem to have seen this at all, or you would have posted the real code instead of posting somethhing that is in neither of your SCXes. Are you even aware of this? Maybe you even attached another SCX than you actually use yourself. And another DBF file than you actually query locally.

Your sloppyness with all of this is making you your own enemy, that's actually all there is to it. There are still many possible reasons why you don't get the result you expect to get. But nobody can help you if you aren't consistent with what you post vs. what your SCX does. I can only point back to my first advice, set a breakpoint and see what executes step by step. Verify you get the right date limits in the query parameters, see whether you actually query the dbf file you actually want to query. That could be done by seeing whether DBF('a_vtas') is actually the DBF you wantd to query. it might not be open before you query, so do this after the query.

Chriss

 

[jobsilva]

Dear teachers:

You are absolutely right, I am a newbie and poorly done.

It's true my code is poorly structured.

I had never used SQL commands that much, I had always used the native commands, and I have not used any tables other than the native ones.

I am terrified of using another database engine, I have no idea what I am going to do when I have to deliver some development with a client, much less how to create the installable so that it works without problems.

I made many changes to the date format because I don't have enough knowledge, but necessity has forced me to try to release programs that work, even if they are patched.

I admire the elegance of your code and I try to do my best when I read your advice.

In the form I sent them I removed several pieces of code and did not test it before submitting it, such as accessing the table when starting the form.

I am infinitely grateful to each of you for your wise advice.

I promise to put more effort, learn every day and follow their advice.

I solved the problem by reinstalling Windows and Visual FoxPro. On one occasion the power went out and I think something could have been damaged from there.

After re-installing the entire form, it already presents the correct data the first time.

Thank you all very much for your advice and effort to solve my problem.

I send you an affectionate hug.