Run-Time error '13'    Type Mismatch

tesla764
tesla764 used Ask the Experts™
on
I am receiving a Run-Time error 13 when I run code that generates a report. Could someone please help me figure out why this is happening?  Let me know if you need more information.
Thanks in advance.

I have also attached 2 separate text files that contain the VB6 code and the Stored Procedure that is seen below

First is the VB6 code that the system points to when I click 'Debug'
Below that code segment is the Stored Procedure that the code calls

VB6 code...
Run-Time error '13'  Type Mismatch

The line Debug points to is...
.Parameters.Append .CreateParameter("iShipType", adTinyInt, adParamInput, , CInt(Trim(parasplit(3))))

VB6 Code segment...

Case "ShippingSummaryReport", "ItemUsageReport"
            With cmd
                .Parameters.Append .CreateParameter("iCompCode", adVarChar, adParamInput, 3, (Trim(parasplit(0))))
                .Parameters.Append .CreateParameter("iStartDate", adDate, adParamInput, , CDate(Trim(parasplit(1))))
                .Parameters.Append .CreateParameter("iEndDate", adDate, adParamInput, , CDate(Trim(parasplit(2))))
                .Parameters.Append .CreateParameter("iSFWord", adVarChar, adParamInput, 1000, Trim(parasplit(3)))
                .Parameters.Append .CreateParameter("iRCWord", adVarChar, adParamInput, 1000, Trim(parasplit(4)))
                .Parameters.Append .CreateParameter("iTerritoryWord", adVarChar, adParamInput, 1000, (Trim(parasplit(5))))
               
                Select Case strReportName
                    Case "ShippingSummaryReport"
                        .Parameters.Append .CreateParameter("iShipType", adTinyInt, adParamInput, , CInt(Trim(parasplit(3))))
                        .Parameters.Append .CreateParameter("iGroupNumb", adVarChar, adParamInput, 10, (Trim(parasplit(7))))
                        SQLstr = "spITS_ShippingSummaryReport"
                    Case "ItemUsageReport"
                        .Parameters.Append .CreateParameter("iDivCodeWord", adVarChar, adParamInput, 1000, Trim(parasplit(6)))
                        .Parameters.Append .CreateParameter("iItemIDWord", adVarChar, adParamInput, 1000, Trim(parasplit(7)))
                        .Parameters.Append .CreateParameter("iShipType", adTinyInt, adParamInput, , CInt(parasplit(8)))
                        SQLstr = "spITS_ItemUsageReport"
                End Select
            End With

Stored procedure...

USE [rpr_DEV]
GO
/****** Object:  StoredProcedure [dbo].[spITS_ShippingSummaryReport]    Script Date: 03/05/2014 08:54:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spITS_ShippingSummaryReport]
/*
      Author:            Marc Roy      
      Date Created:      04/26/2006
      Purpose:      Gather data for Item Usage report
      Where used:      ITS
      Current Version      spITS_ShippingSummaryReport
      Notes:
      
      Revision History:
            20060327      MRR      Proc Creation      
            20070828      MRR      Changed field names to match ttx files used in reports, due to CR10 upgrade requires exact match
*/

-- =============================================
-- parameters
-- =============================================
      @iCompCode varchar(3),            --Company Code
      @iStartDate datetime,            --Start date
      @iEndDate datetime,            --End Date
      @iSFWord varchar(1000),            --word that holds Sales Forces to be reported on
      @iRCWord varchar(1000),            --word that holds Role Codes to be reported on
      @iTerritoryWord varchar(1000),      --word that holds Territories to be reported on
      @iShipType tinyint,            --0 = both, 1 = Regular, 2 = Immediate
      @iGroupNumb varchar(10)            -- If Group Number is used, ignore Comp_Code
AS

SET NOCOUNT ON

DECLARE @paramWord varchar(8000),
      @result int,
      @callResult int,
            @callErrorString varchar(255),
      @TotalLineItems bigint

