• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1950
  • Last Modified:

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.SqlException 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_Edit"
  Server="."
  Source=".Net SqlClient Data Provider"
  State=4
  StackTrace:
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at DB_FAQ.FAQ_Question_Edit(Int32 IKey, String QuestionTitle, String QuestionHTML, Int32 EditedBy, DateTime EditedOn, Int32 Module_IKey, String delete) in C:\Elvis\wwwroot\NIC\App_Code\IT\DB_FAQ.vb:line 152
       at FAQ_Question_Edit.btnSave_Click(Object sender, EventArgs e) in C:\Elvis\wwwroot\NIC\DesktopModules\IT\FAQ_Question_Edit.aspx.vb:line 84
       at System.Web.UI.WebControls.LinkButton.OnClick(EventArgs e)
       at System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

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

Open in new window

0
feesu
Asked:
feesu
  • 4
  • 3
1 Solution
 
imitchieCommented:
In your create proc, you have
       @IKey1 int output,

which means that it needs to be set to a variable in your code, to retrieve the value
0
 
imitchieCommented:
setting only @IKey2 and not @IKey1 is like caling
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
with a single paramter, i.e.
System.Data.SqlClient.SqlInternalConnection.OnError(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
0
 
feesuAuthor Commented:
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.
   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

Open in new window

0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
imitchieCommented:
first of all, line 15 or your SP should be
IF @IKey2 IS NULL
you cannot test for = null (will never be true)

I recommend you make the following changes to VB code.  Notice that I reuse the pIKEY Variable. It is either left at IKey1, or IKey2 takes over (which is what you want anyway).
        Dim pIKEY As SqlParameter
        ' no if's or buts, ikey1 MUST be declared. just don't use it
        pIKEY = New SqlParameter("@IKey1", SqlDbType.Int, 4)
        pIKEY.Direction = ParameterDirection.Output
        CMD.Parameters.Add(pIKEY)
 
        If IKey <> 0 Then
            pIKEY = New SqlParameter("@IKey2", SqlDbType.Int, 4)
            pIKEY.Value = IKey
            CMD.Parameters.Add(pIKEY)
        End If

Open in new window

0
 
feesuAuthor Commented:
but you need to consider that @IKey2 is not an Output!
0
 
feesuAuthor Commented:
Plus, IF @SomePara = NULL always works for me :)
0
 
feesuAuthor Commented:
Aha! So it ignores the output in case yo specify a value...
It worked fine imitchie, thank you very much.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now