Ed
asked on
An SqlParameter with ParameterName '@IVRECORDID' is not contained by this SqlParameterCollection.
Hi
I keep getting the error in the title whenever I try to retun a parameter from a SQL insert procedure.
Please can you tell me what I'm missing. It keeps looking for the parameter IVRECORDID but it is supplied by the SP.
it worked fine inserting records but now I want to return an ID after insert.
cmd.Parameters("@IVRECORDI D").Direct ion = ParameterDirection.Output
VB
SQL SP
I keep getting the error in the title whenever I try to retun a parameter from a SQL insert procedure.
Please can you tell me what I'm missing. It keeps looking for the parameter IVRECORDID but it is supplied by the SP.
it worked fine inserting records but now I want to return an ID after insert.
cmd.Parameters("@IVRECORDI
VB
If Session("ModeType") = "Insert" Then
Dim strConnString As String = ConfigurationManager.ConnectionStrings("IrisConnectionString").ConnectionString
Dim con As New SqlConnection(strConnString)
Dim cmd As New SqlCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "INSERT_IQA"
cmd.Parameters.Add("@TRAINEEID", SqlDbType.NVarChar).Value = Request("TRAINEEID")
cmd.Parameters.Add("@POT", SqlDbType.Int).Value = Request("POT")
cmd.Parameters.Add("@QUALSCHEMEID", SqlDbType.Int).Value = ddlQualSchemeID.SelectedValue.Trim()
cmd.Parameters.Add("@IVTYPEID", SqlDbType.Int).Value = ddlIVType.SelectedValue.Trim()
cmd.Parameters.Add("@STAFFID", SqlDbType.NVarChar).Value = User.Identity.Name
cmd.Parameters.Add("@DATEPLANNED", SqlDbType.Date).Value = txtDateCompleted.Text.Trim()
cmd.Parameters.Add("@DATEACTUAL", SqlDbType.Date).Value = txtActualDate.Text.Trim()
cmd.Parameters.Add("@DATECOMPLETED", SqlDbType.Date).Value = txtDateCompleted.Text.Trim()
cmd.Parameters.Add("@IVOUTCOMEID", SqlDbType.Int).Value = ddlOutcome.SelectedValue.Trim()
cmd.Parameters.Add("@IVSITEID", SqlDbType.Int).Value = ddlIQASite.SelectedValue.Trim()
cmd.Parameters.Add("@ASSESSORID", SqlDbType.NVarChar).Value = ddlAssessorID.SelectedValue.Trim()
cmd.Parameters("@IVRECORDID").Direction = ParameterDirection.Output
con.Open()
cmd.ExecuteNonQuery()
con.Close()
' Hidden Value to Select Scope Identity value in Gridview
hdIVRecordID.Value = cmd.Parameters("@IVRECORDID").Value.ToString()
' Bind the GridView
GridIVRecs.DataBind()
SQL SP
ALTER PROCEDURE [dbo].[INSERT_IQA]
@IVRECORDID int output,
@TRAINEEID varchar(16),
@POT int,
@QUALSCHEMEID int,
@IVTYPEID int,
@STAFFID int,
@DATEPLANNED datetime,
@DATEACTUAL datetime,
@DATECOMPLETED datetime,
@IVOUTCOMEID int,
@IVSITEID int,
@ASSESSORID varchar(16)
AS
Begin
INSERT INTO I_TRAINEE_IV_RECORDS
([TRAINEEID]
,[POT]
,[QUALSCHEMEID]
,[IVTYPEID]
,[STAFFID]
,[DATEPLANNED]
,[DATEACTUAL]
,[DATECOMPLETED]
,[IVOUTCOMEID]
,[IVSITEID]
,[ASSESSORID])
VALUES
(@TRAINEEID
,@POT
,@QUALSCHEMEID
,@IVTYPEID
,@STAFFID
,@DATEPLANNED
,@DATEACTUAL
,@DATECOMPLETED
,@IVOUTCOMEID
,@IVSITEID
,@ASSESSORID)
--SET @IVRECORDID=SCOPE_IDENTITY()
-- RETURN @IVRECORDID
end
ASKER
Thanks but I tried that before I commented it out. Tried it again, still the same.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You need to UnComment the IVRECORDID in stored procedure for returning the output.
Open in new window