I cannt seem to populate a GRID with an SQL command

[FoxEgg]

I cannot get this to work (I am such a dunce)

What I wanted to do was search for a NAME in a TABLE.

I have a TEXT Box as a Data Entry and I wanted to use an entry format like...

LAST NAME,FIRST NAME... where the comma was the clue that the letters followed were FIRST NAME..

I have put my code below... but come what may I cannot get the data to appear in the GRID... I have changed the grid RecordSource, the RecordSourceType...

Can someone tell me where I am going wrong....

Thanks

 In TEXT1 Interactive Change - So I get immediate updates
SET CURSOR on
SELECT 1
*!*	SET ORDER to 1   && PT_EPISODE
SET ORDER TO 2   && LAST_NAME
*GO top
cLName  = ALLTRIM(thisform.text1.Value)

IF "," $ cLName
cLName1 = SUBSTR(cLname, 1, (ATC(",", cLname)-1))
cFName1 = ALLTRIM(SUBSTR(cLName, LEN(cLName1)+2, 10))  

IF LEN(cFName1)> 0

SELECT patient_episodes.Last_name, patient_episodes.first_name, patient_episodes.file_num ;
   FROM patient_episodes; 
   where patient_episodes.last_name= cLName1 and patient_episodes.first_name = cFName1;
   INTO TABLE temp.dbf;
   ORDER BY last_name
thisform.Refresh()
	  
ELSE

SELECT patient_episodes.Last_name, patient_episodes.first_name, patient_episodes.file_num ;
   FROM patient_episodes; 
   where patient_episodes.last_name= cLName1;
   INTO TABLE temp.dbf;
   ORDER BY last_name
   
thisform.grid1.visible =.t.
ENDIF


ELSE

SELECT patient_episodes.Last_name, patient_episodes.first_name, patient_episodes.file_num ;
   FROM patient_episodes; 
   where patient_episodes.last_name= cLName;
   INTO TABLE temp.dbf;
   ORDER BY last_name
   
thisform.grid1.visible =.t.
ENDIF

 

[Dan Freeman]

Couple of things:

1) You are selecting INTO TABLE, which means each query must close the temp table in order to recreate it. This will destroy your grid's layout.

2) When you close and reopen a grid's data source you need to re-set the data source to tell the grid to requery it.

3) Look up parameterized views in the help file. Using a parameterized view for this query would take less code and would obviate #1 and #2.

 

[FoxEgg]

