这个问题比较特殊，帮忙看看了

xiang70 2002-12-19 09:26:44

我在asp中执行sql server 的存储过程，采用了多种方法，都不行。
但如果打印输出到sql query里面去执行，该存储过程是正确的，各位帮忙看看是怎么回事。
采用的方法有

'dim cm
'set cm=server.CreateObject ("adodb.command")
'cm.ActiveConnection =conn
'cm.CommandText ="tj_rcj_analyse_rpt_p"
'cm.Parameters.append cm.CreateParameter ("startyear",3,1,,startyear)
'cm.Parameters.append cm.CreateParameter ("startmonth",3,1,,startmonth)
'cm.Parameters.append cm.CreateParameter ("endyear",3,1,,endyear)
'cm.Parameters.append cm.CreateParameter ("endmonth",3,1,,endmonth)
'cm.Parameters.append cm.CreateParameter ("tjtype",2,1,,tjtype)
'cm.Parameters.append cm.CreateParameter ("groupid",2,1,,groupid)
'cm.Execute

还有一种方法

connex.execute strsql
strsql是正确的，connex连接是建立了的
这里的strsql是构造的，比较长

...全文

105 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

xiang70 2002-12-19

打赏
举报

strsql="select name,rcjtype,unit,price,amount,totalsum from tj_rcj_analyse_rpt order by rcjtype"
rs.Open strsql,conn,1,3
'Response.Write strsql
rs.PageSize=get_system_info("pub_doc_row_count")
%>

</div><hr></div><br>
<div align="center">
<table width=80%><tr><td align="center"><b><font size="5">
<%
Response.Write err.Description
Response.Write ctname
'select case tjtype
' case "1"
' Response.Write startyear&"年"&startmonth&"月"
' case "2"
' Response.Write startyear&"年"
' case "3"
' Response.Write startyear&"年"&startmonth&"月"&"-"&endyear&"年"&endmonth&"月"
'end select
%>
人材机耗量分析报表</font></b></td></tr></table><br>
<table width=80%>
<font size="2"><tr><td>工程名称：
<%
'得到工程名称：
Response.Write ctname
if groupid<>"-1" then Response.Write "（"&groupname&"）"
%>
</td><td align="right">合同编号：<%=ctno%>
</td><tr></font></table>

<table border="1" width="80%" height="29" bordercolor="#000000" cellspacing="0" cellpadding="0" bordercolorlight="#000000" bordercolordark="#FFFFFF">
<tr>
<td width="10%" align="center" bordercolorlight="#000000" bordercolordark="#FFFFFF"><font size="2">序号</font></td>
<td width="50%" align="center" bordercolorlight="#000000" bordercolordark="#FFFFFF"><font size="2"><%=get_col_meaning("tj_rcj_analyse_rpt","name")%></font></td>
<td width="10%" align="center" bordercolorlight="#000000" bordercolordark="#FFFFFF"><font size="2"><%=get_col_meaning("tj_rcj_analyse_rpt","unit")%></font></td>
<td width="10%" align="center" bordercolorlight="#000000" bordercolordark="#FFFFFF"><font size="2"><%=get_col_meaning("tj_rcj_analyse_rpt","price")%></font></td>
<td width="10%" align="center" bordercolorlight="#000000" bordercolordark="#FFFFFF"><font size="2"><%=get_col_meaning("tj_rcj_analyse_rpt","amount")%></font></td>
<td width="10%" align="center" bordercolorlight="#000000" bordercolordark="#FFFFFF"><font size="2"><%=get_col_meaning("tj_rcj_analyse_rpt","totalsum")%></font></td>
</tr>
<%
for row=1 to rs.PageSize
if rs.EOF then
exit for
end if
%>
<tr>
<td width="10%" align="center" bordercolorlight="#000000" bordercolordark="#FFFFFF"><font size="2"><%=row%></font></td>
<td width="50%" bordercolorlight="#000000" bordercolordark="#FFFFFF"><font size="2"><%=tospace(rs("name"))%></font></td>
<td width="10%" align="center" bordercolorlight="#000000" bordercolordark="#FFFFFF"><font size="2"><%=tospace(rs("unit"))%></font></td>
<td width="10%" align="right" bordercolorlight="#000000" bordercolordark="#FFFFFF"><font size="2"><%=tomoney(rs("price"))%></font></td>
<td width="10%" align="right" bordercolorlight="#000000" bordercolordark="#FFFFFF"><font size="2"><%=tomoney(rs("amount"))%></font></td>
<td width="10%" align="right" bordercolorlight="#000000" bordercolordark="#FFFFFF"><font size="2"><%=tomoney(rs("totalsum"))%></font></td>
</tr>
<%
rs.MoveNext
next
%>
</table>

<br>
<table width=80%><tr><td align="center">第<%=curpageno%>页共<%=rs.PageCount%>页</td></tr></table></div>
</form>
<%
'关闭与数据库的联接
rs.Close
dbconnclose(conn)
set rs=nothing
set rs1=nothing
%></BODY>
</HTML>

xiang70 2002-12-19

打赏
举报

存储过程在数据库里面的query 分析器可以执行，这个存储过程太长，包含太多的表，这样就是说存储过程没有问题，我把这个源asp文件贴出来



<script language="Vbscript">
function ctsel()
document.FORM1.groupid.selectedIndex = 0
document.FORM1.submit
end function
</script>
<%
dim conn,rs,strsql,tjtype,curpagenum,row,rs1,query,startyear,startmonth,endyear,endmonth
dim ctid,groupid,ctno,ctname,groupname,reqrc,connex

tjtype=Request.QueryString("type")
query=Request.QueryString("query")
startyear=Request.QueryString("startyear")
startmonth=Request.QueryString("startmonth")
endyear=Request.QueryString("endyear")
endmonth=Request.QueryString("endmonth")
ctid=Request.QueryString("ctid") '当前标段合同id
groupid=Request.QueryString("groupid") '当前合同分组id
reqrc=Request.QueryString("reqrc") 'reqrc为1表示从合同选择带来的请求

'权限验证
select case tjtype
case "1"
call verifyauth(3114,1) '月统计的验证
case "2"
call verifyauth(3116,1) '年统计的验证
case "3"
call verifyauth(3117,1) '时间段统计的验证
end select

set conn=dbConnOpen("hkr")
set connex=dbconnopen("hkr")
set rs=server.CreateObject ("adodb.recordset")
%>
<HTML>
<HEAD>
<link rel="stylesheet" href="css/right.css" type="text/css">


<style media="print">
.noprint { display: none }
</style>
</HEAD>
<BODY>

<FORM id=FORM1 name=FORM1 action="" method=get>
<div id=idControls class="noprint" style="width:100%" align="left">
<input type=hidden name=type value=<%=tjtype%>>
<input type=hidden name=query value=2>
合同：<select size="1" name="ctid" style="width:300;font-size: 9pt" onchange="Vbscript:ctsel">
<% '合同选择
strsql="select distinct name,id,no,signid from ct_attri_v where signid>0 order by signid"
rs.Open strsql,conn

do while not rs.eof '选取标段合同

if ctid="" then
ctid=rs("id")
end if
ctid=cint(ctid)

Response.Write "<option value='" & rs("id") & "'"

if cint(rs("id"))=cint(ctid) then'缺省显示
Response.Write " selected "
ctname = rs("name") '当前的标段合同名称
ctno=rs("no") '当前的合同编号
end if

Response.Write "> " & rs("no") & " （"&rs("name")&"）</option>"
rs.movenext
loop
rs.close

%></select>
合同组号:<select size="1" name="groupid" style="width:160;font-size: 9pt"><option value="-1">全部</option>
<% '合同组号选择
strsql="SELECT id,hkrno,no,name FROM ct_group_projamount_v where ctid=" & ctid & " and (substring(hkrno,3,7) like '[0-9][0-9]' or substring(hkrno,3,7) like '[0-9]') order by sortno"
rs.Open strsql,conn

if groupid="" then
groupid=-1'rs("hkrno")
end if

do while not rs.eof'选取组

Response.Write "<option value='" & rs("id") & "'"

if rs("id")=cint(groupid) then'缺省显示
Response.Write " selected "
groupname = rs("name") '当前的合同分组名称
end if

response.write ">" & rs("name") & "(组号:" & rs("no") & ")" & "</option>"
rs.movenext
loop
rs.close

%></select>
<%
'操作区

'自动得到日期选择期
select case tjtype
case "1"
call date_select(2,startyear,startmonth,"","startyear","startmonth","",0,0)
case "2"
call date_select(1,startyear,"","","startyear","","",0,0)
case "3"
Response.Write "<br><br>"
Response.Write "从 "
call date_select(2,startyear,startmonth,"","startyear","startmonth","",0,0)
Response.Write "——— 到 "
call date_select(2,endyear,endmonth,"","endyear","endmonth","",0,0)
end select

if query="2" then
'调用存储过程得到统计的数据
select case tjtype
case "1" '按月统计
strsql=" exec tj_rcj_analyse_rpt_p "
strsql=strsql & startyear &","& startmonth &",0,0"
endyear=0
endmonth=0
case "2" '按年统计
strsql="exec tj_rcj_analyse_rpt_p "
strsql=strsql & startyear&" ,0,0,0"
startmonth=0
endyear=0
endmonth=0
case "3" '按时间段统计
strsql="exec tj_rcj_analyse_rpt_p "
strsql=strsql&startyear &","& startmonth&","&endyear&","&endmonth
end select

if groupid = "-1" then
groupid=(-1)*ctid
strsql=strsql & "," & tjtype & "," & -1*ctid '当统计全部分组时,为存贮过程合同id,为区别分组id,合同id以负值标识
else
strsql=strsql & "," & tjtype & "," & groupid
end if
connex.execute strsql
dbconnclose connex
'dim cm
'set cm=server.CreateObject ("adodb.command")
'cm.ActiveConnection =conn
'cm.CommandText ="tj_rcj_analyse_rpt_p"
'cm.Parameters.append cm.CreateParameter ("startyear",3,1,,startyear)
'cm.Parameters.append cm.CreateParameter ("startmonth",3,1,,startmonth)
'cm.Parameters.append cm.CreateParameter ("endyear",3,1,,endyear)
'cm.Parameters.append cm.CreateParameter ("endmonth",3,1,,endmonth)
'cm.Parameters.append cm.CreateParameter ("tjtype",2,1,,tjtype)
'cm.Parameters.append cm.CreateParameter ("groupid",2,1,,groupid)

'cm.Execute
end if

%><INPUT id=submit1 type=submit value=统计报表 name=submit1 class="Button"><div align="right">
<%
if query="0" then
Response.Write "<hr>"
Response.Write "</form>"
Response.Write "</body>"
Response.Write "</html>"
Response.End
end if