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:
Code:
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:
Code:
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:
Code:
Local loQueryParameters
loQueryParameters = ThisForm.oQueryparameters
And then use that in your SQL Query:
Code:
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
