一个复合查询的问题求助大家！（在线）先谢了！

[kusan2004]

两个下拉框和一个文本框，现在文本框可以进行模糊查询，但是就是下拉框的值不知道怎么取，希望大家多多帮助。代码如下：
<form name="form1" action="seater.asp" method="post">

<p><font color="#164DA8"></font>
<input name="hospitalname" type="text" id="hospitalname" >
<input type="submit" name="submit" value="进入选项" style="border: 1px solid #3466BB; background-color: #B6CAEB; color: #3466BB">
</p>
<p><font color="#164DA8">所属区县：
<select name="suoshu" size="1">
<option value="00">全部</option>
<option value="01">东城区</option>
<option value="02">西城区</option>
<option value="03">崇文区</option>
<option value="04">宣武区</option>
<option value="05">朝阳区</option>
<option value="06">丰台区</option>
<option value="07">石景山区</option>
<option value="08">海淀区</option>
<option value="09">门头沟区</option>
<option value="10">房山区</option>
<option value="11">通州区</option>
<option value="12">顺义区</option>
<option value="13">昌平区</option>
<option value="14">大兴区</option>
<option value="15">平谷区</option>
<option value="16">怀柔区</option>
<option value="17">密云县</option>
<option value="18">延庆县</option>
</select>
医院类别：
<select name="leibie" size="1">
<option value="00">全部</option>
<option value="01">对外综合</option>
<option value="02">对外专科</option>
<option value="03">对外中医</option>
<option value="04">社区卫生站</option>
<option value="05">对内</option>
</select>
</font></p>
<%
Set Conn=Server.CreateObject("adodb.Connection")
conn = "PROVIDER=SQLOLEDB;Server=localhost;DATABASE=message;UID=sa;PWD=sa;"
hospitalname=trim(request("hospitalname"))
suoshu=Replace(request.querystring("suoshu"),"'","")
leibie=Replace(request.querystring("leibie"),"'","")
Set RS = Server.CreateObject("ADODB.RecordSet")
sqltemp="Select * from mytable where 1=1"
if hospitalname<>"" then
sqltemp= " select * from mytable where Name like '%" & hospitalname & "%'"
end if
if suoshu<>"" then
sqltemp= " select * from mytable where area like '%" & suoshu & "%' "
end if
if leibie<>"" then
sqltemp=" select * from mytable where level like '%" & leibie & "%' "
end if
if suoshu=01 then
sqltemp=sqltemp & " and area like '东城区%'"
if suoshu=02 then
sqltemp=sqltemp & " and area like '西城区%'"
if suoshu=03 then
sqltemp=sqltemp & " and area like '崇文区%'"
if suoshu=04 then
sqltemp=sqltemp & " and area like '宣武区%'"
if suoshu=05 then
sqltemp=sqltemp & " and area like '朝阳区%'"
if suoshu=06 then
sqltemp=sqltemp & " and area like '丰台区%'"
if suoshu=07 then
sqltemp=sqltemp & " and area like '石景山区%'"
if suoshu=08 then
sqltemp=sqltemp & " and area like '海淀区%'"
if suoshu=09 then
sqltemp=sqltemp & " and area like '门头沟区%'"
if suoshu=10 then
sqltemp=sqltemp & " and area like '房山区%'"
if suoshu=11 then
sqltemp=sqltemp & " and area like '通州区%'"
if suoshu=12 then
sqltemp=sqltemp & " and area like '顺义区%'"
if suoshu=13 then
sqltemp=sqltemp & " and area like '昌平区%'"
if suoshu=14 then
sqltemp=sqltemp & " and area like '大兴区%'"
if suoshu=15 then
sqltemp=sqltemp & " and area like '平谷区%'"
if suoshu=16 then
sqltemp=sqltemp & " and area like '怀柔区%'"
if suoshu=17 then
sqltemp=sqltemp & " and area like '密云县%'"
else
sqltemp=sqltemp & " and area like '延庆县%'"
end if
end if
end if
end if
end if
end if
end if
end if
end if
end if
end if
end if
end if
end if
end if
end if
end if


