大家好,我是一个新新手,我写了一个asp,想要用case循环读取一些openquery的数据,但是灵异事件发生了,不知何故,请各位大侠多多指教:
以下是具体语句,我保证单独读取每个openquery都可以读出数值,但是把好多openquery放到case里却一个数值都读不出来呢? 是openquery响应太慢?还是其他原因呢?请高手指教(备注:openquery里读出的是单独一个数值)
fla = rs("machine")
select case fla
Case "DM2C"set rs1=server.CreateObject("adodb.recordset")
rs1.ActiveConnection=conn
rs1.CursorType=1
rs1.LockType=1
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D2C_CYCLE%''')"rs1.open sql1,conn1,1,1
if rs1.eof or rs1.bof then
qu = rs("machine")
Else
qu = rs1("value")End if
rs1.close
conn1.close
Set rs1=Nothing
Set conn1=Nothing
sql1 = ""Case "DM2B"set rs1=server.CreateObject("adodb.recordset")
rs1.ActiveConnection=conn
rs1.CursorType=1
rs1.LockType=1
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D2B_CYCLE%''')"rs1.open sql1,conn1,1,1
if rs1.eof or rs1.bof then
qu = rs("machine")
Else
qu = rs1("value")End if
rs1.close
conn1.close
Set rs1=Nothing
Set conn1=Nothing
sql1 = ""
Case "DM4C"set rs1=server.CreateObject("adodb.recordset")
rs1.ActiveConnection=conn
rs1.CursorType=1
rs1.LockType=1
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D4C_CYCLE%''')"rs1.open sql1,conn1,1,1
if rs1.eof or rs1.bof then
qu = rs("machine")
Else
qu = rs1("value")End if
rs1.close
conn1.close
Set rs1=Nothing
Set conn1=Nothing
sql1 = ""Case "DM5C"set rs1=server.CreateObject("adodb.recordset")
rs1.ActiveConnection=conn
rs1.CursorType=1
rs1.LockType=1
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D3_5C_CYCLE%''')"rs1.open sql1,conn1,1,1
if rs1.eof or rs1.bof then
qu = rs("machine")
Else
qu = rs1("value")End if
rs1.close
conn1.close
Set rs1=Nothing
Set conn1=Nothing
sql1 = ""
Case Else
qu =0End select
以下是具体语句,我保证单独读取每个openquery都可以读出数值,但是把好多openquery放到case里却一个数值都读不出来呢? 是openquery响应太慢?还是其他原因呢?请高手指教(备注:openquery里读出的是单独一个数值)
fla = rs("machine")
select case fla
Case "DM2C"set rs1=server.CreateObject("adodb.recordset")
rs1.ActiveConnection=conn
rs1.CursorType=1
rs1.LockType=1
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D2C_CYCLE%''')"rs1.open sql1,conn1,1,1
if rs1.eof or rs1.bof then
qu = rs("machine")
Else
qu = rs1("value")End if
rs1.close
conn1.close
Set rs1=Nothing
Set conn1=Nothing
sql1 = ""Case "DM2B"set rs1=server.CreateObject("adodb.recordset")
rs1.ActiveConnection=conn
rs1.CursorType=1
rs1.LockType=1
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D2B_CYCLE%''')"rs1.open sql1,conn1,1,1
if rs1.eof or rs1.bof then
qu = rs("machine")
Else
qu = rs1("value")End if
rs1.close
conn1.close
Set rs1=Nothing
Set conn1=Nothing
sql1 = ""
Case "DM4C"set rs1=server.CreateObject("adodb.recordset")
rs1.ActiveConnection=conn
rs1.CursorType=1
rs1.LockType=1
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D4C_CYCLE%''')"rs1.open sql1,conn1,1,1
if rs1.eof or rs1.bof then
qu = rs("machine")
Else
qu = rs1("value")End if
rs1.close
conn1.close
Set rs1=Nothing
Set conn1=Nothing
sql1 = ""Case "DM5C"set rs1=server.CreateObject("adodb.recordset")
rs1.ActiveConnection=conn
rs1.CursorType=1
rs1.LockType=1
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D3_5C_CYCLE%''')"rs1.open sql1,conn1,1,1
if rs1.eof or rs1.bof then
qu = rs("machine")
Else
qu = rs1("value")End if
rs1.close
conn1.close
Set rs1=Nothing
Set conn1=Nothing
sql1 = ""
Case Else
qu =0End select
建议每个case中实例化不同的rs对象
fla = rs("machine") response.write fla '输出值看下select case ucase(fla) '全转大写
Case "DM2C"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D2C_CYCLE%''')"
Case "DM2B"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D2B_CYCLE%''')"
Case "DM4C"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D4C_CYCLE%''')"
Case "DM5C"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D3_5C_CYCLE%''')"
Case Else
sql1 = ""
qu =0
End selectif sql1 <> "" then
set rs1=server.CreateObject("adodb.recordset")
rs1.ActiveConnection=conn
rs1.CursorType=1
rs1.LockType=1 rs1.open sql1,conn1,1,1
if rs1.eof or rs1.bof then
qu = rs("machine")
Else
qu = rs1("value") End if
rs1.close
conn1.close
Set rs1=Nothing
Set conn1=Nothing
else
response.write "sorry,p 都没有!!"
end if
<?xml version="1.0" encoding="utf-8" ?>
- <machines>
DM1Fsorry,p 都没有!!
<pack mac="DM1F" cycle="0" pk1="-934099" pk2="-888913" pk3="-865399" pk4="-888913" pk5="-794334" pk6="-600524" ok1="0" ok2="0" ok3="0" ok4="0" ok5="0" ok6="0" var="0" wpo="leix;yozw;zhz;zhnw" pak="pk1/pk2" />
DM1Gsorry,p 都没有!!
<pack mac="DM1G" cycle="0" pk1="-322203" pk2="-297251" pk3="-262759" pk4="-297251" pk5="-192709" pk6="-339450" ok1="0" ok2="0" ok3="0" ok4="0" ok5="0" ok6="0" var="0" wpo="leix;yozw;zhz;zhnw" pak="pk1/pk2" />
DM1Hsorry,p 都没有!!
<pack mac="DM1H" cycle="0" pk1="-342873" pk2="-332481" pk3="-264767" pk4="-281821" pk5="-194483" pk6="-332481" ok1="0" ok2="0" ok3="0" ok4="0" ok5="0" ok6="0" var="0" wpo="leix;yozw;zhz;zhnw" pak="pk1/pk2" />
DM1Bsorry,p 都没有!!
<pack mac="DM1B" cycle="0" pk1="-382000" pk2="-382000" pk3="-382000" pk4="-382000" pk5="-382000" pk6="-382000" ok1="0" ok2="0" ok3="0" ok4="0" ok5="0" ok6="0" var="0" wpo="leix;yozw;zhz;zhnw" pak="pk1/pk2" />
DM1Isorry,p 都没有!!
<pack mac="DM1I" cycle="0" pk1="0" pk2="0" pk3="0" pk4="0" pk5="0" pk6="0" ok1="0" ok2="0" ok3="0" ok4="0" ok5="0" ok6="0" var="0" wpo="leix;yozw;zhz;zhnw" pak="pk1/pk2" />
DM1Jsorry,p 都没有!!
<pack mac="DM1J" cycle="0" pk1="0" pk2="0" pk3="0" pk4="0" pk5="0" pk6="0" ok1="0" ok2="0" ok3="0" ok4="0" ok5="0" ok6="0" var="0" wpo="leix;yozw;zhz;zhnw" pak="pk1/pk2" />
DM2C
<pack mac="DM2C" cycle="1286871" pk1="52405" pk2="127947" pk3="168357" pk4="32301" pk5="1286871" pk6="1286871" ok1="1286871" ok2="1286871" ok3="1286871" ok4="1286871" ok5="1286871" ok6="1286871" var="0" wpo="leix;bliw;zhz;zwag" pak="pk1/pk2" />
DM2B
<pack mac="DM2B" cycle="DM2B" pk1="52405" pk2="127947" pk3="168357" pk4="32301" pk5="1286871" pk6="1286871" ok1="1286871" ok2="1286871" ok3="1286871" ok4="1286871" ok5="1286871" ok6="1286871" var="0" wpo="leix;bliw;zhz;zwag" pak="pk1/pk2" />
DM3Dsorry,p 都没有!!
<pack mac="DM3D" cycle="0" pk1="-462313" pk2="-462313" pk3="-462313" pk4="-462313" pk5="-235079" pk6="-6" ok1="0" ok2="0" ok3="0" ok4="0" ok5="0" ok6="0" var="0" wpo="leix;bliw;zhz;zwag" pak="pk1/pk2" />
DM3Fsorry,p 都没有!!
<pack mac="DM3F" cycle="0" pk1="-155053" pk2="-155053" pk3="-109064" pk4="0" pk5="0" pk6="0" ok1="0" ok2="0" ok3="0" ok4="0" ok5="0" ok6="0" var="0" wpo="leix;bliw;zhz;zwag" pak="pk1/pk2" />
DM3Gsorry,p 都没有!!
<pack mac="DM3G" cycle="0" pk1="-160862" pk2="-160862" pk3="-100825" pk4="0" pk5="0" pk6="0" ok1="0" ok2="0" ok3="0" ok4="0" ok5="0" ok6="0" var="0" wpo="leix;bliw;zhz;zwag" pak="" />
DM3Esorry,p 都没有!!
<pack mac="DM3E" cycle="0" pk1="0" pk2="0" pk3="0" pk4="0" pk5="0" pk6="0" ok1="0" ok2="0" ok3="0" ok4="0" ok5="0" ok6="0" var="0" wpo="leix;bliw;zhz;zwag" pak="" />
DM3Hsorry,p 都没有!!
<pack mac="DM3H" cycle="0" pk1="0" pk2="0" pk3="0" pk4="0" pk5="0" pk6="0" ok1="0" ok2="0" ok3="0" ok4="0" ok5="0" ok6="0" var="0" wpo="leix;bliw;zhz;zwag" pak="" />
DM3Isorry,p 都没有!!
<pack mac="DM3I" cycle="0" pk1="0" pk2="0" pk3="0" pk4="0" pk5="0" pk6="0" ok1="0" ok2="0" ok3="0" ok4="0" ok5="0" ok6="0" var="0" wpo="leix;bliw;zhz;zwag" pak="" />
DM5Bsorry,p 都没有!!
<pack mac="DM5B" cycle="0" pk1="-482131" pk2="-477134" pk3="-482131" pk4="0" pk5="0" pk6="0" ok1="0" ok2="0" ok3="0" ok4="0" ok5="0" ok6="0" var="0" wpo="leix;bliw;zhz;zwag" pak="" />
DM5C
<pack mac="DM5C" cycle="DM5C" pk1="-482131" pk2="-477134" pk3="-482131" pk4="0" pk5="0" pk6="0" ok1="0" ok2="0" ok3="0" ok4="0" ok5="0" ok6="0" var="0" wpo="leix;kliu;szha;zhz" pak="" />
DM4Bsorry,p 都没有!!
<pack mac="DM4B" cycle="0" pk1="-491431" pk2="-486500" pk3="-491431" pk4="0" pk5="0" pk6="0" ok1="0" ok2="0" ok3="0" ok4="0" ok5="0" ok6="0" var="0" wpo="leix;kliu;szha;zhz" pak="" />
DM4C
<pack mac="DM4C" cycle="DM4C" pk1="-491431" pk2="-486500" pk3="-491431" pk4="0" pk5="0" pk6="0" ok1="0" ok2="0" ok3="0" ok4="0" ok5="0" ok6="0" var="0" wpo="leix;kliu;szha;zhz" pak="" />
</machines>
因为我用的是Ge的Historian 3.1,我不知道其他的方法查询historian的数据库 5555555
DM2C
<pack mac="DM2C" cycle="1286871" pk1="52405" pk2="127947" pk3="168357" pk4="32301" pk5="1286871" pk6="1286871" ok1="1286871" ok2="1286871" ok3="1286871" ok4="1286871" ok5="1286871" ok6="1286871" var="0" wpo="leix;bliw;zhz;zwag" pak="pk1/pk2" />
DM2B
<pack mac="DM2B" cycle="DM2B" pk1="52405" pk2="127947" pk3="168357" pk4="32301" pk5="1286871" pk6="1286871" ok1="1286871" ok2="1286871" ok3="1286871" ok4="1286871" ok5="1286871" ok6="1286871" var="0" wpo="leix;bliw;zhz;zwag" pak="pk1/pk2" />
这两行是输出正常的。。fla = rs("machine")
select case ucase(fla) '全转大写
Case "DM2C"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D2C_CYCLE%''')"
Case "DM2B"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D2B_CYCLE%''')"
Case "DM4C"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D4C_CYCLE%''')"
Case "DM5C"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D3_5C_CYCLE%''')"
Case Else
sql1 = ""
qu =0
End selectif sql1 <> "" then
set rs1=server.CreateObject("adodb.recordset")
rs1.ActiveConnection=conn
rs1.CursorType=1
rs1.LockType=1 rs1.open sql1,conn1,1,1
if rs1.eof or rs1.bof then
qu = rs("machine")
Else
qu = rs1("value") End if
rs1.close
conn1.close
Set rs1=Nothing
Set conn1=Nothing
end if
以上代码肯定没问题。。因为你的值不在case里面,所以输出有空的
rs1.close
conn1.close
Set rs1=Nothing
Set conn1=Nothing
在循环之中 在作怪我删掉了rs1.close
conn1.close
Set rs1=Nothing
Set conn1=Nothing
就没有问题了但是我又不知道在哪里close掉rs1 conn1 好
请指教啊 谢谢
<!--#include file="conn1.asp" -->
<%
page=Trim(Request.QueryString("page"))
if page="" or page=<1 then
page=1
end if
set rs = Server.CreateObject("ADODB.Recordset")
sql = "select * from news order by id desc"
rs.open sql,conn,1
rs.PageSize=20
rs.absolutepage=page
pagecount=rs.pagecount
zongye = rs.recordcount
%>
<% if rs.eof and rs.bof then
response.Write("还没有任何记录!")
else
i=0
Response.Write("<?xml version='1.0'?>")
Response.Write("<book zongCount='"&zongye&"' cy='"&qu&"'>")
do while i<rs.pagesize and not rs.eof fla = rs("machine") select Case uCase(fla) '全转大写
Case "DM2C"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D2C_CYCLE%''')" Case "DM1F"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D1F_CYCLE%''')" Case "DM1G"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D1G_CYCLE%''')" Case "DM1B"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D1B_CYCLE%''')" Case "DM1I"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D1I_CYCLE%''')" Case "DM1J"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D1J_CYCLE%''')" Case "DM1H"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D1H_CYCLE%''')" Case "DM2B"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D2B_CYCLE%''')" Case "DM3D"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D3D_CYCLE%''')" Case "DM3F"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D3F_CYCLE%''')" Case "DM3G"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D3G_CYCLE%''')" Case "DM3E"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D3E_CYCLE%''')" Case "DM3H"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D3H_CYCLE%''')" Case "DM3I"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D3I_CYCLE%''')" Case "DM5B"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D3_5A_CYCLE%''')"
Case "DM4C"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D4C_CYCLE%''')"
Case "DM5C"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D3_5C_CYCLE%''')"
Case "DM4B"
sql1="SELECT * FROM OPENQUERY(hist, 'select value, timestamp from ihRawData where samplingmode=''CurrentValue '' AND tagname like ''%D4_CYCLE%''')"
Case Else
'response.Write ("!!ELSE!!") sql1 = ""
qu =0
End Select
set rs1=server.CreateObject("adodb.recordset")
rs1.ActiveConnection=conn1
rs1.CursorType=1
rs1.LockType=1
if sql1 <> "" then rs1.open sql1,conn1,1,1
if rs1.EOF and rs1.bof then
qu = 0
Else
qu = rs1("value") End if else
'response.write "sorry,p 都没有!!"
end if
Response.Write("<news xm='"&rs("machine")&"' qq='"&rs("minn")&"' nr='"&rs("maxn")&"' bt='"&rs("pk")&"' website='"&rs("dt")&"' wpo='"&rs("wpo")&"' cy='"&qu&"' ov='"&rs("ov")&"' id='"&rs("id")&"' sj='"&rs("dt")&"'></news>")rs.movenext
i=i+1
Loop
rs1.close
conn1.close
Set rs1=Nothing
Set conn1=Nothing
end IfResponse.Write("</book>")
rs.close
conn.close
Set rs=Nothing
Set conn=Nothing
%>