Issue returning a value from stored procedure in VBA

[ironwarrior]

Hey all,

I'm having an issue getting a return value from a stored procedure that I'm calling from VBA. I've tried looking around for a solution, but can't seem to get it to work. This is what I have at the moment:

Dim strDate As String       
Dim strWOStatus As String   
Dim CurrentConnection As ADODB.Connection 
Dim adoCMD As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim ParamReturn As ADODB.Parameter
Dim PartsUsedMTD As Integer

'set default connection/values
Set CurrentConnection = AdoConnection()

'set AssetID value
strAsset = lngAssetID
   
    
'   Parts Used on Work Orders
strDate = GetMTD(strDate)
Set adoCMD = New ADODB.Command
With adoCMD
    .ActiveConnection = CurrentConnection
    .CommandText = "spPartsUsedMTD"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters("@Asset").Value = strAsset
    .Parameters("@PartDate").Value = strDate
    Set ParamReturn = .CreateParameter("@varPartsUsedMTD", adInteger, adParamReturnValue)
    Set .Parameters("@PartsUSedMTD").Value = ParamReturn
    .Execute , , adExecuteNoRecords
    PartsUsedMTD = .Parameters("@varPartsUsedMTD")
End With

Me.txtPartsUsedMTD = PartsUsedMTD

The problem I am having is this error:
Error: 424
Description: Object Required


The line of code it errors on is:

Set .Parameters("@PartsUSedMTD").Value = ParamReturn

And the value of ParamReturn is always Null after it hits the line before it.

So it seems like it's not really creating the parameter variable SQL Server needs to run it? What am I doing wrong here?

Thanks!

 

[ironwarrior]

Okay, so at least a part of my dilemma has/had to do with the date, which I'm still trying to figure out how to write in. I found this link on ADO parameters that appears to look like it will work (Which I would share if I could at this point), now I just need to get the date piece right...

 

[ironwarrior]

So far I'm the only one replying... that is OK. I'm more than willing to go through this exercise if it helps someone out in the process!

So I've redone my vba code, and I'm going to post my stored proc, because now I'm getting this error:
"Error: -2147217913
Description: Conversion failed when converting date and/or time from character string"

When I plug in the values that should go into the stored proc, it works fine. Here is that piece:

CREATE PROC spPartsUsedMTD
	(
		@Asset VARCHAR(30)
		,@PartDateStart VARCHAR(20) = NULL	
		,@PartDateEnd VARCHAR(20)
		,@PartsUsedMTD INT OUTPUT
	)
AS
BEGIN 
 IF @PartDateStart IS NULL
 (
			SELECT 
				@PartsUsedMTD = SUM(CostExtendedActual)
			FROM 
				[tblWOpart] LEFT JOIN [tblWO] ON [tblWOpart].[WO] = [tblWO].[WO]
			WHERE
				 AssetID = @Asset AND Canceled IS NULL AND partdate <= @PartDateEnd
)
ELSE
(			SELECT 
				@PartsUsedMTD = SUM(CostExtendedActual)
			FROM 
				[tblWOpart] LEFT JOIN [tblWO] ON [tblWOpart].[WO] = [tblWO].[WO]
			WHERE
				 AssetID = @Asset AND Canceled IS NULL AND partdate >= @PartDateStart AND partdate <= @PartDateEnd
)	
END

Here is revised VBA Code:

Dim strEndDate As String      'Beginning date for stored procedure
Dim strMonthStartDate As String   ' Month to date
Dim strYearStartDate As String    ' Year to date
Dim strWOStatus As String  
Dim CurrentConnection As ADODB.Connection   'connection object
Dim adoCMD As ADODB.Command
Dim PartsUsedMTD As Integer
Dim strDate As String       'legacy date field

'set default connection/values
Set CurrentConnection = AdoConnection()

'set AssetID value
strAsset = lngAssetID
   
'set date values for calculations
strEndDate = "'" & Format(DateAdd("d", 1, Date), "yyyymmdd") & "'"
strMonthStartDate = "'" & Format(Date, "yyyymm") & "01'"
strYearStartDate = "'" & Format(Date, "yyyy") & "0101'"


