none
Compile error - ByRef argument type mismatch

    Question

  • I would appreciate some help on this one. I am at a total loss. I am gettign a compile error where I hadn't before. I am fairly certain I did not change anything since the last time I tested this section. The error is ...

    Compile error - ByRef argument type mismatch and it highlights 'OraDatabase' in the below IF Then Statement

            If Forms!Main!Section21!ProposedForestland.Value Then
              Call ProcessLandUse("40", Str(Forms!Main!Section21!ProposedForestlandAcres.Value), True, OraDatabase)
            End If

    I wouldn't think it would have anything to do with the OraDatabase reference or I would assume it would flag it earlier. The code that proceeds this call (IF THEN) statement is below

    Public Sub ExtractPermit()
    Const gcfHandleErrors As Boolean = True
    If gcfHandleErrors Then On Error GoTo Error_Handler
    '   Dim OraSession As Object
        Dim OraDatabase As Object
    '   Dim ApplExtract As Boolean
        Dim PermitExtract As Boolean
    '   Dim OraSelect As Object
        Dim PermitType As String
        Dim PermitSeq As Integer
        Dim OraStatus As Object
        Dim rst As Recordset
        Dim ApplID As String
    '   Dim DelFirst As Boolean
    'On Error GoTo ExtractPermit_Err
        
    '   ApplExtract = False
        PermitExtract = False
        ApplID = ""
        
            
        If MsgBox("Data is Going to be Extracted to SMIS. Continue?", vbYesNo) = vbYes Then
          If MsgBox("Is This the PERMIT Extract?", vbYesNo) = vbYes Then
            If MsgBox("Just to Confirm. This is the PERMIT Extract. Continue?", vbYesNo) = vbYes Then
              PermitExtract = True
            End If
          Else
            MsgBox ("Permit SMIS Upload Terminated.")
          End If
          If PermitExtract Then
            If IsNull(Forms!Main!PermitNumber) Then
              MsgBox ("Permit Number Not Entered. PERMIT Upload Terminated.")
              Exit Sub
            End If
            If IsNull(Forms!Main!DSMRENumber) Then
              MsgBox ("Permittee ID Not Entered. PERMIT Upload Terminated.")
              Exit Sub
            End If
            If Len(Forms!Main!PermitNumber) <> 7 Then
              MsgBox ("Permit Number Not in Proper Format. PERMIT Upload Terminated.")
              Exit Sub
            End If
            If Forms!Main!Section4!OrigPermit Then
              PermitType = "NW"
              PermitSeq = 1
            Else
              If Forms!Main!Section4!AMD Then
                If IsNull(Forms!Main!Section4!AMD_NO.Value) Or _
                   Not IsNumeric(Forms!Main!Section4!AMD_NO.Value) Then
                  MsgBox ("AMD Number Invalid. SMIS PERMIT Upload Terminated.")
                  Exit Sub
                Else
                  PermitType = "AM"
                  PermitSeq = Forms!Main!Section4!AMD_NO.Value
                End If
              Else
                If Forms!Main!Section4!MA Or Forms!Main!Section4!MI Then
                  If IsNull(Forms!Main!Section4!Revision.Value) Or _
                     Not IsNumeric(Forms!Main!Section4!Revision.Value) Then
                    MsgBox ("Revision Number Invalid. PERMIT SMIS Upload Terminated.")
                    Exit Sub
                  Else
                    If Forms!Main!Section4!MA Then
                      PermitType = "MA"
                    Else
                      PermitType = "MI"
                    End If
                    PermitSeq = Forms!Main!Section4!Revision.Value
                  End If
                Else
                  If Forms!Main!Section4!MidTerm Then
                    If IsNull(Forms!Main!Section4!MidTermNo.Value) Or _
                       Not IsNumeric(Forms!Main!Section4!MidTermNo.Value) Then
                      MsgBox ("Midterm Number Invalid. PERMIT SMIS Upload Terminated.")
                      Exit Sub
                    Else
                      PermitType = "MT"
                      PermitSeq = Forms!Main!Section4!MidTermNo.Value
                    End If
                  Else
                    MsgBox ("Permit Type Not Set. SMIS Upload Terminated.")
                    Exit Sub
                  End If
                End If
              End If
            End If
          Else
            MsgBox ("Upload to SMIS Cancelled")
          End If
          End If
          
          If PermitExtract Then
          
            Set OraSession = CreateObject("OracleInProcServer.XOraSession")
            Set OraDatabase = OraSession.OpenDatabase("smisdb", "/", 0&)
            OraDatabase.Parameters.Add "OraResult", OraResult, dbParamOutput
            OraDatabase.Parameters("OraResult").ServerType = dbText
            OraDatabase.Parameters.Add "APPL_NUMBER", Forms!Main!PermitNumber, dbParamInput
            OraDatabase.Parameters("APPL_NUMBER").ServerType = dbParamInput
            OraDatabase.Parameters.Add "APPL_TYPE", PermitType, dbParamInput
            OraDatabase.Parameters("APPL_TYPE").ServerType = dbParamInput
            OraDatabase.Parameters.Add "APPL_SEQ", PermitSeq, dbParamInput
            OraDatabase.Parameters("APPL_SEQ").ServerType = dbParamInput
            OraDatabase.Parameters.Add "MINE_NAME", Forms!Main!Section5!MineName.Value, dbParamInput
            OraDatabase.Parameters("MINE_NAME").ServerType = dbParamInput
            OraDatabase.Parameters.Add "INCR", IIf(IsNull(Forms!Main!Section8!IncrementNumber.Value), Null, Forms!Main!Section8!IncrementNumber.Value), dbParamInput
            OraDatabase.Parameters("INCR").ServerType = dbParamInput
            OraDatabase.Parameters.Add "LAB", Forms!Main!Section17!Section17_9_ID.Value, dbParamInput
            OraDatabase.Parameters("LAB").ServerType = dbParamInput
            OraDatabase.Parameters.Add "FLOOD", IIf(Forms!Main!Section6!MiddlesboroFloodControl.Value, "YES", "NO"), dbParamInput
            OraDatabase.Parameters("FLOOD").ServerType = dbParamInput
            OraDatabase.Parameters.Add "FOREST", IIf(Forms!Main!Section11!NationalForest.Value, "YES", "NO"), dbParamInput
            OraDatabase.Parameters("FOREST").ServerType = dbParamInput
            OraDatabase.Parameters.Add "PARK", IIf(Forms!Main!Section11!NationalPark.Value, "YES", "NO"), dbParamInput
            OraDatabase.Parameters("PARK").ServerType = dbParamInput
            OraDatabase.Parameters.Add "LAND_USE", IIf(Forms!Main!Section11!ProposedPostMining.Value, "YES", "NO"), dbParamInput
            OraDatabase.Parameters("LAND_USE").ServerType = dbParamInput
            OraDatabase.Parameters.Add "REF_AREA", Null, dbParamInput
            OraDatabase.Parameters("REF_AREA").ServerType = dbParamInput
            OraDatabase.Parameters.Add "SPECIES", IIf(Forms!Main!Section14!TreatenedSpeciesYes.Value, "YES", "NO"), dbParamInput
            OraDatabase.Parameters("SPECIES").ServerType = dbParamInput
            OraDatabase.Parameters.Add "ACID", Null, dbParamInput
            OraDatabase.Parameters("ACID").ServerType = dbParamInput
            OraDatabase.Parameters.Add "AQUIFER", IIf(Forms!Main!Section16!AquifersYes.Value, "YES", "NO"), dbParamInput
            OraDatabase.Parameters("AQUIFER").ServerType = dbParamInput
            OraDatabase.Parameters.Add "AQUIFER_USERS", Null, dbParamInput
            OraDatabase.Parameters("AQUIFER_USERS").ServerType = dbParamInput
            OraDatabase.Parameters.Add "WETLAND", IIf(Forms!Main!Section14!WetlandYes.Value, "YES", "NO"), dbParamInput
            OraDatabase.Parameters("WETLAND").ServerType = dbParamInput
            OraDatabase.Parameters.Add "WETLAND_ACRES", IIf(IsNull(Forms!Main!Section14!WetlandAcreage), Null, Forms!Main!Section14!WetlandAcreage), dbParamInput
            OraDatabase.Parameters("WETLAND_ACRES").ServerType = dbParamInput
            OraDatabase.Parameters.Add "FARMLAND", Null, dbParamInput
            OraDatabase.Parameters("FARMLAND").ServerType = dbParamInput
            OraDatabase.Parameters.Add "FARMLAND_ACRES", Null, dbParamInput
            OraDatabase.Parameters("FARMLAND_ACRES").ServerType = dbParamInput
            End If
            
            If Forms!Main!Section4!OrigPermit.Value Then
              Set OraStatus = OraDatabase.DBCreateSQL("begin :OraResult := sp_smn_pkg_mpa03_permit.original_permit_insert(" + _
                                                     ":APPL_NUMBER, " + _
                                                     ":APPL_TYPE, " + _
                                                     ":APPL_SEQ, " + _
                                                     ":MINE_NAME, " + _
                                                     ":INCR, " + _
                                                     ":LAB, " + _
                                                     ":FLOOD," + _
                                                     ":FOREST," + _
                                                     ":PARK," + _
                                                     ":LAND_USE," + _
                                                     ":REF_AREA," + _
                                                     ":SPECIES," + _
                                                     ":ACID," + _
                                                     ":AQUIFER," + _
                                                     ":AQUIFER_USERS," + _
                                                     ":WETLAND," + _
                                                     ":WETLAND_ACRES," + _
                                                     ":FARMLAND," + _
                                                     ":FARMLAND_ACRES);" + _
                                                     " end;", 0&)
            Else
            Set OraStatus = OraDatabase.DBCreateSQL("begin :OraResult := sp_smn_pkg_mpa03_permit.permit_action_insert(" + _
                                                   ":APPL_NUMBER, " + _
                                                   ":APPL_TYPE, " + _
                                                   ":APPL_SEQ, " + _
                                                   ":MINE_NAME, " + _
                                                   ":INCR," + _
                                                   ":LAB," + _
                                                   ":FLOOD," + _
                                                   ":FOREST," + _
                                                   ":PARK," + _
                                                   ":LAND_USE," + _
                                                   ":REF_AREA," + _
                                                   ":SPECIES," + _
                                                   ":ACID," + _
                                                   ":AQUIFER," + _
                                                   ":AQUIFER_USERS," + _
                                                   ":WETLAND," + _
                                                   ":WETLAND_ACRES," + _
                                                   ":FARMLAND," + _
                                                   ":FARMLAND_ACRES);" + _
                                                   " end;", 0&)
            End If
            If IIf(InStr(1, OraDatabase.Parameters("OraResult").Value, "ERR") = 0, False, True) Then
              MsgBox ("Error in Uploading Data: " & vbCrLf & OraDatabase.Parameters("OraResult").Value)
            Else
              If OraDatabase.LastServerErrText <> "" Then
                MsgBox ("Error in Uploading Data: " & vbCrLf & OraDatabase.LastServerErrText)
              End If
           
           
            OraDatabase.Parameters.Remove "MINE_NAME"
            OraDatabase.Parameters.Remove "INCR"
            OraDatabase.Parameters.Remove "LAB"
            OraDatabase.Parameters.Remove "FLOOD"
            OraDatabase.Parameters.Remove "FOREST"
            OraDatabase.Parameters.Remove "PARK"
            OraDatabase.Parameters.Remove "LAND_USE"
            OraDatabase.Parameters.Remove "REF_AREA"
            OraDatabase.Parameters.Remove "SPECIES"
            OraDatabase.Parameters.Remove "ACID"
            OraDatabase.Parameters.Remove "AQUIFER"
            OraDatabase.Parameters.Remove "AQUIFER_USERS"
            OraDatabase.Parameters.Remove "WETLAND"
            OraDatabase.Parameters.Remove "WETLAND_ACRES"
            OraDatabase.Parameters.Remove "FARMLAND"
            OraDatabase.Parameters.Remove "FARMLAND_ACRES"
            
            Call ProcessAcreageBreakdown(OraDatabase)
            Call ProcessSeam(OraDatabase)
            
            '
    '   Add land use database parameters
    '
            OraDatabase.Parameters.Add "LAND_USE", Null, dbParamInput
            OraDatabase.Parameters("LAND_USE").ServerType = dbParamInput
            OraDatabase.Parameters.Add "ACREAGE", Null, dbParamInput
            OraDatabase.Parameters("ACREAGE").ServerType = dbParamInput

    Thank you!

    jim neal

    Monday, November 19, 2012 3:22 PM