if not trim(form1.leibie.value)="00" then
if trim(form1.leibie.value)="01" then
sqltemp=sqltemp & " and type like '对外综合%'"
if trim(form1.leibie.value)="02" then
sqltemp=sqltemp & " and type like '对外专科%'"
if trim(form1.leibie.value)="03" then
sqltemp=sqltemp & " and type like '对外中医%'"
if trim(form1.leibie.value)="04" then
sqltemp=sqltemp & " and type like '社区卫生站%'"
else
if trim(form1.leibie.value)="05" then
sqltemp=sqltemp & " and type like '对内%'"
end if
end if
end if
end if
end if
end if

Rs.CursorLocation=3
rs.open sqltemp,conn,1,3
if not Rs.eof then
Page=request("Page")
if page="" then
page=1
end if
rs.pagesize=20

Rs.AbsolutePage=page
if page<1 then page=1
if page>Rs.AbsolutePage then page=Rs.AbsolutePage
TheMessage ="查询医院 '"&Request.Form("hospitalname") & "' 信息结果如下"
%>

<%

Response.Write "<table width=100% border=0 bgcolor=#164DA8 "_
& "cellspacing=1 cellpadding=2 align=center>"
Response.Write "<tr><td><font color=white><P ALIGN=CENTER>" & TheMessage
Response.Write "</font></td></tr>"
'如果不输出查询结果

Response.Write "</table>"

'如果要输出查询结果
Response.write "<tr bgcolor=white ><td align=center>"
Response.write "<table width=100% bgcolor=#164DA8 " _
& "border=0 cellspacing=1 cellpadding=1 align=center>"

Response.write "<tr><td><font color=white>编号</td>"
Response.write "<td><font color=white>医院名称</font></td>"
Response.write "<td><font color=white>所属区县</font></td>"
Response.write "<td><font color=white>医院类别</font></td>"
Response.write "<td><font color=white>医院等级</font></td>"
for i=1 to rs.pagesize
Response.write "<tr bgcolor=white>"
if rs.eof then exit for
Response.write "<td>" & rs("ID") &"</td>"
Response.write "<td>" & rs("Name") & "</td>"
Response.write "<td>" & rs("area") & "</td>"
Response.write "<td>" & rs("type") & "</td>"
Response.write "<td>" & rs("level") & "</td>"
Response.write "</tr>"

Rs.MoveNext
Next
end if

Response.Write "</table>"

代码多了些，希望大家能够帮助。

[WEIWEI2002]

直接取值就行啦，request.form("suoshu")，结果类似00或者01等
然后再用sql语句查询
如果mytable中area字段内容是朝阳区，宣武区等等的话，我觉得还是<select><option value="朝阳区">朝阳区</option></select>这种形式比较方便
直接用where area=request.form("suoshu")查询就可以了
不知我理解的搂主的意思对不对？

[kusan2004]

附上数据库结构：

1 id nvarchar 255 1
0 name nvarchar 255 1
0 area nvarchar 255 1
0 [level] nvarchar 255 1
0 type nvarchar 255 1

内容就是 ：
01110001 首都医科大学附属北京同仁医院 东城区 对外综合 三级
01110002 中日友好医院 朝阳区 对外综合 三级
01110003 中国医学科学院北京协和医院 东城区 对外综合 三级
01110004 中国人民解放军北京军区总医院 东城区 对外综合 三级
01110005 卫生部北京医院 东城区 对外综合 三级
01110006 北京市公安医院 东城区 对外综合 三级

[kusan2004]

乱了些，不好意思

[colee]

一看下面这段代码就不对劲，用select case吧

