Alternate way to SELECT a table

[Bryan - Gendev]

I have a problem in my single form app. The app opens over 30 tables and reports statistics on them.

The results are to be stored in a table I have named STORAGE. This is created early in the app.

Two sets of results are put into this table with the REPL command at two differnt points in the program.

To ensure I have the right table open I LOCATE a project name in the storage table and message to the user.

On my development PC it works flawlessly but after an installation via Inno compiler the program misses the second storage opening somehow.

Can I use any other opening commands to make certain the table is SELECTED when needed?

The values I want to place in STORAGE table are in storage4

Part of my code is

Set Default To (mysystempath)
If !Used('storage')
	Use storage In 0
Endif
Select storage
Go Top
Locate For Project = myproject
If Found()
    If 'SHU'$compname()
	nAnswer = Messagebox('Found correct project table for TMG tables values')
     Endif
Endif

	Set Default To (mysystempath)
	If !Used('storage4')
		Use storage4 In 0
	Endif
	Select storage4
	nhits = 0
	Go Top
	Scan
		nfield = storage4.NPER
		nhits = storage4.NTAGS
		
		Select storage
		Do Case
		Case nfield = 0
			Replace  storage.N_et00 With nhits

etc etc

Thanks

GenDev

 

[Olaf Doschke]

It's a bit unclear what you mean.

But you can use DBF("storage4") to get the full filename with drive and path to the table opened as alias storage4 and verify it's the right one.

Bye, Olaf.

 

[Bryan - Gendev]

Olaf,

Its STORAGE I am trying to write to..and doesn't get SELECTED.

GenDev

 

[Mike Lewis]

Given this code:

Select storage
  Do Case
  Case nfield = 0
    Replace  storage.N_et00 With nhits
 etc.

Then if nField is indeed 0, the REPLACE should work. There's no question that the Storage table exists and is open. If it wasn't, you would have go an error on the SELECT.

If you can't see the updates after you have done the REPLACE, it could be because the table is buffered and you haven't committed the updates; or because the table is at EOF.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

You might be at EOF in storage. then some numeric field is handled as 0. So LOCATE/SEEK in storage before replacing there. At least check EOF().

Bye, Olaf.

 

[mjcmkrsr]

Hi Mike

Imagine what you're doing - reading our code you're selecting STORAGE4, go to its 1st record and then while scanning it you're selecting STORAGE and never select STORAGE4 back. In fact you're replacing the STORAGE.N_et00 with the first field value of STORAGE4.