Will do that tomorrow.. ((3:30am here) Thanks...
JF

 

[Mike Lewis]

FoxEgg,

Another problem is that you are (correctly) trimming the search terms, but not trimming the fields within the table.

In other words, you have this:

...  where patient_episodes.last_name= cLName1 
and patient_episodes.first_name = cFName1 ...

but what you need is this:

...  where ALLTRIM(UPPER(patient_episodes.last_name)) = ;
  ALLTRIM(UPPER(cLName1 )) ;
  AND ALLTRIM(UPPER(patient_episodes.first_name)) = ;
  ALLTRIM(UPPER(cFName1)) ...

As well as the addition of ALLTRIM(), the UPPER() will make the search case-insensitive.

In addition, you need to do what Dan suggested. One way to do that is to set the grid's RecordSource to nothing before you do the SELECT, and then to set it to the name of the table afterwards.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Mike, I'd not suggest to make the where clause that complicated. The Upper() is indeed needed for a case insensitive match, but this moight not be needed and the alltrim is only needed for an exact match. You can simply set ANSI ON to get the matching "as expected" in most cases.

What do I mean by "as expected"? Assume the last name field is Char(20), then you store "Smith" into it. But what is really stored is "Smith" plus 15 spaces. Nevertheless you expect Where lastname="Smith" to return records for last name being "Smith", ignoring the additional 15 spaces stored in the Char(20) field.

Doing ALLTRIM() only is needed, if you fear the exact match, but you don't need to fear that. And you also don't need to fear finding "Smithson" when searching for "Smith", ANSI does cause a very proper match without needing much thought about the where expression and thus yields better readable sql code.

What you should do anyway is Alltrim() the input of the user, so you actually do search for "Smith" and not " Smith" or "Smith ", especially searching for " Smith" would not find a record.

In short my recommendation is, keep your code clean and readable and choose the best settings for that to work, in case of SQL ANSI ON.

You only need to do more complex where clauses, if you want to make matches with patterns, when using LIKE instead of = for example.

Overall my recommendadtion is to do this in regard of the where:

Set Ansi On && a per datasession setting, not neceessarily per SQL
cLName1 = Alltrim(GetWordnum(cLname,1,","))
cFName1 = Alltrim(GetWordnum(cLname,2,","))

Select ... Where last_name= cLName1 and first_name= cFName1

And if you insist on case insensitive search do index on UPPER(last_name) and on UPPER(first_name) and then do search via

Set Ansi On && a per datasession setting, not neceessarily per SQL
cLName1 = Upper(Alltrim(GetWordnum(cLname,1,",")))
cFName1 = Upper(Alltrim(GetWordnum(cLname,2,",")))

Select ... Where Upper(last_name)= cLName1 and Upper(first_name)= cFName1

In both cases the sql is better readable and it works with using indexes accellerating the sql. While an index on ALLTRIM(UPPER(last_name)) is not forbidden, VFP does PAD that index to the field length anyway, as indexes ALWAYS have fixed length expressions. With ANSI ON you don't need that.

Bye, Olaf.

 

[Mike Lewis]

Olaf, what you say makes a lot of sense (as always). I was just trying to keep the explanation simple, to help FoxEgg identify the root of the problem.

In fact, looking back over the thread, it's not clear whether the problem is that the SELECT doesn't work, or whether the correct results are returned but the grid isn't displaying them for some reason.

FoxEgg, I suggest that, right after your SELECT statements, you do a BROWSE. That will at least tell you if the query is working as expeced. Could you then let us know whether or not it is.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

I'm quite sure dan is right about the grid problematic. You can't rereate the recordsource of a grid. Besides a view, a Cursor adapter cursor can also be requeried without destructing the grid structure.

Bye, Olaf.

 

[mjcmkrsr]

Hi,
My recommendation
Have two TextBoxes on your Form TEXT1 and TEXT2

SELECT patient_episodes.Last_name, patient_episodes.first_name, patient_episodes.file_num ;
FROM patient_episodes ;
WHERE IIF(EMPTY(ALLTRIM(THISFORM.TEXT1.VALUE)),.T.,UPPER(ALLTRIM(THISFORM.TEXT1.VALUE))) AND ; IIF(EMPTY(ALLTRIM(THISFORM.TEXT2.VALUE)),.T.,UPPER(ALLTRIM(THISFORM.TEXT2.VALUE))) ;
ORDER BY last_name ;
INTO CURSOR crsTEMP

THISFORM.REFRESH()

HTH

Mark

 

[mjcmkrsr]

Sorry, forgot

...
into cursor crsTemp

With THISFORM.grdYourGrid
.Visible = .T.
.ColumnCount = -1
.RecordSource = "crsTemp"
EndWith

ThisForm.Refresh()

OR

use a parameterized view

hth

Mark

 

[mjcmkrsr]

Sorry, was too quick - third try

SELECT patient_episodes.Last_name, patient_episodes.first_name, patient_episodes.file_num ;
FROM patient_episodes ;
WHERE IIF(EMPTY(ALLTRIM(THISFORM.TEXT1.VALUE)),.T.,UPPER(Last_Name) = UPPER(ALLTRIM(THISFORM.TEXT1.VALUE))) AND ; IIF(EMPTY(ALLTRIM(THISFORM.TEXT2.VALUE)),.T.,UPPER(First_Name) = UPPER(ALLTRIM(THISFORM.TEXT2.VALUE))) ;
ORDER BY last_name ;
INTO CURSOR crsTEMP

With THISFORM.grdYourGrid
.Visible = .T.
.ColumnCount = -1
.RecordSource = "crsTemp"
EndWith

ThisForm.Refresh()

Should be ok now )

Mark

 

[Mike Lewis]

Mark,

If I've understood you right, what you have achieved is combining two SELECTs into a single statement. Nothing wrong with that, but I'm not sure if you have addressed the original problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[mjcmkrsr]

