for x=0 to 99
strSQL = strSQL & "SELECT sum(fs) as total2 FROM TABLE1 where 班号=" & x & ";"
next x
strSQL="execute testp " & strSQL 'testp 存储过程名
rst.open strSQL, cnn, , , adCmdText
do until rst.State = adStateClosed
total1 = total1 + rst!total2
set rst = rst.NextRecordset
loop
msgbox total1
strSQL = strSQL & "SELECT sum(fs) as total2 FROM TABLE1 where 班号=" & x & ";"
next x
strSQL="execute testp " & strSQL 'testp 存储过程名
rst.open strSQL, cnn, , , adCmdText
do until rst.State = adStateClosed
total1 = total1 + rst!total2
set rst = rst.NextRecordset
loop
msgbox total1
解决方案 »
- sqlserver有没有像联合外键之类的东西
- 请问一个查询的写法
- 求解mssql中主键详解,在线等~~~~~
- [100分]注入求语句....
- 兄弟们来看看:ADO执行SQL语句,如果同时执行几条语句
- 请问添加数据时如何自动加上编号...如:表A 中有字段A1和A2,A1是主键,我用这条语句添加数据:insert into A(A2) values (...) (急啊)
- 怎样通过update语句直接更新存储过程中的内容
- set no_browsetable on/off 是?
- 错误原因???高手请指教!
- sql 查询返回结果的数据量
- 我刚学SQL,编了程序,在服务器上建立了数据库,但不知道该怎样将数据库从我的服务器上转交给用户,着急,有答案即结贴!!
- DISTINCT的问题,急啊
执行这个存储过程后得到了很多个记录集,用这个做例子吧。
*********************************************/
Create Procedure procGetList
As
--第一个
Select * from Inventory
go--第二个
Select * from OrderItem
go
打算在.NET下填充到一个Dataset里面,如果使用SQLDataAdapter/OLEDBDataAdapter来填充的话我总是失败。
SQLServer 查询分析器看看
连上Northwind
执行这个系统存储过程
sp_help Orders
得到的数据不止一个记录集,肯定有一个机制能进行操作。
我贴出代码:
项目名称:Dict
使用工具:Visual Studio .NET 2002
-------------------------------------------------------------------------
页面文件:[使用Panel调用显示]
-------------------------------------------------------------------------
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm2.aspx.vb" Inherits="Dict.WebForm2"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>WebForm2</title>
<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.0">
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:Panel id="Panel1" style="Z-INDEX: 101; LEFT: 36px; POSITION: absolute; TOP: 46px" runat="server" /><FONT face="宋体"></FONT>
<asp:Button id="Button1" style="Z-INDEX: 102; LEFT: 204px; POSITION: absolute; TOP: 15px" runat="server" Text="Load"></asp:Button>
<asp:TextBox id="TextBox1" style="Z-INDEX: 103; LEFT: 37px; POSITION: absolute; TOP: 15px" runat="server"></asp:TextBox>
</form>
</body>
</HTML>
------------------------------------------------------------------------------
后台代码
-----------------------------
Public Class WebForm2
Inherits System.Web.UI.Page
Protected WithEvents Panel1 As System.Web.UI.WebControls.Panel
Protected WithEvents TextBox1 As System.Web.UI.WebControls.TextBox
Protected WithEvents Button1 As System.Web.UI.WebControls.Button#Region " Web 窗体设计器生成的代码 " '该调用是 Web 窗体设计器所必需的。
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent() End Sub Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: 此方法调用是 Web 窗体设计器所必需的
'不要使用代码编辑器修改它。
InitializeComponent()
End Sub#End Region Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'在此处放置初始化页的用户代码
End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim MyConnection As New SqlClient.SqlConnection("data source=localhost;initial catalog=Northwind;password=sa;user id=sa")
Dim MySqlCommand As New SqlClient.SqlCommand("sp_help", MyConnection)
Dim MyDataAdapter As New SqlClient.SqlDataAdapter(MySqlCommand) MySqlCommand.CommandType = CommandType.StoredProcedure Dim paramHelpText As New SqlClient.SqlParameter("@objname", SqlDbType.NVarChar, 776)
paramHelpText.Value = TextBox1.Text.Trim
MySqlCommand.Parameters.Add(paramHelpText) MyConnection.Open() Dim ds As New DataSet()
MyDataAdapter.Fill(ds)
MyConnection.Close() Dim datagrid1 As System.Web.UI.WebControls.DataGrid
Dim TextBox2 As System.Web.UI.WebControls.Label
Dim i As Integer
If ds.Tables.Count > 0 Then
For i = 0 To ds.Tables.Count - 1
datagrid1 = New System.Web.UI.WebControls.DataGrid()
TextBox2 = New System.Web.UI.WebControls.Label()
datagrid1.Font.Name = "Tahoma"
datagrid1.Font.Size = System.Web.UI.WebControls.FontUnit.Point(9)
datagrid1.DataSource = ds.Tables(i)
datagrid1.DataBind() TextBox2.Font.Name = "Tahoma"
TextBox2.Font.Size = System.Web.UI.WebControls.FontUnit.Point(9)
TextBox2.Text = "---------------------------" & ds.Tables(i).TableName & "---------------------------"
Panel1.Controls.Add(TextBox2)
Panel1.Controls.Add(datagrid1)
Next
End If
End Sub
End Class
Dim MyConnection As New SqlClient.SqlConnection("data source=localhost;initial catalog=Northwind;password=sa;user id=sa")
Dim MySqlCommand As New SqlClient.SqlCommand("sp_help", MyConnection)'这里执行了一个存储过程,能返回不固定数量的记录集,以次举例
Dim MyDataAdapter As New SqlClient.SqlDataAdapter(MySqlCommand) MySqlCommand.CommandType = CommandType.StoredProcedure Dim paramHelpText As New SqlClient.SqlParameter("@objname", SqlDbType.NVarChar, 776)--这里举例,他可以返回不限制数量的集合
paramHelpText.Value = TextBox1.Text.Trim'获取文本框输入的Northwind数据库里面的对象名,可以是表,存储过程,视图等等
MySqlCommand.Parameters.Add(paramHelpText) MyConnection.Open() Dim ds As New DataSet()
MyDataAdapter.Fill(ds)'这里填充一个Dataset
MyConnection.Close()
'最后运行:在文本框输入一个Northwind数据库中的对象名称[可以是表、视图、存储过程、函数等等],列出该对象的所有属性,这是多个记录集,并且没有固定结果。