sql server - Cannot format input parameters correctly for SQL stored procedure -

so here's deal, have stored procedure accepts in 3 input parameters , has 1 output parameter. looked this:

create procedure upgettestidforanalyte @thiswoid nvarchar(60), @sampleid nvarchar(60),@analyte nvarchar(60), @testid int out    select @testid = t1.testid    tblwosampletest t1 join tbltest t2    on t1.testid=t2.testid     (t1.sampleid =  @sampleid) , (t2.analyte =  @analyte)  , (t1.woid = @thiswoid)     select @testid =isnull(@testid,0)  go 

it worked great when executed on ms-sql server management studio 2008, when called in ms-access 2010 (vba) following error: conversion failed when converting nvarchar value '1406-00591' data type int. (my @thiswoid input)

so posted question , posted everything(the way called stored procedure/ datatypes of table columns..ect) , told looked fine , had values assigning input parameters, i'll post link previous question @ bottom of page if curious. found kind of loop hole rid of error using 'like' instead of '=' because data type constraint isn't strict 'like' (i know not best solution i'm desperate make progress). anyways sql code looks this:

create procedure upgettestidforanalyte2 @thiswoid nvarchar(60), @sampleid nvarchar(60),@analyte nvarchar(60), @testid int out    select @testid = t1.testid    tblwosampletest t1 join tbltest t2    on t1.testid=t2.testid     (t1.sampleid  @sampleid) , (t2.analyte  @analyte)  , (t1.woid @thiswoid)     select @testid =isnull(@testid,0)  go 

again works great in sql management studio when call in ms-access returns 0. little test changed sql code once more see if can return other 0 forcing values knew return value, here code:

create procedure upgettestidforanalyte3 @thiswoid nvarchar(60), @sampleid nvarchar(60),@analyte nvarchar(60), @testid int out    select @testid = t1.testid     tblwosampletest t1 join tbltest t2     on t1.testid = t2.testid     (t1.woid '1406-00591') , (t2.analyte 'lead') , (t1.sampleid 1)    select @testid =isnull(@testid,0)  go 

finally yes correct output saved @testid. great making progress. (sort of). curious , change 'like' '=' , returns correct value! @ point don't know whats going on roll it. change sql code upgettestidforanalyte2 (i labeled them differently btw) , when call stored procedure take same values , assign them input parameters such:

set cmd = new adodb.command     cmd.activeconnection = conn     cmd.commandtype = adcmdstoredproc     cmd.commandtext = "upgettestidforanalyte2"     cmd.parameters.append cmd.createparameter("@analyte", advarchar, adparaminput, 60, "lead")     cmd.parameters.append cmd.createparameter("@thiswoid", advarchar, adparaminput, 60, "1406-00591")     cmd.parameters.append cmd.createparameter("@sampleid", addouble, adparaminput, 60, 1)     cmd.parameters.append cmd.createparameter("@testid", addouble, adparamoutput, , adparamreturnvalue)     cmd.execute     conn.close      thistestid = cmd.parameters("@testid").value  

it returns sets testid 0. initialized thistestid 5 make sure changing, , changing 0. make sure ran upgettestidforanalyte same input values , got same error got above. right now. here inforamtion on tables using got sql code:

select column_name, data_type, character_maximum_length, is_nullable  information_schema.columns ic (table_name = 'tblwosampletest' , column_name = 'woid')   or (table_name = 'tblwosampletest' , column_name = 'sampleid')   or (table_name = 'tbltest' , column_name = 'analyte)'  

results: define variables pass as:

dim analyte string dim thiswoid string dim thissampleid long dim thistestid long 

link previous question (not gonna lie it's kind of confusing) unknown invalid type converson in sql stored procedure okay if needs more information or clarification ask, help! if don't know answer suggest tests do.

your variable order out of order proc.

your vb order is:

cmd.parameters.append cmd.createparameter("@analyte", advarchar, adparaminput, 60, "lead") cmd.parameters.append cmd.createparameter("@thiswoid", advarchar, adparaminput, 60, "1406-00591") cmd.parameters.append cmd.createparameter("@sampleid", addouble, adparaminput, 60, 1) cmd.parameters.append cmd.createparameter("@testid", addouble, adparamoutput, , adparamreturnvalue) 

and proc order is:

@thiswoid nvarchar(60),  @sampleid nvarchar(60), @analyte nvarchar(60),  @testid int out 

unless specifiy in vb cmd.namedparameters=true ordinal order used, , vb @analyte put sql @thiswoid, , vb @thiswoid put sql @sampleid -- nvarchar-int conversion issue.

get names in order, or set named params attribute true, , fix sql proc @sampleid int match table.

you should golden after that.