Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Other Forums  MS Access  Error on Cmd.Parameters.Refresh

Author  Topic  tad Starting Member 31 Posts Posted - 2002-05-23 : 18:18:16 When executing, Cmd.Parameters.Refresh, to get the parameters, I get an error for the parameters immediately. Why? Public Function spCOA_MFormDetail(ByRef pErrorText, ByVal pSite_No, ByVal pCOA_FORM_NO, ByVal pPROPERTY_NO, ByVal pTEST_NO, ByVal pGROUP_NO, ByVal pRMA_NO, ByVal pTEST_PROC, ByVal pPARAMETER, ByVal pCHAR_NAME, ByVal pRFLAG, ByVal pMF, ByVal pRSLT_NO, ByRef pCOA_FORM_DETAILID As Long) As Integer On Error GoTo ErrorHandler spCOA_MFormDetail = True ' Failed Dim R As New ADODB.Recordset, Cmd As New ADODB.Command, I As Integer Cmd.ActiveConnection = CurrentProject.Connection Cmd.CommandText = "dbo.spCOA_MFormDetail" Cmd.CommandType = adCmdStoredProc Cmd.Parameters.Refresh For I = 0 To Cmd.Parameters.Count - 1 Select Case Cmd.Parameters(I).Name Case "@pCOA_FORM_NO" Cmd.Parameters(I).Value = pCOA_FORM_NO Case "@pPROPERTY_NO" Cmd.Parameters(I).Value = pPROPERTY_NO Case "@pSITE_NO" Cmd.Parameters(I).Value = pSite_No Case "@pTEST_NO" Cmd.Parameters(I).Value = pTEST_NO Case "@pGROUP_NO" Cmd.Parameters(I).Value = pGROUP_NO Case "@pRMA_NO" If IsNull(pRMA_NO) = False Then Cmd.Parameters(I).Value = pRMA_NO Case "@pRSLT_NO" If IsNull(pRSLT_NO) = False Then Cmd.Parameters(I).Value = pRSLT_NO Case "@pRFLAG" If IsNull(pRFLAG) = False Then Cmd.Parameters(I).Value = pRFLAG Case "@pMF" If IsNull(pMF) = False Then Cmd.Parameters(I).Value = pMF Case "@pTEST_PROC" If IsNull(pTEST_PROC) = False Then Cmd.Parameters(I).Value = pTEST_PROC Case "@pPARAMETER" If IsNull(pPARAMETER) = False Then Cmd.Parameters(I).Value = pPARAMETER Case "@pCHAR_NAME" If IsNull(pCHAR_NAME) = False Then Cmd.Parameters(I).Value = pCHAR_NAME Case "@pCOA_FORM_DETAILID" If pCOA_FORM_DETAILID > 0 Then Cmd.Parameters(I).Value = pCOA_FORM_DETAILID End If Case Else End Select Next I Set R = Cmd.Execute For I = 0 To Cmd.Parameters.Count - 1 Select Case Cmd.Parameters(I).Name Case "@RETURN_VALUE" If Cmd.Parameters(I).Value <> 0 Then Err.Raise Cmd.Parameters(I).Value, "Stored Procedure", "Occurred while executing stored procedure" Exit For End If Case "@pCOA_FORM_DETAILID" pCOA_FORM_DETAILID = Cmd.Parameters(I).Value Case Else End Select Next I spCOA_MFormDetail = False ' Pass Done: On Error Resume Next R.Close Set R = Nothing Exit Function ErrorHandler: pErrorText = CStr(Err.Number) & ":" & Err.Description & vbCrLf & "clsCOAData.spCOA_MFormDetail" spCOA_MFormDetail = True ' Error Occurred Resume Done End Function CREATE Procedure spCOA_MFormDetail @pSITE_NO INT, @pCOA_FORM_NO INT, @pGROUP_NO SMALLINT, @pTEST_NO INT, @pPROPERTY_NO INT, @pTEST_PROC NVARCHAR(30)=NULL, @pPARAMETER NVARCHAR(20)=NULL, @pRMA_NO NVARCHAR(2)=NULL, @pRFLAG NVARCHAR(1)=NULL, @pMF REAL=NULL, @pRSLT_NO INT=NULL, @pCHAR_NAME NVARCHAR(30) = NULL, @pCOA_FORM_DETAILID INT = NULL OUTPUT As DECLARE @Error INT IF @pCOA_FORM_DETAILID IS NULL BEGIN INSERT INTO COA_FORM_DETAIL (COA_FORM_NO, GROUP_NO, TEST_NO, PROPERTY_NO, TEST_PROC, PARAMETER, RMA_NO, RFLAG, MF, RSLT_NO, CHAR_NAME) VALUES (@pCOA_FORM_NO, @pGROUP_NO, @pTEST_NO, @pPROPERTY_NO, @pTEST_PROC, @pPARAMETER, @pRMA_NO, @pRFLAG, @pMF, @pRSLT_NO, @pCHAR_NAME) SELECT @pCOA_FORM_DETAILID = @@IDENTITY, @Error = @@ERROR END ELSE BEGIN UPDATE COA_FORM_DETAIL SET GROUP_NO = @pGROUP_NO, PARAMETER = @pPARAMETER, TEST_PROC = @pTEST_PROC, RMA_NO = @pRMA_NO, RFLAG = @pRFLAG, MF = @pMF, RSLT_NO = @pRSLT_NO, CHAR_NAME = @pCHAR_NAME WHERE COA_FORM_DETAILID = @pCOA_FORM_DETAILID SELECT @Error = @@ERROR END RETURN(@Error) GO