说明在sql server中使用没问题,但是在vb调取提取记录是就出现问题.
请各位大侠帮我修改下.谢谢SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- spDayRead ALTER proc spDayRead
--@strDate DATETIME
as
begin
CREATE TABLE ##TEMP1
(
BillID CHAR(14) ,
OutDate DATETIME ,
OilName CHAR(10) ,
OilType CHAR(10) ,
OilSort CHAR(4) ,
RealQuantity DECIMAL(6,4) ,
BuyerName CHAR(50) ,
Dname CHAR(50) ,
IfOut CHAR(10)
)
CREATE TABLE ##TEMP2
(
BillID CHAR(14) ,
OutDate DATETIME ,
OilName CHAR(10) ,
OilType CHAR(10) ,
OilSort CHAR(4) ,
RealQuantity DECIMAL(6,4) ,
BuyerName CHAR(50) ,
Dname CHAR(50) ,
IfOut CHAR(10)
)
DECLARE @REALQANTITY DECIMAL(6,4)
DECLARE @TATOLQANTITY DECIMAL(6,4)
DECLARE @STRSQL VARCHAR(2000)
INSERT INTO ##TEMP1 SELECT OutOilNoticeID AS BillID, OutOilDate AS OutDate,OilName,
OilType, case OilSort when 'J' then '经营' when 'G' then '国储' when 'D' then '代管' end as OilSort,
RealQuantity, BuyerName,
Dname,case IfOut when '1' then '已发' when '0' then '未发' end as IfOut
FROM tOutOilNotice
WHERE IFOUT='1' AND OutOilDate = CONVERT(char(19), GETDATE(), 101)
--OutOilDate=@strDate "
SELECT @REALQANTITY=case SUM(RealQuantity)when null then SUM(RealQuantity) else 0 end FROM ##TEMP1INSERT INTO ##TEMP2
SELECT OutOilNoticeID AS BillID, OutOilDate AS OutDate,OilName,
OilType, case OilSort when 'J' then '经营' when 'G' then '国储' when 'D' then '代管' end as OilSort,
RealQuantity, BuyerName ,
Dname, case IfOut when '1' then '已发' when '0' then '未发' end as IfOut
FROM tOutOilNotice
WHERE OutOilDate = CONVERT(char(19), GETDATE(), 101)
--OutOilDate=@strDate
ORDER BY IfOutSELECT @TATOLQANTITY =case SUM(RealQuantity)when null then SUM(RealQuantity) else 0 end FROM ##TEMP2INSERT INTO ##TEMP2(BillID,OilName,OilType,OilSort,RealQuantity,BuyerName,Dname,IfOut) VALUES('','','','',@REALQANTITY,'实收量','统计','')
INSERT INTO ##TEMP2(BillID,OilName,OilType,OilSort,RealQuantity,BuyerName,Dname,IfOut) VALUES('','','','',@TATOLQANTITY,'应收量','统计','')
SELECT * FROM ##TEMP2 DROP TABLE ##TEMP1
DROP TABLE ##TEMP2return
endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
请各位大侠帮我修改下.谢谢SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- spDayRead ALTER proc spDayRead
--@strDate DATETIME
as
begin
CREATE TABLE ##TEMP1
(
BillID CHAR(14) ,
OutDate DATETIME ,
OilName CHAR(10) ,
OilType CHAR(10) ,
OilSort CHAR(4) ,
RealQuantity DECIMAL(6,4) ,
BuyerName CHAR(50) ,
Dname CHAR(50) ,
IfOut CHAR(10)
)
CREATE TABLE ##TEMP2
(
BillID CHAR(14) ,
OutDate DATETIME ,
OilName CHAR(10) ,
OilType CHAR(10) ,
OilSort CHAR(4) ,
RealQuantity DECIMAL(6,4) ,
BuyerName CHAR(50) ,
Dname CHAR(50) ,
IfOut CHAR(10)
)
DECLARE @REALQANTITY DECIMAL(6,4)
DECLARE @TATOLQANTITY DECIMAL(6,4)
DECLARE @STRSQL VARCHAR(2000)
INSERT INTO ##TEMP1 SELECT OutOilNoticeID AS BillID, OutOilDate AS OutDate,OilName,
OilType, case OilSort when 'J' then '经营' when 'G' then '国储' when 'D' then '代管' end as OilSort,
RealQuantity, BuyerName,
Dname,case IfOut when '1' then '已发' when '0' then '未发' end as IfOut
FROM tOutOilNotice
WHERE IFOUT='1' AND OutOilDate = CONVERT(char(19), GETDATE(), 101)
--OutOilDate=@strDate "
SELECT @REALQANTITY=case SUM(RealQuantity)when null then SUM(RealQuantity) else 0 end FROM ##TEMP1INSERT INTO ##TEMP2
SELECT OutOilNoticeID AS BillID, OutOilDate AS OutDate,OilName,
OilType, case OilSort when 'J' then '经营' when 'G' then '国储' when 'D' then '代管' end as OilSort,
RealQuantity, BuyerName ,
Dname, case IfOut when '1' then '已发' when '0' then '未发' end as IfOut
FROM tOutOilNotice
WHERE OutOilDate = CONVERT(char(19), GETDATE(), 101)
--OutOilDate=@strDate
ORDER BY IfOutSELECT @TATOLQANTITY =case SUM(RealQuantity)when null then SUM(RealQuantity) else 0 end FROM ##TEMP2INSERT INTO ##TEMP2(BillID,OilName,OilType,OilSort,RealQuantity,BuyerName,Dname,IfOut) VALUES('','','','',@REALQANTITY,'实收量','统计','')
INSERT INTO ##TEMP2(BillID,OilName,OilType,OilSort,RealQuantity,BuyerName,Dname,IfOut) VALUES('','','','',@TATOLQANTITY,'应收量','统计','')
SELECT * FROM ##TEMP2 DROP TABLE ##TEMP1
DROP TABLE ##TEMP2return
endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
问题是为什么我在用vb 调用的时候
出现问题
Dim recReturn As ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = gConnection
cmd.CommandText = "spDayRead"
cmd.CommandType = adCmdStoredProc
Set recReturn = New ADODB.Recordset
Set recReturn = cmd.Execute()
If Not recReturn.EOF Then '此时出现问题 提示是对象关闭是,无法操作
原因是存储过程有问题
我弄了个简单的就能过
这不是主要问题
我说了问题不在vb
在我的存储过程上
我想问的是我的存储过程是否合法
vb调去的时候为什么就无法实现呢?