data environment problem 2

[funghaifeng]

hi there
i'm a newbie in VFP 9.0 programming and now i had a problem in my code..

i have 3 form in this project (not all, but this 3 is the problem)

form 1 called form 2, and form 2 called form 3..
form 2 and 3 doesn't use any data environment because i've read that if the base form had a table, the other form that called form that form will acknowledge the table too..
so i place all my tables in form 1 (because that form using all the table) and connect all the tables with relation
here is the problem :
when i called form 2 the data still appear, but when form 2 called form 3, data won't show up, even if its just use 1 table from form 1.
i've tried to give form 3 a table for itself, but it won't work..
is it because the relation so the data locked up in some position?or what?

thx for helping ^^

 

[Olaf Doschke]

Instead of:

SET FILTER TO ALLTRIM(UPPER(thisform.txtnmobat.Value)) $UPPER(t_obat.nama_obat)

Try this:

Local lcFiltervalue
lcFiltervalue = ALLTRIM(UPPER(thisform.txtnmobat.Value))
SET FILTER TO '&lcFiltervalue.' $ UPPER(nama_obat) IN t_obat

It's all about scope:
Because when form3 is active, thisform is not form1, but form3, this makes the filter not work, most probably. Setting the filter the way I showed is independant on the current form.

Using macro substitution within quotes makes it a literal value, which is staying the same, even if you switch to other forms and the txtnmobat control gets out of scope and/or the variable lcFiltervalue gets out of scope. SET("FILTER") will be a filter condition as 'any userinput' $ UPPER(nama_obat)

You may want to disallow ' in the user input for reasons of breaking the filter condition. For example you could do lcFiltervalue = chrtran(ALLTRIM(UPPER(thisform.txtnmobat.Value)),"'","")

Last: I would recommend to not work with filters, but with queries, but that's a totally different story.

Bye, Olaf.

 

[funghaifeng]

olaf

thx for the advice, like you said. i am going to use queries that mike gave me,, ^^
thank you thank you, i really appreciate your help..

mike..

i'm sorry if my attitude disturbing you..i didn't mean to rushing you or what. but you must understand that i am in indonesia, which is GMT +6 .. , so it's in the middle of the night here and i have waiting for your answer from the morning , but it's allright i know you bussy too, sorry to disturb you..
and i'm sorry too if my english was a mess, i'm not so fluent in this language..

i'm gonna try what you suggested and will keep you informed about this.. thank you..

 

[Mike Lewis]

No need to apologise, Funghaifeng. I also know how difficult it can be to work across multiple time zones.

Let us know how you get on with my solution.

I think the important thing is to move away from filters. SET FILTER is very useful, but it can cause problems if you are not familiar with it - especially when used with grids.

I hope you manage to get a good night's sleep.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[funghaifeng]

hi lewis..
it's worked..
but it can't be used in my project, because the grid supposed to show all data at the first run, and the searching method is not by clicking a button but with interactive change, thats the requirement.
so i'm trying to adapt your code to my needs it become like this

thisform.txtnmobat.interactivechange :
------------------------------------------------------------------
IF NOT EMPTY(this.Value)
SELECT * FROM t_obat WHERE ALLTRIM(UPPER(nama_obat)) like

ALLTRIM(UPPER(this.Value)) INTO CURSOR csrResults

IF RECCOUNT("csrResults") = 0
thisform.grdT_obat.RecordSource = ""
thisform.grdT_obat.Refresh
ELSE
thisform.grdT_obat.RecordSource = "csrResults"
thisform.grdT_obat.Refresh
ENDIF
ELSE
SELECT * FROM t_obat WHERE !EMPTY(nama_obat) INTO CURSOR

csrResults
ENDIF
------------------------------------------------------------------

form3.init
------------------------------------------------------------------
SELECT * FROM t_obat WHERE !EMPTY(nama_obat) INTO CURSOR csrResults

&& the where clause doesn't make any different, its there because i found that INTO clause wouldn't work without WHERE

Thisform.grdT_obat.RecordSourcetype = 1
Thisform.grdT_obat.RecordSource = "csrResults"

&& this code work to show all data to grid, but i can't control how much column to show and their header format which is necessary
------------------------------------------------------------------

