I have a stored procedure like this:

Create Procedure [sp_checkitem]
(@Cname [varchar](50),
@Dname [varchar](50),
@CID [int]
)
AS
Set @CID = (Select CatID from [sdb].[dbo].[vw_Categories]
WHERE Category=@Cname AND Domain = @Dname)
GO


AND have this in my excel VB

.....
dim comm as ADODB.Command
dim CaID as Integer

comm.ActiveConnection = con
with comm
.Parameters.Refresh
.Parameters.Append .Parameters.CreateParameter("@Cname", adVarChar,
adParamInputOutput, 50, "History")
.Parameters.Append .Parameters.CreateParameter("@Dname", adVarChar,
adParamInputOutput, 50, "bicycle")
.Parameters.Append .Parameters.CreateParameter("@CID", adInteger,
adParamInputOutput, , CaID)
end with

comm.Execute
....


Where did I go wrong?

Do you set the CommandText/CommandType property somewhere?

"RocketMan" wrote:

I have a stored procedure like this:

Create Procedure [sp_checkitem]
(@Cname [varchar](50),
@Dname [varchar](50),
@CID [int]
)
AS
Set @CID = (Select CatID from [sdb].[dbo].[vw_Categories]
WHERE Category=@Cname AND Domain = @Dname)
GO


AND have this in my excel VB

.....
dim comm as ADODB.Command
dim CaID as Integer

comm.ActiveConnection = con
with comm
.Parameters.Refresh
.Parameters.Append .Parameters.CreateParameter("@Cname", adVarChar,
adParamInputOutput, 50, "History")
.Parameters.Append .Parameters.CreateParameter("@Dname", adVarChar,
adParamInputOutput, 50, "bicycle")
.Parameters.Append .Parameters.CreateParameter("@CID", adInteger,
adParamInputOutput, , CaID)
end with

comm.Execute
....


Where did I go wrong?

Oh yes, that isn't the problem.


Ben McBen wrote:
Do you set the CommandText/CommandType property somewhere?

"RocketMan" wrote:

I have a stored procedure like this:

Create Procedure [sp_checkitem]
(@Cname [varchar](50),
@Dname [varchar](50),
@CID [int]
)
AS
Set @CID = (Select CatID from [sdb].[dbo].[vw_Categories]
WHERE Category=@Cname AND Domain = @Dname)
GO


AND have this in my excel VB

.....
dim comm as ADODB.Command
dim CaID as Integer

comm.ActiveConnection = con
with comm
.Parameters.Refresh
.Parameters.Append .Parameters.CreateParameter("@Cname", adVarChar,
adParamInputOutput, 50, "History")
.Parameters.Append .Parameters.CreateParameter("@Dname", adVarChar,
adParamInputOutput, 50, "bicycle")
.Parameters.Append .Parameters.CreateParameter("@CID", adInteger,
adParamInputOutput, , CaID)
end with

comm.Execute
....


Where did I go wrong?

K, answered my own question.

I finally dim 'ed an integer and did a .parameter.count to find that
VB showed 7 parameters! WHERE DID THEY COME FROM!?!?!? Makes me
wanna bash the original coder with a mallet.

and no, there isn't a command to do .parameter.clear or something.
You have to do a delete on each one....so
I did a while statement
while .parameter.count 0
.parameter.delete .parameter.count -1
wend

THEN added the parameters.

GRRRRRRRRRRRRRR

RocketMan wrote:
I have a stored procedure like this:

Create Procedure [sp_checkitem]
(@Cname [varchar](50),
@Dname [varchar](50),
@CID [int]
)
AS
Set @CID = (Select CatID from [sdb].[dbo].[vw_Categories]
WHERE Category=@Cname AND Domain = @Dname)
GO


AND have this in my excel VB

....
dim comm as ADODB.Command
dim CaID as Integer

comm.ActiveConnection = con
with comm
.Parameters.Refresh
.Parameters.Append .Parameters.CreateParameter("@Cname", adVarChar,
adParamInputOutput, 50, "History")
.Parameters.Append .Parameters.CreateParameter("@Dname", adVarChar,
adParamInputOutput, 50, "bicycle")
.Parameters.Append .Parameters.CreateParameter("@CID", adInteger,
adParamInputOutput, , CaID)
end with

comm.Execute
...


Where did I go wrong?