Hi Mike,

I suggest a solution to the two main issues FoxEgg is addressing.

I combine his 3 SELECTS's into 1

SELECT ...

and give a hint how to show the data in the grid


WITH ThisForm.grdYourGrid
...

ENDWITH

Anything else would be a guess since he does not specify further.

hth

Mark

 

[FoxEgg]

Hi everyone and thanks... I got a bit busier than I thought.... and you have all provided a lot of great suggestions. Thank you.

First of all, I have read all of your the suggestions... and you have expanded my knowledge of VFP and my options for solving my problem.

To answer some of the suggestions

1. I have all the entries in the database stored in uppercase...(not elegant but solves one problem)

2. I liked the two textbox option...

3. Yes ...if I remove into table temp.. I get a correct Browse table

The problem was that I wanted my secretaries to type in (and therefore search on) LAST_NAME and then a COMMA then FIRST_NAME and show the result in a grid. I could not get the darned grid to show the data

In the properties box, I did set the grid record source to temp... didn't work.
Tried alias, table, every one... no go...

Then I added Mark's bit of code (see below) after my last line of code.

And it worked

With THISFORM.grid1
     .Visible = .T.
     .ColumnCount = -1
     .RecordSource = "temp"    
EndWith

ThisForm.Refresh()

So then I tried

With THISFORM.grid1
     .RecordSource = "temp"    
EndWith

and it worked. I typed in SMI,JEA and the Grid showed SMITH JEAN. Whooopee!

Now I just have to work out how to get rid of it telling me that cursor temp.dbf already exists, do I want to get rid of it?


I suspect that is something to do with requery...


Thanks to all... I have no idea why I had to specify the record source programatically... There again I don't understand the Shroedinger Wave equation either.


By the way.. how do I stop VFP asking me to delete the cursor all the time...

Fox Egg

 

[Dan Freeman]

SET SAFETY OFF

(but be careful because this means REALLY off, and that includes when you actually want the warning.)

 

[mjcmkrsr]

Hi FoxEgg,

I would not SET SAFETY OFF rather put this code before the SELECT statement (since it's a CURSOR)

lcAlias = "TEMP"
If Used(lcAlias)
Use in (lcAlias)
Endif

SELECT ....

Btw you may want to choose a different name for the "TEMP" cursor - at long it might get too confusing

hth

Mark

 

[ChrisRChamberlain]

Not addressing the issues in this thread, if you need to close the cursor/table 'TEMP', you can simplify

lcAlias = "TEMP"
If Used(lcAlias)
   Use in (lcAlias)
Endif

to

USE IN SELECT([TEMP])

FAQ184-2483​

Chris
PDFcommander.com
motrac.co.uk

 

[Mike Lewis]

I recommend always having SET SAFETY OFF in applications.

If the user sees a prompt asking them if they want to overwrite a table or file, at best they will be anxious. At worst (and most likely), they will play it safe and answer No, in which case the subsequent logic will almost certainly fail.

Better to avoid the situation by making sure the prompt doesn't appear.

Of course, it's a different situation when you are working with your own data in the development environment.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Dan Freeman]

I would not SET SAFETY OFF rather put this code before the SELECT statement (since it's a CURSOR)

It's not a cursor if the user is getting a message about overwriting a dbf (which is what was reported).

 

[FoxEgg]

Thanks again to all...

But I don't believe that I have specified that temp is a dbf... it is not in the DataBase nor can I see that I have defined it as a table anywhere!

So if I understand Dan... I shouldn't get this message with safety either set to ON or OFF Correct ?

BUT if I... SET SAFETY OFF.. it will fix my problem... which is 'heuristically speaking' all I want.

Let me get back to you after I try one or two of your great suggestions.

John Fox


PS I want a medal for my 'heuristically speaking' comment... I am convinced that it must be a first on VFP Tek-Tips.

 

[Dan Freeman]

But I don't believe that I have specified that temp is a dbf...

Your original queries posted in this thread use the syntax INTO TABLE TEMP.

INTO TABLE creates a dbf. It cannot NOT create a dbf.

Unless you've changed your destination, you're creating a dbf.