Solved

SQL SERVER, 2000 - Procedure expects parameter , which was not supplied.

Posted on 2007-12-02
7
1,927 Views
Last Modified: 2008-02-01
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
Comment
Question by:feesu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20394100
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20394119
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
 

Author Comment

by:feesu
ID: 20394342
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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 25

Accepted Solution

by:
imitchie earned 250 total points
ID: 20394403
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
 

Author Comment

by:feesu
ID: 20394703
but you need to consider that @IKey2 is not an Output!
0
 

Author Comment

by:feesu
ID: 20394706
Plus, IF @SomePara = NULL always works for me :)
0
 

Author Comment

by:feesu
ID: 20394719
Aha! So it ignores the output in case yo specify a value...
It worked fine imitchie, thank you very much.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question