Question

vb 调用存储过程

我有一个SQL的存储过程如下：

/*对帐单
@FNumber 客户代码
@FStarDate 起始日期
@FEndDate 结束日期

*/
CREATE Procedure dbo.DeliveryList
--version 09/01/22 by Michael
(
@FNumber varchar(10),
@FStartDate datetime,
@FEndDate datetime
)
as
select a.FHeadSelfS0226 as 送货单号,d.FName as 品名,b.FQty as 数量,round((b.FPrice/1.17),4) as 未税单价
into #temp1
from SEOutStock a,SEOutStockEntry b,t_Organization c,t_ICItem d
where a.FInterID=b.FInterID AND a.FCustID=c.FItemID
AND b.FItemID=d.FItemID
AND c.FNumber=@FNumber
AND a.FTranType=83
AND a.FDate between @FStartDate AND @FEndDate

declare @sql varchar(8000)
set @sql = 'select 品名,未税单价 '
select @sql = @sql + ' , max(case 送货单号 when ''' + 送货单号 + ''' then 数量 else 0 end) [' + 送货单号 + ']'
from (select distinct 送货单号 from #temp1) as a
set @sql = @sql + ' from #temp1 group by 品名,未税单价'
exec(@sql)

drop table #temp1
GO

现在想用VB调用以上的存储过程，并把结果显示到LISTVIEW中，请高手指教，谢谢！
3个参数需要在VB环境下赋值，然后调用这个存储过程，所以请高手帮忙给我个详细的帮助，谢谢！

Answer 1

CREATE Procedure dbo.DeliveryList
--version 09/01/22 by Michael
(
@FNumber varchar(10),
@FStartDate datetime,
@FEndDate datetime,
@sql varchar(8000) output--这里将sql变量作为输出参数传递给VB的变量
)
as
select a.FHeadSelfS0226 as 送货单号,d.FName as 品名,b.FQty as 数量,round((b.FPrice/1.17),4) as 未税单价
into #temp1
from SEOutStock a,SEOutStockEntry b,t_Organization c,t_ICItem d
Where a.FInterID = b.FInterID And a.FCustID = c.FItemID
AND b.FItemID=d.FItemID
AND c.FNumber=@FNumber
AND a.FTranType=83
AND a.FDate between @FStartDate AND @FEndDate

set @sql = 'select 品名,未税单价 '
select @sql = @sql + ' , max(case 送货单号 when ''' + 送货单号 + ''' then 数量 else 0 end) [' + 送货单号 + ']'
from (select distinct 送货单号 from #temp1) as a
set @sql = @sql + ' from #temp1 group by 品名,未税单价'
exec(@sql)

drop table #temp1
GO

在VB中调用

Dim FNumber As String, FStartDate As Date, FEndDate As Date, sql As String
Dim con As New ADODB.Connection，com As New ADODB.Command
Dim ReturnValue As Integer
'将con连接到你的数据库
' Dim str as string
' str = "Provider = SQLOLEDB.1;Persist Security Info = False;" & _
"User ID = sa;Password = 登陆服务器密码;Data Source = 127.0.0.1;" & _
"Initial Catalog = 数据库名"
' cn.Open str '这里是连接数据库的样例
Set com.ActiveConnection = con
com.CommandText = "dbo.DeliveryList" '设置Command对象源。
ListView1.ListItems.Add ，，sql
set con=nothing