sql server programming: Been a while since I used ADO, but try giving your named parameter the same
name as the parameter in the SP:

Set adoPm = .CreateParameter("@var_ContractID", adVariant, adParamInput,
, strContractID)

[quoted text, click to view]

[quoted text, click to view]

1. Make sure that the data type the stored procedure uses for
@var_ContractID is the same data type as the ContractID column in
tblContractAuditHistory. Since the column's name ends with "ID", it
suggests to me that this might be some kind of integer. Passing a
VARCHAR(50) to an INT column will at the very least require a CAST() But
I'm just guessing there because I don't know what your table looks like.

[quoted text, click to view]

2. If you're using VARCHAR as your data type in the stored procedure, you
should be using String as the data type of your VB variables. Don't ever
use Variants. Variants are evil. Variants hate you. Variants actually use
a form of A.I. to actively seek ways to hurt you. OK, I'm going overboard.
But they are (a) slow as death (b) prone to errors for a whole big long list
of reasons and (c) really really fat in memory.

3. For what you're doing, the data type of your strContractID and
strContractStatus variables looks like it should be String, and I believe
the data type of your ADODB.Parameter above should be either adBSTR or
adString (depending on what version of ADO you're using). That much will at
least match your VARCHAR(50) parameter of your stored procedure. But I'm
still assuming that's the data type of the table's column.

4. It's been a LONG time since I was actively working in "Classic VB", but
in your call to .CreateParameter, I believe the parameter's name has to
match the name of the parameter in the stored procedure, so instead of
saying "ContractID", I think it has to say "@var_ContractID"

[quoted text, click to view]

The VB Error code for a type mismatch is 13. Anytime you get an error code
that's huge and negative, that's an error that came from a COM component, in
this case, ADO. In other words, your Parameter or Command exploded on the
line where you say adoCmd.Execute
--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS."
-- General Barringer, "War Games"

I have a problem executing the following Stored Procedure from code.

The SP Text runs like this:


ALTER PROCEDURE prc_InsertContractDirectStatus­_tmp
@var_ContractID VARCHAR(50)


insert into tblContractAuditHistory (ContractID)
VALUES (@var_ContractID)


The VB code which calls the procedure runs like this:


Dim adoRs As ADODB.Recordset
Dim adoCmd As ADODB.Command
Dim adoPm As ADODB.Parameter


Dim strContractID As Variant
Dim strContractStatus As Variant


Set adoCmd = New ADODB.Command


With adoCmd
.ActiveConnection = strConnection


Set adoPm = .CreateParameter("ContractID", adVariant, adParamInput,
, strContractID)
.Parameters.Append adoPm
.CommandText = "prc_InsertContractDirectStatu­s_tmp"
.CommandType = adCmdStoredProc


adoCmd.Execute
End With


However i get the following error message:
Type name is invalid.


Error code : -2147217872


Any ideas as to why this is happening?

I think there is error in your .createparameter...
it should be ("var_ContractID",200 - type - varchar,1 - input,50 - lenght of
string)

BM
[quoted text, click to view]