SELECT @paramword =       @iCompCode + '|' +
                  CONVERT(varchar,@iStartDate, 101) + '|' +
                  CONVERT(varchar,@iEndDate, 101) + '|' +
                  LTRIM(RTRIM(@iSFWord)) + '|' +
                  LTRIM(RTRIM(@iRCWord)) + '|' +
                  LTRIM(RTRIM(@iTerritoryWord)) + '|' +
                  LTRIM(RTRIM(@iGroupNumb))

EXECUTE @result = spITS_AddProcLogEntry @callResult OUTPUT,@callErrorString OUTPUT, @paramword, 'spITS_ShippingSummaryReport', 'Starting Item Usage Report....'

-- =============================================
-- Spilt Parameter Words
-- =============================================

SELECT       ELEMENT
INTO      #SalesForces
FROM      fnSplit(@iSFWord,';')

SELECT       ELEMENT
INTO      #RoleCodes
FROM      fnSplit(@iRCWord,';')

SELECT       ELEMENT
INTO      #Territories
FROM      fnSplit(@iTerritoryWord,';')


-- =============================================
-- Get Orders Needed
-- =============================================
EXECUTE @result = spITS_AddProcLogEntry @callResult OUTPUT,@callErrorString OUTPUT, @paramword, 'spITS_ShippingSummaryReport', 'Retrieving Orders....'

SELECT       TOP 0
      Order_Numb,
      Sales_Forc,
      Role_Code,
      Territory,
      Ship_Type
INTO      #JustOrderNumbers
FROM      dbo.Orders WITH (NOLOCK)


IF @iGroupNumb = ''
      BEGIN
            INSERT       
            INTO      #JustOrderNumbers
            SELECT       Order_Numb,
                  Sales_Forc,
                  Role_Code,
                  Territory,
                  Ship_Type
            FROM       dbo.orders WITH (NOLOCK)
            WHERE       ship_date between @iStartDate AND @iEndDate AND
                   comp_code = @iCompCode AND
                  NOT orders.ship_date IS NULL


            EXECUTE @result = spITS_AddProcLogEntry @callResult OUTPUT,@callErrorString OUTPUT, @paramword, 'spITS_ShippingSummaryReport', 'Retrieved Orders by Date....'

      END

ELSE
      BEGIN

            INSERT       
            INTO      #JustOrderNumbers
            SELECT       Order_Numb,
                  Sales_Forc,
                  Role_Code,
                  Territory,
                  Ship_Type
            FROM       dbo.orders WITH (NOLOCK)
            WHERE       orders.group_numb = @iGroupNumb AND
                        NOT orders.ship_date IS NULL


            EXECUTE @result = spITS_AddProcLogEntry @callResult OUTPUT,@callErrorString OUTPUT, @paramword, 'spITS_ShippingSummaryReport', 'Retrieved Orders by Date....'

      END
-- =============================================
-- Check for Filtered Orders
-- =============================================

EXECUTE @result = spITS_AddProcLogEntry @callResult OUTPUT,@callErrorString OUTPUT, @paramword, 'spITS_ShippingSummaryReport', 'Filtering Orders Orders....'


IF NOT LTRIM(RTRIM(@iSFWord)) = ''
      BEGIN      
            DELETE       #JustOrderNumbers
            FROM      #JustOrderNumbers AS OrderNumbers
            LEFT      JOIN      #SalesForces AS SalesForces ON SalesForces.Element = OrderNumbers.Sales_Forc
            WHERE      SalesForces.Element IS NULL            

            EXECUTE @result = spITS_AddProcLogEntry @callResult OUTPUT,@callErrorString OUTPUT, @paramword, 'spITS_ShippingSummaryReport', 'Filtered Sales Forces'
      END


