Using ASP nesting Js+sql server to create a two-level continuous drop-down box

Js|server|sql| pull Down

With the rapid development of network technology, more and more e-commerce sites have emerged. And in the construction of a powerful E-commerce site often do not have to provide online registration function to customers to register this module, in order to be as convenient as possible to fill out information customers, we often use the dropdown box for customers to choose, especially the two-level continuous drop-down box is most commonly used.

Moreover, I found today CSDN forum often have netizens ask this question, that is, need a two-level link Drop-down box, the first display province name, the second display corresponding city name. In order to answer this question, I write this article specially, hoped can be helpful to the netizen who needs this aspect information.

First, we first design the database (SQL Server 7.0)

Database name: Db_provincecat

Table name: 1) tb_province 2) tb_city

Description: The table tb_province is used to store the province name, and the table tb_city is used to store the corresponding city name.

Design of field names

1 The fields in table Tb_province have the province ID number (Provincecatid), the Province Code (provincecode), the province name (provincename), and table 1 gives the example:

Provincecatid Provincecode Provincename

1 01 Beijing

2 02 Shanghai City

3 03 Tianjin

4 04 Chongqing Municipality

5 05 Guangdong Province

6 06 Zhejiang Province

7 07 Fujian Province

8 08 Hainan Province

9 09 Jiangsu Province

Table 1

2 The fields of table tb_city are City ID number (Cityid), City Code (citycode), City name (CityName), and table 2 shows the example:

Cityid Citycode CityName

1 01001 Beijing

2 02001 Shanghai City

3 03001 Tianjin

4 04001 Chongqing Municipality

5 05001 Guangzhou City

6 05002 Shenzhen City

7 05003 Zhuhai City

8 05004 Maoming

9 05005 Zhongshan City

10 06001 Hangzhou City

11 06002 Wenzhou City

12 07001 Fuzhou City

13 07002 Xiamen

14 08001 Haikou City

15 80,023 Asian Cities

16 80.03 million Ning Shi

17 80,045 Point Mountain City

18 09001 Nanjing City

19 09002 Suzhou City

Table 2

Note: The point to be noted here is that the identity code is to the first two bits of the Yingcheng code.

Second, write code. The following is a complete source code:

Twolevel.asp

<meta http-equiv= "Content-type" content= "text/html; charset=gb2312 ">

<meta name= "generator" content= "Microsoft FrontPage 4.0" >

<meta name= "ProgId" content= "FrontPage.Editor.Document" >

<title> Drop-down List Example </title>

<%

Dim conn

Dim connstr

On Error Resume Next

Set Conn=server. CreateObject ("Adodb.connection")

Connstr= "Driver={sql server};server=wen;uid=sa;pwd=;d Atabase=db_provincecat"

Conn. Open ConnStr

Set Rs=server.createobject ("Adodb.recordset")

Rs.cursorlocation = adUseClient

Sql= "SELECT * from Tb_province"

Rs.Open sql,conn,1,1

If not rs.eof then

Dim Strjscript

Strjscript= ""

Response.Write ("<script language=javascript>") &CHR (13)

Response.Write ("function setcategory (S) {") &CHR (13)

Response.Write ("S.category.length=" +cstr (rs.recordcount+1) + ";") &CHR (13)

Dim i

I=0

While not rs.eof

strjscript=strjscript+ "s.category.options[" +cstr (i) + "].value=" +CHR (a) +trim (Rs ("Provincecode")) +CHR (34) + ";" +CHR (13)

strjscript=strjscript+ "s.category.options[" +cstr (i) + "].text=" +CHR (a) +trim (Rs ("Provincename")) +CHR (34) + ";" +CHR (13)

I=i+1

Rs.movenext

Wend

Response.Write (Strjscript)

Response.Write ("s.category.options[" +cstr (i) + "].value=" +CHR (34) + "None" +chr (34) + ";" +CHR (13))

