Access 2000 ADO Command parameter problem

[chim_knee]

Hi all, hope someone can help!

I am developing an Access 2000 database. I have an append query which takes a number (8) of parameters.

I am using ADO and more specifically an ADO Command object to run the query and supply the parameters (i.e. CreateParameter). The code is approximately:

With adoCmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "appqryNewUser"
.CommandType = adCmdStoredProc
End With

Set adoParam = adoCmd.CreateParameter("Name", adVarChar, adParamInput, 50, txtUserName)
adoCmd.Parameters.Append adoParam

.
.
.
Set adoParam = adoCmd.CreateParameter("BlockUser", adInteger, adParamInput, , CInt(chkUserBlocked))**
adoCmd.Parameters.Append adoParam

adoCmd.Execute


My problem is, I am getting a Data Type Mismatch in Criteria Expression error when I try to execute the command.

I have narrowed it down (by process of elimination) to the parameters that correspond to Yes/No fields in the table it is trying to update (see **). I have tried all sorts of data types when defining the parameter. adBoolean, adInteger, adBit, adTinyInt etc!

I have also tried to convert the TRUE/FALSE value to signed and unsigned integers but still get the same error message.

Does anyone know what the correct parameter data type should be? Or is someone going to tell me I should be using DAO?!

Cheers,
Phil.

[pdV6]

Just a guess, but try changing:

Set adoParam = adoCmd.CreateParameter("BlockUser", adInteger, adParamInput, , CInt(chkUserBlocked))

to:

Set adoParam = adoCmd.CreateParameter("BlockUser", adInteger, adParamInput, , IIf(chkUserBlocked,1,0))

[chim_knee]

Thanks for the suggestion , tried it but it made no difference.

I have tried, for testing, to force the value to 0 (i.e. replaced chkUserBlocked with a literal 0). Tried the parameter type as adVariant... I am fast running out of other options!!

Thanks again. Any other ideas!!

[chim_knee]

Ehem... err, for those who are interested I've solved it.

Basically, even though I "named" the parameters - they have to be in the same order that the query expects...... obvious now but...

Thanks for your effort too pdV6!