[highlight #FCE94F]Select storage4[/highlight]
	nhits = 0
	[highlight #FCE94F]Go Top[/highlight]
	Scan
		nfield = storage4.NPER
		nhits = storage4.NTAGS
		
		[highlight #FCE94F]Select storage[/highlight]
		Do Case
		Case nfield = 0
			Replace  storage.N_et00 With nhits

hth
MK

 

[Olaf Doschke]

Good hint, MK,

so maybe, GenDev, you think you have a relation from storage4 to storage and while scanning storage4 also iterate all records in storage, but you're not.
Either make a relation between Storage4 and Storage or locate in storage, otherwise you always replace in the same record or even nowhere, if EOF("storage")=.T.

Bye, Olaf.

 

[mjcmkrsr]

Hi Mike,

Please have a look at SQL UPDATE.

[link ]

http://support.microsoft.com/kb/191529

[/url]

hth

MK

 

[Mike Lewis]

MK,

I'm sure your suggestions are perfectly valid, but you should be addressing them to GenDev. I can assure you that I already know how to use UPDATE.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[vgulielmus]

I can assure you that I already know how to use UPDATE

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[mjcmkrsr]

Sorry Mike,

I meant of course GenDev

 

[Dan Freeman]

after an installation via Inno compiler the program misses the second storage opening somehow.

What error message are you getting? If it "misses" the opening, you'll get an error on the replace. If there is no error, then you have misdiagnosed the problem.

With Replace, always use the IN clause:

Replace field with value IN alias

And always make sure the target is not at EOF().

 

[DSummZZZ]

I can assure you that I already know how to use UPDATE.

-Dave Summers-

Even more Fox stuff at:

http://www.dave-summers.net/foxprolinks.htm

 

[NasibKalsi]

From the suggestions above, I came up with the following.

* mysystempath = "c:\u\stor" 
Set Default To (mysystempath)

if used('storage')
 select storage
 use
end

if used('storage4')
   select storage4
   use
endif

use &mysystempath.\storage in 0
use &mysystempath.\storage4 in 0

select storage4
go top
do while .not eof()
	nfield = storage4.NPER
	nhits = storage4.NTAGS
	Select storage
        locate for storage.KEY == storage4.KEY  
        if found()
           do case	
	   case nfield = 0
              Replace  storage.N_et00 With nhits
           other
           endcase
      endif
      select storage4
      skip
enddo

 

[Olaf Doschke]

Hi GenDev,

The central part for locating the storage record you replace in is this locate, isn't it?

Locate For Project = myproject

Your code eventually pops up a messagebox, if no record is found, but also not always. What you do always is continue to the section updating storage from storage4. You have to know FOUND() is always !EOF(), so if you didn't find a project in storage, EOF("storage") really is true. You may need to display a not found message no matter if the compname (Company name?) contains SHU or not.

Nasib gives you code to locate records in storage related to storage4, but I think the situation may be you really only work on a single record in storage having many fields from N_et00,N_et01,....to N_et99 or whatever. I give up telling people to not do such tables, I actually do them for displaying data from left to right on a timeline instead of the normal top to bottom each in their own record.

No matter why you do it, you may simply replace all this code by using the cross-tab wizard or pivot table wizard or export your storage4 data to MS Excel and do the pivoting there.

Bye, Olaf.

 

[Dan Freeman]

I can't remember the last time I used DO WHILE NOT EOF().

   Select Storage4
   Scan For Storage4.nField = 0
        If Seek(Storage4.Key,"Storage","key")
              Replace  storage.N_et00 With storage4.nhits
        Endif
   Endscan

This assumes you have an index tag on the KEY field in storage4. (Bad name for a field, by the way. It's a VFP keyword.) You really should have an index tag on that field unless you're really using a piddling number of records.

I suspect you intend the storage4.nhits field to be cumulative, so it should actually be:

Replace  storage.N_et00 With storage.N_et00 + storage4.nhits

 

[mjcmkrsr]

Hi,
My suggestion

select storage4
go top
SCAN
[indent]nfield = storage4.NPER[/indent]
[indent]nhits = storage4.NTAGS [/indent]
[indent]do case	[/indent]
[indent]case nfield = 0[/indent]
[indent]UPDATE STORAGE SET Storage.N_et00 = nHits WHERE STORAGE.PKEY = STORAGE4.PKEY && you'll have to define the Primary Keys if they aren't[/indent]
[indent]case ...[/indent]
[indent]...[/indent]
[indent]endcase[/indent]
ENDSCAN

hth
MK

 

[Bryan - Gendev]

Olaf,

U said in part
The central part for locating the storage record you replace in is this locate, isn't it?

You are correct in that STORAGE is a single record table with 80 fields from which a csv file is written. STORAGE contains the results of my analysis of the 29 tables in the 3rd party genealogy program.

My client wants to distribute my small application to users of the genealogy program to gather statistical data of user's projects. He will combine the csv files from as many users as return a csv file to obtain the data he is interested in.

On my dev PC it finds STORAGE both times every time but on an installed copy on any other PC it misses STORAGE somehow on the second set of REPL - thus the csv is written from the last opened table of the genealogy program.

You were correct when you earlier said (in another thread) that an exe only ( no runtimes) running on users machines would depend on the particular vfp dlls on that PC. I got a perfect result (csv) on my Netbook but th exe caused a runtime error on his PC which ended up in the genealogy program's system folder. The Netbook has an earlier version of the genie program. (I have asked in another thread how to get the latest runtimes for SP2.)

Thanks for all your thoughts.

GenDev

 

[Olaf Doschke]

But your problem is not storage.dbf. It is found, otherwise you'd get an error when you USE storage. Your problem is a record within storage.dfb is not found.
All your replaces don't cause errors, but don't write data, as you are at EOF.

In how many other ways do I need to explain this, so you get it?

Here's a little sample code not erroring, but also not transporting data:

Create Cursor storage (Project c(10), n00 B, n01 B, n02 B)
Insert Into storage Values ("project1",0,0,0)
Create Cursor storage4 (nper I, ntags I)
Insert Into storage4 Values (0,1)
Insert Into storage4 Values (1,4)
Insert Into storage4 Values (2,8)

myproject="project 1"
Select storage
Go Top
Locate For Project = myproject
If Found()
   If 'SHU'$compname()
      nAnswer = Messagebox('Found correct project table for TMG tables values')
   Endif
Endif

Select storage4
nhits = 0
Go Top
Scan
   nfield = storage4.nper
   nhits = storage4.ntags

   Select storage
   Do Case
      Case nfield = 0
         Replace  storage.n00 With nhits
      Case nfield = 1
         Replace  storage.n01 With nhits
      Case nfield = 2
         Replace  storage.n02 With nhits
   Endcase
EndScan

Select storage
browse

Procedure compname()
   Return Getenv("COMPUTER")
Endproc

If you run this, no error occurs, but storage still is empty. Now what happened? In this case the problem can't be storage is not found, it's a cursor I create, it's there unavoidably. What is not found? project 1 is not found, because the record prepared in storage is project1, not project 1.

You don't get any errors, if you replace in a not found record, your replaces simply go nowhere, into NUL device, nirvana, they are gone. That's all.

Simply look into your data and the best thing to do to catch a false match is to display a message not only on computers with "SHU" in their name, if a project is found, but on all computers and especially if a project is NOT found:

Create Cursor storage (Project c(10), n00 B, n01 B, n02 B)
Insert Into storage Values ("project1",0,0,0)
Create Cursor storage4 (nper I, ntags I)
Insert Into storage4 Values (0,1)
Insert Into storage4 Values (1,4)
Insert Into storage4 Values (2,8)

myproject="project 1"
Select storage
Go Top
Locate For Project = myproject
[b]If !Found()
   Messagebox("Didn't find project "+myproject+". Sorry.")
   Return && nothing to do here
Endif[/b]

Select storage4
nhits = 0
Go Top
Scan
   nfield = storage4.nper
   nhits = storage4.ntags

   Select storage
   Do Case
      Case nfield = 0
         Replace  storage.n00 With nhits
      Case nfield = 1
         Replace  storage.n01 With nhits
      Case nfield = 2
         Replace  storage.n02 With nhits
   Endcase
EndScan

Select storage
browse

If you run this, you simply get the message box. Change myproject="project1", remove the space in "project 1", and then the project is found, the REPLACEs update data in the n00 to n02 fields and the browse shos the data transferred from storage4 to storage.

So your overall only is your data, not your code. But aside of your data problem you could have a two line code using macro substitution for the field name and a REPLACE using that macro substituted field name instead of the whole CASE statement and even that is not the easiest thing you can do, you can transpose a series of records into one line with the cross tab wizard, it even could do so for all projects at the same time.

At first now, look into your data, look into what myproject and whether you simply made an error filling in storage.project or myproject correctly.

Bye, Olaf.

Bye, Olaf.

 

[Dan Freeman]

Perhaps a more short and sweet demo of what's going on is in order.

Create Cursor Test (test c(10))
Append Blank
? "End of file?", Eof()
Skip
?"End of file?", Eof()
Replace Test with "Foo"
Browse Nowait
[/test]

Notice the first test for EOF() is .f. even though you're on the last (only) record.

When you're on the last record of a table and SKIP you will not get an error message. There is a "phantom" record one beyond the last record in every table. It used to be really useful to create blank memory variables before SCATTER MEMVAR BLANK was added to the language.

BUT if you Replace while on the phantom record, nothing gets replaced because it's a ghost. There isn't a real record there.

When a locate fails, you're left at EOF() -- the phantom record. THAT is why your replace is failing. It doesn't have anything to do with SELECT.