PB7.0 Universal Selection Field Query Window Design
This article describes a general-purpose query window implemented in PB7.0, which can be used to specify the query, output field, sort field, and query conditions of any table. Rich features, easy to use, flexible, and can be accepted from any PB application, thereby greatly improving software development efficiency.
This article is a sister article of "PB7.0 General Full Field Query Technology" (Computer World Weekly 2000 No. 3). Different technical solutions introduced by two articles, the style, form and usage of the query window is also different, but can be used at the same time and enrich the system function.
In PB7.0, the contents of the tables in the database typically display output with data window objects, and data window objects are generally established in advance. Can dynamically establish a data window object in accordance with the field specified by the user during the program operation, further, and then filter the record by the user specified by the user to achieve the purpose of the query? The answer is yes. We designed a window that not only implemented the above functions, but also acrounted to any PB application, query, sort, and printing any table, very practical value, is willing to share with readers. The following describes the implementation methods and specific steps.
First, ready
1. First build an application object Serch, and write the following code for its OPEN event:
Sqlca.dbms = ProfileString ("Pb.ini", "Database", "DBMS", "")
SQLCA.DBPARM = ProfileString ("Pb.ini", "Database", "DBPARM", "")
CONNECT Using SQLCA;
IF SQLCA.SQLCODE <> 0 THEN
MessageBox ("Database Connection Fail", Sqlca.sqlerrtext)
HALT / / Termination Application
END IF
Open (w_serch) // Open Universal Selection Field Query Window
2. Establish any database (for example, Adaptive Server Anywhere 6.0 Database Test.db), configure ODBC, Profile, and establish several tables required. Such as: NHXX (Farmers Information Table), TJZL (Production Information Table), etc. You can use any existing databases and tables.
Second, window design
The general query is implemented in the window, the technical core is in the control of the window.
First, create a window object W_Serch. It includes a data window control DW_1, a drop-down list DDLB_1, three command buttons CB_3, CB_4, CB_5, a single line edit box SLE_2 and a tag TAB_1.
A list box LB_1 is placed on the first tab_1 of the label Tab_1, a single line edit box SLE_1 and two command buttons CB_1, CB_2.
The window and the first tab tabpage_1 are shown below.
To make data transfer between the windows, declare the following four instance variables:
String s_tn // table name variable
String s_seq = "a" // Sort Multiple Variable (Default Assembly)
String s_cn, s_ct, s_cp // field name, field type, comparative variable
String T_C [50, 3] // All "Field Name", "Field Type" and "Field Title" in the Storage Table
The role of the drop-down list box DDLB_1 in the window is to select a different table. Set two items in its Item property: "Farmers Information" and "Production Information" as the table name prompt information. When a certain item is selected, the corresponding table name will be sent to the instance variable S_TN through the code. Different tables can be selected by modifying and adding ITEM items and corresponding code, thereby implementing queries for arbitrary tables. The "farmers information" and "production information" set here are two examples. When the option of the drop-down list box DDLB_1 changes, generate the SelectionChanged event, send the selected table name to the selected table name, the field name, field title delivery number group T_c, and the title of the table T_C, each field title. The list box LB_1, LB_2, LB_3 on the three tabs. Where the first subscript of the array T_C represents a field number, the second subscript value 1, 2, 3 represents a field name, field type, and field title, respectively. Here, the system table PBCATCOL, its field PBC_TNAM, PBC_CNAM, PBC_JTFY, and PBC_HDR values are stored in the table name, field name, field type, and field headings, respectively, respectively.
Integer I // Circulatory Variable (Array subscript)
Choose Case DDLB_1.Text // Determine the corresponding table name according to the table name prompt information
Case "Farmers Information"
s_tn = "nhxx"
Case "Production Information"
s_tn = "tjzl"
End chaoose
Declare Tab_col cursor for // Define Cursor
Select "pbcatcol". "PBC_TNAM", // Name
"pbcatcol". "PBC_CNAM", // field name
"pbcatcol". "PBC_JTFY", // field type
"pbcatcol". "pbc_hdr" // field title
From "pbcatcol"
WHERE "pbcatcol". "PBC_TNAM" =: s_tn;
Open Tab_col; // Open Cursor
i = 1
// field name, type, title port number
Fetch Tab_col Into: S_TN,: T_C [I, 1],: T_C [i, 2],: T_C [i, 3];
Do while sqlca.sqlcode = 0
/ / Set list information (field header)
Tab_1.tabpage_1.lb_1.additem (t_c [i, 3])
Tab_1.tabpage_2.lb_2.additem (t_c [i, 3])
Tab_1.tabpage_3.lb_3.additem (t_c [i, 3])
i
Fetch Tab_col Into: S_TN,: T_C [I, 1],: T_C [i, 2],: T_C [i, 3];
Loop
Close Tab_col; // Close Cursor
The first tab tabpage_1 lists the list box LB_1 lists the headings of the current table, used to determine the output field. In order to select a field by Windows Explorer, it should be set to the multislect property.
The function of the command button CB_2 is to convert the field title in LB_1 into a field name, forming a field table to put into a single line edit box SLE_1. Its Clicked code is as follows:
INT K // Cycle Counter
For k = 1 to lb_1.totalitems () // number of list items
If lb_1.state (k) = 1 THEN / / This item is selected
IF SLE_1.TEXT = "" "" "
// Get field name
SLE_1.TEXT = T_C [k, 1]
Else
// Splicing column name
SLE_1.TEXT = SLE_1.TEXT "," T_C [k, 1] endiff
END IF
NEXT
The function of the command button CB_1 is to clear the content of the single line edit box SLE_1. Clicked code only one line:
SLE_1.TEXT = "" "
The second tab tabpage_2 is shown below. There is a list box LB_2, a single line edit box SLE_3, a command button CB_6, two radio buttons RB_1 and RB_2.
The list box LB_2 lists the titles of the fields of the current table. When you click on one, create the SelectionChanged event. The following code get the field name, add the S_SEQ variable value ("A" or "D"), and form a sorting string to place the single line edit box SLE_3.
Integer K // Circulator
For k = 1 to lb_2.totalitems () // Total number of items list
If lb_2.state (k) = 1 THEN / / This item is selected
IF SLE_3.TEXT = "" "" "
// form a sort string
SLE_3.TEXT = T_C [K, 1] " S_SEQ
Else
// Splicing sort string
SLE_3.TEXT = SLE_3.TEXT "," T_C [K, 1] " S_SEQ
END IF
END IF
NEXT
The radio button RB_1, RB_2 is used to set the value of the sort mode variable S_SEQ, and the Clicked code is
s_seq = "a" (ascending) and
s_seq = "d" (descending)
The function of the command button CB_6 is to clear the content of the single line edit box SLE_3. Clicked code is
SLE_3.TEXT = "" "
The third tab tabpage_3 is shown in Figure 3. It places a list box LB_3, a drop-down list box DDLB_2, two single line edit box SLE_4 and SLE_5, two radio buttons RB_3 and RB_4, two command buttons CB_8 and CB_9.
The list box LB_3 lists the titles of the current tables. When you click on a certain item, generate the SelectionChanged event, send the corresponding field name, field type, and the field type separately from the following code.
INT K // Cycle Counter
For k = 1 to lb_3.totalitems () // number of list items
If lb_3.state (k) = 1 THEN / / This item is selected
S_CN = T_C [K, 1] // Remove the field name
S_CT = T_C [K, 2] // Remove the field type
END IF
NEXT
The drop-down list box DDLB_2 lists the description of the common relational operator (comparison). After selecting one, generate the SelectionChanged event, get the corresponding relationship operator to send instance variables S_CP.
Choose Case DDLB_2.Text
Case "equal"
s_cp = "="
......
Case "contains"
S_CP = "in"
End chaoose
The single line edit box SLE_5 is used to enter the comparison.
Click the button CB_8 (selected), form the conditional expression string to send the single line edit box SLE_4 by the following code.
IF S_CT = "24" THEN
// form a conditional expression string (numeric class)
SLE_4.TEXT = SLE_4.TEXT S_CN S_CP SLE_5.TEXTELSE
/ / Form a conditional expression string (character class)
IF s_cp = "in" THEN
SLE_4.TEXT = SLE_4.TEXT "POS (" S_CN ", '" SLE_5.TEXT "")> 0 "
Else
SLE_4.TEXT = SLE_4.TEXT S_CN S_CP "'" SLE_5.TEXT "'"
END IF
END IF
When forming a conditional expression string, the value class is determined if the field type is a numeric class or a character class. Field type 24 corresponds to binary, integer, numeric, 25 corresponds to char, varchar, date type. If the comparison is "in", it means "containing" relationships, and the POS function is used in the conditional expression string.
The radio button RB_3, RB_4 is used to add a logical operator in SEL_4, and the Clicked code is
SLE_4.TEXT = SLE_4.TEXT "AND" (Add logic operator) and
SLE_4.TEXT = SLE_4.TEXT "OR" (Add logic operator)
The function of the command button CB_9 is to clear the content of the single line edit box SLE_4. Clicked code is
SLE_4.TEXT = "" "
The "Query" button CB_3 in the window is to form a SQL syntax based on the selected table name and the first tab SLE_1, dynamically establish a data window object, and then use the second tab to edit box SLE_3 Sort and filter the contents of the single-line editing box SLE_4 on the third tab page to achieve the purpose of the query. code show as below:
String S_SQL, S_ERR, S_SYN, S_STY
S_SQL = "SELECT" TAB_1.TABPAGE_1.SLE_1.TEXT "from" S_TN
S_Sty = "Style (Type = Grid Horizontal_Spread = 80)"
s_syn = sqlca.syntaxfromsql (s_sql, s_sty, s_err) // forms SQL syntax
DW_1.CREATE (S_SYN) // Establish a data window object
DW_1.settransobject (SQLCA)
DW_1.Retrieve () // Output result
DW_1.setfilter (tab_1.tabpage_3.sle_4.text)
Dw_1.filter () // Filter
DW_1.SETSORT (Tab_1.tabpage_2.sle_3.text) // Sort
DW_1.SORT ()
SLE_2.TEXT = String (dw_1.rowcount ()) // Displays the number of records
The "Print" button CB_5 in the window is DW_1.Print ().
The "exit" button CB_4 in the window is Close (Parent).
Third, use
The front design query window can be used as a general module to any one of the PB applications, and when you modify the item properties of the drop-down list box DDLB_1 in the window and the SelectionChanged code, then open the window.
After the window is open, the first thing to do is the first thing to do, and the drop-down list on the "Table" in the window is clicked in the window, select the table you want. After the table is selected, the corresponding field title is displayed in the list box of three tabs. Next, you can select the output field, the sort field, and the specified condition is queried. 1, select the output field
Click the Field Select tab and select the required field in the list box. Selecting the method as the WINDOWS Explorer Select the file. That is, hold down the CTRL button, then click, separately, hold down the Shift key to click, or drag, continuously. Click the "Select" button to select effective. Click the Clear button to deselect.
2, set the query condition
Click the Retrieval Condition tab, click on a field in the list box, click the "Comparison" drop-down list box, select a comparative character (relational operator), enter your desired in the single line edit box below "Content" Some or all of the information, click the "Select" button, some of the conditional expressions fill in the following single line edit box.
If you continue to specify a condition, click the radio button "and" or "or", the corresponding logical operator fill in the single line edit box, and then specify the partial conditions in front.
Click the "Clear" button to clear the conditional expression in the single line edit box, output all records.
The specified query criteria can be switched on the single-line editing box, or you can also modify it directly.
3, set the sort field
Click the Sort Settings tab, click the "Ascending order" or "Descending" radio box of sorting mode, click a field or multiple fields in the list box, and the query results can be sorted by specifying fields and methods.
Click the Clear button to clear the order field, and output it in natural sequence.
4, start query
Click the "Query" button and the query results are displayed in the data window control.
5, print
Click the window "Print" button to print the query results in the data window control.