if suoshu=01 then
sqltemp=sqltemp & " and area like '东城区%'"
if suoshu=02 then
sqltemp=sqltemp & " and area like '西城区%'"
if suoshu=03 then
sqltemp=sqltemp & " and area like '崇文区%'"
if suoshu=04 then
sqltemp=sqltemp & " and area like '宣武区%'"
if suoshu=05 then
sqltemp=sqltemp & " and area like '朝阳区%'"
if suoshu=06 then
sqltemp=sqltemp & " and area like '丰台区%'"
if suoshu=07 then
sqltemp=sqltemp & " and area like '石景山区%'"
if suoshu=08 then
sqltemp=sqltemp & " and area like '海淀区%'"
if suoshu=09 then
sqltemp=sqltemp & " and area like '门头沟区%'"
if suoshu=10 then
sqltemp=sqltemp & " and area like '房山区%'"
if suoshu=11 then
sqltemp=sqltemp & " and area like '通州区%'"
if suoshu=12 then
sqltemp=sqltemp & " and area like '顺义区%'"
if suoshu=13 then
sqltemp=sqltemp & " and area like '昌平区%'"
if suoshu=14 then
sqltemp=sqltemp & " and area like '大兴区%'"
if suoshu=15 then
sqltemp=sqltemp & " and area like '平谷区%'"
if suoshu=16 then
sqltemp=sqltemp & " and area like '怀柔区%'"
if suoshu=17 then
sqltemp=sqltemp & " and area like '密云县%'"
else
sqltemp=sqltemp & " and area like '延庆县%'"
end if
end if
end if
end if
end if
end if
end if
end if
end if
end if
end if
end if
end if
end if
end if
end if
end if

[kusan2004]

TO：WEIWEI2002(魏巍)
明白你的意思了，就是request.form("suoshu")能否取到下了框里的具体值

[kusan2004]

TO：colee(浪漫的惊喜) 还有没有简单的方法？select case具体用法不知道怎么用？

[kusan2004]

TO：WEIWEI2002(魏巍) ：
where area=request.form("suoshu")这句有错误

[jianxin0560]

“TO：WEIWEI2002(魏巍) ：
where area=request.form("suoshu")这句有错误
”
这么写"where area like"&"'%"& request.form("suoshu")&"%'"

[kusan2004]

自己顶

[WEIWEI2002]

可以取到值，如下：
strSql="select * from mytable where area='" & request("suoshu") & "'"
提示什么错误啊
可以把strSql语句打出来看看，如下：
response.write "strSql=" & strSql
response.end

[zzsdream]

给你一个很好的代码

<form action="http://www.chinaleader.com/cgi-bin/register.cgi" method=post name="creator" enctype="multipart/form-data">