IF NOT LTRIM(RTRIM(@iRCWord)) = ''
      BEGIN      
            DELETE       #JustOrderNumbers
            FROM      #JustOrderNumbers AS OrderNumbers
            LEFT      JOIN #RoleCodes AS RoleCodes ON RoleCodes.Element = OrderNumbers.Role_Code
            WHERE      RoleCodes.Element IS NULL            

            EXECUTE @result = spITS_AddProcLogEntry @callResult OUTPUT,@callErrorString OUTPUT, @paramword, 'spITS_ShippingSummaryReport', 'Filtered Role Codes'
      END

IF NOT LTRIM(RTRIM(@iTerritoryWord)) = ''
      BEGIN      
            DELETE       #JustOrderNumbers
            FROM      #JustOrderNumbers AS OrderNumbers
            LEFT      JOIN #Territories AS Territories ON Territories.Element = OrderNumbers.Role_Code
            WHERE      Territories.Element IS NULL            

            EXECUTE @result = spITS_AddProcLogEntry @callResult OUTPUT,@callErrorString OUTPUT, @paramword, 'spITS_ShippingSummaryReport', 'Filtered Territories'
      END

IF @iShipType = 1
      BEGIN
            DELETE       #JustOrderNumbers
            FROM      #JustOrderNumbers AS OrderNumbers
            WHERE      OrderNumbers.Ship_Type = 'I'            

            EXECUTE @result = spITS_AddProcLogEntry @callResult OUTPUT,@callErrorString OUTPUT, @paramword, 'spITS_ShippingSummaryReport', 'Filtered Immediate Ship Types'
      END

IF @iShipType = 2
      BEGIN
            DELETE       #JustOrderNumbers
            FROM      #JustOrderNumbers AS OrderNumbers
            WHERE      OrderNumbers.Ship_Type = 'R'            

            EXECUTE @result = spITS_AddProcLogEntry @callResult OUTPUT,@callErrorString OUTPUT, @paramword, 'spITS_ShippingSummaryReport', 'Filtered Regular Ship Types'
      END




Print GetDate()
-- =============================================
-- Find Bulk Batches
-- =============================================
SELECT       DISTINCT Batch_Numb,
      Batch_Type
INTO      #BatchTypes
FROM      dbo.MACShip_Batch WITH (NOLOCK)


Print GetDate()
-- =============================================
-- Gather Shipping Data for Orders Found
-- =============================================
SELECT       ShipData.Order_Numb,
      ShipData.Shipment_Type,
      ShipData.Batch_numb,
      ShipData.Weight_Actual,
      ShipData.Weight_Billable,
      ShipData.Service_Symbol,
      ShipData.Service_Name
INTO      #ShipData
FROM      MACShip9.macship.dbo.MACShip_Shipment_Data ShipData
WHERE      ShipData.Order_Numb IN (SELECT Order_Numb FROM #JustOrderNumbers) AND
      NOT ShipData.void = 'Y' AND
      ShipData.MSN >= 0 AND
      ShipData.Record_Type = 'SHIPMENT_SUCCESSFUL'

Print GetDate()
-- =============================================
-- Gather Shipping Info
-- =============================================
SELECT       JustOrderNumbers.Order_Numb,
      JustOrderNumbers.Ship_Type,
      ShipData.Shipment_Type,
      ShipData.Batch_numb,
      BatchTypes.Batch_Type,
      ShipData.Weight_Actual,
      ShipData.Weight_Billable,
      ShipData.Service_Symbol,
      ShipData.Service_Name
INTO      #ShippingInfo
FROM      #JustOrderNumbers JustOrderNumbers
LEFT      JOIN #ShipData AS ShipData ON ShipData.Order_Numb = JustOrderNumbers.Order_Numb
LEFT      JOIN #BatchTypes AS BatchTypes ON BatchTypes.Batch_Numb = ShipData.Batch_Numb
ORDER      BY JustOrderNumbers.Order_Numb


-- =============================================
-- Group Information By Order Number
-- =============================================
SELECT      Order_Numb,
      MAX(Ship_Type) AS Ship_Type,
      Service_Symbol,
      (SELECT dbo.fnITS_MACShip_Return_Shipping_Service_Desc(Service_Symbol)) as Service_Name,
      COUNT(Ship_Type) AS CaseCount,
      SUM( CASE ISNULL(Batch_Type,'') WHEN 'BULK' THEN 1 ELSE 0 END) AS FullCase,
      SUM( CASE ISNULL(Batch_Type,'') WHEN 'OVERPACK' THEN 1 ELSE 0 END) AS OverPack,
      SUM( CASE ISNULL(Batch_Type,'') WHEN '' THEN 1 ELSE 0 END) AS Composite,
      SUM(ISNULL(Weight_Billable,0)) AS TotalWeight,
      SUM(ISNULL(Weight_Billable,0)) / COUNT(Ship_Type) AS AvgWeight
FROM      #ShippingInfo
GROUP      BY Order_Numb,
      Service_Symbol
ORDER      BY Service_Symbol,
      Ship_Type,
      Order_Numb      


-- =====================================================
-- Add 0 Weight Value entries for all Shipping Services
-- =====================================================
SELECT      '000000000' AS Order_Numb,
      SType.Type AS Ship_Type,
      CType.ExplodeCount - 1 AS CaseType,
      0 As Weight,
      SService.Service_Symbol AS Shipping_Service_Symbol,
      (SELECT dbo.fnITS_MACShip_Return_Shipping_Service_Desc(Service_Symbol)) AS Shipping_Service_Name
FROM      (SELECT      DISTINCT
            Service_Symbol
      FROM      #ShippingInfo) SService,
      fnITS_Explode2(3) CType,
      (SELECT CASE ExplodeCount WHEN 1 THEN 'I' ELSE 'R' END AS Type FROM fnITS_Explode2(2)) SType      
UNION ALL

-- =============================================
-- Gather Information for Shipping Service Summary
-- =============================================
SELECT      Order_Numb,
      Ship_Type,
      CASE ISNULL(BATCH_TYPE,'')       WHEN 'BULK' THEN 0
                              WHEN 'OVERPACK' THEN 1
                              WHEN '' THEN 2
      END AS CaseType,
      ISNULL(Weight_Billable,0) AS Weight,
      Service_Symbol AS Shipping_Service_Symbol,
      (SELECT dbo.fnITS_MACShip_Return_Shipping_Service_Desc(Service_Symbol)) AS Shipping_Service_Name
FROM      #ShippingInfo
ORDER      BY Service_Symbol,
      Order_Numb




DROP TABLE #BatchTypes
DROP TABLE #ShipData
DROP TABLE #ShippingInfo
Run-Time-Error.txt
spITS-ShippingSummayReport.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
What's in "parasplit(3)"? I bet it's not an integer...

--
Chris

Author

Commented:
I will check.

Author

Commented:
Here is the code segment where parasplit is defined...

Dim cmd As New ADODB.Command
    Dim parasplit() As String
    Dim rsdata As ADODB.Recordset

    On Error GoTo ProcERR
   
    'split parameter string
    parasplit = Split(strParameter, "|")
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

But at the time of execution, what is the actual value of parasplit(3)? If my hunch is right, it's probably a value which cannot be converted to an integer, hence the type mismatch error you're seeing.

--
Chris

Author

Commented:
Here is another ParaSplit line...

'split parameter string
    parasplit = Split(strParameter, "|")

Author

Commented:
Looks like parasplit is a Pipe Delimeter.

Author

Commented:
Doesn't the Cint function do the conversion to an integer?

Author

Commented:
After investigating I believe parasplit(3) is empty. Would trying to convert an empty array field cause the Type Mismatch error?
Yes, CInt converts a value to an integer. But for it to work that value must be valid for the conversion. E.g. "1" > 1. An empty string or, for example, "tesla764", would result in a type mismatch. Because what integer value are those string values equivalent?

--
Chris

Author

Commented:
I think I found the solution. The parasplit(3) was blank and that is what was cauing the error.
Thanks for your time.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial