Parameter error calling a stored procedure from an .adp code

[cwadams1955]

Hi,

I have an Access 2003 .adp front-end on a SQL Server 2000 back-end. One of the tasks I need to build allows the operator to select a Project Number,Task Number, and Week-Ending Date, and copy the records associated with that criteria to a new Project/Task/Week-Ending Date. I created a stored procedure which accepts the parameters and does the actual work of copying the records, and it works fine when I run it from Query Analyzer and manually input the parameter data. But when I try to call the procedure from VBA code behind the pick screen, I get an error message:

"Procedure 'sp_CopyBillRecords' expects parameter '@WEDateFrom', which was not supplied."

If I set a breakpoint and go into Debug mode, the error occurs on the .Parameters.Refresh statement. Here's the stored procedure code:

CREATE PROCEDURE dbo.sp_CopyBillRecords

@WEDateFrom		datetime,
@WEDateTo		datetime,
@ProjectFrom	varchar(10),
@ProjectTo		varchar(10),
@TaskFrom		varchar(50), 
@TaskTo			varchar(50), 
@ServiceAreaIn	varchar(4), 
@ErrCode		integer OUTPUT

AS

DECLARE	
/* -------------------------------------------
Temp variables for header record
---------------------------------------------- */
@ProjectNo		varchar (10), 
@TaskNo			varchar (50), 
@WEDate			datetime, 
@ServiceArea	varchar (4), 
@Client			varchar (255), 
@ReportDate		datetime, 
@Status			smallint, 
@ApprovedBy		varchar (20), 
@ApprovalDate	datetime, 
@Complete		bit, 
@Comment		varchar (50), 
/* -------------------------------------------
Temp variables for detail records
---------------------------------------------- */
@SequenceNo		bigint, 
@ServiceCode	varchar (10), 
@Category		varchar (10), 
@ChargeCode		varchar (20), 
@Description	varchar (500), 
@Qty			decimal, 
@UnitRate		decimal, 
@UOM			varchar (50), 
@Total			decimal, 
/* -------------------------------------------
Temp variables for Equipment usage records
---------------------------------------------- */
@EquipmentNo	varchar (20), 
/* -------------------------------------------
Temp variables for Working fields
---------------------------------------------- */
@SqlState		nvarchar (4000),
@NewSequenceNo	bigint

SET NOCOUNT ON

SELECT @WEDateTo = dbo.DateOnly(@WEDateTo)
SELECT @WEDateFrom = dbo.DateOnly(@WEDateFrom)

/* -------------------------------------------
Begin SQL transaction
---------------------------------------------- */
BEGIN TRANSACTION

	/* -------------------------------------------
	Copy Header record first
	---------------------------------------------- */
	SELECT @Client = Client 
	FROM	[dbo].[tblBillRecordHeader] 
	WHERE ([ProjectNo] = @ProjectFrom 
	 AND	[TaskNo] = @TaskFrom 
	 AND	[WEDate] = @WEDateFrom 
	 AND	[ServiceArea] = @ServiceAreaIn)

	/* -------------------------------------------
	Set up for new Header record.
	Not all fields will be written out,
	several should use the default values only.
	---------------------------------------------- */
	SET @ProjectNo = @ProjectTo
	SET	@TaskNo = @TaskTo
	SET	@WEDate = @WEDateTo
	SET @ServiceArea = @ServiceAreaIn

	SET @SqlState = 'INSERT INTO [dbo].[tblBillRecordHeader] 
					(ProjectNo, 
					TaskNo, 
					WEDate, 
					ServiceArea, 
					Client) 
			VALUES	(' + dbo.quotestring(@ProjectNo) + ', '
					+ dbo.quotestring(@TaskNo) + ', '
					+ dbo.quotestring(@WEDate) + ', '
					+ dbo.quotestring(@ServiceArea) + ', '
					+ dbo.quotestring(@Client) + ')'

	exec (@SqlState)
	IF @@ROWCOUNT<>1
		Begin
			-- Write to header failed
			SET @ErrCode=91
			GOTO TRAN_FAILED
		End

	/* -------------------------------------------
	Create a cursor to read the detail records.
	---------------------------------------------- */
	DECLARE curDetail INSENSITIVE CURSOR FOR 
		SELECT 	[ProjectNo], 
				[TaskNo], 
				[WEDate], 
				[ServiceArea], 
				[SequenceNo], 
				[ServiceCode], 
				[Category], 
				[ChargeCode], 
				[Description], 
				[Qty], 
				[UnitRate], 
				[UOM], 
				[Total], 
				[Comment] 
		FROM	[dbo].[tblBillRecordDetail] 
		WHERE	[ProjectNo] = @ProjectFrom 
		AND		[TaskNo] = @TaskFrom 
		AND		[WEDate] = @WEDateFrom 
		AND		[ServiceArea] = @ServiceAreaIn

	/* -------------------------------------------
	Open cursor and read first record.
	---------------------------------------------- */
	OPEN curDetail
	FETCH NEXT FROM curDetail 
	INTO	@ProjectNo, 
		@TaskNo, 
		@WEDate, 
		@ServiceArea, 
		@SequenceNo, 
		@ServiceCode, 
		@Category, 
		@ChargeCode, 
		@Description, 
		@Qty, 
		@UnitRate, 
		@UOM, 
		@Total, 
		@Comment

	/* -------------------------------------------
	Initialize the sequence no for new detail records.
	---------------------------------------------- */
	SET @NewSequenceNo = 0

	/* -------------------------------------------
	Begin looping through all matching detail records.
	---------------------------------------------- */
	WHILE (@@FETCH_STATUS = 0)
		BEGIN --B1
			/* -------------------------------------------
			Set up for new Detail record.
			Not all fields will be written out,
			several should use the default values only.
			---------------------------------------------- */
			SET @ProjectNo = @ProjectTo
			SET	@TaskNo = @TaskTo
			SET	@WEDate = @WEDateTo
			SET @NewSequenceNo = @NewSequenceNo + 1

			SET @SqlState = 'INSERT INTO [dbo].[tblBillRecordDetail] 
										(ProjectNo, 
										TaskNo, 
										WEDate, 
										ServiceArea, 
										SequenceNo, 
										ServiceCode, 
										Category, 
										ChargeCode, 
										Description, 
										UnitRate, 
										UOM) 
								VALUES	(' + dbo.quotestring(@ProjectNo) + ', '
										+ dbo.quotestring(@TaskNo) + ', '
										+ dbo.quotestring(@WEDate) + ', '
										+ dbo.quotestring(@ServiceArea) + ', '
										+ dbo.quotestring(@SequenceNo) + ', '
										+ dbo.quotestring(@ServiceCode) + ', '
										+ dbo.quotestring(@Category) + ', '
										+ dbo.quotestring(@ChargeCode) + ', '
										+ dbo.quotestring(@Description) + ', '
										+ dbo.quotestring(@UnitRate) + ', '
										+ dbo.quotestring(@UOM) + ')'

			exec (@SqlState)
			IF @@ROWCOUNT<>1
				Begin
					-- Write to detail failed
					SET @ErrCode=92
					GOTO TRAN_FAILED
				End

			/* -------------------------------------------
			Get next detail record.
			---------------------------------------------- */
			FETCH NEXT FROM curDetail 
			INTO	@ProjectNo, 
				@TaskNo, 
				@WEDate, 
				@ServiceArea, 
				@SequenceNo, 
				@ServiceCode, 
				@Category, 
				@ChargeCode, 
				@Description, 
				@Qty, 
				@UnitRate, 
				@UOM, 
				@Total, 
				@Comment
		
		END
		/* -------------------------------------------
		End detail record loop.
		---------------------------------------------- */

	/* -------------------------------------------
	Create a cursor to read the equipment usage records.
	---------------------------------------------- */
	DECLARE curEUDetail INSENSITIVE CURSOR FOR 
		SELECT 
				[ProjectNo], 
				[TaskNo], 
				[WEDate], 
				[ServiceArea], 
				[SequenceNo], 
				[EquipmentNo], 
				[Description], 
				[Qty], 
				[UOM], 
				[Comment] 
		FROM	[dbo].[tblBillRecordEUDetail] 
		WHERE	[ProjectNo] = @ProjectFrom 
		AND		[TaskNo] = @TaskFrom 
		AND		[WEDate] = @WEDateFrom 
		AND		[ServiceArea] = @ServiceAreaIn

	/* -------------------------------------------
	Open cursor and read first record.
	---------------------------------------------- */
	OPEN curEUDetail
	FETCH NEXT FROM curEUDetail 
	INTO	@ProjectNo, 
		@TaskNo, 
		@WEDate, 
		@ServiceArea, 
		@SequenceNo, 
		@EquipmentNo, 
		@Description, 
		@Qty, 
		@UOM, 
		@Comment 

	/* -------------------------------------------
	Initialize the sequence no for new EU records.
	---------------------------------------------- */
	SET @NewSequenceNo = 0

	/* -------------------------------------------
	Begin looping through all matching EU records.
	---------------------------------------------- */
	WHILE (@@FETCH_STATUS = 0)
		BEGIN --B2
			/* -------------------------------------------
			Set up for new EU record.
			Not all fields will be written out,
			several should use the default values only.
			---------------------------------------------- */
			SET @ProjectNo = @ProjectTo
			SET	@TaskNo = @TaskTo
			SET	@WEDate = @WEDateTo
			SET @NewSequenceNo = @NewSequenceNo + 1

			SET @SqlState = 'INSERT INTO [dbo].[tblBillRecordEUDetail] 
										(ProjectNo, 
										TaskNo, 
										WEDate, 
										ServiceArea, 
										SequenceNo, 
										EquipmentNo, 
										Description, 
										UOM) 
								VALUES	(' + dbo.quotestring(@ProjectNo) + ', '
										+ dbo.quotestring(@TaskNo) + ', '
										+ dbo.quotestring(@WEDate) + ', '
										+ dbo.quotestring(@ServiceArea) + ', '
										+ dbo.quotestring(@SequenceNo) + ', '
										+ dbo.quotestring(@EquipmentNo) + ', '
										+ dbo.quotestring(@Description) + ', '
										+ dbo.quotestring(@UOM) + ')'

			exec (@SqlState)
			IF @@ROWCOUNT<>1
				Begin
					-- Write to detail failed
					SET @ErrCode=93
					GOTO TRAN_FAILED
				End

			/* -------------------------------------------
			Get next EU record.
			---------------------------------------------- */
			FETCH NEXT FROM curEUDetail 
			INTO	@ProjectNo, 
				@TaskNo, 
				@WEDate, 
				@ServiceArea, 
				@SequenceNo, 
				@EquipmentNo, 
				@Description, 
				@Qty, 
				@UOM, 
				@Comment 		

		END
		/* -------------------------------------------
		End EU record loop.
		---------------------------------------------- */

/* -------------------------------------------
End of process.
Commit the transaction.
---------------------------------------------- */
COMMIT TRANSACTION
SET @ErrCode = 0

/* -------------------------------------------
Close cursors and release resources
---------------------------------------------- */
CLOSE curDetail
DEALLOCATE curDetail

CLOSE curEUDetail
DEALLOCATE curEUDetail

RETURN

/* -------------------------------------------
Errors occurred
Rollback the transaction.
---------------------------------------------- */
TRAN_FAILED:

ROLLBACK TRANSACTION

GO

and here's the VBA code which calls it.

    Dim strRetVal As String, strMsg As String
    Dim com As New ADODB.Command
    Dim i As Integer

    With com
        .ActiveConnection = CurrentProject.Connection
        .CommandText = "[dbo].[sp_CopyBillRecords]"
        .CommandType = adCmdStoredProc
        .Parameters.Refresh
        .Parameters("@WEDateFrom") = Me!WEDateFrom
        .Parameters("@WEDateTo") = Me!WEDateTo
        .Parameters("@ProjectFrom") = Me!ProjectNoFrom
        .Parameters("@ProjectTo") = Me!ProjectNoTo
        .Parameters("@TaskFrom") = Me!TaskNoFrom
        .Parameters("@TaskTo") = Me!TaskNoTo
        .Parameters("@ServiceAreaIn") = strServiceArea
        .Parameters("@ErrCode").Direction = adParamReturnValue
        .Execute , , adAsyncExecute
        While .State = adStateExecuting
            DoEvents
        Wend
        i = .Parameters("@ErrCode")
        
        Select Case i
            Case Is = 0
                ' Process completed successfully
            Case Is = 91
                strMsg = "Error copying header record"
            Case Is = 92
                strMsg = "Error copying detail records"
            Case Is = 93
                strMsg = "Error copying equipment usage records"
            Case Else
        End Select
        
    End With
    Set com = Nothing
    
    If i > 0 Then
        strFieldName = ""
        Err.Raise vbObjectError + 1005, , strMsg
    End If

Now, I copied this code from a previous application which worked perfectly (changed the parameter and procedure names, of course, and set the connection using the current connection because the original built a dynamic connection and in this case, I don't need anything like that) and I can't see anything wrong with it. Any ideas?

 

[PWise]

what happans when you take out the

.Parameters.Refresh


line

 

[cwadams1955]

Same error message, except it occurs on the following line where the parameter value is set: .Parameters("@WEDateFrom") = Me!WEDateFrom

 

[PWise]

try

[COLOR=red]
set[/color] .ActiveConnection = CurrentProject.Connection

 

[cwadams1955]

Same error message, with or without the .refresh line.

 

[cwadams1955]

Okay, I changed the code to do the following:

            With com
                SET .ActiveConnection = CurrentProject.Connection
                .CommandText = "dbo.sp_CopyBillRecords"
                .CommandType = adCmdStoredProc
                .CommandTimeout = 0
                '.Parameters.Refresh
                .Parameters.Append .CreateParameter("@WEDateFrom", adVarChar, adParamInput, 10, "'" & strDateFrom & "'")
                .Parameters.Append .CreateParameter("@WEDateTo", adVarChar, adParamInput, 10, "'" & strDateTo & "'")
                .Parameters.Append .CreateParameter("@ProjectFrom", adVarChar, adParamInput, 10, "'" & Me.ProjectNoFrom & "'")
                .Parameters.Append .CreateParameter("@ProjectTo", adVarChar, adParamInput, 10, "'" & Me.ProjectNoTo & "'")
                .Parameters.Append .CreateParameter("@TaskFrom", adVarChar, adParamInput, 50, "'" & Me.TaskNoFrom & "'")
                .Parameters.Append .CreateParameter("@TaskTo", adVarChar, adParamInput, 50, "'" & Me.TaskNoTo & "'")
                .Parameters.Append .CreateParameter("@ServiceAreaIn", adVarChar, adParamInput, 4, "'" & strServiceArea & "'")
                .Parameters.Append .CreateParameter("@ErrCode", adInteger, adParamReturnValue)
                
                '.Parameters("@WEDateFrom") = Me!WEDateFrom.Value
                '.Parameters("@WEDateTo") = Me!WEDateTo.Value
                '.Parameters("@ProjectFrom") = Me!ProjectNoFrom.Value
                '.Parameters("@ProjectTo") = Me!ProjectNoTo.Value
                '.Parameters("@TaskFrom") = Me!TaskNoFrom.Value
                '.Parameters("@TaskTo") = Me!TaskNoTo.Value
                '.Parameters("@ServiceAreaIn") = strServiceArea
                '.Parameters("@ErrCode").Direction = adParamReturnValue
                .Execute , , adAsyncExecute

and changed the date parameters inside the stored proc to also be varchar(10) and then do a CAST to convert them to date/time. Now, I get a message that:

"Application uses a value of the wrong type for the current operation." (I only changed the dates to text in the first place because I got this message when I changed the code to use .Append.)

WTH? I'm not an expert in this particular area, but I must have worked on twenty or thirty forms doing the exact same process as my original code, and all of them worked - have been working for two years without a hitch. It makes no sense that I can see to have to jump through so many hoops to get this one task running.

 

[danvlas]

What's the value of Me!WEDateFrom when setting the parameter value? Could it be Null?


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[cwadams1955]

No, the attached VBA code is part of the OnClick procedure for a command button, and I'm actually validating all fields in that procedure before it gets to the point where it calls the stored proc. I set a breakpoint in the procedure to check this, and at the calling point, all fields contain valid data.

 

[cwadams1955]

Got it!! The sp call didn't like the single quotes around the fields. Removed those and all parameters were accepted. Here's the code that worked:

            strDateFrom = Format(Me.WEDateFrom)
            strDateTo = Format(Me.WEDateTo)
            Set com = New ADODB.Command
            
            ' If everything is okay, now we can build the criteria and create the header and detail records
            ' This section calls a stored procedure on the server to do the copy
            With com
                .ActiveConnection = CurrentProject.Connection
                .CommandText = "dbo.sp_CopyBillRecords"
                .CommandType = adCmdStoredProc
                .CommandTimeout = 0
                '.Parameters.Refresh
                .Parameters.Append .CreateParameter("@WEDateFromStr", adVarChar, adParamInput, 10, strDateFrom)
                .Parameters.Append .CreateParameter("@WEDateToStr", adVarChar, adParamInput, 10, strDateTo)
                .Parameters.Append .CreateParameter("@ProjectFrom", adVarChar, adParamInput, 10, Me.ProjectNoFrom)
                .Parameters.Append .CreateParameter("@ProjectTo", adVarChar, adParamInput, 10, Me.ProjectNoTo)
                .Parameters.Append .CreateParameter("@TaskFrom", adVarChar, adParamInput, 50, Me.TaskNoFrom)
                .Parameters.Append .CreateParameter("@TaskTo", adVarChar, adParamInput, 50, Me.TaskNoTo)
                .Parameters.Append .CreateParameter("@ServiceAreaIn", adVarChar, adParamInput, 4, strServiceArea)
                .Parameters.Append .CreateParameter("@ErrCode", adInteger, adParamReturnValue)
                
                '.Parameters("@WEDateFrom") = Me!WEDateFrom.Value
                '.Parameters("@WEDateTo") = Me!WEDateTo.Value
                '.Parameters("@ProjectFrom") = Me!ProjectNoFrom.Value
                '.Parameters("@ProjectTo") = Me!ProjectNoTo.Value
                '.Parameters("@TaskFrom") = Me!TaskNoFrom.Value
                '.Parameters("@TaskTo") = Me!TaskNoTo.Value
                '.Parameters("@ServiceAreaIn") = strServiceArea
                '.Parameters("@ErrCode").Direction = adParamReturnValue
                .Execute , , adAsyncExecute
                While .State = adStateExecuting
                    DoEvents
                Wend
                i = .Parameters("@ErrCode")
                
                Select Case i
                    Case Is = 0
                        ' Process completed successfully
                    Case Is = 91
                        strMsg = "Error copying header record"
                    Case Is = 92
                        strMsg = "Error copying detail records"
                    Case Is = 93
                        strMsg = "Error copying equipment usage records"
                    Case Else
                End Select
                
            End With
            Set com = Nothing
            
            If i > 0 Then
                strFieldName = ""
                Err.Raise vbObjectError + 1005, , strMsg
            End If

Still don't know why the original code didn't work, but at this point, I don't really care, I just need to get it running. Thanks for the feedback.