like you did earlier, i just wrote it off the top of my head cause now i felt very sleepy already (it's 01.00 A.M),and i'm sitting in front of my computer for almost 15 hours, i think i need some rest. we'll continue this tommorow (if you have some free time of course..)
thank you..

 

[Mike Lewis]

Again, I think you're making this too complicated.

If the grid needs to show the all the data when you first launch the form, all you need to do is to create the cursor in the Init, but with no WHERE clause, like this:

thisform.Grid1.RecordSource = ""
SELECT * FROM t_obat ;
  INTO CURSOR csrResults
thisform.Grid1.RecordSource = "csrResults"

I'm not convinced that this a good approach. If the user enters a non-existant value, or simply leaves the textbox blank, the grid will be cleared, which might be confusing. If you didn't clear the grid in those circumstances, it would look as if the previous search results were the results of the most recent query, which they are not.

But those are user interface issues, which you will have to decide for yourself.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

i found that INTO clause wouldn't work without WHERE

untrue.

Select * from table INTO cursor xyz

INTO needs a) CURSOR b) ARRAY or c) TABLE as a destination, but no WHERE clause. And if you want a where clause making no difference to a missing WHERE clause, then do WHERE .T.

Bye, Olaf.

 

[funghaifeng]

Olaf :

really? then why when i write down SELECT * FROM table INTO CURSOR xyz, it's always shown a message box that said "Syntax Error", until i add a WHERE clause. then the message box disappear?

mike :

like i said, it's just my simple thought just to try your solution and i'm going to bed after that

umm, i think you miss something in my code mike, look at the textbox code and see inside the outer else clause, it's cover when the textbox is empty and it fill the grid with all data.

and you should know, that code in my text box, will make my grid completely BLANK, without any single line appear.

 

[funghaifeng]

mike

now the all the data can appear in the grid (thanks to you)
and i place this code in

form1.init :
------------------------------------------------------------------
SELECT nama_obat, harga FROM t_obat INTO CURSOR csrResults
Thisform.grdT_obat.RecordSourcetype = 1
Thisform.grdT_obat.RecordSource = "csrResults"
thisform.grdT_obat.column2.header1.Alignment = 2
thisform.grdT_obat.column3.header1.Alignment = 2
thisform.grdT_obat.column2.header1.FontBold = .T.
thisform.grdT_obat.column3.header1.FontBold = .T.
------------------------------------------------------------------

but now the problem is with the filtering, i can't filter the data anymore. i've tried to used the query that you gave. this is the code :

thisform.txtnmobat.interactivechange :
------------------------------------------------------------------
IF NOT EMPTY(this.Value)
LOCAL word
word = ALLTRIM(UPPER(this.Value))
SELECT nama_obat,harga FROM t_obat WHERE ALLTRIM(UPPER(nama_obat)) like "%word%" INTO CURSOR csrResults

IF RECCOUNT("csrResults") = 0
thisform.grdT_obat.RecordSource = "csrResults"
thisform.lblhasil.Caption = "not found"
thisform.lblhasil.Visible = .t.
ELSE
thisform.lblhasil.Visible = .f.
thisform.grdT_obat.RecordSource = "csrResults"
ENDIF
thisform.grdT_obat.column1.header1.Alignment = 2
thisform.grdT_obat.column1.header1.Caption = "Nama Obat"
thisform.grdT_obat.column2.header1.Alignment = 2
thisform.grdT_obat.column1.header1.FontBold = .T.
thisform.grdT_obat.column2.header1.FontBold = .T.
thisform.grdT_obat.column1.width = 275
thisform.grdT_obat.column2.width = 101
ELSE
SELECT nama_obat,harga FROM t_obat INTO CURSOR csrResults
thisform.grdT_obat.RecordSource = "csrResults"
thisform.grdT_obat.column1.header1.Alignment = 2
thisform.grdT_obat.column1.header1.Caption = "Nama Obat"
thisform.grdT_obat.column2.header1.Alignment = 2
thisform.grdT_obat.column1.header1.FontBold = .T.
thisform.grdT_obat.column2.header1.FontBold = .T.
thisform.grdT_obat.column1.width = 275
thisform.grdT_obat.column2.width = 101
thisform.lblhasil.Visible = .f.
ENDIF
thisform.grdT_obat.Refresh
------------------------------------------------------------------

