Procedure Or Function Expects Parameter Which Was Not Supplied?
Apr 7, 2010
Driving me mad on a personal project; I know I've done this before but elsewhere and don't have the code. As far as I can see, I'm setting the parameter, I'm setting its value, the connection is open, yet when I try to fill the dataset I get the error 'Procedure or function expects parameter "@test" which was not supplied'. (This is obviously a simplified test! Same error on this or the real, rather longer code though.)
C#:
SqlCommand l_oCmd;
DataSet l_oPage = new DataSet();
l_oCmd = new SqlCommand("usp_test", g_oConn);
I have a stored procedure which is declared as follows: ALTER PROCEDURE dbo.thisProc @ID int,@TypeID int, @DocID int, @Section varchar(10) What I need is to be able to do this: If @ID is supplied, execute a particular if block if @ID is not supplied then move check if @TypeID is input and then execute another if block. I don't want to write multiple sql queries to deal with different inputs/page sections that a user is viewing.
SAMPLE CODE: CREATE PROCEDURE GetArticle @ID int,@DoTypeID int, @DocID int, @Sec varchar(10) AS IF @ID IS NOT NULL AND , @DocID IS NOT NULL AND @Sec = 'inner' BEGIN SELECT "my query is here" WHERE Articles.ID = @ID AND Articles.DocID = @DocID END ELSE IF @ID IS NULL AND @DocID IS NULL AND @Sec = 'list' BEGIN EXEC GetDocList @DocTypeID END ELSE IF @ID IS NULL AND @DocID IS NULL AND @Sec = 'full' BEGIN EXEC GetDocList @DocTypeID END
I have finally got my paging code for a datalist to work correctly. However, the stored procedure I use receives a Parameter called 'name' which is a VarChar 4. I'm having a little trouble implementing the handling of the 'name' parameter in the following code. I receive the error [Procedure or function 'search' expects parameter '@name', which was not supplied.]
Private Sub DataBind() Dim objConn As New SqlConnection(ConfigurationManager.ConnectionStrings("SQL2005_504887_2onetennisConnectionString").ConnectionString) [code]....
I think it will look something like the following but I'm not sure because this syntax doesn't look like it would work with my above code.
we recently upgraded an application (.NET 2.0) that we maintain to use Oracle 11g. The application uses MS Enterprise Library 2.0. We've found that when the database.ExecuteNonQuery(Oracle.DataAccess.Client.OracleCommand) method is called, it bombs when the stored procedure expects a parameter as a number, but receives a string. This didn't happen prior to upgrading. If I cast the parameter to an Int, I don't recieve the error. Was something possible missed during the upgrade? The issue occurs regardless if we are hitting an 11g database, or a 10g database.
EDIT: I neglected to mention that this same issue does not occur when ExecuteDataReader is called (handles implicit conversion of the datatype).
I am maintaining a sql proc someone else wrote long ago. Basically I have a stored procedure which calls a function. If I try to run the stored procedure, or to make changes to it, and then run it, I am getting an error message which says "An insufficient number of arguments were supplied for the procedure of function dbo.GetRecentComment
This is very odd, because the stored procedure runs successfully when called via the C# code. Yet if it is run within query analyzer with the correct parameters, I get that error message. Here is a little peice of the stored proc which calls the function. And below that is the function which is being called. It appears that the second parameter passed to the function is optional, yet I am not sure. Anyway, here is some of the code from the proc, which calls the function.
SELECT CompletionDate, dbo.GetRecentComment(Request.RequestId) AS Comments -- This line is where the function is called FROM Request
Here is the function which is being called
ALTER FUNCTION [dbo].[GetRecentComment] ( @RequestId int, @SmallTag bit -- smalltag appears to be optional. ) RETURNS VARCHAR(500) AS BEGIN
I have an ASP.NET PageMethod with the following signature:
<WebMethod()> _ Public Shared Function SaveCodes(ByVal codes As List(Of Code)) As String
I am trying to pass a JSON object to the PageMethod from the client side, but I get an error that the type String cannot be converted to type of List<Code>.
Here is the json that I'm building on the client and sending to the method:
[code]....
I have been able to pass in simple data types and a single instance of the Code class, but I can't seem to find the magin to pass a List to the server method. Can anyone show me what I'm doing wrong?
How can I execute a stored procedure that takes in parameters without having to specify the prameters name? The name of the parameter in the stored procedure may change from CustomerID to CustID so I don't want to have to keep changing my code.Rather than doing what is provided below where you specify the parameter name - command.Parameters.Add("@dtStart", SqlDbType.DateTime); command.Parameters["@dtStart"].Value = startDate; command.Parameters.Add("@CustomerID", SqlDbType.NChar); command.Parameters["@CustomerID"].Value = customerID;
My Stored procedure takes one parameter @IDs and it has values in it like '1,2,3'. I want to get rid of these single quotes. How can I do it? Like I want just 1,2,3 and NOT '1,2,3'. How do I modify this parameter value inside my SP?
Being new to using the declarative syntax of SqlDataSource I am trying to figure out a way to set the value of a parameter to a stored procedure. I have a Client_ID that is passed via the Request Object and I need to set the Client_ID before the stored procedure of the SqlDataSource is executed.
Does the stored procedure parameter have to be predefined in the ASPX markup or can it be added dynamically in the code-behind? Would anyone have an example that demonstrates the SqlDataSource markup with a stored procedure and parameter as well as setting that parameter value in code-behind?
I am having some trouble with the below code snippet. It is a stored procedure which executes on load that has an output parameter which should display in a label. Familiar which Vb however not so much in C#.
I have an application with 40 or so separate databases that share a number of identical table schemas. Each is for a separate agency that performs functions identical (nearly) to the others. I have a number of reports that involve complex queries that each agency is required to produce. There is a "top-level" agency that would like to run the same reports but have the data consolidated for ALL agencies. I have tried having the query generated simply re-produced for each sub-agency by using a "UNION" ..
SELECT .... FROM [Agency].[dbo].[Table] ... UNION SELECT ... FROM [Next Agency].[dbo].[Table]....
This works for a few agencies selected/included but when they try to select all agencies or more than a few, it fails because the query is too large. I have tested a stored procedure:
CREATE PROC Test @DB AS SELECT ... FROM [@DB].[dbo].[Table]...
Thinking I can build a smaller query by having up to 40 items of "EXEC Test (db) UNION EXEC Test (next db)" That also fails.... won't permit the database specifier as a parameter (plus, I don't know if I can do a UNION on the EXEC results).
select AssetCode ,Description ,EquipmentID from [FATMS].[fnMaintenanceHeaderRead](@ContextUser,@ContextClient,@ContextLang,@ContextApp,@ContextOrg,'1',@fnXmlParamMaintenanceHeader) MH where MH.Status IN ( @Status)
when i put three values for parameter (@status) not work but one value work
how to put three values please to parameter @status when excecute that stored procedure
I have stored procedure that increments a number. I am supposed to use the newly generated number and insert it into a table. My question is how do I execute the stored procedure, with a hardcoded value of 3 that it expects as a paramter, receive the number it generates as the output parameter and use it in the insert statement.
Here is the stored procedure code:
CREATE PROCEDURE [dbo].[Generate] @myid int, @next_no int output AS update mytable set mynumber=mynumber + 1, @next_no = number where myid=@myid RETURN
Here is my insert statement, that I tried and it failed:
