请问VB2010中如何返回存储过程查询结果列表(内附图)
在SQL Server里面新建一存储过程,执行。运行正常,如图:正确返回7条结果然后我用VB2010新建一程序执行此存储过程,目前能够正确返回记录条数及状态,但我现在想在新建程序中得到和上图一样的列表查询结果。在此向各位高手请教该怎么做?现程序执行如图:
之前我为这问题发过一帖:
http://bbs.csdn.net/topics/390611889?page=1#post-395761646
但是没能解决,希望各位高手帮忙解答感谢~! --------------------编程问答-------------------- 你调用存储过程,返回数据集,并绑定到DataGridView显示数据
http://ymgcn.blog.163.com/blog/static/12296697200911214339956/ --------------------编程问答--------------------
有结果但你没有绑定到DataGridView,
DataGridView.DataSource=list; //list为存储过程返回的数据列表
DataGridView.DataBind(); --------------------编程问答-------------------- 摸索了半天还是没弄出来,在这里求个代码吧感谢~! --------------------编程问答-------------------- http://blog.csdn.net/y_love_f/article/details/9199517 --------------------编程问答--------------------
Imports System.Data.SqlClient
Imports System.Reflection
Imports System.IO
Imports System
'............
Dim YearDetailTbl As New DataTable
YearDetailTbl = GetTable("exec dbo.YearReportProcedure @beginDate=N'" & "2013-10-1'")
YearDetail_BindingSource.DataSource = YearDetailTbl.DefaultView
'...........
Public Function GetTable(ByVal Sql As String) As System.Data.DataTable
Dim Tb As New System.Data.DataTable
Dim cmd As SqlCommand
cmd = New SqlCommand(Sql, OpenSqlConnection())
cmd.CommandTimeout = 15
cmd.CommandType = CommandType.Text
Dim AD As New SqlDataAdapter
Try
AD.SelectCommand = cmd
AD.Fill(Tb)
Catch ex As SqlException
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "执行Sql 出错!")
Return Nothing
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "执行Sql系统错误!")
Return Nothing
End Try
AD.Dispose()
Return Tb
End Function
'====================================
'存储过程如下:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Auto by the procedure
-- Create date: 2013/10/4 17:35:23
-- Description: Year Report Procedure
-- =============================================
ALTER PROCEDURE [dbo].[YearReportProcedure]
@beginDate datetime = N'2000-1-1',
@goodsOwner nvarchar(50) = ''
AS
declare @NextYear int
declare @endDate datetime
declare @入库数tmp decimal(18,3)
declare @出库数tmp decimal(18,3)
set @NextYear=Year(@beginDate) +1
CREATE TABLE #t (月份 int,入库数 decimal(18,3) ,出库数 decimal(18,3),余额 decimal(12,3))
BEGIN
SET NOCOUNT ON;
WHILE cast(Year(@beginDate) as int) < @NextYear
BEGIN
SET @endDate=dateadd(ss,-1,dateadd(mm,1,@beginDate))
if @goodsOwner = ''
begin
SELECT @入库数tmp=sum(入库数) , @出库数tmp=sum(出库数) from 货主进出库详细 a where (日期 between @beginDate and @endDate)
INSERT INTO #t(月份,入库数,出库数,余额)
SELECT month(@beginDate), @入库数tmp, @出库数tmp, sum(库存) from 货主进出库详细 a WHERE NOT EXISTS
(SELECT 1 FROM 货主进出库详细
WHERE 油品=a.油品 AND 货主=A.货主 and 日期<= @endDate AND 序号 > a.序号) AND a.日期 <= @endDate
end
else
begin
SELECT @入库数tmp=sum(入库数) , @出库数tmp=sum(出库数) from 货主进出库详细 a where (日期 between @beginDate and @endDate) and 货主 =@goodsOwner
INSERT INTO #t(月份,入库数,出库数,余额)
SELECT month(@beginDate), @入库数tmp, @出库数tmp, sum(库存) from 货主进出库详细 a WHERE NOT EXISTS
(SELECT 1 FROM 货主进出库详细
WHERE 油品=a.油品 AND 货主=A.货主 and 日期< @endDate AND 序号 > a.序号) AND a.日期 < @endDate and 货主 = @goodsOwner
end
SELECT @beginDate=dateadd(mm,1,@beginDate)
IF @beginDate > getdate()
break
END
SELECT * FROM #t
DROP Table #t
END
'要建临时表 CREATE TABLE #t
'最后要 SELECT * FROM #t
'再删除它 DROP Table #t
补充:.NET技术 , VB.NET