April 1, 2003 at 2:04 pm
hello everybody,
I am trying to create a temporary table in my stored procedure and use the result set in the calling program( which is in VB). I am using ADO. I am getting an error when I run the VB. operation is no allowed when the object is closed. I have marked the line where it occurs. What am I doing wrong?
This is my stored proc.
CREATE PROCEDURE sp_get_total_qty_rate
@customerid char(15),
@datefrom datetime,
@dateto datetime
AS
DECLARE @dDateFrom datetime, @dDateTo datetime, @sCustomerid char(15), @fdnqty numeric, @nRate numeric
/* Create temporary table*/
CREATE TABLE #TempTable ( Quantity numeric, Rate numeric)
INSERT INTO #TempTable VALUES ( 123, 20 )
SELECT * FROM #TempTable
RETURN
GO
Below is the code in VB where i am calling the stored proc.
Dim oRSRate As New ADODB.Recordset
Dim oCmd As New ADODB.Command
Dim oParam As New ADODB.Parameter
Dim oConn As New ADODB.Connection
Dim sStr As String
oConn.Open sConn
Set oCmd.ActiveConnection = oConn
oCmd.CommandText = "sp_get_total_qty_rate"
oCmd.CommandType = adCmdStoredProc
Set oParam = oCmd.CreateParameter("customerid", adChar, adParamInput, 15, CustomerID.Value)
oCmd.Parameters.Append oParam
Set oParam = oCmd.CreateParameter("datefrom", adDate, adParamInput, , Format(amDateFrom.Value, "yyyy/mm/dd"))
oCmd.Parameters.Append oParam
Set oParam = oCmd.CreateParameter("dateto", adDate, adParamInput, , Format(amDateTo.Value, "yyyy/mm/dd"))
oCmd.Parameters.Append oParam
oCmd.CommandTimeout = 30
Set oRSRate = oCmd.Execute()
/*** This is the statement wher the error occurs **********/
While Not oRSRate.EOF
sStr = sStr & oRSRate("Quantity") & " " & oRSRate("Rate") & vbCrLf
oRSRate.MoveNext
Wend
MsgBox sStr
oRSRate.Close
April 1, 2003 at 2:09 pm
Hello,
What you need to do is to "set nocount on" in your stored procedure, before the create table and then place a "set nocount off" after the insert statement. You stored procedure is actual return multiple recordsets, One recordset for the insert, which returns a closed recordset because there is no data and one recordset for the select statement. To return only one recordset you need to turn the nocount on. Hope this helps,
Greg
Gregory Olds
Systems Developer
Gregory Olds
Systems Developer
April 1, 2003 at 2:14 pm
Your new stored procedure should look like this:
CREATE PROCEDURE sp_get_total_qty_rate
@customerid char(15),
@datefrom datetime,
@dateto datetime
AS
DECLARE @dDateFrom datetime, @dDateTo datetime, @sCustomerid char(15), @fdnqty numeric, @nRate numeric
set nocount on
/* Create temporary table*/
CREATE TABLE #TempTable ( Quantity numeric, Rate numeric)
INSERT INTO #TempTable VALUES ( 123, 20 )
set nocount off
SELECT * FROM #TempTable
RETURN
GO
Gregory Olds
Systems Developer
Gregory Olds
Systems Developer
April 1, 2003 at 3:37 pm
Out of curiosity, why do you turn nocount off again before the select statement. I have been using the SET NOCOUNT ON in all my procedures but I never turn it off again.
April 2, 2003 at 11:10 am
Hello Gregory
Thanks for ur response, It was exactly what I wanted.
Regards
Reem
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply