ADO Error: an insufficient number of arguments....

EssCee

Im currently using Access 2000 which is connected to a SQL database. I have a query (Or View) that runs every month. The problem i had was i had to manually make some changes every month to get to the data i needed so i changed this View into a Function with a parameter so i can input the detail(s) i need and it will run correctly - this works so far but the original View was also used within other queries that were created. Now the problem i have now is some of the other views that are now connected with the newly created Function comes up with the error:

ADO error: an insufficient number of arguments were supplied for the procedure or function Name_Of_Function.

I called the newly created function the exact name as the original view to ensure i had no problems. Any idea of whats happening here and how to resolve

Thanks

Madhu K Nair

ADO error: an insufficient number of arguments were supplied for the procedure or function Name_Of_Function

Error says that you are calling this function without providing all the parameters required. Check where all are you referred this Function and check the parameters provided.

Madhu

Jens K. Suessmeyer

Couold you please post the call as well as the function here

Jens K. Suessmeyer

---
http://www.sqlserver2005.de

---

Ekrem Önsoy

Well, actually the answer is in your question.

It did not need a parameter in the past while it was a view so the other views were using it. After a while (according to your text) you turned it in to a function and add a parameter but you did not change your old views which uses this ex-view to use a parameter. You still call this function from your views without a parameter and that's why SQL Server yells for a parameter.

EssCee

Heres the function i have:

Code Block

SELECT TOP 100 PERCENT dbo.[Totals].User, dbo.[Totals].[Account Name], dbo.[Totals].Company, dbo.[Totals].Name,
dbo.[User].Amount AS [Month Amount], dbo.[User].Profit AS [Month Profit], SUM(dbo.[Totals].[Y Amount]) AS [Y Amount],
SUM(dbo.[Totals].[Y Profit]) AS [Y Profit], dbo.[User].Month
FROM dbo.[User] RIGHT OUTER JOIN
dbo.[Totals] ON dbo.[User].[Account Name] = dbo.[Totals].[Account Name] AND
dbo.[User].User = dbo.[Totals].User
GROUP BY dbo.[Totals].User, dbo.[Totals].[Account Name], dbo.[Totals].Company, dbo.[Totals].Name,
dbo.[User].Amount, dbo.[User].Profit, dbo.[User].Month
HAVING (NOT (dbo.[Totals].User = N'Temp')) AND (dbo.[User].Month = @Month)
ORDER BY dbo.[Totals].User, dbo.[Totals].Company

Where it states Month = @Month is where the problem is i think. This Function runs fine as i want it to. But when im in another view that uses this function it get the above error. The only way i dont get the error is when i type in the month then all runs fine - but i would prefer it to ask me what month i need the data for

Thanks

Ekrem Önsoy

@Month is a parameter here and when you want to run your Function, you have to use all parameters. As you add this parameter thing after creating this Function, your old views do not know your new function needs a parameter now. So they try to use this new function without a parameter and then you get an error.

You need to update your views to use this month parameter with your function, otherwise it'll keep raising this error.

EssCee

I updated the view and added the Month into the criteria. I still get the same message. Only when i enter the month in the function and leave out the parameter it works correctly..........

Ekrem Önsoy

Can you show me exactly the Function itself

"Create Function <function_name> ... " included and show me an example how you use this function with the parameter.

EssCee

Im not creating a function via code. Im using Access 2002 to create the function and double clicking all the items that i require.

Once the function is created in Access then again i use Access to create a view or modify the view i have the problem with.

Jens K. Suessmeyer

Do you want to put the parameter in the View is this a view or just a saved query in Access

Jens K. Suessmeyer

---
http://www.sqlserver2005.de
---

EssCee

If i put a parameter into the view then i get an error (i.e. i cant do it) i have one function then i have a view that uses the function created (the code for the function is above).

Thanks

ADO Error: an insufficient number of arguments....

Re: ADO Error: an insufficient number of arguments....

Re: ADO Error: an insufficient number of arguments....

Re: ADO Error: an insufficient number of arguments....

Re: ADO Error: an insufficient number of arguments....

Re: ADO Error: an insufficient number of arguments....

Re: ADO Error: an insufficient number of arguments....

Re: ADO Error: an insufficient number of arguments....

Re: ADO Error: an insufficient number of arguments....

Re: ADO Error: an insufficient number of arguments....

Re: ADO Error: an insufficient number of arguments....