Problems Using Stored Procedure With Numeric Parameters In Excel 2003 / VBA

File Structure:


--**************************************************************************************************************************

USE [Test]

GO

/****** Object: Table [dbo].[tbl_Test] Script Date: 03/04/2008 09:51:20 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tbl_Test](

[TestID] [int] IDENTITY(1,1) NOT NULL,

[TestValue] [numeric](18, 2) NOT NULL CONSTRAINT [DF_tbl_Test_TestValue] DEFAULT ((0)),

CONSTRAINT [PK_tbl_Test] PRIMARY KEY CLUSTERED

(

[TestID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

--**************************************************************************************************************************

Stored Procedure:


--**************************************************************************************************************************

USE [Test]

GO

/****** Object: StoredProcedure [dbo].[proc_Ins_Test] Script Date: 03/04/2008 09:52:23 ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER ON

GO

/*

Name: proc_Ins_Test

*/

ALTER PROCEDURE [dbo].[proc_Ins_Test]

(

@TestValue numeric(18,2)=0

)

AS

BEGIN TRAN

BEGIN

INSERT INTO tbl_Test (TestValue) VALUES (@TestValue)

END

COMMIT TRAN

SELECT MAX(TestID) AS MaxValue FROM tbl_Test

RETURN 1

--**************************************************************************************************************************

(I have it returning a value through SELECT to simulate returning a key value from the newly inserted record. I also use the RETURN value to fetch any error codes.)



'***************************************************************************************************************************

Public Sub PSInsertTest(cnConn As ADODB.Connection)




Dim rst As ADODB.Recordset

Dim cmd As ADODB.Command

Dim stProcName As String 'Stored Procedure name





Set rst = New ADODB.Recordset

Set cmd = New ADODB.Command





'Defines the stored procedure commands

stProcName = "dbo.proc_Ins_Test" 'Define name of Stored Procedure to execute.

cmd.CommandType = adCmdStoredProc 'Define the ADODB command

cmd.ActiveConnection = cnConn 'Set the command connection string

cmd.CommandText = stProcName 'Define Stored Procedure to run





'Append Parameters

With cmd


.Parameters.Append .CreateParameter("@return_value", adInteger, adParamReturnValue)

.Parameters.Append .CreateParameter("@TestValue", adNumeric, adParamInput)

.Parameters.Item("@TestValue").NumericScale = 2

.Parameters.Item("@TestValue").Precision = 18

.Parameters.Item("@TestValue").Value = 123.45

End With





'Execute stored procedure and return to a recordset

Set rst = cmd.Execute





If Not rst.EOF Then

rst.MoveFirst

MsgBox (rst.Fields("MaxValue").Value)

Else


MsgBox ("No Value")

End If





'Close database connection and clean up

If CBool(rst.State And adStateOpen) = True Then rst.Close

Set rst = Nothing



End Sub
'***************************************************************************************************************************

It goes ahead and inserts the record, but gets an error when I try to retrieve the "SELECT"ed value. I get the same results with the following statement:


rst.Open cmd, , adOpenStatic, adLockReadOnly


The error is: Operation is not allowed when the object is closed





If I use the following command, I get the error below:


rst.Open cmd.Execute

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.



Any insights would be helpful




Bob