开发前台怎样得到后台存储过程返回的记录集

[linste]

如：
Rs.Open "dt_DR_Instruction '2009-10-08','505268-01',2 ", Cn, 3, 2

这样无法得到存储过程dt_DR_Instruction 在这三个参数'2009-10-08','505268-01',2 下的返回记录集

[linste]

[Quote=引用 1 楼 citybird 的回复:]
set rs=cn.execute("Exec dt_DR_Instruction '2009-10-08','505268-01',2")试试
[/Quote]

不行

[lyserver]

存储过程不是LZ那样使用的，参考《VB实现SQL Server 2000存储过程调用》。

[三楼の郎]

[Quote=引用 2 楼 linste 的回复:]
引用 1 楼 citybird 的回复:
set rs=cn.execute("Exec dt_DR_Instruction '2009-10-08','505268-01',2")试试


不行
[/Quote]
没问题啊，为啥不行，把你的存储过程和VB的代码贴出来，我这里运行得好好的。

[linste]

[Quote=引用 4 楼 citybird 的回复:]
引用 2 楼 linste 的回复:
引用 1 楼 citybird 的回复:
set rs=cn.execute("Exec dt_DR_Instruction '2009-10-08','505268-01',2")试试


不行

没问题啊，为啥不行，把你的存储过程和VB的代码贴出来，我这里运行得好好的。
[/Quote]

执行这句没问题，但提取rs的内容就有问题了，如:在下面再加一句子msgbox Rs.RecordCount

[三楼の郎]

CREATE PROCEDURE test 
@newstypeid int,
@usesrid int
AS
select * from news where newstypeid=@newstypeid and userid=@userid
GO

Private Sub Command1_Click()
    Dim conn As New ADODB.Connection
    conn.Open "provider=msdasql;driver=sql server;server=.;uid=sa;pwd=;database=mydb"    
    Dim rs As New ADODB.Recordset
    conn.CursorLocation = adUseClient
    Set rs = conn.Execute("exec test 9,16470")
    Debug.Print rs.RecordCount
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
End Sub

[linste]

[Quote=引用 3 楼 lyserver 的回复:]
存储过程不是LZ那样使用的，参考《VB实现SQL Server 2000存储过程调用》。
[/Quote]

我知道不能这样用，只是想用其它方法实现这种效果

[三楼の郎]

[Quote=引用 5 楼 linste 的回复:]
引用 4 楼 citybird 的回复:
引用 2 楼 linste 的回复:
引用 1 楼 citybird 的回复:
set rs=cn.execute("Exec dt_DR_Instruction '2009-10-08','505268-01',2")试试


不行

没问题啊，为啥不行，把你的存储过程和VB的代码贴出来，我这里运行得好好的。


执行这句没问题，但提取rs的内容就有问题了，如:在下面再加一句子msgbox  Rs.RecordCount
[/Quote]
cn.CursorLocation = adUseClient 这句加在set rs=cn.execute前面

[linste]

[Quote=引用 6 楼 citybird 的回复:]
SQL codeCREATEPROCEDURE test@newstypeidint,@usesridintASselect*from newswhere newstypeid=@newstypeidand userid=@useridGO
VB codePrivateSub Command1_Click()Dim connAsNew ADODB.Connection
conn.Open"provider=msdasql;driver=sql server;server=.;uid=sa;pwd=;database=mydb"Dim rsAsNew ADODB.Recordset
conn.CursorLocation= adUseClientSet rs= conn.Execute("exec test 9,16470")
Debug.Print rs.RecordCountSet rs=Nothing
conn.CloseSet conn=NothingEnd Sub
[/Quote]


CREATE PROCEDURE dt_DR_Holidays
@id int
AS

declare @n int
set @n=300

select IDENTITY(INT,1,1) as ID ,convert(varchar(10),ko.kop,120) as Ddate
into #t
from(
select DATEADD(day,number,@date) as kop
from master.dbo.spt_values
where type='p' and number between 0 and @n-1) ko left join tbl_DR_Holidays k
on DATEDIFF(DAY,ko.kop ,k.docdate)=0
where k.docdate is null

select a.*,#t.docdate from tbl_DR_Holidays a left join #t on a.id=#t.id
where a.id=@id

drop table #t

我的后台是这样的，前台按你那样不行啊

[三楼の郎]

CREATE PROCEDURE dt_DR_Holidays
@id int
AS

declare @n int
set @n=300

select IDENTITY(INT,1,1) as ID ,convert(varchar(10),ko.kop,120) as Ddate
into #t
from(
select DATEADD(day,number,@date) as kop
from master.dbo.spt_values
where type='p' and number between 0 and @n-1) ko left join tbl_DR_Holidays k
on DATEDIFF(DAY,ko.kop ,k.docdate)=0
where k.docdate is null

select a.*,#t.docdate from tbl_DR_Holidays a left join #t on a.id=#t.id
where a.id=@id

--drop table #t

改成这样再试试，#t表都删除了还怎么返回，你就是直接在SQL里执行也返回不了数据啊

[linste]

Dim Cn As New Connection
Dim Rs As New Recordset

Set Cn = CurrentProject.Connection

Rs.CursorLocation = adUseClient

Set Rs = Cn.Execute("exec dt_DR_Instruction '" & Me.DocDate & "','" & Me.DocID & "'," & [DocID].[Column](1))

MsgBox Rs.RecordCount

Set Rs = Nothing
Set Cn = Nothing

加了还是不行

[三楼の郎]

[Quote=引用 10 楼 citybird 的回复:]
CREATE PROCEDURE dt_DR_Holidays
@id int
AS

declare @n int
set @n=300

select IDENTITY(INT,1,1) as ID ,convert(varchar(10),ko.kop,120) as Ddate
into #t
from(
select  DATEADD(day,number,@date) as kop
from master.dbo.spt_values
where type='p' and number between 0 and @n-1) ko left join tbl_DR_Holidays k
on DATEDIFF(DAY,ko.kop ,k.docdate)=0
where k.docdate is null

select a.*,#t.docdate from tbl_DR_Holidays a left join #t on a.id=#t.id
where a.id=@id 

--drop table  #t

改成这样再试试，#t表都删除了还怎么返回，你就是直接在SQL里执行也返回不了数据啊
[/Quote]
实际就是把drop table #t这句给去掉。

[linste]

[Quote=引用 10 楼 citybird 的回复:]
CREATE PROCEDURE dt_DR_Holidays
@id int
AS

declare @n int
set @n=300

select IDENTITY(INT,1,1) as ID ,convert(varchar(10),ko.kop,120) as Ddate
into #t
from(
select  DATEADD(day,number,@date) as kop
from master.dbo.spt_values
where type='p' and number between 0 and @n-1) ko left join tbl_DR_Holidays k
on DATEDIFF(DAY,ko.kop ,k.docdate)=0
where k.docdate is null

select a.*,#t.docdate from tbl_DR_Holidays a left join #t on a.id=#t.id
where a.id=@id 

--drop table  #t

改成这样再试试，#t表都删除了还怎么返回，你就是直接在SQL里执行也返回不了数据啊
[/Quote]

返回的应该是这一句的内容 select a.*,#t.docdate from tbl_DR_Holidays a left join #t on a.id=#t.id
where a.id=@id

[三楼の郎]

你直接在SQL查询分析器执行一下存储过程看能不能返回一个列表，笨，你要想返回记录集#t表是不能删除的，删除了#t表还能返回个毛啊！

[linste]

[Quote=引用 14 楼 citybird 的回复:]
你直接在SQL查询分析器执行一下存储过程看能不能返回一个列表，笨，你要想返回记录集#t表是不能删除的，删除了#t表还能返回个毛啊！
[/Quote]

晕，有返回值

1 2009-10-11 00:00:00.000 星期日 NULL 2009-10-08

[linste]

alter PROCEDURE dt_DR_Holidays (@date datetime,
@id int )
AS

declare @n int
set @n=300

select IDENTITY(INT,1,1) as ID ,convert(varchar(10),ko.kop,120) as Ddate
into #t
from(
select DATEADD(day,number,@date) as kop
from master.dbo.spt_values
where type='p' and number between 0 and @n-1) ko left join tbl_DR_Holidays k
on DATEDIFF(DAY,ko.kop ,k.docdate)=0
where k.docdate is null

select a.*,#t.Ddate from tbl_DR_Holidays a left join #t on a.id=#t.id
where a.id=@id

drop table #t

刚才没全，这个是完整的

[三楼の郎]

[linste]

执行这句 dt_DR_Holidays '2009-10-08',1

返回值是
1 2009-10-11 00:00:00.000 星期日 NULL 2009-10-08

[三楼の郎]

Sorry 看错了，我还以为你直接返回#t表的内容呢，原来是个Left Join语句。

Rs.CursorLocation = adUseClient
写错了，应该是
Cn.CursorLocation = adUseClient

[linste]

[Quote=引用 19 楼 citybird 的回复:]
Sorry 看错了，我还以为你直接返回#t表的内容呢，原来是个Left Join语句。

Rs.CursorLocation = adUseClient
写错了，应该是
Cn.CursorLocation = adUseClient
[/Quote]

Dim Cn As New Connection
Dim Rs As New Recordset

Set Cn = CurrentProject.Connection

Cn.CursorLocation = adUseClient
Rs.CursorLocation = adUseClient

Set Rs = Cn.Execute("exec dt_DR_Holidays '2009-10-08',1 ")

MsgBox Rs.RecordCount

Set Rs = Nothing
Set Cn = Nothing


还是不行，算了，用个表过渡下（将proc的返回值插到一个表中，然后再提取那个表的数据）

[贝隆]

飘过，帮顶