USING TEXT VALUE IN FOXPRO 1

[spysab]

Hi Guyz!

Im new in foxpro and just relying on modification of whatever codes i see working. Can you teach me how use textbox value for this code :

SELECT brand,type1,description,group from itemlist WHERE description LIKE '%XXL%' AND TYPE1 = "Modular"

i want '%XXL%' and "Modular" to came from a value of a textbox in a form "itemlist" so i can create different query from the form itself.

Thanks.

 

[Mike Lewis]

Welcome to the forum - and to Visual FoxPro.

I am assuming that you want to run your code from within the form, that is, from one of the form's methods. I'm also assuming you have two textboxes on the form: one containing the description and one containing the type.

If that's right, you could to this:

SELECT brand,type1,description,group from itemlist ;
  WHERE AT(THISFORM.txtDesc.Value, Description) > 0 AND TYPE1 = THISFORM.txtModular.Value

where txtDesc and txtModular are the names of the respective textboxes.

You say Itemlist is the name of your form. But your code indicates that it is also the name of the table which you are querying. Is that right?

Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Depends on where this SQL-Select is.

If the query is within a form object (eg in the Click event of a command button) you can address the text value of a textbox with ThisForm.TextBoxName.Value (or similar - controls on a form can nest in container controls and then the full addressing becomes more complex). So you need to find out the name of the textbox. When you write code in any form element intellisense will help you see one hierarchy level down when you start from THISFORM. You might also use addressing starting from where you are, bubbling up: This.Parent.TextBoxName.Value. But I'll not deep dive into what to use when.

If the query is not part of the form you can't use ThisForm...something...Value to address the textbox, so the value has to be passed to the code with the query in some way.

That alone makes it recommendable to never use any of these control addressing directly within SQL queries. You want your query to be parameterized, so you're using variables for parameters, and though that makes it a multiple step process it's very adaptable to whatever situation you're in.

So the plan to make use of is:
1. Declare variables (LOCAL lcDescription, lcType1)
2. Set them to the values you need (lcDescription = ThisForm.txtDescription.Value)
3. Use them in the SQL:

SELECT brand,type1,description,group from itemlist WHERE description LIKE '%'+lcDescription+'%' AND TYPE1 = lcType1

But the real fun begins, when you realize how you can even strip off the necessity to know the textbox name addressing (step 2), which means you may later nest the same textbox in containers or unnest them, put them on a pageframe, etc. - as UX demands are. And don't need to adjust the code. And secondary even more fun, that you can pack all necessary variables into just one, so even in case you need to pass them somewhere else, this just becomes one parameter. You can do this, when you make use of the easy control data binding VFP offers not just for tables but also for variables or object.properties as the source/sink of the controls display values.

In such forms to filter data with user input, you can always apply the same pattern, you create a single variable, an object, that has all the query parameters as its properties and add that to the top form level, that'll be put into the form.init:

Local loQueryParameters
loQueryParameters = CreateObject("empty")
AddProperty(loQueryParameters,"cDescription","") && may set this to a default value other than empty string here
AddProperty(loQueryParameters,"cType1","")
Thisform.Addproperty("oQueryparameters",loQueryParameters)

You set your description textbox controlsource to Thisform.oQueryparameters.cDescription and the type1 textbox controlsource to Thisform.oQueryparameters.cType1

Now anywhere the textboxes are on the form, they always feed ThisForm.oQueryparameters properties with their value and you can use them in your query by grabbing that one object back into loQueryParameters and use that in the SQL query:

Local loQueryParameters
loQueryParameters = ThisForm.oQueryparameters

And then use that in your SQL Query:

SELECT brand,type1,description,group from itemlist WHERE description LIKE '%'+loQuerypaarameters.cDescription+'%' AND TYPE1 = loQuerypaarameters.cType1

If you don't want to start out with that much changes just pick out the addressing of form elements goes like ThisForm.Objectname and what controls display usually will be in the Value property of them.

There's much more to say, I just limit myself to the coolest perhaps: You can pass around this one variable loQuerypaarameters as a parameter to another form or a PRG or anything, and wherever the query then needs it it's just one parameter, as it's an object with the N properties you need. So that's a very versatile solution to generally decouple SQL from UI via parameter variables, in this case in the form of a parameter object variable with all the necessary properties.

The only thing that needs VFP9 is CreateObject("empty") which creates an object with nothing, no init, no methods, events or properties, so it'll only have your query parameters. If you have an older VFP version you can use CreateObject("custom"), then also use loQueryParameters.AddProperty(propertyname,value) instead of AddProperty(loQueryParameters,propertyname,value), the rest of the idea works the same also back to perhaps VFP2.6 for Windows. I'm sure at least to VFP6. VFP SQL always enabled us to use easy variable parameterization which also is the foundation of parameterized views, where you just add a questionmark before the variable name. It's just another reason to use this, as it enables you to define views on the basis of that code, which won't work when you write form control addressing into the query.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[spysab]

sorry guyz ive been to an accident thats why i haven't get back to this thread.
thanks and will study all your replies so i can work smoothly as beginner in using foxpro.

Actually im running the code just in its command window. i dont have any forms yet. but eventually that's my purpose. to use it in a form. so i just get an answer in advance.

thanks guyz. your replies really helps.

 

[Olaf Doschke]

If you're looking for the easiest way of user input for query parameters, the simplest way to ask for input is INPUTBOX(), look up how to parameterize it.

You asked from the other end of having a given textbox and thus form.

VFP also automatically pops up inputboxes for paremeters of views, if you don't predefine their values before using a view (and thereby executing the sql query of it). Look up CREATE SQL VIEW.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

And as you say you inherited code and try to manage to get on with it by making some changes as far as you're knowing or are guided, it's typically legacy code that defines screens as far as my understanding of it goes using things like @SAY to display something and @GET to define inputs. Workflow controlled by READs. Mainly, you're not having a modern UI that lets you decide in which order what to use, the code allows branching with IF and CASE statements but mainly is a simple single main thread of code executing sequentially.

You have two chances of conversion, either turn screens into forms and maintain the visual look but not the functionality or keep the functionality but not the layout or you run the legacy code as is.

It's not a great time to learn legacy coding, there are few video tutorials, there are of course ton of articles and magazines and books, but the least of them about legacy developing or maintenance.

If you really want to use forms you should start from scratch, the conversion methods are not just "less ideal", they are quite catastrophic, so people often stay with the legacy code.

If you ever want to start over you might keep some calculations, but in the end, you could also start over in something more modern, Winforms itself is a bit outdated in a web dominated world and while there is no switch of apps on the desktop toi things like WPF, there is a switch away from desktops.

Last, not least, a "warning": You will likely not get the help you need to adjust your legacy code here unless you explicitly specify or post legacy code. We're working on the level of Visual Foxpro and object-oriented programming and one might argue how OOP some work is, the visual aspect and the ease of the visual designers is clöearly known, we wouldn't talk so much about events and methods mostly relevant for visual classes in VCX libraries or at least SCX forms. I'm quite sure you can mix in SCX forms into screen code and DO FORM some.scx to branch off into a modal form, for example. But you get a strange mix of incompatible worlds of ways of coding in Foxpro.

We do our best, but you should know you're in a minority here and even of experts not all of us may be able to help in the best way possible.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name