'   Parts Used on Work Orders
Set adoCMD = New ADODB.Command
With adoCMD
    .ActiveConnection = CurrentConnection
    .CommandText = "spPartsUsedMTD"
    .CommandType = adCmdStoredProc
    .Parameters.Item("@Asset").Value = strAsset
    .Parameters.Item("@PartDateStart").Value = strMonthStartDate
    .Parameters.Item("@PartDateEnd").Value = strEndDate
    .Parameters(0) = PartsUsedMTD
    .Execute
End With

It breaks at the Execute statement. Now, when I plug in some test values into the stored proc to see if that's the problem, it works fine. So that tells me it's the way I'm writing it in VBA that's not coming across.

- IW

 

[Galaxiom]

The single quotes should not be included in the string date parameters.

Any particular reason why you are processing dates as strings?

You should also be declaring the parameter datatypes in the VBA.

 

[ironwarrior]

The single quotes should not be included in the string date parameters.

Any particular reason why you are processing dates as strings?

You should also be declaring the parameter datatypes in the VBA.

So I got it to work, part of it was the single quotes, part was just how I had the VBA setting up the parameters, then how the stored procedure was set up.

Here is how I fixed it:
VBA Code -

'set default connection/values
Set CurrentConnection = AdoConnection()

'set AssetID value
strAsset = lngAssetID
   
'set date values for calculations
strEndDate = Format(DateAdd("d", 1, Date), "yyyymmdd")
strMonthStartDate = Format(Date, "yyyymm") & "01"
strYearStartDate = Format(Date, "yyyy") & "0101"

'   Parts Used on Work Orders MTD
Set adoCMD = New ADODB.Command
With adoCMD
    .ActiveConnection = CurrentConnection
    .CommandText = "spPartsUsedMTD"
    .CommandType = adCmdStoredProc
    Set Param = .CreateParameter("Asset", adVarChar, adParamInput, 30)
    .Parameters.Append Param
    Set Param = .CreateParameter("PartDateStart", adVarChar, adParamInput, 20)
    .Parameters.Append Param
    Set Param = .CreateParameter("PartDateEnd", adVarChar, adParamInput, 20)
    .Parameters.Append Param
    Set Param = .CreateParameter("PartsUsedMTD", adInteger, adParamOutput)
    .Parameters.Append Param
    'Set Parameter values
    .Parameters("Asset") = strAsset
    .Parameters("PartDateStart") = strMonthStartDate
    .Parameters("PartDateEnd") = strEndDate
    .Execute adExecuteNoRecords
    .Parameters("PartsUsedMTD") = PartsUsedMTD
End With

MSSQL -

ALTER PROC spPartsUsedMTD
	(
		@Asset VARCHAR(30)
		,@PartDateStart VARCHAR(20) = NULL	
		,@PartDateEnd VARCHAR(20)
		,@PartsUsedMTD INT OUTPUT
	)
AS
BEGIN 
 IF @PartDateStart IS NULL
 SET @PartsUsedMTD =
 (
			SELECT 
				SUM(CostExtendedActual)
			FROM 
				[tblWOpart] LEFT JOIN [tblWO] ON [tblWOpart].[WO] = [tblWO].[WO]
			WHERE
				 AssetID = @Asset AND Canceled IS NULL AND partdate <= @PartDateEnd
)
ELSE
SET @PartsUsedMTD =
(			SELECT 
				SUM(CostExtendedActual)
			FROM 
				[tblWOpart] LEFT JOIN [tblWO] ON [tblWOpart].[WO] = [tblWO].[WO]
			WHERE
				 AssetID = @Asset AND Canceled IS NULL AND partdate >= @PartDateStart AND partdate <= @PartDateEnd
)	
END

Keep in mind for anyone that has a similar problem and is trying to adapt this code: *I set this up to return one output parameter back out, and not a recordset* hence the adExecuteNoRecords option.