feesu
asked on
SQL SERVER, 2000 - Procedure expects parameter , which was not supplied.
Experts,
Ive got the attached SP. I intend to have add/edit/delete code in the same SP, so I created this.
I pass its parameters from a class; @IKey2 = NULL (I dont pass it) whenever I need to add new record. So that I get back the new ID in the output @IKey1.
And I specify @IKey2 whenever I need to edit (delete or update) along with a parameter that specifies deletion.
In our case, I pass @IKey2 as 63, which is an existing record, even though, it says:
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------
System.Data.SqlClient.SqlE xception was unhandled by user code
Class=16
ErrorCode=-2146232060
LineNumber=0
Message="Procedure 'FAQ_Question_Edit' expects parameter '@IKey1', which was not supplied."
Number=201
Procedure="FAQ_Question_Ed it"
Server="."
Source=".Net SqlClient Data Provider"
State=4
StackTrace:
at System.Data.SqlClient.SqlC onnection. OnError(Sq lException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlI nternalCon nection.On Error(SqlE xception exception, Boolean breakConnection)
at System.Data.SqlClient.TdsP arser.Thro wException AndWarning (TdsParser StateObjec t stateObj)
at System.Data.SqlClient.TdsP arser.Run( RunBehavio r runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlC ommand.Fin ishExecute Reader(Sql DataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlC ommand.Run ExecuteRea derTds(Com mandBehavi or cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlC ommand.Run ExecuteRea der(Comman dBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlC ommand.Int ernalExecu teNonQuery (DbAsyncRe sult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlC ommand.Exe cuteNonQue ry()
at DB_FAQ.FAQ_Question_Edit(I nt32 IKey, String QuestionTitle, String QuestionHTML, Int32 EditedBy, DateTime EditedOn, Int32 Module_IKey, String delete) in C:\Elvis\wwwroot\NIC\App_C ode\IT\DB_ FAQ.vb:lin e 152
at FAQ_Question_Edit.btnSave_ Click(Obje ct sender, EventArgs e) in C:\Elvis\wwwroot\NIC\Deskt opModules\ IT\FAQ_Que stion_Edit .aspx.vb:l ine 84
at System.Web.UI.WebControls. LinkButton .OnClick(E ventArgs e)
at System.Web.UI.WebControls. LinkButton .RaisePost BackEvent( String eventArgument)
at System.Web.UI.WebControls. LinkButton .System.We b.UI.IPost BackEventH andler.Rai sePostBack Event(Stri ng eventArgument)
at System.Web.UI.Page.RaisePo stBackEven t(IPostBac kEventHand ler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePo stBackEven t(NameValu eCollectio n postData)
at System.Web.UI.Page.Process RequestMai n(Boolean includeStagesBeforeAsyncPo int, Boolean includeStagesAfterAsyncPoi nt)
Ive got the attached SP. I intend to have add/edit/delete code in the same SP, so I created this.
I pass its parameters from a class; @IKey2 = NULL (I dont pass it) whenever I need to add new record. So that I get back the new ID in the output @IKey1.
And I specify @IKey2 whenever I need to edit (delete or update) along with a parameter that specifies deletion.
In our case, I pass @IKey2 as 63, which is an existing record, even though, it says:
--------------------------
System.Data.SqlClient.SqlE
Class=16
ErrorCode=-2146232060
LineNumber=0
Message="Procedure 'FAQ_Question_Edit' expects parameter '@IKey1', which was not supplied."
Number=201
Procedure="FAQ_Question_Ed
Server="."
Source=".Net SqlClient Data Provider"
State=4
StackTrace:
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlI
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at DB_FAQ.FAQ_Question_Edit(I
at FAQ_Question_Edit.btnSave_
at System.Web.UI.WebControls.
at System.Web.UI.WebControls.
at System.Web.UI.WebControls.
at System.Web.UI.Page.RaisePo
at System.Web.UI.Page.RaisePo
at System.Web.UI.Page.Process
CREATE PROCEDURE [FAQ_Question_Edit]
(
@IKey1 int output,
@IKey2 int = NULL,
@QuestionTitle nvarchar(200) = NULL,
@QuestionHTML ntext = NULL,
@EditedBy int = NULL,
@EditedOn smalldatetime=NULL,
@Module_IKey int = NULL,
@Delete nvarchar(1) = NULL
)
AS
BEGIN
IF @IKey2 = NULL
BEGIN
INSERT INTO FAQ_Question
(
QuestionTitle,
QuestionHTML,
EditedBy,
EditedOn,
[Module_IKey]
)
VALUES
(
@QuestionTitle,
@QuestionHTML,
@EditedBy,
@EditedOn,
@Module_IKey
)
SELECT @IKey1 = SCOPE_IDENTITY()
END
ELSE
IF @Delete='N'
BEGIN
UPDATE FAQ_Question SET
QuestionTitle=@QuestionTitle,
QuestionHTML=@QuestionHTML,
EditedBy=@EditedBy,
EditedOn=@EditedOn,
Module_IKey=@Module_IKey
WHERE IKey=@IKey2
END
ELSE
BEGIN
DELETE FAQ_Question
WHERE IKey=@IKey2
END
END
GO
setting only @IKey2 and not @IKey1 is like caling
System.Data.SqlClient.SqlI nternalCon nection.On Error(SqlE xception exception, Boolean breakConnection)
with a single paramter, i.e.
System.Data.SqlClient.SqlI nternalCon nection.On Error(True /* breakConnection */)
There are plenty of examples of using OUTPUT parameters in C#, just a quick google search turns this one up as the first
http://www.codeproject.com/useritems/simplecodeasp.asp
search term: c# declare stored procedure output parameter
System.Data.SqlClient.SqlI
with a single paramter, i.e.
System.Data.SqlClient.SqlI
There are plenty of examples of using OUTPUT parameters in C#, just a quick google search turns this one up as the first
http://www.codeproject.com/useritems/simplecodeasp.asp
search term: c# declare stored procedure output parameter
ASKER
imitchie,
This is the class i call the SP from. Please look at it and tell me how may i rectify that bug. I need to encapsulate all in one SP.
This is the class i call the SP from. Please look at it and tell me how may i rectify that bug. I need to encapsulate all in one SP.
Public Function FAQ_Question_Edit(ByVal IKey As Integer, _
ByVal QuestionTitle As String, _
ByVal QuestionHTML As String, _
ByVal EditedBy As Integer, _
ByVal EditedOn As Date, _
ByVal Module_IKey As Integer, _
Optional ByVal delete As String = "N" _
) As Integer
Dim CN As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("SiteSqlServer").ToString)
Dim CMD As SqlCommand
CMD = New SqlCommand("FAQ_Question_Edit", CN)
CMD.CommandType = CommandType.StoredProcedure
Dim P As SqlParameter
Dim pIKEY As SqlParameter
If IKey = 0 Then
pIKEY = New SqlParameter("@IKey1", SqlDbType.Int, 4)
pIKEY.Direction = ParameterDirection.Output
CMD.Parameters.Add(pIKEY)
Else
pIKEY = New SqlParameter("@IKey2", SqlDbType.Int, 4)
pIKEY.Value = IKey
CMD.Parameters.Add(pIKEY)
End If
P = New SqlParameter("@QuestionTitle", SqlDbType.NVarChar)
P.Value = QuestionTitle
CMD.Parameters.Add(P)
P = New SqlParameter("@QuestionHTML", SqlDbType.NText)
P.Value = QuestionHTML
CMD.Parameters.Add(P)
P = New SqlParameter("@EditedBy", SqlDbType.Int, 4)
P.Value = EditedBy
CMD.Parameters.Add(P)
P = New SqlParameter("@EditedOn", SqlDbType.SmallDateTime)
P.Value = EditedOn
CMD.Parameters.Add(P)
P = New SqlParameter("@module_iKey", SqlDbType.Int, 4)
P.Value = Module_IKey
CMD.Parameters.Add(P)
P = New SqlParameter("@Delete", SqlDbType.NVarChar)
P.Value = delete
CMD.Parameters.Add(P)
CN.Open()
CMD.ExecuteNonQuery()
CN.Close()
Return CInt(pIKEY.Value)
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
but you need to consider that @IKey2 is not an Output!
ASKER
Plus, IF @SomePara = NULL always works for me :)
ASKER
Aha! So it ignores the output in case yo specify a value...
It worked fine imitchie, thank you very much.
It worked fine imitchie, thank you very much.
@IKey1 int output,
which means that it needs to be set to a variable in your code, to retrieve the value