我在网上找了一个存储过程分页代码,但是出现了下面这样的问题用C#来调用就能正常返回值,VB就不能正常返回值,但是两者都能读来数据,程序均不报错.C# 代码C# Source Code
//<%@ Page Language="C#" ValidateRequest ="false" EnableViewState ="false" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
protected void Page_Load(Object sender, EventArgs e)
{
int intPageNo,intPageSize,intPageCount;
intPageSize = 10;
if (Request["CurrentPage"]==null)
{
intPageNo = 1;
}
else
{
intPageNo = Int32.Parse(Request["CurrentPage"]);
}
SqlConnection mySqlConnection = new SqlConnection(sCon());
SqlCommand mySqlCommand = new SqlCommand("up_GetTopicList", mySqlConnection);
mySqlCommand.CommandType = CommandType.StoredProcedure;
SqlParameter workParm;
//搜索表字段,以","号分隔
workParm = mySqlCommand.Parameters.Add("@a_TableList", SqlDbType.VarChar, 200);
mySqlCommand.Parameters["@a_TableList"].Value = "sId,sType,sDate";
//搜索表名
workParm = mySqlCommand.Parameters.Add("@a_TableName", SqlDbType.VarChar, 30);
mySqlCommand.Parameters["@a_TableName"].Value = "sTable";
//搜索条件,如"select * from aa where a=1 and b=2 and c=3"则条件为"where a=1 and b=2 and c=3"
workParm = mySqlCommand.Parameters.Add("@a_SelectWhere", SqlDbType.VarChar, 500);
mySqlCommand.Parameters["@a_SelectWhere"].Value = "where sType='value'";
//表主键字段名,必须为INT类型
workParm = mySqlCommand.Parameters.Add("@a_SelectOrderId", SqlDbType.VarChar, 50);
mySqlCommand.Parameters["@a_SelectOrderId"].Value = "sId";
//排序,可以使用多字段排序但主键字段必需在最前面
workParm = mySqlCommand.Parameters.Add("@a_SelectOrder", SqlDbType.VarChar, 50);
mySqlCommand.Parameters["@a_SelectOrder"].Value = "order by sId desc";
//页号
workParm = mySqlCommand.Parameters.Add("@a_intPageNo", SqlDbType.Int);
mySqlCommand.Parameters["@a_intPageNo"].Value = intPageNo;
//每页显示数
workParm = mySqlCommand.Parameters.Add("@a_intPageSize", SqlDbType.Int);
mySqlCommand.Parameters["@a_intPageSize"].Value = intPageSize;
//总记录数(存储过程输出参数)
workParm = mySqlCommand.Parameters.Add("@RecordCount", SqlDbType.Int);
workParm.Direction = ParameterDirection.Output;
//当前页记录数(存储过程返回值)
workParm = mySqlCommand.Parameters.Add("RowCount", SqlDbType.Int);
workParm.Direction = ParameterDirection.ReturnValue;
mySqlConnection.Open();
Repeater.DataSource = mySqlCommand.ExecuteReader();
Repeater.DataBind();
mySqlConnection.Close();
Int32 RecordCount = (Int32 )mySqlCommand.Parameters["@RecordCount"].Value;
Int32 RowCount = (Int32 )mySqlCommand.Parameters["RowCount"].Value;
Response .Write (RecordCount );
Response.Write("<br /><br />");
Response.Write(RowCount);
LabelRecord.Text = RecordCount.ToString();
LabelRow.Text = intPageNo.ToString();
intPageCount = RecordCount/intPageSize;
if ((RecordCount%intPageSize)>0)
intPageCount += 1;
LabelPage.Text = intPageCount.ToString();
if (intPageNo>1)
{
HLFistPage.NavigateUrl = "select.aspx?CurrentPage=1";
HLPrevPage.NavigateUrl = String.Concat("procedure.aspx?CurrentPage=","",intPageNo-1);
}
else
{
HLFistPage.NavigateUrl = "";
HLPrevPage.NavigateUrl = "";
//HLFistPage.Enabled = false;
//HLPrevPage.Enabled = false;
}
if (intPageNo<intPageCount)
{
HLNextPage.NavigateUrl = String.Concat("procedure.aspx?CurrentPage=", "", intPageNo + 1);
HLEndPage.NavigateUrl = String.Concat("procedure.aspx?CurrentPage=", "", intPageCount);
}
else
{
HLNextPage.NavigateUrl = "";
HLEndPage.NavigateUrl = "";
//HLNextPage.Enabled=false;
//HLEndPage.Enabled=false;
}
}
private static string sCon()
{
string k = "connection string";
return k;
}
</script>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link href="/style.css" rel="stylesheet" />
<style type="text/css">
.high { font-family: "宋体"; font-size: 9pt; line-height: 140%}
.mid { font-size: 9pt; line-height: 12pt}
.small { font-size: 9pt; line-height: normal}
.TP10_5 {
font-size: 14px;
line-height: 140%;
}
</style>
<style type="text/css">A:link {
COLOR: #cc6666
}
</style>
</head>
<body>
<form id="Form1" runat="server">
<span class="high"> 第<font color="#CC0000"><asp:Label id="LabelRow" runat="server"/></font>页 | 共有<asp:Label id="LabelPage" runat="server"/>页
| <asp:Label id="LabelRecord" runat="server"/>条信息 |
<asp:HyperLink id="HLFistPage" Text="首页" runat="server"/>
| <asp:HyperLink id="HLPrevPage" Text="上一页" runat="server"/>
| <asp:HyperLink id="HLNextPage" Text="下一页" runat="server"/>
| <asp:HyperLink id="HLEndPage" Text="尾页" runat="server"/></span><br/>
<asp:Repeater id=Repeater runat="server">
<HeaderTemplate>
<table width="583" border="0" cellspacing="0" cellpadding="0">
<tr>
<td bgcolor="#000000"><table width="100%" border="0" cellpadding="4" cellspacing="1" class="TP10_5">
<tr bgcolor="#999999">
<td align="center"> <strong><font color="#FFFFFF">订单号</font></strong></td>
<td align="center"> <strong><font color="#FFFFFF">服务项目</font></strong></td>
<td align="center"> <strong><font color="#FFFFFF">预订日期</font></strong></td>
<td align="center"> <strong><font color="#FFFFFF">操作人员</font></strong></td>
<td align="center"> <strong><font color="#FFFFFF">分配状态</font></strong></td>
<td> <div align="center"></div></td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr align="center" bgcolor="#FFFFFF" class="small" onMouseOver='this.style.background="#CCCCCC"' onMouseOut='this.style.background="#FFFFFF"'>
<td><%# DataBinder.Eval(Container.DataItem, "sId") %></td>
<td><%# DataBinder.Eval(Container.DataItem, "sType") %></td>
<td><%# DataBinder.Eval(Container.DataItem, "sDate") %></td>
<td> </td>
<td> </td>
<td><a href="javascript:void(window.open('info.asp?id=<%# DataBinder.Eval(Container.DataItem, "InfoId") %>','订单分配','height=600,width=1000'))">订单详情</a></td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table></td>
</tr>
</table>
</FooterTemplate>
</asp:Repeater>
</form>
</body>
</html>
//<%@ Page Language="C#" ValidateRequest ="false" EnableViewState ="false" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
protected void Page_Load(Object sender, EventArgs e)
{
int intPageNo,intPageSize,intPageCount;
intPageSize = 10;
if (Request["CurrentPage"]==null)
{
intPageNo = 1;
}
else
{
intPageNo = Int32.Parse(Request["CurrentPage"]);
}
SqlConnection mySqlConnection = new SqlConnection(sCon());
SqlCommand mySqlCommand = new SqlCommand("up_GetTopicList", mySqlConnection);
mySqlCommand.CommandType = CommandType.StoredProcedure;
SqlParameter workParm;
//搜索表字段,以","号分隔
workParm = mySqlCommand.Parameters.Add("@a_TableList", SqlDbType.VarChar, 200);
mySqlCommand.Parameters["@a_TableList"].Value = "sId,sType,sDate";
//搜索表名
workParm = mySqlCommand.Parameters.Add("@a_TableName", SqlDbType.VarChar, 30);
mySqlCommand.Parameters["@a_TableName"].Value = "sTable";
//搜索条件,如"select * from aa where a=1 and b=2 and c=3"则条件为"where a=1 and b=2 and c=3"
workParm = mySqlCommand.Parameters.Add("@a_SelectWhere", SqlDbType.VarChar, 500);
mySqlCommand.Parameters["@a_SelectWhere"].Value = "where sType='value'";
//表主键字段名,必须为INT类型
workParm = mySqlCommand.Parameters.Add("@a_SelectOrderId", SqlDbType.VarChar, 50);
mySqlCommand.Parameters["@a_SelectOrderId"].Value = "sId";
//排序,可以使用多字段排序但主键字段必需在最前面
workParm = mySqlCommand.Parameters.Add("@a_SelectOrder", SqlDbType.VarChar, 50);
mySqlCommand.Parameters["@a_SelectOrder"].Value = "order by sId desc";
//页号
workParm = mySqlCommand.Parameters.Add("@a_intPageNo", SqlDbType.Int);
mySqlCommand.Parameters["@a_intPageNo"].Value = intPageNo;
//每页显示数
workParm = mySqlCommand.Parameters.Add("@a_intPageSize", SqlDbType.Int);
mySqlCommand.Parameters["@a_intPageSize"].Value = intPageSize;
//总记录数(存储过程输出参数)
workParm = mySqlCommand.Parameters.Add("@RecordCount", SqlDbType.Int);
workParm.Direction = ParameterDirection.Output;
//当前页记录数(存储过程返回值)
workParm = mySqlCommand.Parameters.Add("RowCount", SqlDbType.Int);
workParm.Direction = ParameterDirection.ReturnValue;
mySqlConnection.Open();
Repeater.DataSource = mySqlCommand.ExecuteReader();
Repeater.DataBind();
mySqlConnection.Close();
Int32 RecordCount = (Int32 )mySqlCommand.Parameters["@RecordCount"].Value;
Int32 RowCount = (Int32 )mySqlCommand.Parameters["RowCount"].Value;
Response .Write (RecordCount );
Response.Write("<br /><br />");
Response.Write(RowCount);
LabelRecord.Text = RecordCount.ToString();
LabelRow.Text = intPageNo.ToString();
intPageCount = RecordCount/intPageSize;
if ((RecordCount%intPageSize)>0)
intPageCount += 1;
LabelPage.Text = intPageCount.ToString();
if (intPageNo>1)
{
HLFistPage.NavigateUrl = "select.aspx?CurrentPage=1";
HLPrevPage.NavigateUrl = String.Concat("procedure.aspx?CurrentPage=","",intPageNo-1);
}
else
{
HLFistPage.NavigateUrl = "";
HLPrevPage.NavigateUrl = "";
//HLFistPage.Enabled = false;
//HLPrevPage.Enabled = false;
}
if (intPageNo<intPageCount)
{
HLNextPage.NavigateUrl = String.Concat("procedure.aspx?CurrentPage=", "", intPageNo + 1);
HLEndPage.NavigateUrl = String.Concat("procedure.aspx?CurrentPage=", "", intPageCount);
}
else
{
HLNextPage.NavigateUrl = "";
HLEndPage.NavigateUrl = "";
//HLNextPage.Enabled=false;
//HLEndPage.Enabled=false;
}
}
private static string sCon()
{
string k = "connection string";
return k;
}
</script>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link href="/style.css" rel="stylesheet" />
<style type="text/css">
.high { font-family: "宋体"; font-size: 9pt; line-height: 140%}
.mid { font-size: 9pt; line-height: 12pt}
.small { font-size: 9pt; line-height: normal}
.TP10_5 {
font-size: 14px;
line-height: 140%;
}
</style>
<style type="text/css">A:link {
COLOR: #cc6666
}
</style>
</head>
<body>
<form id="Form1" runat="server">
<span class="high"> 第<font color="#CC0000"><asp:Label id="LabelRow" runat="server"/></font>页 | 共有<asp:Label id="LabelPage" runat="server"/>页
| <asp:Label id="LabelRecord" runat="server"/>条信息 |
<asp:HyperLink id="HLFistPage" Text="首页" runat="server"/>
| <asp:HyperLink id="HLPrevPage" Text="上一页" runat="server"/>
| <asp:HyperLink id="HLNextPage" Text="下一页" runat="server"/>
| <asp:HyperLink id="HLEndPage" Text="尾页" runat="server"/></span><br/>
<asp:Repeater id=Repeater runat="server">
<HeaderTemplate>
<table width="583" border="0" cellspacing="0" cellpadding="0">
<tr>
<td bgcolor="#000000"><table width="100%" border="0" cellpadding="4" cellspacing="1" class="TP10_5">
<tr bgcolor="#999999">
<td align="center"> <strong><font color="#FFFFFF">订单号</font></strong></td>
<td align="center"> <strong><font color="#FFFFFF">服务项目</font></strong></td>
<td align="center"> <strong><font color="#FFFFFF">预订日期</font></strong></td>
<td align="center"> <strong><font color="#FFFFFF">操作人员</font></strong></td>
<td align="center"> <strong><font color="#FFFFFF">分配状态</font></strong></td>
<td> <div align="center"></div></td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr align="center" bgcolor="#FFFFFF" class="small" onMouseOver='this.style.background="#CCCCCC"' onMouseOut='this.style.background="#FFFFFF"'>
<td><%# DataBinder.Eval(Container.DataItem, "sId") %></td>
<td><%# DataBinder.Eval(Container.DataItem, "sType") %></td>
<td><%# DataBinder.Eval(Container.DataItem, "sDate") %></td>
<td> </td>
<td> </td>
<td><a href="javascript:void(window.open('info.asp?id=<%# DataBinder.Eval(Container.DataItem, "InfoId") %>','订单分配','height=600,width=1000'))">订单详情</a></td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table></td>
</tr>
</table>
</FooterTemplate>
</asp:Repeater>
</form>
</body>
</html>
<%@ Page Language="VB" ValidateRequest ="false" EnableViewState ="false"%>
<%@ Import Namespace ="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server">
Private Sub page_load(ByVal sender As Object, ByVal e As EventArgs)
Dim intPageNo As Integer = 0
Dim intPageSize As Integer = 13
Dim intPageCount As Integer = 0
Dim TempPage As String = ""
If Trim(Request.QueryString("CurrentPage")) <> "" Then
TempPage = Trim(Request.QueryString("CurrentPage"))
If IsNumeric(TempPage) Then
If TempPage < 30000 Then
intPageNo = TempPage
Else
intPageNo = 1
End If
Else
intPageNo = 1
End If
Else
intPageNo = 1
End If
Dim MyConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(GetConnectionString())
Dim MyCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("up_GetTopicList", MyConnection)
MyCommand.CommandType = Data.CommandType.StoredProcedure
MyCommand.Parameters.Add("@a_TableList", Data.SqlDbType.VarChar, 200)
MyCommand.Parameters("@a_TableList").Value = "InfoId,InfoType,InfoTitle,InfoDate"
MyCommand.Parameters.Add("@a_TableName", Data.SqlDbType.VarChar, 30)
MyCommand.Parameters("@a_TableName").Value = "SiteInfoTable"
MyCommand.Parameters.Add("@a_SelectWhere", Data.SqlDbType.VarChar, 500)
MyCommand.Parameters("@a_SelectWhere").Value = "Where InfoType='news'"
MyCommand.Parameters.Add("@a_SelectOrderId", Data.SqlDbType.VarChar, 50)
MyCommand.Parameters("@a_SelectOrderId").Value = "InfoId"
MyCommand.Parameters.Add("@a_SelectOrder", Data.SqlDbType.VarChar, 50)
MyCommand.Parameters("@a_SelectOrder").Value = "Order By InfoId Desc"
MyCommand.Parameters.Add("@a_intPageNo", SqlDbType.Int)
MyCommand.Parameters("@a_intPageNo").Value = intPageNo
MyCommand.Parameters.Add("@a_intPageSize", SqlDbType.Int)
MyCommand.Parameters("@a_intPageSize").Value = intPageSize
MyCommand.Parameters.Add("@RecordCount", SqlDbType.Int)
MyCommand.Parameters("@RecordCount").Direction = ParameterDirection.Output
MyCommand.Parameters.Add("RowCount", SqlDbType.Int)
MyCommand.Parameters("RowCount").Direction = ParameterDirection.ReturnValue
MyConnection.Open()
sData.DataSource = MyCommand.ExecuteReader()
sData.DataBind()
Dim RowsTotal As String = MyCommand.Parameters("RowCount").Value
Response.Write(RowsTotal)
Response.Write("<br /><br />")
Response.Write("<br /><br />")
MyCommand.Dispose()
MyConnection.Dispose()
End Sub
Private Function GetConnectionString() As String
Try
Dim MyString As String = ""
Dim MyClasses As MicrosoftSqlServerConnection.Administrators = New MicrosoftSqlServerConnection.Administrators()
MyString = MyClasses.ReturnAdministratorStr()
Return MyString
Catch ex As Exception
Return "!"
End Try
End Function
</script><html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>VB</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Repeater runat ="server" ID ="sData">
<HeaderTemplate >
this head
</HeaderTemplate>
<ItemTemplate >
<table width ="100%">
<tr>
<td>
<%# DataBinder.Eval(Container.DataItem, "InfoId")%>
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "InfoType")%>
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "InfoTitle")%>
</td>
<td>
<%# DataBinder.Eval(Container.DataItem, "InfoDate")%>
</td>
</tr>
</table>
</ItemTemplate>
</asp:Repeater>
</div>
</form>
</body>
</html>
@a_TableList Varchar(200),
@a_TableName Varchar(30),
@a_SelectWhere Varchar(500),
@a_SelectOrderId Varchar(20),
@a_SelectOrder Varchar(50),
@a_intPageNo int,
@a_intPageSize int,
@RecordCount int OUTPUT
as
/*定义局部变量*/
declare @intBeginID int
declare @intEndID int
declare @intRootRecordCount int
declare @intRowCount int
declare @TmpSelect NVarchar(600)
/*关闭计数*/
set nocount on
/*求总共根贴数*/
select @TmpSelect = 'set nocount on;select @SPintRootRecordCount = count(*) from '+@a_TableName+' '+@a_SelectWhere
execute sp_executesql
@TmpSelect,
N'@SPintRootRecordCount int OUTPUT',
@SPintRootRecordCount=@intRootRecordCount OUTPUT
select @RecordCount = @intRootRecordCount
if (@intRootRecordCount = 0) --如果没有贴子,则返回零
return 0
/*判断页数是否正确*/
if (@a_intPageNo - 1) * @a_intPageSize > @intRootRecordCount
return (-1)
/*求开始rootID*/
set @intRowCount = (@a_intPageNo - 1) * @a_intPageSize + 1
/*限制条数*/
select @TmpSelect = 'set nocount on;set rowcount @SPintRowCount;select @SPintBeginID = '+@a_SelectOrderId+' from '+@a_TableName+' '+@a_SelectWhere+' '+@a_SelectOrder
execute sp_executesql
@TmpSelect,
N'@SPintRowCount int,@SPintBeginID int OUTPUT',
@SPintRowCount=@intRowCount,@SPintBeginID=@intBeginID OUTPUT
/*结束rootID*/
set @intRowCount = @a_intPageNo * @a_intPageSize
/*限制条数*/
select @TmpSelect = 'set nocount on;set rowcount @SPintRowCount;select @SPintEndID = '+@a_SelectOrderId+' from '+@a_TableName+' '+@a_SelectWhere+' '+@a_SelectOrder
execute sp_executesql
@TmpSelect,
N'@SPintRowCount int,@SPintEndID int OUTPUT',
@SPintRowCount=@intRowCount,@SPintEndID=@intEndID OUTPUT
if @a_SelectWhere='' or @a_SelectWhere IS NULL
select @TmpSelect = 'set nocount off;set rowcount 0;select '+@a_TableList+' from '+@a_TableName+' where '+@a_SelectOrderId+' between '
else
select @TmpSelect = 'set nocount off;set rowcount 0;select '+@a_TableList+' from '+@a_TableName+' '+@a_SelectWhere+' and '+@a_SelectOrderId+' between '
if @intEndID > @intBeginID
select @TmpSelect = @TmpSelect+'@SPintBeginID and @SPintEndID'+' '+@a_SelectOrder
else
select @TmpSelect = @TmpSelect+'@SPintEndID and @SPintBeginID'+' '+@a_SelectOrder
execute sp_executesql
@TmpSelect,
N'@SPintEndID int,@SPintBeginID int',
@SPintEndID=@intEndID,@SPintBeginID=@intBeginID
return(@@rowcount)
--select @@rowcount
GO
/////////////////////////////用VB来调用的话rowcount和RecordCount均返回0
用C#来调用的话就能正常返回
程序都不会报错,都能正常读出数据库的数据.
不知道是为什么,请大家指点.我的测试运行环境
Windows Server 2003 + SQL Server 2005 + .NET Framework 2.0
2:把代码分开写,然后加上断点跟踪,记得加上try catch