parameter Error

[bobbytom]

Deal friends,

when i run my stored procedure from vb it is showing an error "This application has improperly defined a parameter object"
pl. guide me why it is showing

the code is given

Set cmd = New Command
With cmd
.ActiveConnection = con
.CommandType = adCmdStoredProc
.CommandText = "sp_BrandCode"
End With

Dim param As adodb.Parameter
Set param = cmd.CreateParameter("@brandName", adVarChar, adParamInput, Len(cmbBrandName), cmbBrandName)
cmd.Parameters.Append param

Set param = cmd.CreateParameter("@brandCode", adChar, adParamOutput, 0, 0)
cmd.Parameters.Append param

cmd.Execute


MsgBox cmd.Parameters(0).Value



stored procedure
---------------------


CREATE PROCEDURE [sp_BrandCode]
@BrandName varchar(100),
@BrandCode char(4) output
AS
select @brandCode from BrandMaster where brandName = @brandname


thanks
bobbytom

[ksw]

I would change your code to this

Set cmd = New Command 
With cmd 
.ActiveConnection = con 
.CommandType = adCmdStoredProc 
.CommandText = "sp_BrandCode" 
End With 
 
Dim param As adodb.Parameter 
Set param = cmd.CreateParameter("@brandName", adVarChar, adParamInput, Len(cmbBrandName), cmbBrandName) 
cmd.Parameters.Append param 
 
cmd.Execute 
 
MsgBox rscmd!BrandCode

Then change your stored procedure to this:

CREATE PROCEDURE [sp_BrandCode]
@BrandName varchar(100)
AS

select BrandCode from BrandMaster
where brandName = @brandname

return


I haven't tested this, but when you have a command object that returns a recordset, you should automatically have access to a new object named rsCommandName. Through this, you can access the individual fields and rows the same as any ADO recordset.

--KSW

[Mongo]

You're on the right track bob, you need to clarify & polish the details of your definitions.

1. Since you define @BrandCode as datatype char(4) in your stored proc, how could you better
define your corresponding vb output parameter? (Hint: adChar & adParamOutput are good, but what
about setting the optional size & value to zero on a char(4) datatype?)

2. The cmd.Parameters(0).Value is a stored proc return value. It is an SQLServer int datatype.
However, notice this is moot info, since your stored proc def currently has no return value.
A return_value and an output value are two different ani-mules.

3. It's a subtle stored proc point, but using SELECT to SET a parameter generates a server side
recordset. When you need to do it, you should; otherwise, save some server CPU cycles and use
the more efficient SET.

4. Other more nefarious issues arise when you code to return multiple records to single parameter
or potentially send parameters of greater than defined length.

5. Naming conventions have their time & place. The "sp_" tends to have specific system procedure
connotations, so you might consider using a different prefix for your user defined procs.

Try exploring these points playing with this:

-- SQL Server side
CREATE PROC up_BrandCode (@BrandName varchar(100), @BrandCode char(4) OUTPUT) AS
DECLARE @res int 
  SET NOCOUNT ON
  SET @res = ( SELECT count(*) FROM BrandMaster WHERE brandName = @BrandName )
  IF EXISTS( SELECT brandCode FROM BrandMaster WHERE brandName = @BrandName ) BEGIN
    SET ROWCOUNT 1
    SET @BrandCode = ( SELECT brandCode FROM BrandMaster WHERE brandName = @BrandName )
    SET ROWCOUNT 0
  END
RETURN @res

' On the VB-side
Dim strConn As String 
Dim cmd As ADODB.Command 
Dim con As ADODB.Connection 
Dim param As ADODB.Parameter 
  strConn = "Provider=SQLOLEDB;server=<servername>;Integrated Security='SSPI';database=<databasename>;" 
  Set con = New ADODB.Connection 
  With con
    .Provider = "MSDASQL" 
    .CursorLocation = adUseServer 
    .ConnectionString = strConn 
    .Open
  End With 
  cmbBrandName=TRIM$(LEFT$(cmbBrandName,100)) 
  Set cmd = New ADODB.Command 
  With cmd
    .ActiveConnection = con 
    .CommandType = adCmdStoredProc 
    .CommandText = "up_BrandCode" 
    .Parameters.Append .CreateParameter("@brandName", adVarChar, _
      adParamInput, Len(cmbBrandName), cmbBrandName) 
    Set param = .CreateParameter("@brandCode", adChar, adParamOutput, 4, "Oops") 
    .Parameters.Append param 
    .Execute , , adExecuteNoRecords 
  End With 
  MsgBox "My output is value: " & param.Value
  MsgBox "My return_value is: " & cmd.Parameters(0).Value

Hope this helps.