Using GRID with SQL Statement

[stanmurphy]

Hello all,

I have a form with a grid. The recordsource for the grid is a SQL statement. RecordsourceType is set to SQL Statement.

The SQL statement is:

Select x.clientid, x.apptdate, x.appttime, y.counname, x.appttype from appointments x left join counsellor y on x.counname = y.counname where x.clientid=m.clientid noconsole

In the Init of the form I have:

thisform.grdAppoint.recordsource=" "

to refresh the grid each time the form is run.

When I DO the form, I get a browse window showing the SQL results overlaying the form, and when I ESC from the browse, the grid is empty (rows and columns but no results).

What am I doing wrong?

 

[Mike Gagnon]

smurphy

thisform.grdAppoint.recordsource="" && no space


Select x.clientid, x.apptdate, x.appttime, y.counname, x.appttype from appointments x left join counsellor y on x.counname = y.counname where x.clientid=m.clientid INTO CURSOR MYCURSOR

thisform.grdAppoint.recordsource="MYCURSOR"
Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first

 

[danceman]

other options
put your select statement in the load event of the form. this happens before the form objects are initialized.

like mike said you forgot to use the into cursor mycursor

then in the design of the grid you can use alis as the source an assign it to mycursor. with the select in the load event the cursor will be there when the grid becomes active. Attitude is Everything

 

[keepingbusy]

Mike

I know this thread goes back some, but having searched through the forum, it has almost helped with a sollution. Can you advise one thing:

I have a form with a pageframe and three pages

Page 1 is a normal view. I have inserted the below code into the click event on page 3 of the pageframe which shows any records that have a blank TITLE

thisform.pageframe1.page3.GRID1.recordsource="" && no space
SELECT ARTIST, TITLE, MEDIA, SELL_PRICE FROM LISTINGS ;
  WHERE EMPTY(TITLE) INTO CURSOR MYCURSOR
thisform.pageframe1.page3.GRID1.recordsource="MYCURSOR"

THISFORM.pageframe1.page3.GRID1.REFRESH
THISFORM.pageframe1.page3.GRID1.SETFOCUS()

The database only has four fields ARTIST, TITLE, MEDIA, SELL_PRICE

The problem is, when you click into the TITLE column where its blank to try and add text to it, you cant. The grid is NOT read only.

Please can you suggest anything?

Many thanks
Lee

Alone we can do so little, together we can do so much

http://www.customsoftware.co.uk

 

[puruawade]

Lee,

Cursors are read only. Try INTO TABLE/DBF instead, in SELECT querry. I bind editable grid to a table.

Puru

 

[Mike Gagnon]

Puru

Cursors are read only

A small correction, not all cursors are read-only

SELECT ARTIST, TITLE, MEDIA, SELL_PRICE FROM LISTINGS ;
  WHERE EMPTY(TITLE) INTO CURSOR MYCURSOR [B]READWRITE [/B]

This would make it a writeable cursor.



Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[MarciaAkins]

Hello KeepingBusy.

>> Page 1 is a normal view. I have inserted the below code into the click event on page 3 of the pageframe which shows any records that have a blank TITLE <<

Since it appears that you want to update the Records in the grid's RecordSOurce, you would be much better off to create an updateable parameterized view for the grid's recordSource. You can set one up very easily using the View designer and you can even drop it into the form's DE and set its NoDataOnLoad property to True until you are ready to populate it with data by issuing a REQUERY() on it.

In my opinion, this is a much better solution than the cursor.





CODE
thisform.pageframe1.page3.GRID1.recordsource="" && no space
SELECT ARTIST, TITLE, MEDIA, SELL_PRICE FROM LISTINGS ;
WHERE EMPTY(TITLE) INTO CURSOR MYCURSOR
thisform.pageframe1.page3.GRID1.recordsource="MYCURSOR"

THISFORM.pageframe1.page3.GRID1.REFRESH
THISFORM.pageframe1.page3.GRID1.SETFOCUS()


Marcia G. Akins

http://www.TightlineComputers.com

 

[keepingbusy]

puruawade

Thanks for your contribution but I'm not sure what you mean by the suggestion you've posted. Could you please explain?

Mike

When I add READWRITE as you've suggested I receive an error message "Command contains unrecognized phrase/keyword"

MarciaAkins

"Since it appears that you want to update the Records in the grid's RecordSOurce, you would be much better off to create an updateable parameterized view for the grid's recordSource. You can set one up very easily using the View designer and you can even drop it into the form's DE and set its NoDataOnLoad property to True until you are ready to populate it with data by issuing a REQUERY() on it"

Sorry, I'm not sure what this means

Lee.....

Alone we can do so little, together we can do so much

http://www.customsoftware.co.uk

 

[puruawade]

Lee,

Sorry!! For the last month I wasn't in contact with Tek-Tips. Hence the delay.

If you haven't yet resolved your problem, here is what I do.

Since whatever changes user makes are supposed to be stored in the database, I bind the grid to the table itself using build option of rightclick menu. This grid is on page 2 of my pageframe.

On page 1 are the objects for updating the field values. User first gets to see page2 with the grid. When he doubleclicks on a row of his choice, existing field values get stored to the objects. After making changes he clicks on SAVE button which UPDATEs the table.

Now you have the page 3 showing records where TITLE is blank. This can be achieved by issueing SET FILTER TO title="" command. For other pages reset SET FILTER TO.

Problem with cursors in versions older than VFP8 is that the cursors are read only. That is why you are be getting that error. What Mike Gagnon has suggested is possible only in VFP8.

I have also added few interesting features to these forms. Check my thread on SORTING THE GRID ON MULTIPLE COLUMNS. I am not versed with the tags hence cannot provide you the link. But click on my handle and then my threads.

Hopefully you will get some new idea.

Puru

 

[MarciaAkins]

Hello Lee.

What I mean is this. Create a Parameterizher by using the View Desingener or in code like this:

CREATE SQL VIEW lv_Appt AS Select x.clientid, x.apptdate, x.appttime, y.counname, x.appttype from appointments x left join counsellor y on x.counname = y.counname where x.clientid = ?vp_clientid

To make the view updateable, if it is created in the designer, there is a check box. To do so in code, see the DbSetProp() function documentation in the on-line help.

Actually, the on-line help is very good with respect to the documentation on parameterized views in particular as well as views in general.

Anyway, once the view is defined, in can be dropped into the form's DE just like a dbf. Set its NoDataOnLoad property to .T. The use this as the grid's RecordSource (RecordSOurceType = 1-Alias. Now, when you want to change the content of the grid, all you need is 2 lines of code:

vp_clientid = SomeValue
REQUERY( 'lv_APPT' )

Does that help to clear things up for you?



Marcia G. Akins

http://www.TightlineComputers.com

 

[keepingbusy]

Hi puruawade & MarciaAkins

Thank you for your replies and I'll post back soon. I did resolve the issue in another way but I'm open to suggestions like yours if it simplifies the problem

Kindest regards

Lee......

Alone we can do so little, together we can do so much

http://www.customsoftware.co.uk

 

[puruawade]

Hi Keepingbusy !!

You can share your solution with the forum members who shrare their ideas with you. If not code, at least algorithm or concept.

Puru

 

[keepingbusy]

Hi puruawade

Firstly, sorry for not posting, like your previous posting "Sorry!! For the last month I wasn't in contact with Tek-Tips. Hence the delay." I get busy and somethings get side tracked.

After a discussion it was agreed that we didn't need any TITLES that were empty so I managed to resolve the issue by simply placing a command button on the form with the following in the click event:

nmessage=MESSAGEBOX("Confirm, Delete All Titles That Are Empty?"+space(5),4+64+256,"System Message"
IF nmessage=6
  DELE FOR EMPTY(TITLE)
ENDI
PACK
SET ORDER TO TITLE
GO TOP
THISFORM.REFRESH
THISFORM.GRID1.SETFOCUS()

Hope that clears it up
Kindest regards
Lee

Alone we can do so little, together we can do so much
(VisFox Version 6 User)

http://www.customsoftware.co.uk