ADO .NET return params

bmains · October 15th, 2003, 02:35 PM

Hello,

I have ADO.NET code trying to insert data using a stored procedure to a SQL Server 2000 database. The stored procedure looks like:

Code:

create procedure sp_insertRole
(
  RoleName varchar(100),
  RoleDesc varchar(512) = NULL,
  RoleID   numeric = 0 output
)

as

set nocount on

insert into Role
(
  RoleName,
  RoleDesc
)
values
(
  @RoleName,
  @RoleDesc
)

set @RoleID = @@identity

My code is:

Code:

set objConnection as new SQLConnection(cs)
set objAdapter as new SQLDataAdapter

objAdapter.InsertCommand = new SQLCommand("sp_insertRole", objConnection)
objAdapter.InsertCommand.CommandType = CommandType.StoredProcedure

objAdapter.InsertCommand.Parameters.Add("@RoleName", sqldbtype.varchar).Value = strRoleName
objAdapter.InsertCommand.Parameters.Add("@RoleDesc", sqldbtype.varchar).Value = strRoleDesc

objAdapter.InsertCommand.Parameters.Add(new SQLParameter("@RoleID", sqldbtype.Decimal, 18, ParamDirection.Output))

objConnection.Open()
objAdapter.InsertCommand.ExecuteNonQuery()
intRoleID = objAdapter.InsertCommand.Parameters("@RoleID").Value
objConnection.Close()

When I execute the stored procedure separately in Query Analyzer, it has the correct value, but when I execute it through the ADO.NET code, no value is set (it is set to default of zero in stored proc). Any ideas why the return value is not returning? I've looked at many examples on the web, and they all seem to be doing the same thing I'm doing...

Thanks,

Brian Mains

planoie · October 15th, 2003, 03:49 PM

Something doesn't look right about your sproc create code... this is how I would have pictured it...

create procedure sp_insertRole
(
  @RoleName varchar(100),
  @RoleDesc varchar(512) = NULL,
  @RoleID numeric = 0 output
)

Peter

bmains · October 16th, 2003, 07:03 AM

OK, thanks, the "@" got left out somehow... anyways, even with that problem resolved, I'm still having errors. Anybody know why it won't return a value from the output parameter?