Learning purpose: Learn basic operations of database 2 (query records)
On day four we have a program like this:
<%
set conn=server.createobject("adodb.connection")
conn.open "driver={microsoft access driver (*.mdb)};dbq="&server.mappath("example3.mdb")
exec="select * from guestbook"
set rs=server.createobject("adodb.recordset")
rs.open exec,conn,1,1
%>
What we query is all records, but when we want to modify or delete records, it is impossible to query all records, so we have to learn to retrieve appropriate records. Let’s look at a statement first:
a="Zhang San"
b=111
exec="select * from guestbook where name='"+a+"'and tel="+b
What is added after where is the condition, and is and, or or. I think everyone knows the meaning of =, <=, >=, <, >. What this sentence means is to search for records whose name is Zhang San and whose phone number is 111. Another point is that if you want to search whether a field contains a string, you can write like this: where instr(name,a), that is, search for people whose name contains the string a (Zhang San).
My a and b here are constants. You can let a and b be variables submitted by the form, so that you can do a search.
Let’s take a look at this code and understand it:
<form name="form1" method="post" action="example6.asp">
Search:<br>
name =
<input type="text" name="name">
andtel=
<input type="text" name="tel">
<br>
<input type="submit" name="Submit" value="Submit">
<input type="reset" name="Submit2" value="Reset">
</form>
example6.asp：
<%
name=request.form("name")
tel=request.form("tel")
set conn=server.createobject("adodb.connection")
conn.open "driver={microsoft access driver (*.mdb)};dbq="&server.mappath("example3.mdb")
exec="select * from guestbook where name='"+name+"' and tel="+tel
set rs=server.createobject("adodb.recordset")
rs.open exec,conn,1,1
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<%
do while not rs.eof
%><tr>
<td><%=rs("name")%></td>
<td><%=rs("tel")%></td>
<td><%=rs("message")%></td>
<td><%=rs("time")%></td>
</tr>
<%
rs.movenext
loop
%>
</table>
</body>
</html>
Today I actually talked about a where. Let’s go back and do experiments and implement instr(). See you tomorrow!