SQL Server :: An Insufficient Number Of Arguments Were Supplied For The Procedure Of Function
Feb 1, 2011
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
When i execute this SP in DB, it works fine. But when i execute from asp.net app it is throwing error: Procedure or function ... has too many arguments specified
GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[sp_Customer] @brchID char(40), @brchName VARCHAR(50), @userID VARCHAR(50) AS BEGIN TRANSACTION DECLARE @req_pk int DECLARE @customer_org_pk int IF EXISTS(SELECT * FROM [customer] WHERE [org_id] like @brchID) BEGIN SELECT [org_pk] FROM [customer] WHERE [org_id] like @brchID END ELSE BEGIN EXEC sp_get_next_control_no 'organization.org_pk',@customer_org_pk out; INSERT INTO [customer] (org_pk,[org_id],[org_name]) VALUES(@customer_org_pk,@brchID,@brchName) SELECT [org_pk] FROM [customer] WHERE [org_id] like @brchID END --Address Book Entry IF NOT EXISTS(SELECT * FROM [address_book] WHERE [prsn_id] like @userID) BEGIN exec sp_get_next_control_no 'person.prsn_pk',@req_pk out; END IF (@@ERROR <> 0) BEGIN -- Rollback the transaction ROLLBACK RETURN END ELSE BEGIN COMMIT END
Here is my asp method:
public static string Customer(string brchId, string brchName, string userId) { string spName = "st_Customer"; SqlParameter OrgPk = new SqlParameter("@org_pk", SqlDbType.Int); OrgPk.Direction = ParameterDirection.Output; OrgPk.Size = 50; try { SqlHelper.ExecuteNonQuery(Config.ConnectionString, CommandType.StoredProcedure, spName, new SqlParameter("@brchID", brchId), new SqlParameter("@brchName", brchName), new SqlParameter("@userID", userId), OrgPk ); return Convert.ToString(OrgPk.Value); } }
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 am getting problem "procedure or function emp_info has toomany arguments specified" while inserting data into two tables using stored procedure.I specified same number of parameters in stored procedure as specified in the apllication.
I have a gridview that has a column of checkboxes in which you use to select a row of data. Once you select a checkbox or multiple checkboxes and click the 'process' button the data from the rows selected should enter a table in a database. It works fine when i check just one checkbox, but when i check more than one i get the following error: 'Procedure or function insCSSampleDet has too many arguments specified.' Below is a copy of my loop
For Each gvrow As GridViewRow In GridView2.Rows Dim CheckBox1 As CheckBox = DirectCast(gvrow.FindControl("CheckBox1"), CheckBox) If CheckBox1.Checked Then SamplesDataSource2.InsertCommandType = SqlDataSourceCommandType.StoredProcedure SamplesDataSource2.InsertCommand = "insCSSampleDet" SamplesDataSource2.InsertParameters.Add("CSHdrRowid", "1") SamplesDataSource2.InsertParameters.Add("CSPartno", DirectCast(gvrow.FindControl("Label2"), Label).Text) SamplesDataSource2.InsertParameters.Add("CSPartDesc", DirectCast(gvrow.FindControl("Label3"), Label).Text) SamplesDataSource2.InsertParameters.Add("CSQty", DbType.Int32, "1") SamplesDataSource2.Insert() End If NextFor Each gvrow As GridViewRow In GridView2.Rows Dim CheckBox1 As CheckBox = DirectCast(gvrow.FindControl("CheckBox1"), CheckBox) If CheckBox1.Checked Then SamplesDataSource2.InsertCommandType = SqlDataSourceCommandType.StoredProcedure SamplesDataSource2.InsertCommand = "insCSSampleDet" SamplesDataSource2.InsertParameters.Add("CSHdrRowid", "1") SamplesDataSource2.InsertParameters.Add("CSPartno", DirectCast(gvrow.FindControl("Label2"), Label).Text) SamplesDataSource2.InsertParameters.Add("CSPartDesc", DirectCast(gvrow.FindControl("Label3"), Label).Text) SamplesDataSource2.InsertParameters.Add("CSQty", DbType.Int32, "1") SamplesDataSource2.Insert() End If Next
I dont understand why this is occurring, my store pro worked ok before (and i am very sure i am not passing too many arguments to the store pro) but since i have switched to using a "formview"control it has stopped. store pro and ASP.NET with sql data source are shown below. Apologies if the formatting nacks up.
I am using Visual Studio 2005, and VB.NET. I am using a FormView. I have the default mode set to Insert. I fill out the info and click on the Add button and get this error. I have turned Trace on in the page and written suggested code to output the parameters. The parameters exactly match those in the stored procedure in SQL Server 2005. They are even in the same order.
This is my second attempt. First I had the FormView in normal mode. But there are no records yet and I was getting errors due to that. I tried the EmptyData template to no avail. So I decided to make a page that is only for adding new records. I removed the Select template. I have seen posts elsewhere for this error, but they all are concerning the use of a GridView, which I'm not using for this. I am really confused (not the first time). Is the lack of the Select template a problem? When it was still there it was still giving this error. The select statement had the same parameters. The first parameter is a Control parameter, value taken from a hidden field on the form. This worked for another FormView on a different page, so I'm really confused why I can't get this to work.
I have a stored procedure that updates orderhistory. When I test it from Enterprise Manager, that sucker works fine. But when accessing it from asp.net I get the following error... Procedure or function sp_UpdateOrderHistory has too many arguments specified. From my aspx page I created a gridview, enabled editing, deleted the columns I didn't want displayed. I specified the datasource, pointing at the sp for updating. So far so good..
I can click on Edit, and the edit fields are shown, but when I click UPDATE, the above error is generated. I've checked the sp parameters and they match (as far as I can tell) the update paramters in the page. Not sure why this is failing to update?
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 to believe that I'm just missing something silly on this one. It SHOULD work... but alas, it is not. Here is my code:
SELECT s.[id], s.[familyid], s.[lastname], s.[firstname], s.[middlename], s.[suffix], s.[nickname], s.[dob], fix(datediff(d, isnull(s.DOB, Now()), NOW()) / 365) as AGE, s.[allergies], s.[notes], s.[photographsOK], s.[school], f.[city], f.[state] FROM [Students] s, [families] f, [employee] e where s.[familyid] = f.[id] and f.[franchise] = e.[franchisename] and e.[username] = @Username order by f.[lastname]"
This was working fine before I put the "IsNull"handler in the datediff function to handle problems with null dates. Now I get the following error when I try to run it.Wrong number of arguments used with function in query expression 'fix(datediff('d', isnull(s.[DOB], now()), NOW()) / 365)'. I even tried using just the ISNULL(s.[DOB], NOW()) without the Datediff and get the same error.
I have the following sp that uses a scalar function to get the cost of a product
The procedure calculates profit of each bill,and using two tables
1 - Estimate Header
2- Estimate details
For each estimate invoice sp will compare items profit by comparing items rate with the rate it is purchased.I use a Scalar sql Function to get the Purchase Cost of each item in an invoice.This fuction cause the slow execution
Can this scalar function be replace with something else to get this sp execute faster....now it taking around 25 seconds there is only 1100 invoice in the database.
select BillNumber,sdate [Date],case when t1.Customer = 0 then 'Account Payable' else t2.AccountName end Customer ,case when t1.CashOrCredit = 0 then 'Cash' else 'Credit' end CashOrCredit,(select Sum(gross) from EstimateDetails where billNumber = t1.BillNumber)Gross,t1.netDisc,(select sum(TaxAmt) from EstimateDetails where billNumber = t1.BillNumber)Tax,t1.Ces, Amount,t1.roundOff,Paid Payment,
i am developing a webaaplication ASP.net using C# and MYSQL as backend..
i am facing problem to execute this query in MYSQL:
SELECT ID, Date, Event, Remark, school, section, class, schooltype, shift FROM sessioncalendar WHERE (school = 'SECONDARY SCHOOL') AND (class = 'IX') AND (section = 'B') AND (shift = 'SHIFT I') AND (schooltype = 'XYZ School') AND MONTH ( Date )= '6'
AS the query works well wethout addind AND MONTH ( Date )= '6' ... but when i add this line query generate error
( Error in list of function arguments: ')' not recognized. Unable to parse query text.)
While same query is working successfully with Sql Server 2005...but i hvnt found any reason why my query is not working with Date(datetime) column..i hv google about it..but i found synatax is same in both SQL SErver 2005 and MY SQL syntax
a partner has sent over a old school web service to instal on one of our webservers. I cant open the project since its been precompiled, how do i deploy that to my webserver (IIS 7.5 on 2012)? what do i need to copy or is there a way to suck it in from the files in IIS?
I have a ASP.NET web page which contains a table of check boxes and a button. I need to find the IDs of all the check boxes which are checked when the button click happens. Once the list of IDs are collected, it needs to be passed on to the server. I am able to do it using jQuery and PageMethods.How can I achieve this in the button click handler in the code behind file? i.e. the IDs of all the check boxes which are checked when button click happens.
