Solved

Run-Time error '13'    Type Mismatch

Posted on 2014-03-05
10
2,086 Views
Last Modified: 2014-03-05
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
0
Comment
Question by:tesla764
  • 7
  • 3
10 Comments
 
LVL 5

Expert Comment

by:advfinance
ID: 39906381
What's in "parasplit(3)"? I bet it's not an integer...

--
Chris
0
 

Author Comment

by:tesla764
ID: 39906404
I will check.
0
 

Author Comment

by:tesla764
ID: 39906452
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, "|")
0
 
LVL 5

Expert Comment

by:advfinance
ID: 39906462
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
0
 

Author Comment

by:tesla764
ID: 39906469
Here is another ParaSplit line...

'split parameter string
    parasplit = Split(strParameter, "|")
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:tesla764
ID: 39906472
Looks like parasplit is a Pipe Delimeter.
0
 

Author Comment

by:tesla764
ID: 39906488
Doesn't the Cint function do the conversion to an integer?
0
 

Author Comment

by:tesla764
ID: 39906590
After investigating I believe parasplit(3) is empty. Would trying to convert an empty array field cause the Type Mismatch error?
0
 
LVL 5

Accepted Solution

by:
advfinance earned 500 total points
ID: 39906670
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
0
 

Author Comment

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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now