VB6记录集无法使用存储过程打开 [英] VB6 Recordset not opening with stored procedure

问题描述

我已经制作了一个存储过程来构建一个临时表，然后返回最终结果.

当我在SSMS中对其进行测试时，它返回一个记录集，并且可以正常工作；当我在VB中进行记录时，记录集在通过所有内容后将无法打开，但在VB或ADODB中均未返回错误.
存储过程

I have made a stored procedure to build a temporary table and then return the end results.

When i test it in SSMS it returns a single recordset and works ok, when i do it in VB the recordset will not open after passing everything but no errors are returned in either VB or ADODB.

Stored Procedure

USE [MainDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SYS_Purchasing_CDOrder] @PONo as varchar(10) AS
BEGIN
	DECLARE @HeaderID int
	DECLARE @FrameID int
	DECLARE @RowOn int = 1
	DECLARE @RowCount int
	DECLARE @tmpParts TABLE(HeaderID int, FrameID int, SetCode varchar(3), PCode varchar(99))
	DECLARE @tmpPurchase TABLE(LineID int IDENTITY(1,1) NOT NULL, CustRef varchar(99), HeaderID int, FrameID int, PONo varchar(10), Width int, Height int, Qty int, OrdDate DATE, DelDate DATE, CDPart varchar(99), LB varchar(3), Opener varchar(99), Handed varchar(99))
	
	INSERT INTO @tmpPurchase SELECT JobNo+'_'+CustRef, HeaderID, FrameID, PONo, Width, Height, Qty, CAST(OrdDate AS DATE), CAST(DelDate AS DATE), null, null, null, null FROM SYS_Purchasing WHERE PONo = @PONo AND PartType = 'CDOOR'
	SET @RowCount = (SELECT MAX(LineID) FROM @tmpPurchase)
	WHILE @RowOn <=@RowCount
	BEGIN
		SET @HeaderID = (SELECT HeaderID FROM @tmpPurchase WHERE LineID = @RowOn)
		SET @FrameID = (SELECT FrameID FROM @tmpPurchase WHERE LineID = @RowOn)
		INSERT INTO @tmpParts SELECT HEADER_ID, FRAME_ID, SetCode, PCode FROM PARTS WHERE (HEADER_ID = @HeaderID AND FRAME_ID = @FrameID) AND (SetCode = 'CDR' OR SetCode = 'LB')
		UPDATE @tmpPurchase SET CDPart = (SELECT PCODE FROM @tmpParts WHERE HeaderID = @HeaderID AND FrameID = @FrameID AND SetCode = 'CDR') WHERE LineID = @RowOn
		UPDATE @tmpPurchase SET LB = (SELECT SETCODE FROM @tmpParts WHERE HeaderID = @HeaderID AND FrameID = @FrameID AND SetCode = 'LB') WHERE LineID = @RowOn
		UPDATE @tmpPurchase SET OPENER = (SELECT SASHPCODE FROM SASHES WHERE Header_ID = @HeaderID AND Frame_ID = @FrameID AND SASHPCODE LIKE 'CDS%') WHERE LineID = @RowOn
		UPDATE @tmpPurchase SET Handed = (SELECT SASHType FROM SASHES WHERE Header_ID = @HeaderID AND Frame_ID = @FrameID AND SASHPCODE LIKE 'CDS%') WHERE LineID = @RowOn
		SET @RowOn += 1
	END
	SELECT CustRef, HeaderID, FrameID, PONo, Width, Height, Qty, OrdDate, DelDate, CDPart, LB, Opener, Handed, SupplierCode, ColourExt, ColourInt, Cassette, SpecialRequirements  FROM @tmpPurchase LEFT JOIN SYS_PURCHASING_CDMatrix ON CDPart = SYS_PURCHASING_CDMatrix.WDProdCode 
END

VB代码

VB Code

Set Cmd1 = New ADODB.Command
Set rs = New ADODB.Recordset
Set sqlServer = New ADODB.Connection
'extra code here to open the connection, this opens ok
sqlServer.Open
Cmd1.ActiveConnection = sqlServer
Cmd1.CommandType = adCmdStoredProc
Cmd1.CommandText = "[SYS_Purchasing_CDOrder]"
Set Parm1 = Cmd1.CreateParameter("@PONo", adVarChar, adParamInput, 10, OrderNo)
Cmd1.Parameters.Append Parm1
rs.Open Cmd1
Debug.Print rs.State 'This always returns 0

如果我将存储过程更改为只直接从表中返回数据，它就可以正常工作，但是当我使用临时表时，它就会倒下并死掉.

之所以这样做，是因为Parts表具有超过1100万条记录(超过5gb的数据)，并且速度可能很慢.

在此先感谢

Ray

If i change the stored procedure to just return data straight from a table it works ok, but when im using the temp tables it just falls over and dies.

The reason for doing it this way is because the Parts table has over 11 million records (over 5gb of data) and can be slow.

Thanks in advance

Ray

推荐答案

一些想法:

• 创建不带方括号的命令:Cmd1.CommandText = "SYS_Purchasing_CDOrder"
• 创建不带"at"的参数:Cmd1.CreateParameter("PONo", ...
• 在过程顶部附近，调用SET NOCOUNT ON;我有时看到这样做有帮助.
• 检查ARITHABORT设置.

A few ideas:

• Create your command without the square brackets: Cmd1.CommandText = "SYS_Purchasing_CDOrder"
• Create your parameter without the ''at'': Cmd1.CreateParameter("PONo", ...
• Near the top of your procedure, call SET NOCOUNT ON; I''ve seen sometimes that this helps.
• Check the ARITHABORT setting.

好，我已经通过链接一些视图和存储过程对此进行了排序.

问候

Ray

OK i have sorted this by linking some views and stored procedures.

Regards

Ray

Pablo，谢谢您的答复.

完成所有这些操作，但仍然没有运气，我已经输入了nocount，但是修改后它不会显示.

我确实在我的帖子中提到过，将其作为仅已联接的表运行将花费很长时间，因为其中有1个表具有超过1100万条记录，这就是为什么我只返回需要的表的原因.
问候

雷

Pablo, thanks for your reply.

Done all of them and still no luck, I had already put nocount in but it does not show when I modify it.

I did mention in my post that running this as just joined tables would take to long as 1 of the tables has over 11 millions records in it which is why i return just the ones I need.

Regards

Ray

这篇关于VB6记录集无法使用存储过程打开的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！