Common condition query module in ASP project

I. Ask questions:
In large ASP projects, the conditional query function is involved. You must be able to query specific fields. Some data is obtained from multiple tables. Therefore, you must be able to query multiple tables.
In general, most of the practices are that each program performs a query function, because each query table is different and involves different fields. However, in a large ASP project, this not only increases the workload and affects the development progress, but also does not make the system modular and code reusable, which increases the difficulty of system maintenance. Therefore, it is necessary to modularize conditional queries.

Ii. solution:
We designed the conditional query function:
1. Select a query field );
2. SELECT query conditions (<,>, =, <=, >=, <>, null, not null, and like );
3. Enter the condition value;
4. Select the operation relationship (OR, and );
5. add or delete query conditions to or from the list box.
For details, see:

Taking the news table (dbnews) as an example, we assume that the news table package contains the title, time, content, and publisher.
When querying databases, we generally use the following:
"Select * from news where time = '2017-05-22 'and user = 'Tom '", if we can return the condition string "time = '2017-05-22 'and user = 'Tom' in the condition query, the problem is solved. But how can we create different programs and tables? This is how to define the query fields.
Here, we write the query field as a process and call it in the query module. In this way, the query field is generated dynamically.
After selecting a query field and matching condition, add it to the <SELECT> </SELECT> box. You can continue to select another query condition, then add it to <SELECT>. After the execution is complete, click the query button. Use the query module to generate a statement such as "time = '2017-05-22 'and user = 'Tom'", which can be called in the program.

Iii. solution:
For the source code of the ASP common condition query module, see (4. Source Code ).
1. Definition Process
Before calling the conditional query module, you must first define a searchfield () process in the program:
Sub searchfield ()
Response. Write "<option value = '1title'> title </option>"
Response. Write "<option value = '1time'> time </option>"
Response. Write "<option value = '1content'> content </option>"
Response. Write "<option value = '1user'> publisher </option>"
End sub
Here, "value" is the field name. If the field is of the numeric type, add 1 before the field name and 2 for the numeric type. For example, "1 title ".
Note: if the data is obtained from multiple tables, such as select * from news, user; then the value of value must be added to the corresponding table, such as 1news. Title. In this way, you can query data from multiple tables.

2. Call the conditional query module:
<! -- # Include file = "../public/condition_search.asp" -->

3. Add button event:
Add the "condition query" button and add the onclick = "search_dis (S1)" event:
<Input type = "button" name = consearch onclick = "search_dis (S1)">

By default, the condition query module is hidden on the page. When you click the "condition query" button, the page is displayed, which is beautiful and does not affect user browsing.