<script language="javascript">
<!--
var where = new Array(35);
function comefrom(loca,locacity) { this.loca = loca; this.locacity = locacity; }
where[0]= new comefrom("请选择省份名","请选择城市名");
where[1] = new comefrom("北京","|东城|西城|崇文|宣武|朝阳|丰台|石景山|海淀|门头沟|房山|通州|顺义|昌平|大兴|平谷|怀柔|密云|延庆");
where[2] = new comefrom("上海","|黄浦|卢湾|徐汇|长宁|静安|普陀|闸北|虹口|杨浦|闵行|宝山|嘉定|浦东|金山|松江|青浦|南汇|奉贤|崇明");
where[3] = new comefrom("天津","|和平|东丽|河东|西青|河西|津南|南开|北辰|河北|武清|红挢|塘沽|汉沽|大港|宁河|静海|宝坻|蓟县");
where[4] = new comefrom("重庆","|万州|涪陵|渝中|大渡口|江北|沙坪坝|九龙坡|南岸|北碚|万盛|双挢|渝北|巴南|黔江|长寿|綦江|潼南|铜梁|大足|荣昌|壁山|梁平|城口|丰都|垫江|武隆|忠县|开县|云阳|奉节|巫山|巫溪|石柱|秀山|酉阳|彭水|江津|合川|永川|南川");
where[5] = new comefrom("河北","|石家庄|邯郸|邢台|保定|张家口|承德|廊坊|唐山|秦皇岛|沧州|衡水");
where[6] = new comefrom("山西","|太原|大同|阳泉|长治|晋城|朔州|吕梁|忻州|晋中|临汾|运城");
where[7] = new comefrom("内蒙古","|呼和浩特|包头|乌海|赤峰|呼伦贝尔盟|阿拉善盟|哲里木盟|兴安盟|乌兰察布盟|锡林郭勒盟|巴彦淖尔盟|伊克昭盟");
where[8] = new comefrom("辽宁","|沈阳|大连|鞍山|抚顺|本溪|丹东|锦州|营口|阜新|辽阳|盘锦|铁岭|朝阳|葫芦岛");
where[9] = new comefrom("吉林","|长春|吉林|四平|辽源|通化|白山|松原|白城|延边");
where[10] = new comefrom("黑龙江","|哈尔滨|齐齐哈尔|牡丹江|佳木斯|大庆|绥化|鹤岗|鸡西|黑河|双鸭山|伊春|七台河|大兴安岭");
where[11] = new comefrom("江苏","|南京|镇江|苏州|南通|扬州|盐城|徐州|连云港|常州|无锡|宿迁|泰州|淮安");
where[12] = new comefrom("浙江","|杭州|宁波|温州|嘉兴|湖州|绍兴|金华|衢州|舟山|台州|丽水");
where[13] = new comefrom("安徽","|合肥|芜湖|蚌埠|马鞍山|淮北|铜陵|安庆|黄山|滁州|宿州|池州|淮南|巢湖|阜阳|六安|宣城|亳州");
where[14] = new comefrom("福建","|福州|厦门|莆田|三明|泉州|漳州|南平|龙岩|宁德");
where[15] = new comefrom("江西","|南昌市|景德镇|九江|鹰潭|萍乡|新馀|赣州|吉安|宜春|抚州|上饶");
where[16] = new comefrom("山东","|济南|青岛|淄博|枣庄|东营|烟台|潍坊|济宁|泰安|威海|日照|莱芜|临沂|德州|聊城|滨州|菏泽");
where[17] = new comefrom("河南","|郑州|开封|洛阳|平顶山|安阳|鹤壁|新乡|焦作|濮阳|许昌|漯河|三门峡|南阳|商丘|信阳|周口|驻马店|济源");
where[18] = new comefrom("湖北","|武汉|宜昌|荆州|襄樊|黄石|荆门|黄冈|十堰|恩施|潜江|天门|仙桃|随州|咸宁|孝感|鄂州");
where[19] = new comefrom("湖南","|长沙|常德|株洲|湘潭|衡阳|岳阳|邵阳|益阳|娄底|怀化|郴州|永州|湘西|张家界");
where[20] = new comefrom("广东","|广州|深圳|珠海|汕头|东莞|中山|佛山|韶关|江门|湛江|茂名|肇庆|惠州|梅州|汕尾|河源|阳江|清远|潮州|揭阳|云浮");
where[21] = new comefrom("广西","|南宁|柳州|桂林|梧州|北海|防城港|钦州|贵港|玉林|南宁地区|柳州地区|贺州|百色|河池");
where[22] = new comefrom("海南","|海口|三亚");
where[23] = new comefrom("四川","|成都|绵阳|德阳|自贡|攀枝花|广元|内江|乐山|南充|宜宾|广安|达川|雅安|眉山|甘孜|凉山|泸州");
where[24] = new comefrom("贵州","|贵阳|六盘水|遵义|安顺|铜仁|黔西南|毕节|黔东南|黔南");
where[25] = new comefrom("云南","|昆明|大理|曲靖|玉溪|昭通|楚雄|红河|文山|思茅|西双版纳|保山|德宏|丽江|怒江|迪庆|临沧");
where[26] = new comefrom("西藏","|拉萨|日喀则|山南|林芝|昌都|阿里|那曲");
where[27] = new comefrom("陕西","|西安|宝鸡|咸阳|铜川|渭南|延安|榆林|汉中|安康|商洛");
where[28] = new comefrom("甘肃","|兰州|嘉峪关|金昌|白银|天水|酒泉|张掖|武威|定西|陇南|平凉|庆阳|临夏|甘南");
where[29] = new comefrom("宁夏","|银川|石嘴山|吴忠|固原");
where[30] = new comefrom("青海","|西宁|海东|海南|海北|黄南|玉树|果洛|海西");
where[31] = new comefrom("新疆","|乌鲁木齐|石河子|克拉玛依|伊犁|巴音郭勒|昌吉|克孜勒苏柯尔克孜|博尔塔拉|吐鲁番|哈密|喀什|和田|阿克苏");
where[32] = new comefrom("香港","");
where[33] = new comefrom("澳门","");
where[34] = new comefrom("台湾","|台北|高雄|台中|台南|屏东|南投|云林|新竹|彰化|苗栗|嘉义|花莲|桃园|宜兰|基隆|台东|金门|马祖|澎湖");
where[35] = new comefrom("其它","|北美洲|南美洲|亚洲|非洲|欧洲|大洋洲");
function select() {
with(document.creator.province) { var loca2 = options[selectedIndex].value; }
for(i = 0;i < where.length;i ++) {
if (where[i].loca == loca2) {
loca3 = (where[i].locacity).split("|");
for(j = 0;j < loca3.length;j++) { with(document.creator.city) { length = loca3.length; options[j].text = loca3[j]; options[j].value = loca3[j]; var loca4=options[selectedIndex].value;}}
break;
}}
document.creator.newlocation.value=loca2+loca4;
}
function init() {
with(document.creator.province) {
length = where.length;
for(k=0;k<where.length;k++) { options[k].text = where[k].loca; options[k].value = where[k].loca; }
options[selectedIndex].text = where[0].loca; options[selectedIndex].value = where[0].loca;
}
with(document.creator.city) {
loca3 = (where[0].locacity).split("|");
length = loca3.length;
for(l=0;l<length;l++) { options[l].text = loca3[l]; options[l].value = loca3[l]; }
options[selectedIndex].text = loca3[0]; options[selectedIndex].value = loca3[0];
}}
-->
</script>
<body onload="init()">
<font color=#000000><b>来自：</b><br>请输入您所在国家的具体地方。此项可选<br><br>
省份 <select name="province" onChange = "select()"></select>　城市 <select name="city" onChange = "select()"></select><br>
我在 <input type=text name="newlocation" maxlength=12 size=12 style="font-weight: bold">　不能超过12个字符（6个汉字）
</form>

[kusan2004]

谢谢大家，现在还有一些小问题：附上代码

if request.form("form")<>"" then
dim ss1,ss3
if suoshu="全部" then
ss1=""
else
ss1=" and Name ='"&suoshu&"'"
end if

if leibie<>"全部" then
if hospitalname<>"" then
ss3 ＝ " and "& leibie &" like '%" & hospitalname & "%'"
else
ss3="order by id desc"
end if
else
if hospitalname<>"" then
ss3="and Name like '%" & hospitalname & "%' order by id desc"
else
ss3="order by id desc"
end if
end if

sql="select * from mytable where 1=1"&ss1&ss3
else
sql="select * from mytable order by id desc"
end if

Microsoft VBScript 编译器错误 错误 '800a0408'

无效字符

/search1.asp，行71

ss3 ＝ " and "& leibie &" like '%" & hospitalname & "%'"
---^
是不是ss3没有附初始值？格式应该没问题。

[fenglik]

select case request.form("suoshu")
case "01"
sqltemp=sqltemp & " and area like '东城区%'"
case "02"
sqltemp=sqltemp & " and area like '西城区%'"
....
end select