HELP No return data from my stored procedure
 

I have a stored procedure like this:

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


AND have this in my excel VB
NOTE THAT I ALSO TRIED adParamReturnValue

.....
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
....

BUT I CaID is unchanged. Executing the select statement in Query
Analizer gets me the the right number.
Where did I go wrong?

HELP No return data from my stored procedure
 

"RocketMan" wrote:

I have a stored procedure like this:

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


AND have this in my excel VB
NOTE THAT I ALSO TRIED adParamReturnValue

.....
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
....

BUT I CaID is unchanged. Executing the select statement in Query
Analizer gets me the the right number.
Where did I go wrong?



You can use comm.CreateParameters:

Dim CaID As Integer

comm.CommandText = "sp_checkitem"
comm.CommandType = adCmdStoredProc
comm.NamedParameters = True

With comm
..Parameters.Append .CreateParameter("@Cname", adVarChar, adParamInput, 50,
"History")
..Parameters.Append .CreateParameter("@Dname", adVarChar, adParamInput, 50,
"bicycle")
..Parameters.Append .CreateParameter("@CID", adInteger, adParamOutput)
End With

comm.Execute

CaID = comm.Parameters("@CID")
Debug.Print CaID

cn.Close



If you don't use NamedParameters = True you can access parameters by their
position (.Parameters (0) is return value, .Parameters (1) is @Cname,
..Parameters (2) is @Dname and .Parameters (3) is @CID. For @Cname and @Dname
direction is adParamInput and for @CID it is adParamOutput. After you execute
the command you can assign the value of @CID to a variable (CaID =
comm.Parameters("@CID"). You use comm.CreateParameter, not
..Parameters.CreateParameter.


If you want to use .Parameters.Refresh you don't need comm.CreateParameter:


Dim CaID As Integer

comm.CommandText = "sp_checkitem"
comm.CommandType = adCmdStoredProc
comm.NamedParameters = True

With comm

..Parameters.Refresh
..Parameters("@Cname") = "History"
..Parameters("@Dname") = "bicycle"
= adParamOutput
'test
For Each p In .Parameters
Debug.Print p.Name, p.Type, p.Direction
Next

End With

comm.Execute

CaID = comm.Parameters("@CID")
Debug.Print CaID

cn.Close


Hope that helps.

--
urkec