it didn't work because everytime i type a character in the textbox, the grid becomes blank, and the label "notfound" show up eventought what i type is exist in table.
what am i exactly look for is to find data that CONTAIN a character or a word that inserted to the textbox, so i'm trying to used LIKE clause but it doesn't work (i've never used query to filter in foxpro before, i've always used filter function until now..)

when i used my old code, i made several change until it become like this :

------------------------------------------------------------------
IF !EMPTY(this.Value)
SET FILTER TO ALLTRIM(UPPER(this.value)) $UPPER(csrResults.nama_obat)
ELSE
SET FILTER TO
ENDIF
thisform.grid1.Refresh
------------------------------------------------------------------

the eror is "Function argument value, type, or count is invalid.
", and it pointed to "THISFORM.GRID1.REFRESH()

 

[funghaifeng]

it's done.....

my failt is in the LIKE clause, i changed it to LIKE('*'+cari+'*',ALLTRIM(UPPER(nama_obat)))

and it's work now...

thanks mike, i really appreciate your help..
thank you and have a merry Christmas ..

thank you Olaf and have a merry Christmas too..

 

[Mike Lewis]

Gald to hear you finally got it working, Funghaifeng.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

funghaifeng,

thanks and best wishes back to you.
Glad you finally figured something out.

In regard to the last question, the counterquestion is: What vfp version are you using?

SELECT * FROM table INTO CURSOR xyz

That sql you gave works perfectly for me in VFP6-9. And where are you doing this? Perhaps start in the command window. Make sure the previous line doesn't end in ; - that's all I could imagine would break that sql into a syntax error. Besides using nonallowed names, eg a space in a table name or a cursor or table name beginning with a number.

The where clause is optional, always has been.

Bye, Olaf.

 

[funghaifeng]

olaf

i used vfp 9.0..
maybe the fox is getting tired already because of 15 hours of use LOL
hahaha

 

[Olaf Doschke]

Forget my last question, you started this thread with "i'm a newbie in VFP 9.0 programming".

In VFP9 there is no problem. I couldn't imagine there would be in a vfp version earlier than vfp6, unless it would be as old as not having sql included in the foxpro language at all.

So, perhaps post your concrete sql statement in the form "SELECT * FROM table INTO CURSOR xyz", that is giving you a syntax error, and we'll see what's wrong.

In general, you posted to few things to really tell what should be ammended. Glad you found a way to make your forms work anyway. My post suggesting to change the filter must have overlapped with mikes suggestion to move to sql, a good move anyway.

Last not least: If you want to have somebody figure out a problem, foxpro helps you to easily post the full codebase of your problem. The class browser has a toolbar button called "view class code" that outputs a prg viewcode.prg that makes your whole class postable to forums as source code.

It's also usable with scx forms, not only with classlibs or classes, as the name suggests.

As a step by step guide:

1. open class browser from the tools menu
2. click on the "file open" toolbar button.
3. in the file open dialog change file type from visual class library to form if that applies to your forms.
3. choose an scx
4. click on the "view class code" toolbar button. It's an icon with an up arrow and two rectangles under it. It's left of the spyglass icon

You might shorten the output for a proper posting, of course it also doesn't help to post the whole thing, then you could rather provide . This would have helped a lot, sorry for mentioning it so late. Might be handy for the next time.

Bye, Olaf.

 

[funghaifeng]

olaf :

yeah, i realize now that i didn't know much about this VFP (such a shame)
but thanks anyway for your help olaf, i really glad i have meet guys like you and mike.. i really really appreciate it...

thank you again..
eventhough there is still much trouble in my project but i'm trying to fix it my self now, thank you..

see you next time

bye, alvin (funghaifeng it's just a nickname )

 

[Olaf Doschke]

>i didn't know much about this VFP (such a shame)
Noone is a know-it-all, that's not a shame.

It's also no shame to have errors in code or misbehaviour you can't explain. And I believe you with your syntax error. I still can say from profound experience it's not a general problem, if you don't mind.

Also it's hard to help, if you're not answering counter questions.

Bye, Olaf.

 

[funghaifeng]

err...

i'm sorry i you feel that i am neglecting your post or what. i didn't meant to do that
but maybe next time..