First, ask questions:
In large ASP projects, the function of conditional query is involved. Users require the ability to query for specific fields. For some data, it is taken from multiple tables, so you also need to be able to query multiple tables.
Generally speaking, most of the practice is to do a query function for each program, because each query table is different, the fields involved are also different. However, if you do this in a large ASP project, not only increase the workload, affect the development progress, but also can not make the system modular, code reuse, increase the difficulty of system maintenance. Therefore, it is necessary for us to make conditional queries modular.
Second, the solution to the idea:
The function of our design criteria query is:
1, select the Query field (field);
2, select the query conditions (<, >, =, <=, >=, <>, NULL, NOT NULL, like);
3, the input condition value;
4, select the operational relationship (or, and);
5, add the query criteria to the list box, or remove from the list box.
For detailed interface, please look at the following figure:
We take the news table (Dbnews) as an example, assuming that the news table contains title, time, content, and publisher.
When querying a database, we typically use the following:
"SELECT * from News Where time= ' 2002-05-22 ' and user= ' Tom '", if we can return the conditional string of "time= ' 2002-05-22 ' and user= ' Tom" in a conditional query, The problem is solved. But what if it's a different program and a different table? This is the way to see how to define a field for a query.
Here, we write the query field as a process in the program, called in the query module, so that the query field is dynamically generated.
The user chooses a query field, and matching criteria, add it to the <select></select> box, the user can continue to select another query criteria, and then add it to <select>, completed, click the Query button. Use the query module to generate a statement such as: "Time= ' 2002-05-22 ' and user= ' Tom", so that it can be invoked in the program.
Third, the solution:
ASP General condition query module source code refer to (four, source codes).
1. Define the process
Before calling a conditional query module, you first define a Searchfield () procedure in your 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 ' > Publishers </option>"
End Sub
Where the value is the field name, if the field is a character type, add 1 to the field name, and the numeric type adds 2. such as: "1Title".
Note: If the data is fetched from multiple tables, such as SELECT * from News,user, then value will be added to the corresponding table, such as: 1news.title. This allows you to query data from multiple tables.
2. Call Condition Query module:
<!--#include file= ". /public/condition_search.asp "-->
3. Add button Event:
Join the Conditional Query button and add the event:
<input type= "button" Name=consearch >
By default, the conditional query module is hidden in the page, when the "Conditional query" button is displayed, so that the page is beautiful and will not hinder user browsing.
In this way, we complete the call to the conditional query module
When the query conditions are selected, after the execution of the query, the program getsql text box will return the query conditions, such as: "Title= ' Chian ' and user= ' Tom '", with Request.Form ("GetSQL") to obtain! When you add it to the SQL statement, a new query is generated.
Four, ASP General condition query module source code
<script language=javascript>
<!--
Hide or show a conditional query module
function Search_dis (ob) {
if (ob.style.display== "None")
{ob.style.display= ""; Window.location.href (' #down ');}
Else
{ob.style.display= ' none ';}
}
Add the conditional 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);
Judge the data type of the field, if 1, is the character, date type, 2, is the numeric type;
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 conditional 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 the inquiry condition and submit it;
function Getval () {
var gettxt,setable;
Gettxt = "";
var path = Window.location.pathname; Get page links and file names
var line = Path.lastindexof ("/"); Get the last '/' position
var dot = path.indexof ("."); Get the first '. ' The location
var Name = path.substring (Line+1,dot); Get 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 a conditional clause
setable = Frm_Search.Setable.value;
window.open (filename+ ' gettxt= ' +gettxt, ' _self '); Transfer query criteria clauses 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= "bgcolor=" "#006699" >
<div align= "right" ><font color= "#FFFFFF" style= "font-size:10pt" > Query fields:
</font></div>
</td>
<TD width= "159" bgcolor= "#F2F2F2" >
<select name= "Field" style= "font-size:12px" ><%call Searchfield ()%></select>
</td>
<TD width= "102" bgcolor= "#E1E1E1" >
<div align= "center" >
<input type= "button" Name= "Addsql" value= "Add >>>"
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 Criteria-----------</option>
</select>
</td>
</tr>
<tr>
<TD width= "134" height= "bgcolor=" "#006699" >
<div align= "right" ><font color= "#FFFFFF" style= "font-size:10pt" > Query criteria:
</font></div>
</td>
<TD width= "159" bgcolor= "#F2F2F2" >
<select name= "Condition" style= "font-size:12px" >
<option value= "=" > Equals </option>
<option value= ">" > Greater than </option>
<option value= ">=" > Greater than or Equal </option>
<option value= "<" > less than </option>
<option value= "<=" > less than or equal to </option>
<option value= "<>" > Not equal to </option>
<option value= "null" > Null </option>
<option value= ' not null ' > not empty </option>
<option value= "like" > Matching </option>
</select>
</td>
<TD width= "102" bgcolor= "#E1E1E1" >
<div align= "center" >
<input type= "button" Name= "Delsql" value= "<<< Delete"
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= "bgcolor=" "#006699" >
<div align= "right" ><font color= "#FFFFFF" style= "font-size:10pt" > Condition Value:
</font></div>
</td>
<TD width= "159" bgcolor= "#F2F2F2" >
<input type= "text" id= "Textval" size= "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" > </td>
</tr>
<tr>
<TD width= "134" height= "bgcolor=" "#006699" >
<div align= "right" ><font color= "#FFFFFF" style= "font-size:10pt" > Relational operators:
</font></div>
</td>
<TD width= "159" bgcolor= "#F2F2F2" >
<select name= "Relation" style= "font-size:12px" >
<option value= "and" > and </option>
<option value= "or" > or </option>
</select>
</td>
<TD width= "102" bgcolor= "#E1E1E1" >
<div align= "center" >
<input type= "button" Name= "SEARCH_OK" value= "Query"
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>
