Assistance Populating Grid 1

[FoxEgg]

I know I should know how to do this... but I am blanking

In TEXT1 I have

SELECT * FROM PATIENT_EPISODES;
   WHERE last_name = cLName1 .and. first_name = cFName;
   NOCONSOLE;
   NOWAIT;
   INTO CURSOR temp

I use an interactive change to cLName1 and cFName... and I want to display the results in a one column Grid ...

I cannot seem to get the properties right....

Should I make a QUERY and use that as the RowSource...

Sorry.... holiday mind block

FoxEgg

 

[white605]

Are you looking for the RowSourceType and RowSource properties?
wjwjr

 

[Mike Lewis]

Hi FoxEgg,

What exactly is the problem? What's going wrong?

In general, you can create a cursor (which is what your SELECT is doing), and use one or more of its fields to populate a grid. You do that by setting the grid's RecordSource to 1, its RecordSourceType to the name of the cursor, its ColumnCount to the number of fields you want to show, and the individual columns' ControlSource to the names of the fields. Is that what you are doing?

Also, I don't see the point of using NOCONSOLE or NOWAIT in your code. Those keywords don't apply when you are creating a cursor (in fact, I'm surprised they are allowed).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[FoxEgg]

Thanks for replying... (It must be the 45C (113F) we had on 1 January that fried the cerebral cortex).

I wrote a simple name finder routine for my database... it was supposed to use a sequential search (using InteractiveChange in a TextBox) and I used a comma as a separator for LastName and FirstName...

I detect the comma with

IF "," $ cLName

and hence generate cLName1 which is just the LastName and cFName which is the FirstName

I tried using

SET FILTER TO last_name = cLName1 .and. first_name = cFName

And putting the result into a grid and it worked FINE... but it was occassionally very slow (It was really slow on backspacing in the TextBox)and so I thought it was due to the SET FILTER ... hence the attempt to use a SELECT.. to go faster... (*** see note later)


Only problem is that I could not seem to populate the grid using SELECT and a temp cursor as easily as using the SET FILTER on the whole Table.

So that having been said.... You have SORT OF solved one problem..

In general, you can create a cursor (which is what your SELECT is doing), and use one or more of its fields to populate a grid. You do that by setting the grid's RecordSource to 1, its RecordSourceType to the name of the cursor, its ColumnCount to the number of fields you want to show, and the individual columns' ControlSource to the names of the fields. Is that what you are doing?

I set the grid's RecordSource to the name of the cursor (ie temp), and its RecordSourceType to 1 (I guessed it was wrong way around).. and ColumnCount to 1 and the ControlSource to (and I hope this is right) temp.patient_episode (?is the 'temp' right ?)

and it is nearly right....


AND NOW FOR THE *** NOTE LATER....

I thought SET FILTER was supposed to be fast... so I looked at my code again... and (doh !) I used COUNT to detect if there were any matches for the SET FILTER condition... AND I think that counts the whole database.... 100,000 records each time and that might cause the slowness...

... but returning to the SELECT.... I still have not completely nailed the Grids properties YET... the Header shows all the fields...

ANd I still haven't got a fast enough search,,, meaning my code is wrong somewhere,,,

Star for providing direction... Thanks... and happy NY

FoxEgg

 

[Mike Lewis]

FoxEgg,

45C (113F) we had on 1 January

Southern hemisphere, I assume. Here, we had 5C, which is much warmer than usual for Hogmanay.

putting the result into a grid and it worked FINE... but it was occassionally very slow

Yes, it would be - even without using COUNT() to detect if there were any matches. In general, the combination of grids and filters seem to defeat Rushmore optimisation, so you will see a performance loss even if the filtered fields are indexed.

(I guessed it was wrong way around)

You guessed right. My mistake.

ControlSource to (and I hope this is right) temp.patient_episode (?is the 'temp' right ?)

That's correct if Patient_Episode is the name of the field that you want to show in the grid. Based on what you have told me, I would have thought you wanted to show the patients' names, in which case you would either to have two columns (one with a ControlSource to last_name and the other to first_name); or, to combine them into a single field within the SELECT (use the AS clause to specify a name for that field) and set the ControlSource accordingly.

the Header shows all the fields...

Hmm. Not sure about that. If you've set the ColumnCount to 1, you should be seeing one column.

ANd I still haven't got a fast enough search

Check your indexes. The query will only be optimised if the conditoins in your WHERE clause match the indexes on the relevant fields. For example, " ... WHERE last_name = cLName1 ... " implies an index on last_name, not on UPPER(last_name).

Star for providing direction... Thanks... and happy NY

Thanks, and to you too. i hope you manage to solve the outstanding issues. Be sure to report back.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[FoxEgg]

Thanks to you Northern Hemispheroids.... Its good to see that Mike Y has pointed out that I am only out of date since 1997. I am using VFP7, but I have VFP8... no plans to get VFP9. Thanks for the info Mike

So what I did was got rid of the COUNT... and it was much faster,,, However, it was still a bit slow (but tolerably so) on the BACKSPACE.

I will check the Indexes... And I do use a SEEK.

Keep Warm.. cold here today 19C.

Thanks Again

FoxEgg