In this way, the "condition query" module is called.
After the query condition is selected and the query is executed, the getsql text box returns the query condition, for example, "Title = 'chian 'and user = 'Tom'". request is used. form ("getsql! When you add it to an SQL statement, a new query is generated.

Iv. Source Code of ASP common condition query module

<Script language = JavaScript>
<! --
// Hide or display condition query module
Function search_dis (OB ){
If (OB. style. Display = "NONE ")
{OB. style. Display = ""; window. Location. href ('# low ');}
Else
{OB. style. Display = "NONE ";}
}

// Add the condition query statement to the <SELECT> query list box;
Function addse (){
Var val, val_tmp, SQL, oadd, texttype;
VaR field, condition, textval, relation;
Field = frm_search.field.options [frm_search.field.selectedindex]. text;
Textval = "'" + frm_search.textval.value + "'";
Condition = frm_search.condition.options [frm_search.condition.selectedindex]. text;
Relation = frm_search.relation.options [frm_search.relation.selectedindex]. text;
Val_tmp = frm_search.field.value;
Val_tmp = val_tmp.substring (0, 1 );
// Determine the Data Type of a field. If it is set to 1, it indicates a character or a date. If it is set to 2, it indicates a numeric value;
If (val_tmp = 1)
{Texttype = "'" + frm_search.textval.value + "'";}
Else
{Texttype = "" + frm_search.textval.value + "";}
Val_field = frm_search.field.value;
Val_field = val_field.substr (1 );
Val = val_field + frm_search.condition.value + texttype + frm_search.relation.value;
SQL = field + condition + textval + relation;
Oadd = Document. createelement ("option ")
Oadd. value = val;
Oadd. Text = SQL;
Frm_search.sesql.add (oadd );
}

// Delete the condition query statement from the <SELECT> query list box;
Function movese (){
For (I = 1; I <frm_search.sesql.options.length; I ++ ){
If (frm_search.sesql.options [I]. Selected ){
Frm_search.sesql.remove (frm_search.sesql.selectedindex );
}
}
}

// Obtain and submit the query conditions;
Function getval (){
VaR gettxt, setable;
Gettxt = "";
VaR Path = Window. Location. pathname; // get the Page Link and file name
VaR line = path. lastindexof ("/"); // gets the last '/' position
VaR dot = path. indexof ("."); // obtain the first '.' position
VaR name = path. substring (LINE + 1, dot); // get the file name
VaR filename = Name + ". asp ";
For (I = 1; I <frm_search.sesql.options.length; I ++ ){
Gettxt = gettxt + "" + frm_search.sesql.options [I]. value ;}
VaR valleng = gettxt. lastindexof ("");
Gettxt = gettxt. substr (0, valleng); // remove the last relational operator of the Condition Clause.
Setable = frm_search.setable.value;
// Window. Open (filename + '? Gettxt = '+ gettxt,' _ Self '); // send the query condition clause to the current page
Frm_search.getsql.value = gettxt;
Frm_search.action = filename;
Frm_search.submit ();
}
// -->
</SCRIPT>
<A name = "down"> </a>
<Form name = "frm_search" method = "Post" Action = "">
<Input type = "hidden" name = getsql value = "">
<Table bgcolor = # 6699cc width = 100%>
<Tr> <TD>
<Table width = "617" border = "0" cellspacing = "1" cellpadding = "2" bgcolor = "# cccccc"

Align = "center">
<Tbody id = S1 style = display: None>
<Tr>
<TD width = "134" Height = "25" bgcolor = "#006699">
<Div align = "right"> <font color = "# ffffff" style = "font-size: 10pt"> query field:

</Font> </div>
</TD>
& Lt; TD width = "159" bgcolor = "# f2f2f2" & gt;
<Select name = "field" style = "font-size: 12px"> <% call searchfield () %> </SELECT>
</TD>
& Lt; TD width = "102" bgcolor = "# e1e1e1" & gt;
<Div align = "center">
<Input type = "button" name = "addsql" value = "add & gt;" onclick = "addse ()"

Style = "Background: # ffdead; border-bottom: # 665b8e 1px solid; border-left: # ffffff 1px

Solid; border-Right: # 665b8e 1px solid; border-top: # ffffff 1px solid; color: #333333; cursor:

Hand; font-size: 12px; Height: 20px; padding-bottom: 1px; padding-left: 1px; padding-right:

1px; padding-top: 1px ">
</Div>
</TD>
<TD rowspan = "4" bgcolor = "# f2f2f2" width = "221">
<Select id = "sesql" size = "6">
<Option value = ""> ------------ query condition ----------- </option>
</SELECT>
</TD>
</Tr>
<Tr>
<TD width = "134" Height = "25" bgcolor = "#006699">
<Div align = "right"> <font color = "# ffffff" style = "font-size: 10pt"> query condition:

</Font> </div>
</TD>
& Lt; TD width = "159" bgcolor = "# f2f2f2" & gt;
<Select name = "condition" style = "font-size: 12px">
<Option value = "="> wait </option>
<Option value = "& gt;"> greater than </option>
<Option value = "& gt; ="> greater than or equal to </option>
<Option value = "& lt;"> smaller than </option>
<Option value = "& lt; ="> less than or equal to </option>
<Option value = "& lt; & gt;"> not equal to </option>
<Option value = "null"> null </option>
<Option value = "not null"> not empty </option>
<Option value = "like"> match </option>
</SELECT>
</TD>
& Lt; TD width = "102" bgcolor = "# e1e1e1" & gt;
<Div align = "center">
<Input type = "button" name = "delsql" value = "& lt; Delete" onclick = "movese ()"

Style = "Background: # ffdead; border-bottom: # 665b8e 1px solid; border-left: # ffffff 1px

Solid; border-Right: # 665b8e 1px solid; border-top: # ffffff 1px solid; color: #333333; cursor:

Hand; font-size: 12px; Height: 20px; padding-bottom: 1px; padding-left: 1px; padding-right:

1px; padding-top: 1px ">
</Div>
</TD>
</Tr>
<Tr>
<TD width = "134" Height = "25" bgcolor = "#006699">
<Div align = "right"> <font color = "# ffffff" style = "font-size: 10pt"> item value:

</Font> </div>
</TD>
& Lt; TD width = "159" bgcolor = "# f2f2f2" & gt;
<Input type = "text" id = "textval" size = "15" style = "background-color: White; border-

Bottom: #000000 1px solid; border-left: #000000 1px solid; border-Right: #000000 1px solid; border-

Top: #000000 1px solid; font-size: 9pt ">
</TD>
<TD width = "102" bgcolor = "# e1e1e1"> & nbsp; </TD>
</Tr>
<Tr>
<TD width = "134" Height = "25" bgcolor = "#006699">
<Div align = "right"> <font color = "# ffffff" style = "font-size: 10pt"> Relational operators:

</Font> </div>
</TD>
& Lt; TD width = "159" bgcolor = "# f2f2f2" & gt;
<Select name = "relation" style = "font-size: 12px">
<Option value = "and"> and </option>
<Option value = "or"> or </option>
</SELECT>
</TD>
& Lt; TD width = "102" bgcolor = "# e1e1e1" & gt;
<Div align = "center">
<Input type = "button" name = "search_ OK" value = "query" onclick = "getval ()"

Style = "Background: # deb887; border-bottom: # 665b8e 1px solid; border-left: # ffffff 1px

Solid; border-Right: # 665b8e 1px solid; border-top: # ffffff 1px solid; color: #333333; cursor:

Hand; font-size: 12px; Height: 20px; padding-bottom: 1px; padding-left: 1px; padding-right:

1px; padding-top: 1px ">
</Div>
</TD>
</Tr>
</Tbody>
</Table>
</TD> </tr>
</Table>
</Form>