Solved

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

Posted on 2007-12-02
7
1,920 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
  • 4
  • 3
7 Comments
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 25

Accepted Solution

by:
imitchie earned 250 total points
Comment Utility
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
Comment Utility
but you need to consider that @IKey2 is not an Output!
0
 

Author Comment

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

Author Comment

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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now