Stored procedure error 1

[jbehrne]

Hi all,

I have a stored procedure that I would like to have return the created autoid (this is the primary key field, an integer and an identity with an autoincrement of 1). However, when I run the following code I receive the following errors:

Error 9
Description: An SQLParameter with ParameterName '@Error'
is not contained by this SQLParameterCollection.

and

Error 5
Description: Procedure 'sp_NewLeaveSlip' expects parameter
@LeaveID, which is not supplied.

Here is the stored procedure:

CREATE PROCEDURE dbo.sp_NewLeaveSlip(
@LeaveID int OUTPUT,
@EID varchar(10),
@DateInput datetime,
@StartD datetime,
@StartT datetime,
@EndT datetime,
@EndD datetime,
@Hours decimal(18,2),
@LeaveT varchar(50),
@Comments varchar(200),
@FMLASelected bit,
@FMLAReason varchar(100),
@Name varchar(75) )
AS INSERT INTO dbo.LeaveSlips
                        (EmployeeID, DateLeaveInput, StartDate, StartTime, EndTime, EndDate, HoursRequested, LeaveType, Remarks, FMLA, FMLAReason, 
                        EmployeeName)
VALUES      (@EID, @DateInput, @StartD, @StartT, @EndT, @EndD, @Hours, @LeaveT, @Comments, @FMLASelected, @FMLAReason, @Name)
SET @LeaveID = @@IDENTITY
RETURN @@ERROR
GO

HERE is my code calling the stored procedure:

Dim sCmd = New SqlCommand("sp_NewLeaveSlip", cnSql)

                sCmd.CommandType = CommandType.StoredProcedure

        
        Try
            With sCmd.Parameters
                .Add("@EID", SqlDbType.VarChar).Value = IdNumber                                        
                .Add("@DateInput", SqlDbType.DateTime).Value = Today()                                  
                .Add("@StartD", SqlDbType.DateTime).Value = Me.txtStartDate.Text                              
                .Add("@StartT", SqlDbType.DateTime).Value = Me.txtStartTm.Text                              
                .Add("@EndT", SqlDbType.DateTime).Value = Me.txtEndTm.Text                              
                .Add("@EndD", SqlDbType.DateTime).Value = Me.txtEndDate.Text                            
                .Add("@Hours", SqlDbType.Decimal).Value = Me.txtHoursRequested.Text                     
                .Add("@LeaveT", SqlDbType.VarChar).Value = Me.cmbLeaveType.Text                         
                .Add("@Comments", SqlDbType.VarChar).Value = Me.txtComment.Text                         
                .Add("@FMLASelected", SqlDbType.Bit).Value = Me.chkFMLA.CheckState                      
                .Add("@FMLAReason", SqlDbType.VarChar).Value = Me.cmbFMLAReason.Text                    
                .Add("@Name", SqlDbType.VarChar).Value = Me.cmbEmployeeName.Text                        
            End With
            sCmd.Parameters("@Error").Direction = ParameterDirection.ReturnValue
            sCmd.Parameters("@LeaveID").Direction = ParameterDirection.Output
        Catch ex As Exception
            MsgBox("Error: " & Err.Number & "Description: " & Err.Description)
        End Try


        sCmd.ExecuteNonQuery()
            If sCmd.Parameters("@Error").Value = 0 Then
                SelectedLeaveID = sCmd.Parameters("@LeaveID").Value
                CanPrint = True
            Else
                CanPrint = False
                MsgBox("An uknown error has occurred." & vbCrLf & "Please record this information and contact your computer analyst immediately!" & vbCrLf & vbCrLf & "Error: " & Err.Number & vbCrLf & "Description: " & Err.Description, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Error"))
                Exit Function
            End If

Any help would be greatly appreciated!

Thanks,

jbehrne

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[gmmastros]

This will not help you get rid of the vb error, but....

Please, please, please... never use @@Identity with SQL Server. Instead, use Scope_Identity()

[tt][blue]
SET @LeaveID = Scope_Identity()
[/blue][/tt]

Read this to understand why.

http://www.sqljunkies.ddj.com/Article/92CC4817-604D-4344-8BE0-4490F8ED24B6.scuk

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbehrne]

Thanks - I acutally meant to change that to Scope_Identity().

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[mspelly]

Hi all,

I have a stored procedure that I am returning a value.However, when I run the following code I receive the following errors:

Error 9
Description: An SQLParameter with ParameterName '@Error'
is not contained by this SQLParameterCollection.

and

Error 5
Description: Procedure 'usp_CheckForDuplication' expects parameter @CountID, which is not supplied.

Stored Procedure:-
ALTER PROCEDURE [dbo].[usp_CheckForDuplication]
@CheckID TINYINT,
@TechTypeAbbv VARCHAR(5),
@Count INT OUTPUT
AS
Declare @Error AS INT

IF @CheckID = 1 --tb1TechnologyType
BEGIN
SELECT @Count = COUNT(TechAbbv)
FROM tb1TechnologyType
WHERE TechAbbv = @TechTypeAbbv
SET @Error = @@ERROR
RETURN @Error
END

VB Code:

Public Function TST_fnCheckForDuplication(ByVal strStoredProcedureName As String, ByVal iCheckID As Integer, Optional ByVal strTechAbbv As String = vbNullString) As Integer
Dim cmd As SqlCommand
Dim prm As SqlParameter
Dim iCount As Integer
Try
cn = New SqlConnection(gstrDatabaseConnection)
'create the command object and pass the procedure name and connection string
cmd = New SqlCommand(strStoredProcedureName, cn)
cmd.CommandType = CommandType.StoredProcedure
prm = New SqlParameter("@CheckID", SqlDbType.Int, ParameterDirection.Input)
cmd.Parameters.Add(prm).Value = iCheckID
prm = New SqlParameter("@TechTypeAbbv", SqlDbType.VarChar, 5, ParameterDirection.Input)
cmd.Parameters.Add(prm).Value = strTechAbbv
prm = New SqlParameter("@Count", SqlDbType.Int, ParameterDirection.Output)
cmd.Parameters.Add(prm)
cmd.Parameters("@Error").Direction = ParameterDirection.ReturnValue
Call TST_fnOpen_Database()
cmd.ExecuteNonQuery()
If cmd.Parameters("@Error").Value = 0 Then
iCount = CInt(cmd.Parameters("@Count").Value)
Call TST_fnClose_Database()
Return iCount
Else
Call TST_fnErrHandler("mDatabase", "TST_fnCheckForDuplication")
End If
Catch ex As Exception
Call TST_fnErrHandler("mDatabase", "TST_fnCheckForDuplication")
End Try
End Function
Any answer greatly appreciated

Thanks
mspelly

 

[RonRepp]

George:

I've always followed your advice, but now I know the reason why.

Thanks for the article...a star for you.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net