NOFILTER question 2

[yert33]

VFP 9 SP1:
Populating a grid with cursor as the recordsource.
Here's my code:

select county, countycode, certnumber, lastname, ;
firstname, permaddr, permcity, dob ;
from realdbf ;
where realdbf.lastname=m.mlastname and ;
realdbf.firstname=m.mfirstname and ;
realdbf.dob = m.mdob;
into cursor crsTemp NOFILTER

select (thisform.grdMyGrid.recordsource)
append from dbf( [crsTemp] )
use in crsTemp
index on lastname+dob tag lndob
set order to tag lndob

go top in (thisform.grdMyGrid.recordsource) && A CURSOR
thisform.grdMyGrid.refresh()

The user selects a county dbf via a combobox and the dbf is opened with the alias "realdbf". Then they type in the m.mlastname, m.mfirstname and m.mdob fields to set up the search strings.
The above code is called when they click on the Search button.
Works great.

So I wanted to improve it some and create a reset button to re-init the m.mlastname, etc. vars so the user had a "clean slate" with which to start the next search if they were searching within the same county dbf. Let's call it the "New Search" button. (I already have a "Reset All" button that starts from scratch, meaning it zaps the grid's recordsource cursor, re-inits the memvars and sets focus back to the DBF selecting combobox. )

The "new Search" button also zaps the grid's recordsource and re-inits the memvars but when the Select is called the cursor crsTemp is always empty.

The only difference in the Reset All button and the New Search button is that the New search Button doesn't close the realdbf and open another one.

I hope I'm explaining this well.

So in trying to debug this I tried leaving out the NOFILTER clause in the Select statement. When I do, it appears that the WHERE clauses are ignored for it returns all records from the realdbf.

Is this how it is supposed to work? And if so, why?

Leaving off the NOFILTER does not change the wierd behaviour of the New Search button, though.

Thanks for reading this.

 

[mm0000]

Can you change your query changing the '=' signs in the query with '==' and then see if it works.

 

[JimWWinter]

When you do a query against one table with a WHERE clause and don't include the NOFILTER clause you get a filtered view of the underlying table without an actual cursor. As a result, any manipulation of that view has to refer back to the original table and the WHERE clause does not apply to the table so you get all the records. That's why they added the NOFILTER clause, I think in version 6, so that you could have an actual cursor that you could manipulate independently of the underlying table.

As to the empty cursor, I have no idea what causes that.

Good luck,
Jim

 

[yert33]

Just got back to this forum. Thanks to all for replying

mm0000 - I will try the == here in a few minutes

Mike - Yes, I have the indices. That's something you taught me!

JimWWinter - That's a good explanation, I think I understand now.

I will reply again with the == results.

 

[Olaf Doschke]

Hi yert33,

I think you are experiencing grid reconstruction. The result cursor is in fact not empty, but the grid goes blank. It does not like it's recordsource to disappear, even for the shortest time. And when the cursor exists and you run a SQL command that creates that cursor again, it vanishes for split seconds.

Before rebuilding the gridcursor with SQL, set the grid.recordsource = "", after the SQL ran, set the recordsource back. Even better, temporarily set the recordsource to a cursor of the same structure. It's easier than you think:

* regenerate grid cursor

* 1. generate temporary replacement cursor:
local lcAlias
lcAlias = grid.recordsource
Select * from (lcAlias) where .F. into cursor curGridTemp nofilter

* 2. temporarily replace grid.recordsource
grid.recordsource = "curGridTemp"

* 3. regenerate the grid cursor
Select ... into cursor (lcAlias) nofilter

* 4. reset the recordsource
grid.recordsource = lcAlias
use in select("curGridTemp")

Bye, Olaf.

 

[yert33]

Thank you, Olaf.

I think I am doing something very similar to your suggestion. Maybe this IS my problem.

On the form LOAD event I create the grid' recordsource like this:

CREATE CURSOR crsVGrid ;
 (County C(20), countycode C(3), certnumber C(8), lastname C(35), ;
  firstname C(20), permaddr C(60), permcity c(25), dob c(8) NULL)

Then the user selects a county.DBF from a combobox and types whatever characters in the search string fields before the SELECT statement is called.

The SELECT statement fills a temporary cursor (crsTemp) as you can read above. Then I append from the temporary cursor into the "real" cursor, the grid recordsource cursor.
Here's my code from the search button Click event:

thisform.LockScreen = .T.
zap in (thisform.grdMyGrid.recordsource)

select mcounty

m.mlastname  = alltrim(m.mlastname)
m.mfirstname = alltrim(m.mfirstname)

** padding with zeros done in txtDOB valid
** parse date
m.yr = substr(m.mDOB,7,2)
m.mo = IIF(substr(m.mDOB,1,2)='00', '  ',substr(m.mDOB,1,2))
m.dy = IIF(substr(m.mDOB,4,2)='00', '  ' ,substr(m.mDOB,4,2))
m.srchDOB = '19'+ m.yr + m.mo + m.dy

LOCAL m.loThermo
m.loThermo = NEWOBJECT("cusSQLThermo","&gcClsPath.cusSQLThermo.VCX")

select county, countycode, certnumber, lastname, ;
	firstname, permaddr, permcity, dob ;
	from mcounty ;
	where mcounty.lastname=m.mlastname and ;
	mcounty.firstname=m.mfirstname and  ;
	mcounty.dob = m.srchdob;
	into cursor crsTemp nofilter

RELEASE m.loThermo

select (thisform.grdMyGrid.recordsource)
append from dbf( [crsTemp] )
use in crsTemp

index on lastname+firstname+dob tag lnfndob
set order to tag lnfndob

go top in (thisform.grdMyGrid.recordsource)
thisform.grdMyGrid.refresh()
thisform.grdMyGrid.setfocus()

thisform.LockScreen = .F.

This works fine EXCEPT when I try to reset the search fields and do the SELECT again. That's when the temporary cursor appears to be empty. BUT if I force the user to reselect the county.dbf it works fine again.

Sorry if I have digressed somewhat. As you can see I am ZAPing the grid recordsource cursor each time. Is this not equivalent to your suggestion of temporarily replacing the recordsource?

Thank you again, sir.

 

[Olaf Doschke]

Hi yert,

the way you do preserve the grid structure is also fine. The only thing is, you don't need to create the index tag lnfndob each time, zap only empties the cursor and it's cdx, it does not delete tags. So put the index definition to the load event.

Are you sure m.lastname and all the other variables aren't out of scope here? Are you working with PRIVATE variables, set before you create the form or in the load event? If you want to be sure these variables hold their values throughout each method of the form, make these form properties. But despite of that I see nothing, that could result in the described behaviour.

Set a break point before the SQL select and inspect how the filter variables are set in the debugger, inspect what DBF() mcounty is and see why the SQL has no result.

Bye, Olaf.

 

[Olaf Doschke]

Hi yert,

towards NOFILTER: If you leave that out, DBF([crsTemp]) can be the file name of the table, that is DBF([mcounty]). And so instead of appending the result in crsTemp you append the whole mcounty. So the NOFILTER is really needed,
as you want to append from dbf([crsTemp]). It's important, that crsTemp is it's own file and not just a filter to the dbf in the From clause, because append from dbf() will append from the file which is returned by the dbf() function, the whole file.

Can it be, that there are records in the dbf, which is open as alias mcounty, that are not stored yet, that are in the tablebuffer? Than what you normally do, closing the dbf and reopening it, would store that and SQL could select from all records. So perhaps the simplest way would be, to close and reopen mcounty, although it seems useless:

&&if used("mcounty")
   use dbf("mcounty") in select("mcounty")
&&endif

Bye, Olaf.

 

[yert33]

Mike - Yes, there is an individual DBF for each county. and all the county DBF's are in the same folder.

I'm checking that the path hasn't changed with your debug suggestion...


Olaf - I will move the index statements: nice touch. The search variables are declared PUBLIC in the main prg file (I still have that old PUBLIC habit I will work on.... Hi, my name is Trey and I'm a PUBLIC-aholic....;-) )

Anyway what strikes me is what you say about crsTemp being it's own cursor, not a filtered view of the table. Because I am not explicitly creating crsTemp outside of the SELECT statement. I bet you've found my problem.

Be back in a few......

Thanks to you guys.

 

[yert33]

DBF path doesn't change.

Watching these in debugger;

DBF([crsTemp])
DBF([crsMyGrid])

I have explicitly created crsTemp in the Load event so I know (I think) that it's a real cursor. Watching it in debugger, it has a filename and path (*.TMP file).

My reset code is seemingly causing the crsTemp filename to change - but the filename for crsVGrid, it never changes.

So instead of a crsTemp being a cursor I created a table in the Load event named crsTemp. Changed my SELECT statement to read:

elect county, countycode, certnumber, lastname, ;
    firstname, permaddr, permcity, dob ;
    from mcounty ;
    where mcounty.lastname=m.mlastname and ;
    mcounty.firstname=m.mfirstname and  ;
    mcounty.dob = m.srchdob;
    into table crsTemp

But that caused VFP to start barking about "needs row buffering for this operation" and I got wrapped around the axle on that for way too long. Back to the explicit cursor now.

BTW, it acted the same way with an actual table as the crsTemp.

Olaf, I put your code

if used("mcounty")
   use dbf("mcounty") in select("mcounty")
endif

in my reset Click event and it coudn't find the alias mcounty. So I think this may be the scent now.


Still banging....

 

[yert33]

dbf("mcounty") is staying the same (..\county.cdbf)
and
used("mcounty") is staying True

before, during and after my reset button Click.

But subsequent Selects are not returning data which is the original problem.

This is driving me crazy....

 

[yert33]

OK - a little progress:

The county file source seems to be persistent. I think I've narrowed it down to the WHERE clause.

The FIRST time the SELECT is run, the where clauses work. Subsequent times, they don't. I replaced the SELECT clause temporarily without any WHERE clauses and my reset button returns all records, as shoudl be.

The Watcher in the debugger says that my search variables are "there" all the time... more experimenting.....

 

[yert33]

IT'S MY M.SRCHDOB SEARCH STRING THAT IS CAUSING THIS ABEND....

 

[yert33]

Son of a.....

OK, first of all thanks to everyone who responded. Mike and Olaf, I'm giving you stars becuase you guys were herding me into the right area. Here's the resolution:


The first time the SELECT ran, the var m.mDOB had been initialzed to "".

My reset Click code re-initialized it to " / / ", thus causing my srchDOB snippet to blow out, And cause subsequent SELECTs to fail.

I feel so stupid. I've taken over 2.5 workdays screwing with this. Maybe I'm not cut out to be a coder. At least not an efficient one, eh? Problem with this is that we'r eon a deadline we have no contro over and I hope I don't waste this much time on the code that lies ahead of me.

Thank you all for holding my hand on this one.

Trey

 

[yert33]

Yes, it is, Mike. Thank you for it. I will get to fox.wiki sometime today....