SQL查询无返回结果问题

[g9ma01]

a.asp的代码如下
<table>
<tr>
<td width="260" height="50" colspan="2"><font size=3><b>查询</b></font></td>
</tr>
<tr>
<td width="260" height="30" colspan="2">请选择所查询的单位:</td>
<form name="xuanze_form" method="post" action="b.asp">
<td width="260" height="50" colspan="2">
<select name="dw">
<option value="701">d1</option>
<option value="703">d2</option>
<option value="704">d3</option>
<option value="705">d4</option>
<option value="706">d5</option>
<option value="707">d6</option>
<option value="708">d7</option>
</select>
</td>
<tr>
<td height="28" align="right">起始日期 </td>
<td width="30%"> <input type="text" name="qsrq" maxlength=8 size="16" class="input" tabindex="1"> </td> <input type="hidden" name="submits" value="查 询" tabindex="3">
<td width="30%" align="left" rowspan="2"> <input type="image" src="/image/btn_check.gif"> </td>
</tr>
<tr>
<td height="30" align="right">终止日期 </td>
<td> <input type="text" name="zzrq" maxlength=8 size="16" class="input" tabindex="2"> </td>
</tr>
</form>
</table>

b.asp代码如下：
<div id=tableExcel>
<%
Server.ScriptTimeOut=999999
dim conn,rs,qsrq,zzrq,dw
dw=Request("dw")
qsrq=Request("qsrq")
zzrq=Request("zzrq")
Set conn=Server.CreateObject("ADODB.Connection")
connstr="Provider=OraOLEDB.Oracle;Data Source=cxnew;User Id=xxxx;Password=xxxxxxxx;"
conn.Open connstr
sql="select b.GROUP_NAME,COUNT(a.id_no) cntusr,SUM(a.paymoney) pfee from WPAY_200811 a,DGROUPMSG_00 b where trim(a.GROUP_ID)=trim(b.GROUP_ID) and a.BACK_FLAG=0 and a.OP_CODE in ('1104','1108','1109') and substr(b.BOSS_ORG_CODE,1,4)="&dw&" and to_char(a.OP_TIME,'yyyymmdd') between "&qsrq&" and "&zzrq&" group by b.GROUP_NAME"
set rs=conn.execute(sql)
if rs.RecordCount=0 then
%>
<script language=vbscript>
msgbox "无数据，请核对日期",48,"添加失败"
window.history.go(-1)
</script>
<%else%>
<center><font size="5" color="#FF0000"><b>  查询结果
</b></font>       
<font size="5">  </font></b>

<table border=1 width="90%" align=center>
<td align="center">渠道</td>
<td align="center">用户数</td>
<td align="center">费用</td></tr>
<% if rs.eof=false then %>
<tr>
<td align="center"><%=rs.fields("group_name") %></td>
<td align="center"><%=rs.fields("cntusr") %></td>
<td align="center"><%=rs.fields("pfee") %></td>
</tr>
<% rs.movenext %>
<% while rs.eof=false %>
<tr>
<td align="center"><%=rs.fields("group_name") %></td>
<td align="center"><%=rs.fields("cntusr") %></td>
<td align="center"><%=rs.fields("pfee") %></td>
</tr>
<%
rs.movenext
wend
end if
%>
</table>
<% rs.close
conn.close
%>

<%end if%>
</div>
红色部分的SQL语句在TOAD中运行有返回结果，为什么在ASP页面运行返回结果为空呢？

[g9ma01]

问题依然没有得到解决

[g9ma01]

自己顶

[uniunicnc]

关注

[g9ma01]

谢，顶

[g9ma01]

希望周末友人

[g9ma01]

等待。。。。。。。。。。。。。。。。

[uniunicnc]

顶啊

[liuhaibing0903]

Response.Write dw,qsrq,zzrq
再将结果代入到SQL语句里在数据库里查询看是否有结果？

[gingerkang]

conn.cursorlocation=3
conn.open connstr

[g9ma01]

楼上的，加上conn.cursorlocation=3后出现错误提示为“OraOLEDB(0x80004005) ORA-01722:invalid number”

[g9ma01]

这个问题咋就这么难解决呢

[g9ma01]

楼上的，是ORACLE数据库，语句在ORACLE可以查出结果的

[g9ma01]

顶到一楼

[fenglik]

sql="select b.GROUP_NAME,COUNT(a.id_no) cntusr,SUM(a.paymoney) pfee from WPAY_200811 a,DGROUPMSG_00 b where trim(a.GROUP_ID)=trim(b.GROUP_ID) and a.BACK_FLAG=0 and a.OP_CODE in ('1104','1108','1109') and substr(b.BOSS_ORG_CODE,1,4)="&dw&" and to_char(a.OP_TIME,'yyyymmdd') between "&qsrq&" and "&zzrq&" group by b.GROUP_NAME"

'set rs=conn.execute(sql)

response.write sql

把显示出来的语句在sqlserver中运行下看看

学会自己调试

[g9ma01]

继续顶

[文盲老顾]

<!--#include file="Inc/CONN.ASP"-->
<%
Dim conStr
Set con = Server.CreateObject("ADODB.Connection")
	conStr = "Driver={Oracle in OraHome92};Server=192.168.1.51;Dbq=mlk;Uid=system;Pwd=******;"
con.Open conStr

Response.Write "<center>"

Call ControlPanel()

%>
<table style="width:99%;margin-top:5px;">
	<form action="<%=Url%>" method="post">
	<tr>
		<td><textarea name="sql" style="width:100%;border:1px outset black;" rows="7"><%=Request("sql")%></textarea></td>
	</tr>
	<tr>
		<td align="right"><input type="submit" value="立刻运行SQL指令" /></td>
	</tr>
	</form>
</table>
<%
Dim Sql,RS,i,n
n = 0
Sql = Replace(Request("sql"),vbcrlf,"")
If Sql & "" <> "" Then
	On Error Resume Next
	Set RS = con.Execute(Sql)
	Response.Write "<div style=""text-align:left;width:99%;"">指令：“<b><font color=blue>" & Sql & "</b></font>”，运行结果："
	If Err Then
		Response.Write "<font color=""red"">异常</font><br/><br/>错误号：" & Err.Number & "<br/>错误描述：<font color=red>" & Err.Description
		Err.Clear
	Else
		Response.Write "<font color=""green"">成功</font><br/><br/>"
		If RS.Fields.Count > 0 Then
			Response.Write "<div style=""width:99%;overflow:auto;height:300px;"" id=""out""><div style=""width:1000%;"" id=""in""><table id=""tb"" cellspacing=""0"" cellpadding=""0"" border=""1"">"
			Response.Write "<tr align=""center""><td>sn</td>"
			For i = 0 To RS.Fields.Count - 1
				Response.Write "<td>" & RS.Fields(i).Name & "(" & RS.Fields(i).Type & ")<br/>" & i & "</td>"
			Next
			Response.Write "</tr>"
			Do While Not RS.Eof
				n = n + 1
				Response.Write "<tr><td>" & n & "</td>"
				For i = 0 To RS.Fields.Count - 1
					Response.Write "<td>"
					If IsNull(RS(i)) Then
						Response.Write "<i style=""color:blue;"">(null)</i>"
					ElseIf RS.Fields(i).Type = 205 Then
						Response.Write "<font color=""red"">" & Server.HTMLEncode(BIN2STR(RS(i),CharSet)) & "</font>"
					Else
						Response.Write Server.HTMLEncode(RS(i))
					End If
					Response.Write " </td>"
				Next
				Response.Write "</tr>"
				RS.MoveNext
			Loop
			Response.Write "</table></div></div><script>document.getElementById('in').style.width = document.getElementById('tb').offsetWidth;</script>"
		End If
	End If
	Response.Write "</div>"
End If

Response.Write "</center>"

Sub ControlPanel
%>
<div class="sys_banner">
	<div class="sys_title">运行 SQL 指令</div>
	<div class="tab16">运行 SQL 指令，该操作属于<font color=red>高度危险操作</font>，请在本地测试后再运行相应的指令。</div>
</div>
<%
End Sub
%>

在 asp 环境下连接 oracle，然后使用这个程序运行你的 sql 看看提示

[g9ma01]

我把TRIM去掉就出结果了，自己也搞不懂

[三楼の郎]

WPAY_200811表 DGROUPMSG_00表里的Group_ID什么类型的，不是字符型的不能用trim

[g9ma01]

varchar

[zmhqyw]

trim(cstr(Group_ID))