Advanced ASP programming (20): querying data records of ADO Components

First, understand the principle.
1. Enter the text box to query the content.
2. Submit the query information to the page for processing.
3. The program page is mainly used to receive query information. Based on this information, a specific SQL query statement is called to obtain the query result and display it.

In fact, the main essence is the writing of SQL statements.
PreviousExtractSelect * form whattable where id = "& id
InsertIs "insert into whattable (xx_rs) values ('" & content &"')"
DeleteIs "delete from whattable where id =" & id
ModifyFor "update whattable set xx_rs = '" & log_content & "'where id =" & id
ThenQuerySelect * form whattable where xx_rs like '% "& wahtkey &" % '"

The following is an example to study
1. Create a zip table in the database zipcode. mdb.
Field id, type automatic number (keyword)
Field placename, type text
Field province, type text
Field zipcode, type text
Field borough, type text

2. Add Database Information Content
Id, no need to add
City and city corresponding to placename
Province corresponding to province
Zip code
Borough Region ID

3. Connection file conn. asp

<% Db_path = "zipcode. mdb" Set conn = Server. CreateObject ("ADODB. Connection ") Connstr = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" & Server. MapPath (db_path) Conn. Open connstr %>

4. search. asp
<Form action = "search. asp "method =" post "> <br/> <input type =" text "name =" zipcode "> <br/> <input type =" submit "name =" submit "value =" search "> <br/> </form> <br/>
[Ctrl + A select all for copy prompt: you can modify some code before clicking run]

5. Information Query page, which is also search. asp

<! -- # Include file = "conn. asp" --> <% If request. form ("submit") = "search" then Whatzip = request. form ("zipcode ") Set rs = Server. CreateObject ("ADODB. Recordset ") SQL = "Select * from zip where zipcode like '%" & whatzip & "% '" Rs. Open SQL, conn, 1, 1 %> <% If rs. EOF and rs. BOF then Response. write ("not found ") Else Do Until rs. EOF Response. write ("Response. write ("<br> province:" & rs ("province ")) Rs. MoveNext Loop End if %> <Br> <a href = "search. asp"> again </a> <% Rs. close Set rs = Nothing Conn. close Set conn = Nothing Else %> <Form action = "search. asp" method = "post"> <Input type = "text" name = "zipcode"> <Input type = "submit" name = "submit" value = "search"> </Form> <% End if %>

The above uses the like expression for fuzzy search. To perform exact search, you can directly use
SQL = "Select * from zip where zipcode = '%" & whatzip & "% '"

Of course, the area code query has not yet been added. You can try to improve it yourself, or make a big combination of hybrid queries, separate queries, fuzzy queries, and precise queries.
See the debugging page.