Response.Write ("s.category.options[" +cstr (i) + "].text=" +CHR (34) + "* * Please select the province * * *" +CHR (34) + ";" +CHR (13))

Response.Write ("s.category.options[" +cstr (i) + "].selected=true;" +CHR (13))

Response.Write ("}") &CHR (13)

Rs.close

Set rs=nothing

End If

Set Rs=server.createobject ("Adodb.recordset")

Rs.cursorlocation = adUseClient

Sql= "Select Num=count (*), tb_province.provincecode from Tb_province,tb_city where tb_province.provincecode=substring (tb_city.citycode,1,2) Group by Tb_province.provincecode Order by Tb_province.provincecode "

Rs.Open sql,conn,1,1

If not rs.eof then

Dim Num, J

J=0

Do as not rs.eof

Num (j) =rs ("num")

J=j+1

Rs.movenext

Loop

End If

Rs.close

Set rs=nothing

Sql= ""

Set Rs=server.createobject ("Adodb.recordset")

Rs.cursorlocation = adUseClient

Sql= "Select tb_province.*,tb_city.* from Tb_province,tb_city where Tb_province.provincecode=substring (Tb_ city.citycode,1,2) Order by Tb_province.provincecode "

Rs.Open sql,conn,1,1

J=0

If not rs.eof then

Response.Write ("function Setsmall_cat (D) {") &CHR (13)

Response.Write ("Var valuecategory=d.category.options[d.category.selectedindex].value;") &CHR (13)

Response.Write ("If" (Valuecategory.indexof ("+CHR (34) +" None "+chr (+) +") ==0) {"+CHR" &_

"D.small_cat.length=1;" &_

"D.small_cat.options[0].value=" +CHR (34) + "No" +CHR (34) + ";" +CHR (&_)

"D.small_cat.options[0].text=" +CHR (34) + "* * Please select the city * *" +CHR (+CHR) &_

"D.small_cat.options[0].selected=true;" +CHR (&_)

"}")

I=0

Dim Nextlevel,upcatcode,overflow

Nextlevel= "N"

overflow= "N"

Strjscript= ""

Do as not rs.eof

Upcatecode=trim (Rs ("Provincecode"))

If nextlevel= "N" Then

strjscript= "Else If" (Valuecategory.indexof ("+CHR () +UPCATECODE+CHR () +") ==0) {"+CHR" (13)

strjscript=strjscript+ "D.small_cat.length=" +cstr (Num (j)) + ";" +CHR (13)

J=j+1

Nextlevel= "Y"

End If

strjscript=strjscript+ "d.small_cat.options[" +cstr (i) + "].value=" +CHR (a) +trim (Rs ("Citycode")) +CHR (34) + ";" +CHR (13)

strjscript=strjscript+ "d.small_cat.options[" +cstr (i) + "].text=" +CHR (a) +trim (Rs ("CityName")) +CHR (34) + ";" +CHR (13)

I=i+1

Rs.movenext

If Upcatecode<>trim (Rs ("Provincecode")) Then

overflow= "Y"

End If

If overflow= "Y" Then

Response.Write (strjscript+ "}")

Strjscript= ""

overflow= "N"

Nextlevel= "N"

I=0

End If

Loop

Response.Write ("}</script>") &CHR (13)

Rs.close

Set rs=nothing

End If

Conn.close

Set conn=nothing

%>

<body onload=setcategory (Document.select); Setsmall_cat (Document.select) >

<form name= "Select" method= "POST" action= "#" >

<center>

<Table>

<TR> <td> Please select:</td>

<td><select Onchange=setsmall_cat (document.select) name=category size= "1" ></SELECT> <select Name=small_cat size= "1" ></SELECT> </TD>

</TR>

</Table></center>

</Form>

</body>

Third, the code debugging.

This procedure is debugged and passed in Win2000+iis+sql Server7.0 environment.