Thanks Bob, I had just figured it out right before I read your response. I do have one question, do you have to return the output parameter or is it returned no matter what? And also, If the stored procedure would happen to fail, what would occur, is there a set return value or something? I have a class setup to handle all my data needs and my generatekey is in a function. if anyone would like to see the class I would be more than happy to post it. Also for anyone interested in this post. Here is my final code:
'Stored Procedure
CREATE PROCEDURE spGetKey
@TableName varchar(40), @ReturnValue int output
AS
UPDATE tblESCGlob
SET
intKeyValue = intKeyValue + intIncrementValue,
@ReturnValue = intKeyValue + intIncrementValue
FROM
tblESCGlob
WHERE
strTableName = @TableName
return @ReturnValue
GO
'Code :
Public Function GenerateKey(ByVal lcTableName As String) As Int32
Try
lcTableName = UCase(lcTableName)
Dim lADO As ADO = New ADO()
lADO.strSQL = "spGetKey"
lADO.CreateCommand()
lADO.objCmd.CommandType = CommandType.StoredProcedure
' Add Input Parameter
Dim TableName As SqlClient.SqlParameter = _
lADO.objCmd.Parameters.Add("@TableName", SqlDbType.VarChar)
TableName.Direction = ParameterDirection.Input
TableName.Value = lcTableName
' Add Output Parameter
Dim ReturnValue As SqlClient.SqlParameter = _
New SqlClient.SqlParameter("@ReturnValue", SqlDbType.Int, 4)
ReturnValue.Direction = ParameterDirection.Output
lADO.objCmd.Parameters.Add(ReturnValue)
lADO.OpenConnection()
lADO.ExeNonQueryCommand()
lADO.CloseConnection()
Dim liKeyValue As Int32
liKeyValue = ReturnValue.Value
Return liKeyValue
Catch exc As Exception
GetError("FileError.log", Err.Description, Err.Source, Err.Erl, exc)
End Try
End Function
Randy Belcher
AFG Industries, Inc.