Results 1 to 4 of 4
  1. #1
    ASWilliams is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    10

    ADO Parameter Runtime 13: Type Mismatch

    Hello Access Forums,



    I have a stored procedure in SQL Server that I'm trying to execute using ADO in Access (currently in testing phase for a bigger project to come). However, I get a Type Mismatch error when trying to assign a value to the input parameter:

    Code:
    Dim ADOConn As ADODB.Connection
    Dim ADORec As ADODB.Recordset
    Dim ADOCmd As ADODB.Command
    Dim ADOParam As ADODB.Parameter
    
    Set ADOConn = New ADODB.Connection
    ADOConn.Open ConnStr
    Debug.Print "Connection status: " & ADOConn.State
    
    Set ADOCmd = New ADODB.Command
    ADOCmd.CommandType = adCmdStoredProc
    ADOCmd.CommandText = "TestProc"
           
    ADOCmd.ActiveConnection = ADOConn
    ADOCmd.Parameters.Refresh
        
    Set ADOParam = New ADODB.Parameter
    Set ADOParamOut = New ADODB.Parameter
    ADOParam(1).Value = 201619    '------code breaks here with Type 13 runtime error (type mismatch)
    I'm looking for help on finding out why I'm unable to assign the value to the input parameters. For reference, the code accesses the procedure correctly and returns the expected info on the parameters (using the parameters.refresh line).

    Thanks in advance

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    that error means the param is not setup as the correct data type you think it is.
    its either set as a string
    or smaller number type
    or date

  3. #3
    ASWilliams is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    10
    Thanks for your reply, Ranman. However, the parameter in the stored procedure is indeed bigint data type (see SQL procedure code below).

    Code:
    CREATE procedure [dbo].[TestProc]
    	@FromWeek bigint
    AS
    
    
    	SELECT Top 5 SurrID, Volume
    	FROM tblTotalVolumes
    	WHERE ISOWk = @FromWeek
    	ORDER BY Volume
    GO
    Is there anything else you think I could look at (or am I missing your point)?

    Thanks

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Personally I use pass through queries when I'm going to return records, ADO command objects to execute actions (append, update, etc). I haven't seen that method of handling parameters. My code looks like:

    Code:
        With cmd
          .ActiveConnection = objConn
          .CommandText = "procResAddNew" 
          .CommandType = adCmdStoredProc
    
          .Parameters.Append .CreateParameter("@ReqDate", adDBTimeStamp, adParamInput, , dteReqDate)
          .Parameters.Append .CreateParameter("@AcctID", adInteger, adParamInput, , Me.cboAcctNum)
          .Parameters.Append .CreateParameter("@PuLandmark", adInteger, adParamInput, , Me.cboLandmark)
    
          .Execute
        End With
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Type 13 (Type Mismatch) error
    By sdel_nevo in forum Programming
    Replies: 5
    Last Post: 01-22-2016, 10:01 AM
  2. Replies: 2
    Last Post: 08-24-2015, 09:14 PM
  3. type mismatch
    By seeker63 in forum Programming
    Replies: 2
    Last Post: 12-05-2013, 02:54 PM
  4. type mismatch
    By slimjen in forum Forms
    Replies: 21
    Last Post: 07-24-2012, 03:14 PM
  5. Type mismatch
    By jgelpi16 in forum Programming
    Replies: 1
    Last Post: 08-07-2010, 06:54 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums