这是sql server存储过程
CREATE PROCEDURE p_pdcx
@lx varchar(2),
@cdate varchar(20),
@cdate1 varchar(20),
@dh varchar(100)
AS
if @lx = 0
begin
select pd_id,cdate,stext,0 as pdsl,0 as kcsl,0 as pksl,0 as pysl into #aa from z_tb_pddt where cdate >= @cdate and cdate<=@cdate1 and sx = 1
select sum(pdsl) as pdsl,sum(pdsl-kcsl) as yksl,sum(kcsl) as kcsl,pd_id into #bb from z_tb_pdls where pd_id in (select pd_id from #aa) group by pd_id
update #aa set pdsl = #bb.pdsl ,kcsl = #bb.kcsl from #aa,#bb where #aa.pd_id = #bb.pd_id
update #aa set pysl = #bb.yksl from #aa,#bb where #aa.pd_id = #bb.pd_id and #bb.yksl >=0
update #aa set pksl = #bb.yksl from #aa,#bb where #aa.pd_id = #bb.pd_id and #bb.yksl <0
select * from #aa order by cdate desc
set nocount off
-- return
end
else if @lx = 1
begin
SELECT z_tb_pddt.cdate, z_tb_pddt.pd_id, sum(z_tb_pdls.kcsl) kcsl ,sum(z_tb_pdls.pdsl)as pdsl , sum(z_tb_pdls.pdsl-z_tb_pdls.kcsl) as yksl
FROM z_tb_pddt INNER JOIN z_tb_pdls ON z_tb_pddt.pd_id = z_tb_pdls.pd_id where z_tb_pdls.mm_id = (select id from mm_data where mm_code = @dh)
and z_tb_pddt.cdate >= @cdate and z_tb_pddt.cdate<=@cdate1 and z_tb_pddt.sx = 1
group by z_tb_pddt.cdate, z_tb_pddt.pd_id order by z_tb_pddt.pd_id desc
set nocount off
end
else if @lx = 2
begin
SELECT a.kcsl, a.pdsl, b.MM_Code,b.MM_Name,(a.pdsl-a.kcsl) as yksl, b.MM_Unit FROM z_tb_pdls a INNER JOIN MM_Data b ON a.mm_id = b.ID
Where a.pd_id = @dh order by b.MM_code
set nocount off
end
else if @lx >= 3
begin
SELECT MM_Code,MM_Name,MM_CurrStore,cast(''as varchar(30))as cdate ,id as mm_id into #cc from mm_data where id not in (
select mm_id from z_tb_pdls where pd_id in (select pd_id from z_tb_pddt where cdate >= @cdate and cdate<=@cdate1 and sx = 1))order by id desc SELECT mm_id,cdate into #dd
FROM z_tb_pddt , z_tb_pdls where z_tb_pddt.pd_id = z_tb_pdls.pd_id and
z_tb_pddt.cdate >= @cdate and z_tb_pddt.cdate<=@cdate1 and z_tb_pddt.sx = 1 order by z_tb_pddt.cdate desc
update #cc set cdate = #dd.cdate from #cc ,#dd where #cc.mm_id = #dd.mm_id select * from #cc order by mm_code
end在sql server 查询分析器查询有记录返回值用Vb调用时
如代码:
Dim cmd As New ADODB.Command
Dim parm As New ADODB.Parameter
cmd.ActiveConnection = cn
cmd.CommandText = " p_pdcx "
cmd.CommandType = adCmdStoredProc
Set parm = cmd.CreateParameter(" @lx ", adBSTR, adParamInput, 8, CStr(Me.Combo1.ListIndex))
cmd.Parameters.Append parm
If Me.Combo1.ListIndex < 4 Then
Set parm = cmd.CreateParameter(" @cdate ", adBSTR, adParamInput, 20, Trim(Text7.Text))
cmd.Parameters.Append parm
Set parm = cmd.CreateParameter(" @cdate1 ", adBSTR, adParamInput, 20, Trim(Text8.Text))
cmd.Parameters.Append parm
Else
Set parm = cmd.CreateParameter(" @cdate ", adBSTR, adParamInput, 20, CStr(Date))
cmd.Parameters.Append parm
Set parm = cmd.CreateParameter(" @cdate1 ", adBSTR, adParamInput, 20, CStr(Date - Text1.Text))
cmd.Parameters.Append parm
End If
Set parm = cmd.CreateParameter(" @dh ", adBSTR, adParamInput, 50, Trim(Text1.Text))
cmd.Parameters.Append parm
Set rsTemp = New ADODB.Recordset
Set rsTemp = cmd.Execute()
Set Me.TDBGrid2.DataSource = rstemp
没有结果, 为什么?以前也是这样不过加了
set nocount on
set nocount off 就没问题了现在这个什么样都不行
在DELPHI中就没有这个怪问题
CREATE PROCEDURE p_pdcx
@lx varchar(2),
@cdate varchar(20),
@cdate1 varchar(20),
@dh varchar(100)
AS
if @lx = 0
begin
select pd_id,cdate,stext,0 as pdsl,0 as kcsl,0 as pksl,0 as pysl into #aa from z_tb_pddt where cdate >= @cdate and cdate<=@cdate1 and sx = 1
select sum(pdsl) as pdsl,sum(pdsl-kcsl) as yksl,sum(kcsl) as kcsl,pd_id into #bb from z_tb_pdls where pd_id in (select pd_id from #aa) group by pd_id
update #aa set pdsl = #bb.pdsl ,kcsl = #bb.kcsl from #aa,#bb where #aa.pd_id = #bb.pd_id
update #aa set pysl = #bb.yksl from #aa,#bb where #aa.pd_id = #bb.pd_id and #bb.yksl >=0
update #aa set pksl = #bb.yksl from #aa,#bb where #aa.pd_id = #bb.pd_id and #bb.yksl <0
select * from #aa order by cdate desc
set nocount off
-- return
end
else if @lx = 1
begin
SELECT z_tb_pddt.cdate, z_tb_pddt.pd_id, sum(z_tb_pdls.kcsl) kcsl ,sum(z_tb_pdls.pdsl)as pdsl , sum(z_tb_pdls.pdsl-z_tb_pdls.kcsl) as yksl
FROM z_tb_pddt INNER JOIN z_tb_pdls ON z_tb_pddt.pd_id = z_tb_pdls.pd_id where z_tb_pdls.mm_id = (select id from mm_data where mm_code = @dh)
and z_tb_pddt.cdate >= @cdate and z_tb_pddt.cdate<=@cdate1 and z_tb_pddt.sx = 1
group by z_tb_pddt.cdate, z_tb_pddt.pd_id order by z_tb_pddt.pd_id desc
set nocount off
end
else if @lx = 2
begin
SELECT a.kcsl, a.pdsl, b.MM_Code,b.MM_Name,(a.pdsl-a.kcsl) as yksl, b.MM_Unit FROM z_tb_pdls a INNER JOIN MM_Data b ON a.mm_id = b.ID
Where a.pd_id = @dh order by b.MM_code
set nocount off
end
else if @lx >= 3
begin
SELECT MM_Code,MM_Name,MM_CurrStore,cast(''as varchar(30))as cdate ,id as mm_id into #cc from mm_data where id not in (
select mm_id from z_tb_pdls where pd_id in (select pd_id from z_tb_pddt where cdate >= @cdate and cdate<=@cdate1 and sx = 1))order by id desc SELECT mm_id,cdate into #dd
FROM z_tb_pddt , z_tb_pdls where z_tb_pddt.pd_id = z_tb_pdls.pd_id and
z_tb_pddt.cdate >= @cdate and z_tb_pddt.cdate<=@cdate1 and z_tb_pddt.sx = 1 order by z_tb_pddt.cdate desc
update #cc set cdate = #dd.cdate from #cc ,#dd where #cc.mm_id = #dd.mm_id select * from #cc order by mm_code
end在sql server 查询分析器查询有记录返回值用Vb调用时
如代码:
Dim cmd As New ADODB.Command
Dim parm As New ADODB.Parameter
cmd.ActiveConnection = cn
cmd.CommandText = " p_pdcx "
cmd.CommandType = adCmdStoredProc
Set parm = cmd.CreateParameter(" @lx ", adBSTR, adParamInput, 8, CStr(Me.Combo1.ListIndex))
cmd.Parameters.Append parm
If Me.Combo1.ListIndex < 4 Then
Set parm = cmd.CreateParameter(" @cdate ", adBSTR, adParamInput, 20, Trim(Text7.Text))
cmd.Parameters.Append parm
Set parm = cmd.CreateParameter(" @cdate1 ", adBSTR, adParamInput, 20, Trim(Text8.Text))
cmd.Parameters.Append parm
Else
Set parm = cmd.CreateParameter(" @cdate ", adBSTR, adParamInput, 20, CStr(Date))
cmd.Parameters.Append parm
Set parm = cmd.CreateParameter(" @cdate1 ", adBSTR, adParamInput, 20, CStr(Date - Text1.Text))
cmd.Parameters.Append parm
End If
Set parm = cmd.CreateParameter(" @dh ", adBSTR, adParamInput, 50, Trim(Text1.Text))
cmd.Parameters.Append parm
Set rsTemp = New ADODB.Recordset
Set rsTemp = cmd.Execute()
Set Me.TDBGrid2.DataSource = rstemp
没有结果, 为什么?以前也是这样不过加了
set nocount on
set nocount off 就没问题了现在这个什么样都不行
在DELPHI中就没有这个怪问题
解决方案 »
- VB中利用鼠标移动图象的技巧
- 如何动态添加菜单项!
- excel
- TRUE DBGRID的使用问题请教
- 请问在不改变picture控件大小的情况下,如何使图像“完全填充”到picture控件中,Autosize不行(不用Image控件)!
- 怎样把一个程序捆绑(盒冰)到另一个程序中?
- 7月18日 ” 关于中国教育信息管理系统”的最新消息发布(关于软件组成)。
- vb代码如何实现发带附加的Email
- 再次傻傻得问activereport2pro使用B4纸张的问题
- 如何richtextbox把中文字符当2个字符处理?
- 求VB掉用SQL存储过程并获取返回值方法~帅哥靓妹快进
- 怎么加webbrowser 到form 上啊
请将 set nocount on 加在 As 下面
还有你上面得存储过程 建议改成 goto 结构,不要在 if 内return
有几个操作返回操作数,就会返回几个记录集你可以同过以下得代码查看
Set rs = cmd.ExecuteMsgBox rs.Fields.CountSet rs = rs.NextRecordset
MsgBox rs.Fields.Count
Set rs = rs.NextRecordset
MsgBox rs.Fields.Count你可以看到,如果有多个记录集,如果只有 1个有返回记录则其字段数会大于 0
set nocount on
set nocount off 也是不行的
2 :用sql查询分析器查是没有问题的但如果是这样的代码:
Set rs = cmd.ExecuteMsgBox rs.Fields.CountSet rs = rs.NextRecordset
MsgBox rs.Fields.Count
Set rs = rs.NextRecordset
MsgBox rs.Fields.Count
会报在关闭状态下不能操作记录集
你加再哪了,我怎么没看到,你看看把 set nocount on
加在最前面,去掉所有得 set nocount off
看看行不行
set nocount on
现在加了没有用 set nocount off
现在是这样的了:
Set rs = cmd.Execute
MsgBox rs.Fields.Count
显示有7个字段但没有他把记录集返给我是个空的!而我在sql查询分析器查又有记录集呢
这又为什么?
还有,返回得记录集是只读只向前的游标,要注意操作方法
你可以 先遍历看看是否为空集,不要一下子 Set Me.TDBGrid2.DataSource = rstemp
do until rstemp.eof=true
msgbox rstemp(0).value
rstemp.movenext
loop
在VB中我发现这种情况:
存储过程如:CREATE PROCEDURE p_zdh
@lx varchar(8)
AS
set nocount on
DECLARE @dh int
select @dh = dh from z_tb_dh where stype = RTRIM(@lx)
if @dh is null
begin
set @dh = 0
insert z_tb_dh values(@dh ,RTRIM(@lx))
end
update z_tb_dh set dh = @dh + 1 where stype = RTRIM(@lx)
select @dh as dh
set nocount off
按 Set rs = cmd.Execute
是可以获得值的
如这种存储过程
CREATE PROCEDURE p_pdcx
@lx int,
@cdate varchar(20),
@cdate1 varchar(20),
@dh varchar(100)
AS set nocount on --如果不加这个啊VB干脆直接报错,
select pd_id,cdate,stext,0 as pdsl,0 as kcsl,0 as pksl,0 as pysl from z_tb_pddt where cdate >= @cdate and cdate<=@cdate1 and sx = 1
select sum(pdsl) as pdsl,sum(pdsl-kcsl) as yksl,sum(kcsl) as kcsl,pd_id into #bb from z_tb_pdls where pd_id in (select pd_id from #aa) group by pd_id
update #aa set pdsl = #bb.pdsl ,kcsl = #bb.kcsl from #aa,#bb where #aa.pd_id = #bb.pd_id
update #aa set pysl = #bb.yksl from #aa,#bb where #aa.pd_id = #bb.pd_id and #bb.yksl >=0
update #aa set pksl = #bb.yksl from #aa,#bb where #aa.pd_id = #bb.pd_id and #bb.yksl <0
select * from #aa order by cdate desc
set nocount off
调用
Set rs = cmd.Execute 获得是个空集
如果这个呢
CREATE PROCEDURE p_pdcx
@lx int,
@cdate varchar(20),
@cdate1 varchar(20),
@dh varchar(100)
AS select pd_id,cdate,stext,0 as pdsl,0 as kcsl,0 as pksl,0 as pysl from z_tb_pddt where cdate >= @cdate and cdate<=@cdate1 and sx = 1
调用
Set rs = cmd.Execute 就有记录值
为什么???
真的是临时表的原因??我在试试
我直接用ADODB.Recordset open方式调用最后一个类型的存储过程有记录集喔如果调用第二个类型的存储过程就空集??为什么?
真的受不了有谁小例子源代码发得看看可以另开贴给分
测试代码如下VB
=========================
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Commandcn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Northwind;Data Source=super"
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "test1"
cmd.CreateParameter "customerId", adVarChar, adParamInput, 50
cmd.Parameters("@customerId").Value = "HILAA"Set rs = cmd.Execute
Do Until rs.EOF = True
Debug.Print rs.Fields(0).Value
rs.MoveNext
Loop
rs.Close
cn.Close==========================
SQL 存储过程create procedure test1
@customerId varchar(50)
as --临时表
set nocount on
select * into #aa from Northwind.dbo.Orders Where customerId=@customeridselect * from #aa
set nocount off