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?

 

[Chris Miller]

You don't have to apologize for the level of knowledge you have.

I was just agitated by the fact that the attachment you posted recently doesn't match the code you originally posted.
If you're not even consistent with presenting your problem, nobody can help you. We can all only guess and point out possible reasons, but that's wasted time, if the actual queries in the SCX are not whaat you posted in your original question.

Chriss

 

[Tore Bleken]

Jobsilva,
If you are serious about data, you really should have a UPS. A computer without a UPS is like sitting on a bomb where the timer has started to count down.

 

[Mike Lewis]

I am terrified of using another database engine

I've heard other people say that - but there's really no need. You can start by experimenting with one of the free products such as MySQL or (preferably) the free version of SQL Server. There's plenty of information about it available on line and in books, and plenty of help in forums like this one. Most VFP folk find that it's not all that difficult.

That said, you probably won't need to move away from the native VFP database engine to deal with the issues we've discussed here. But it would definitely be worth your while to further explore the SQL sub-language within VFP.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

In trying to see what could be wrong I analyzed the a_vtas.dbf you attached and I see no general file corruption or index corruption in it.
I see a lot of inconsistencies, just picking out some examples:

Fetchor datetime values are sometimes the empty date, always in conjunction with Granor being "si". In all other case the datetime values stored have no time portion aka are dates at midnight as time. You could also use a date field for that.

The field v_tarj char(2) is space(2) in most records, some are 'si', some 'no'.

The Vend field sometimes is all upper case, sometimes not.

The list can be extended, but nothing of that is file corruption or data errors, I would say that's inconsistent data, but without knowing the whole system I can't tell you whether you should do something about the data or not. I can't explain a 70 in n_vta, because no record has that value and there are no deleted records in that dbf. So I also can't confirm our last sensible explanation of that value coming from a deleted record. I can assure you VFP does not invent values that are not in the data, for values not in a DBF an SQL query would need an expression calculating a value, but there is nothing like that in the query you posted or in the queries I see in some button click events, that would play a role.

So the only sensible remaining explanation is you have queried another DBF file, not the attached one. Blaming it on a faulty Windows installation is very questionable, blaming it on file corruption. Well, I can't see that in the file you attached.

The CDX also is fine, not only as it is a CDX and not an IDX, but I also don't find any corruptions within that CDX. Only the fec_vta field is indexed at all and all records are in the index, which can be seen from comparing a count to nTotal with reccount.

What strikes me as strange is that the fec_vta field only has the values 07/20/2023, 07/30/2023 or 07/31/2023, which seems to me to only be an extract of the data within the date range you wrote as the range you queried. That lets me think it could be data queried INTO TABLE somewhere in your application, and tables generated as files are something I personally avoid as for temporary results we have cursors, if you need extracts as dbf file because reports are fed by that, well, file generation can sometimes go wrong, especially if the hard drive isn't healthy, but then you might have overwritten a corrupt a_vtas.dbf with a DBF that's no correct.

If such files are generated and overwritten it would also explain why you sometimes see values and sometimes not, it would depend on what extract is taken out from yet another central table that's the source of generating temporary a_vtas.dbf, but all that is just guesswork again, based on the fact that the fec_vta value are exactly within the data range you queried.

The buttons in your two CXes, don't have INTO TABLE queries, though, that could only be somewhere else in your whole project.

If you then don't take a snapshot, simpler said a backup or copies of the files as they were exactly at the time you had a problem, then there's also no chance to examine that and see what is wrong with them.

All that also is based on the assumption, this system exists for longer and for more than the 3 dates in the data. Maybe you store data in separate DBFs per month, I don't know, as long as you don't tell more about the system. Then it becomes very likely again, that you get values you don't expect simply because the current directory points to a directory of another month, then you should get no data querying for dates definitely only from July. But who knows how the data is organized in your database.

