Almost every complete application will require a composite query. Building a powerful composite query must first be able to dynamically generate query conditions, and secondly should be able to modify the data to be queried, the last compound query is best to set up a one-to-many two table conditions for query.
In VFP, there are several methods to establish a query: one is to use the Searchclass class in VFP; the other is to create a query; The third is to establish a view, including parameterized view, macro substitution SQL statement view; Four is to build a grid, Sets its data source to an SQL statement or a temporary table.
Either way, the essence of this is the use of SQL statements.
Each of these methods has its own advantages and disadvantages.
The most rigid way to establish a query is to establish a fixed-condition query and not update the data, most of which cannot meet the requirements.
The Searchclass class is powerful, but it can only create conditions for a table to query, and its source code is too complex, almost difficult to modify customization; (beginners must have had the experience of trying to modify the Txtbtn class, Searchclass class after using the Form Wizard to create the form!) After seeing the source code, how many haven't fainted? It is also difficult to refresh the data by setting the grid's data source as a SQL statement or temporary table to modify/update the data. (This aspect of the problem in the NetEase Virtual Community VFP Edition has been a lot of discussion, we can go to see.) The parameterized view is also too simple in the way that the view is built. Whether you use the value of a form control as a parameter or quotation marks at both ends of a parameter, you can only query for a fixed field. If it's a composite query, do you want to create dozens of views first? The most promising approach is to replace the SQL statement with a macro to create a view. The view has the advantage of being able to modify/update the data, if the query condition can be dynamically generated, then it is the most perfect query. The way to create a macro to replace a SQL statement view is to dynamically generate an SQL statement SQLStatement, and then use the Create SQL view viewname as &sqlstatement to dynamically create a view using the macro substitution method. Finally, the data is dynamically displayed in a Grid control. See here, VFP prawns are afraid will shout: stop! you when I am a rookie Ah! Your approach is theoretically possible, but in practice you will encounter the difficulty of refreshing the data on the table. I've already tried! You want to cheat royalties Ah! Hey, this problem has been solved for me! That's why I'm so smug about writing this article! Solution to the problem
A few months ago (poor ^0^ ...), I refer to VFP's sample in the solution interactively Bulid a SQL
The statement example creates a composite query that you want to integrate into my own program. Because the example uses the Browse window to display the results of the query, and my own application used the top-level form, the results were compiled and run to discover that the Browse window was not visible in the top-level form. So I set up a form set with two forms, a form makesql dynamically generate SQL statements, in another form Form1 on the grid to display the results of the query data, in the grid to set the data source, when the problem comes. First use the temporary table as the data source of the table, the result of the first query is correct, change the condition for the second query encountered a well-known "Can't update temporary table" error; Use SQL statements, but it will be weird before the table shows the data, a browse window appears. The table will not be displayed until it is closed, as the Browse window cannot be displayed in a program that uses the top-level form, which results in the program not being able to continue execution.
Finally, you can only use the Create SQL view ViewName as &sqlstatement method, first set up a view tempview, the table RecordSourceType property set to 1-alias, The RecordSource property is set to the view alias Tempview,
Create a view tempview using the Create SQL view Temp view as &sqlstatement in the code after the SQL statement is established on the form Makesql.
After the execution found that the first query is correct, change the query criteria after the query again, appear "View already exists, want to overwrite it?" "In the case of" OK ", there is no data in the table that appears.
Avoid the problem of dialog box to resolve, before building a view with rename view Tempview to Oldview, and then use the delete view Oldview to delete the old view. The code is as follows:
Set database to DatabaseName &&databasename is your name
&& Note: You must write this statement even if you open the database! Otherwise, a "database cannot be found" error occurs.
If used ("Tempview")
Rename View Tempview to Oldview
Delete View Oldview
endif
Create SQL view Tempview as &sqlstatement
=requery ()
IF _tally = 0
#DEFINE Msg_loc "did not find a record of qualifying!" "
But after doing so, the problem with no data on the table still exists. Find the data and find that the Create SQL view statement is used to edit
Process to establish a view, after you have set up a view to save the view definition, and then open the view after the view has data. Therefore, the creat must be
The SQL view statement part of the code is modified as follows:
************************************************
Create SQL view Tempview as &sqlstatement
Use
Use Tempview
************************************************
Full of thought that the problem was solved, the result is even worse. There is no data on the table, even the head and grid are gone! This question
Baffled, look for information and no results, finally, nothing, has been bothering me for several months.
Just last night, when I was in bed, I had a sudden epiphany: now that the table cannot dynamically load the data source view, simply include the table's
Forms are also generated dynamically! As long as the form is dynamically generated, does the data source for the Table object on the form be completely reloaded? There's no need to refresh or anything! Moreover, this method does not need to first establish a tempview view, completely in the program dynamically generated on the idea must, immediately get out of bed. Delete the form Form1 that originally contained the table, the last sentence in the code above
Insert the following code before Thisformset.form1.show:
Is it always okay? Run the program, resulting in a dialog box "Cannot nest class definition in events or methods!" "。 I @#$%&* .... What are you doing here? Examples PRG in textbooks and help files are written in this way!
But fortunately there is a way, I manually set up a class head of the bar! Create a New button class Cmdreturn in the class library mybut, set its cation property to "Back", click event code
Thisform.release. Insert the set Classlib to mybut additive before the code above (note: If the additive parameter is not added, all previously open class libraries will be closed!) , and then define the final class definition statement ... Enddefine all deleted. Run, new form appears! Wait a minute, why is there nothing on this form? :-((Open the debugger, in the "local" window to see, found clearly there are cmdReturn1, Grid1 object Ah! What's going on? Look at each of their properties and find that their visible properties are false! Originally, examples of the help we normally see are PRG files in which objects added to the form by the AddObject () method are visible after the form is displayed. And in the form of the Scx file in the AddObject () method to build anything, its visible properties are false! in essence, the CreateObject (), AddObject () method created object, in fact, only in memory to create an object variable, You must use statements to instantiate them again. For example, we often use the Mainform.show statement is so, not using the Show method, MainForm is just a variable in memory, not a form is not visible instances! Conversely, the Thisform.release statement is from a fundamental
All the object variables on the form are released on the this completely clears the form in memory. The order in which forms and controls are established with the AddObject () method in the PRG file is not the same as in the Scx file, where you add controls to the form and then display the form in the PRG file. Controls on the form inherit the Visible property of the form, and the control is instantiated when the form is instantiated, and the AddObject () method in the Scx file is to display the form before adding controls to the form, so you must manually set the control's Visible property to Ture.
Of course this is more troublesome, simply create a Resultform form class in the Mybut class library, add a command button Cmdreturn and a Grid1 on the form class, set the Cmdreturn property of the command button caption to "Return", Click event code is thisform.release, set Grid1 RecordSourceType property is 1-alias, RecordSource property is Tempview, so
It's much more convenient not to manually enter THISFORMSET.FORM1.CMDRETURN1.VISIBLE=.T in your code.
Run the program, everything ok! finally realized the dynamic generation of query conditions, dynamic display results. The query results can be modified/updated as long as the Dbsetprop () statement is used to set the view as updatable. This is the most perfect compound query I've ever seen! Is it that simple? Yes, it's that simple! A problem for months, the study of the time, the end result is so easy! This is the art of programming!
The solution to this problem, although took a lot of detours, but also let us understand the principle of many vfp, is not very worthwhile?! You will never know these things by the knowledge of books. Some people say: Read three years of the book, but also write one months of procedure, is not it?! Other:
I omitted in this article the beginning of the dynamic generation of SQL statements, the specific approach you can study the VFP from the sample application solution databases directory in the View/queries directory of interactively Bulid a SQL Statement example, you can even make a slight change to use the form in your own program.
I have written an article on its principles, "Interactive building SQL Composite Query-VFP sample application detailed" posted in NetEase Virtual Community VFP Edition, interested friends can be found in the elite area, read the article, you should be able to modify the program to enable it to query a one-to-many database. Because the text is very long, and more boring, it is not here to explain.
Note that the original sample program is used to generate SQL statement queries. To be used to establish the SQL view, you must make some modifications: 1. You do not have to qualify the table alias and database name in SQL queries, but you must do this in order to create an SQL view. Therefore, you need to modify the Makesql form's custom method bldsql code, the source code below the section:
DatabaseName is the name of your database. The essence of the above modification is to qualify the table alias for the field name to be queried, giving
The table alias of the Select form qualifies the database to which it belongs.
2, modify the RunSQL command button click event code, the original code:
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