Answers

  • ProcessLandUse takes 5 arguments, of which OraDatabase is the 5th.

    In your call

    Call ProcessLandUse("40", Str(Forms!Main!Section21!ProposedForestlandAcres.Value), True, OraDatabase)

    you pass only 4 arguments, and OraDatabase is the 4th. It looks like you omitted one of the arguments.


    Regards, Hans Vogelaar

    • Marked as answer by JamesLNeal Monday, November 19, 2012 5:43 PM
    Monday, November 19, 2012 4:28 PM

All replies

  • Please post the code for ProcessLandUse.

    Regards, Hans Vogelaar

    Monday, November 19, 2012 3:47 PM
  • Private Sub ProcessLandUse(ApplID As String, _
                              LandUse As String, _
                              Acreage As String, _
                              PostUse As Boolean, _
                              OraDatabase As Object)
      Const gcfHandleErrors As Boolean = True
      If gcfHandleErrors Then On Error GoTo Error_Handler
      
      OraDatabase.Parameters.Add "APPL_ID", ApplID, dbParamInput
      OraDatabase.Parameters("APPL_ID").ServerType = dbParamInput
      OraDatabase.Parameters.Add "LAND_USE", IIf(LandUse = "NULL", Null, LandUse), dbParamInput
      OraDatabase.Parameters("LAND_USE").ServerType = dbParamInput
      OraDatabase.Parameters.Add "ACREAGE", IIf(Acreage = "NULL", Null, Acreage), dbParamInput
      OraDatabase.Parameters("ACREAGE").ServerType = dbParamInput
      If PostUse Then
        Set OraStatus = OraDatabase.DBCreateSQL("begin :OraResult := sp_smn_pkg_mpa03_permit.post_mine_lu_insert(" + _
                                                ":APPL_ID, " + _
                                                ":LAND_USE, " + _
                                                ":ACREAGE);" + _
                                                " end;", 0&)
      Else
        Set OraStatus = OraDatabase.DBCreateSQL("begin :OraResult := sp_smn_pkg_mpa03_permit.pre_mine_lu_insert(" + _
                                                ":APPL_ID, " + _
                                                ":LAND_USE, " + _
                                                ":ACREAGE);" + _
                                                " end;", 0&)
      End If
      If IIf(InStr(1, OraDatabase.Parameters("OraResult").Value, "ERR") = 0, False, True) Then
        MsgBox ("Error in Uploading Data: " & vbCrLf & OraDatabase.Parameters("OraResult").Value)
      Else
        If OraDatabase.LastServerErrText <> "" Then
          MsgBox ("Error in Uploading Data: " & vbCrLf & OraDatabase.LastServerErrText)
        End If
      End If
      OraDatabase.Parameters.Remove "APPL_ID"
      OraDatabase.Parameters.Remove "LAND_USE"
      OraDatabase.Parameters.Remove "ACREAGE"
      
    Error_Handler_Exit:
        On Error Resume Next
        Exit Sub
    Error_Handler:
        MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
                "Error Number: " & err.Number & vbCrLf & _
                "Error Source: ProcessLandUse" & vbCrLf & _
                "Error Description: " & err.Description, _
                vbCritical, "An Error has Occured!"
        Resume Error_Handler_Exit
    End Sub


    jim neal

    Monday, November 19, 2012 4:23 PM
  • ProcessLandUse takes 5 arguments, of which OraDatabase is the 5th.

    In your call

    Call ProcessLandUse("40", Str(Forms!Main!Section21!ProposedForestlandAcres.Value), True, OraDatabase)

    you pass only 4 arguments, and OraDatabase is the 4th. It looks like you omitted one of the arguments.


    Regards, Hans Vogelaar

    • Marked as answer by JamesLNeal Monday, November 19, 2012 5:43 PM
    Monday, November 19, 2012 4:28 PM
  • I noticed that earlier but talked myself out of it because I thought that the code was successfully ran. However, once I added the missing arguement it went through fine.  Thank you!!!

    jim neal

    Monday, November 19, 2012 5:43 PM