I can only warn about creating DBFs in a DBC that's used shared. I know the DBF comes from a DBC, as first opening it pointed out the DBC is missing, which gives the option to make it a free table. If you add tables to a DBC very frequently, that can be a root cause of file corruptions, because a DBC also is only a free table and generating tables means inserting new records, altering a table updating records, etc. Like any DBF, also these changes can cause corruption, but then it's a more serious corruption than that of data, the structural informations about the dbf get wrong. Ideally, a dbc is only really changd by the developer and then the production database is upgraded with exclusive access without users using the application.

Then you only read the DBC data when your end users use it shared, and reading files has no potential to cause corruptions, file corruptions always and only occur when a file is changed and written to. ou obviously can't prevent that for DBFs of your database, but if you depend on generated DBFs, you better only generate free DBFs and keep them out of the DBC.



Chriss

 

[Chris Miller]

In my last post I talked about an assumption of your application generating tables and how that isn't a very good concept with a DBC database.

Indeed any database servers like MySQL, Firebase, SQL Server etc. would have no problem of altering and creating tables in a database, even during database usage of an application. There is a separation of application and database access, which only is done by the database engine process of the database and it can take the necessary preccautions so alterations of a database structure can go hand in hand with the usual queries of data. A database server can't do wonders when a table alteration renders a query invalid, as a field is dropped or a field name changehas not yet happened and a quer depends on it, which mens database upgrades are also usually done adminsitrative and while no user is connected. But at least you can easily create new tables while users use an application without causing any side effects.

So if you want to make use of such concepts, thats another incentive to use a database server.

Chriss

 

[mjcmkrsr]

Hi,

I suspect JobSilva uses his DBF (a_vtas) as a cash register.

He only has 8 different articles which however appear many times in the DBF.
He calculates the sum of each sold article (cant, precu and sub_tot)
He adds the number of articles sold per customer (tot_arts and n_vta)
He calculates the total price of the sold articles, receives cash and reimburses the due amount (tot_vta, c_paga, c_dev)

I suggest he might want to have a look at the VFP-concept and read some books from Hentzenwerke.com before starting to code his application.

hth

MarK

 

[jobsilva]

Dear teachers.

I greatly appreciate your advice and scolding.
Ypu are right in everything.
I try to learn every day and more from you who have been in this difficult profession for some years.
I'm truly is just beginning this journey.

English is not my native language and programming is a little more complicated for me, I am more gifted with hardware and I have been dedicated to hardware for several decades.
I really appreciate your help and advice, it has helped a lot to overcome my deficiencies in programming and to overcome my programming code.

Thank you very much Mark, it's true, I'm trying to design a point of sale.
I very much appreciate the bibliography you mention, I will try to read it little by little.

Best Regards to all.

 

[mjcmkrsr]

Hi JobSilva,

I wrote a simple demo app several years ago for a friend's grocery store.

It has 4 tables: ITM that lists the name of the items, Item_In for the items' price and quantity you buy and put in stock, Item_Out for the items you sell from stock (an item not in stock may not be sold), Item_TA for handling TANs.
You may download and copy it into a new folder and unzip it there. You run it from VFP user interface by clicking Program then DO ... (choose the folder) and click on TESTBUYSELL.PRG.

Please be aware - this is demo code.

Hence:
[ul]
[li]there may be gotchas or bugs[/li]
[li]the whole tax handling procedure (VAT in Europe) is absent[/li]
[li]the items' providers are not handled[/li]
[li]the shippers are not handled[/li]
[li]you can only print reports for specific TANs[/li]
[li]I kept the user interface simple - you may of course change it to your needs and gusto or rewrite it[/li]
[li]...[/li]
[/ul]

Nevertheless you may play around with the app and I hope its code might give you some hints. Btw all the procedures may be used as properties/methods of visually generated controls.

hth

MarK

 

[jobsilva]

Mjcmkrsr ;

Thanks a lot.

I will study your proyect.

I will surely find many new things to learn.

Best Regards.

 

[mjcmkrsr]

Hi JobSilva,

I have been playing around with the app and found some quirks. Attached a pimped up version.

hth

MarK