ADODB Access to Excel failure - datatype mismatch

[Timo van Esch]

Question for the freaks out here.
I try to run a query on a table, very straightforward.
Table > full select query with 2 parameters.
Then I try to execute & populate an Excel sheet with it.

        Set cmd = New ADODB.Command
            cmd.ActiveConnection = cn
            cmd.CommandType = adCmdStoredProc
        
        'query to populate Report
            cmd.CommandText = "rep_Spend_LFC2"
id = 31
        'create parameters
        Set prm = cmd.CreateParameter("@Period", adDate, adParamInput, , dPeriod)
            cmd.Parameters.Append prm
        Set prm = cmd.CreateParameter("@Category", adVarChar, adParamInput, 25, sLocationCode)
            cmd.Parameters.Append prm
id = 32
        
        'execute and paste
        Set rs = cmd.Execute
            If Not rs.BOF And Not rs.EOF Then
                Range("dbLFC[LocCode]").CopyFromRecordset rs
id = 33
                'copy formulas and formatting
                CopyFormatAndFormulas "dbLFC", "R4:U4"
            End If

It goes alright if I use only the @Period parameter, but if I include the @Category, it goes wrong: Data Type Mismatch

The Query runs normally in Access, no problems.
The @Category is defined as Text, no problems.
The same method is used over and over again in the excel sheet, but only in this one, where I use 2 parameters, it goes wrong...
It just doesn't want to populate the Excel sheet.
Any suggestions?

Thankx up front!
Timo

 

[JHB]

How does the query looks like?
Try to change the order:
From:

Set prm = cmd.CreateParameter("@Period", adDate, adParamInput, , dPeriod)
cmd.Parameters.Append prm         
Set prm = cmd.CreateParameter("@Category", adVarChar, adParamInput, 25, sLocationCode) 
cmd.Parameters.Append prm

To:

Set prm = cmd.CreateParameter("@Category", adVarChar, adParamInput, 25, sLocationCode) 
cmd.Parameters.Append prm 
Set prm = cmd.CreateParameter("@Period", adDate, adParamInput, , dPeriod)
cmd.Parameters.Append prm

 

[Timo van Esch]

Hi JHB,

Thank you for your reply.
I tried the order of parameters, indeed. That did not help. Right now I will try if @Category only will work (leaving out @Period).

 

[JHB]

A good decision, is the value from sLocationCode a text value?

 

[Timo van Esch]

A good decision, is the value from sLocationCode a text value?

yes
right now we are walking through it by F8, see what happens.