May 24, 2014 at 2:24 am
I am getting an error in a stored procedure saying too many arguments are specified. I am using a FormView in ASP.NET (VB.NET) on the web page. The parameters list matches exactly the parameters in the stored procedure. I can't understand what is wrong. In Visual FoxPro I could check the number of parameters that were actually passed to a procedure. I can't find an equivalent funtion in SQL Server. It was a great help in VFP years ago. I find it odd that I can't do the same thing in this "superior product".
<UpdateParameters>
<asp:Parameter Name="nProductID" Type="Int32" />
<asp:Parameter Name="cProductTitle" Type="String" />
<asp:Parameter Name="nCategory3D" Type="Byte" />
<asp:Parameter Name="nSecondaryCat" Type="Byte" />
<asp:Parameter Name="cReadMe" Type="String" />
<asp:Parameter Name="nNumberOfFiles" Type="Byte" />
<asp:Parameter Name="cLongDesc" Type="String" />
<asp:Parameter Name="nPrice" Type="Decimal" />
<asp:Parameter Name="nSalePrice" Type="Decimal" />
<asp:Parameter Name="nPercentOff" Type="Int32" />
<asp:Parameter Name="lOnSale" Type="Boolean" />
<asp:Parameter Name="lFreebie" Type="Boolean" />
<asp:Parameter DbType="Datetime" Name="dSaleStart" />
<asp:Parameter DbType="Datetime" Name="dSaleEnd" />
<asp:Parameter Name="cFeatures" Type="String" />
<asp:Parameter Name="cRequirements" Type="String" />
<asp:Parameter Name="nSoftwareRequirements" Type="Byte" />
<asp:Parameter Name="nDAZversion" Type="Byte" />
<asp:Parameter Name="nPoserVersion" Type="Byte" />
<asp:Parameter Name="nBryceVersion" Type="Byte" />
<asp:Parameter Name="nCarraraVersion" Type="Byte" />
<asp:Parameter Name="cOtherVersions" Type="String" />
<asp:Parameter Name="lShadersOnly" Type="Boolean" />
<asp:Parameter Name="cMarketingCredits" Type="String" />
<asp:Parameter Name="nModelRequired" Type="Byte" />
<asp:Parameter Name="cOtherModel" Type="String" />
<asp:Parameter Name="lUpdated" Type="Boolean" />
<asp:Parameter DbType="Datetime" Name="dUpdateDate" />
</UpdateParameters>
ALTER PROCEDURE [dbo].[MyUpdateCode] --not the actual name of the procedure
@nProductID int,
@cProductTitle varchar(75),
@nCategory3D tinyint,
@nSecondaryCat tinyint,
@cReadMe text,
@nNumberOfFiles tinyint,
@cLongDesc varchar(2000),
@nPrice decimal,
@nSalePrice decimal,
@nPercentOff int,
@lOnSale bit,
@lFreebie bit,
@dSaleStart datetime,
@dSaleEnd datetime,
@cFeatures text,
@cRequirements text,
@nSoftwareRequirements tinyint,
@nDAZversion tinyint,
@nPoserVersion tinyint,
@nBryceVersion tinyint,
@nCarraraVersion tinyint,
@cOtherVersions varchar(500),
@lShadersOnly bit,
@cMarketingCredits varchar(1000),
@nModelRequired tinyint,
@cOtherModel varchar(50),
@lUpdated bit,
@dUpdateDate datetime
AS
BEGIN
Help?
Dana
May 25, 2014 at 1:28 am
Heh... Join the crowd... everyone always has some gripe when they have to make a change in what they're using. 😉 You being an ol' FoxPro user makes it even more difficult because it was a great product that they discontinued. I'm still griping because they took away the functionality that used to lie under the {f4} key and because they took away sp_MakeWebtask.
Anyway, using the built in Windows functionality of an {alt]{click}{drag} to copy one set next to another, I was able to quickly verify that you're correct... they do, indeed, match line for line.
<asp:Parameter Name="nProductID" Type="Int32" /> @nProductID int,
<asp:Parameter Name="cProductTitle" Type="String" /> @cProductTitle varchar(75),
<asp:Parameter Name="nCategory3D" Type="Byte" /> @nCategory3D tinyint,
<asp:Parameter Name="nSecondaryCat" Type="Byte" /> @nSecondaryCat tinyint,
<asp:Parameter Name="cReadMe" Type="String" /> @cReadMe text,
<asp:Parameter Name="nNumberOfFiles" Type="Byte" /> @nNumberOfFiles tinyint,
<asp:Parameter Name="cLongDesc" Type="String" /> @cLongDesc varchar(2000),
<asp:Parameter Name="nPrice" Type="Decimal" /> @nPrice decimal,
<asp:Parameter Name="nSalePrice" Type="Decimal" /> @nSalePrice decimal,
<asp:Parameter Name="nPercentOff" Type="Int32" /> @nPercentOff int,
<asp:Parameter Name="lOnSale" Type="Boolean" /> @lOnSale bit,
<asp:Parameter Name="lFreebie" Type="Boolean" /> @lFreebie bit,
<asp:Parameter DbType="Datetime" Name="dSaleStart" /> @dSaleStart datetime,
<asp:Parameter DbType="Datetime" Name="dSaleEnd" /> @dSaleEnd datetime,
<asp:Parameter Name="cFeatures" Type="String" /> @cFeatures text,
<asp:Parameter Name="cRequirements" Type="String" /> @cRequirements text,
<asp:Parameter Name="nSoftwareRequirements" Type="Byte" /> @nSoftwareRequirements tinyint,
<asp:Parameter Name="nDAZversion" Type="Byte" /> @nDAZversion tinyint,
<asp:Parameter Name="nPoserVersion" Type="Byte" /> @nPoserVersion tinyint,
<asp:Parameter Name="nBryceVersion" Type="Byte" /> @nBryceVersion tinyint,
<asp:Parameter Name="nCarraraVersion" Type="Byte" /> @nCarraraVersion tinyint,
<asp:Parameter Name="cOtherVersions" Type="String" /> @cOtherVersions varchar(500),
<asp:Parameter Name="lShadersOnly" Type="Boolean" /> @lShadersOnly bit,
<asp:Parameter Name="cMarketingCredits" Type="String" /> @cMarketingCredits varchar(1000),
<asp:Parameter Name="nModelRequired" Type="Byte" /> @nModelRequired tinyint,
<asp:Parameter Name="cOtherModel" Type="String" /> @cOtherModel varchar(50),
<asp:Parameter Name="lUpdated" Type="Boolean" /> @lUpdated bit,
<asp:Parameter DbType="Datetime" Name="dUpdateDate" /> @dUpdateDate datetime
I don't even know how to spell "ASP" but I did notice that some of the lines of ASP code have the Name and DBType attributes reversed and don't know if that would be the cause of the problem or not, but thought it might be worth mentioning just in case it is.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 25, 2014 at 1:32 am
Again, I don't know ASP but if any of the parameters aren't populated, is ASP still smart enough to send all of the parameters?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 25, 2014 at 1:51 am
I don't think that it matters about the position in the parameter of the dbtype spec, but I'll check it out. It is ASP.NET. Classic ASP is quite a different animal. In any case, from what I know this error is indicating that more parameters were sent than the procedure has receptors for...but it is ambiguous. I really wish T-SQL had a function like PARAMETERS() in VFP. I could know for sure.
Anyone else have any thoughts? It's surprising that there was only one person replying in a time span of about 23 hours.
Dana
May 25, 2014 at 2:01 am
I just swapped those two dbType attributes to the end like all the other type attributes in the parameters and it didn't make a difference.
Dana
May 25, 2014 at 4:20 am
danaanderson (5/25/2014)
Anyone else have any thoughts? It's surprising that there was only one person replying in a time span of about 23 hours.
not sure what you expect from a volunteer forum..........???
anyways...I know nothing about ASP but a quick google came up with this and others like it on SO.
it may help...may not.....
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 25, 2014 at 9:33 am
danaanderson (5/25/2014)
I don't think that it matters about the position in the parameter of the dbtype spec, but I'll check it out. It is ASP.NET. Classic ASP is quite a different animal. In any case, from what I know this error is indicating that more parameters were sent than the procedure has receptors for...but it is ambiguous. I really wish T-SQL had a function like PARAMETERS() in VFP. I could know for sure.Anyone else have any thoughts? It's surprising that there was only one person replying in a time span of about 23 hours.
Dana
Heh... it IS an SQL Server forum with a large number of people that live in the United states and you've asked an ASP question on a holiday weekend. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 25, 2014 at 11:39 am
Have you tried look at the profiler session of the parameters being passed in? Also - have you made sure that there is in fact only ONE stored proc with that specific name (e.g. myuser.StoredProc and dbo.StoredProc)? If the ASP is using a specific user account it might not be using the default schema.
On profiler: start a session recording, then fire your ASP call at it - you should be able to see what it being passed in.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 25, 2014 at 11:48 pm
J Livingston SQL (5/25/2014)
danaanderson (5/25/2014)
Anyone else have any thoughts? It's surprising that there was only one person replying in a time span of about 23 hours.
not sure what you expect from a volunteer forum..........???
anyways...I know nothing about ASP but a quick google came up with this and others like it on SO.
it may help...may not.....
Well, what I expected was some responses...I see a lot of helpfulness here. That's why it was my first choice to post the question here.
Thank you, I did Google this issue...and saw some things at stackoverflow...but they weren't that one. many times when I go to that site I see things that aren't helpful, that are about different circumstances entirely, or even often have no replies at all. Once in a while I see something that is actually helpful to me. This one was that...it was exactly the answer! Thank you again!
Dana
May 25, 2014 at 11:50 pm
Jeff Moden (5/25/2014)
danaanderson (5/25/2014)
I don't think that it matters about the position in the parameter of the dbtype spec, but I'll check it out. It is ASP.NET. Classic ASP is quite a different animal. In any case, from what I know this error is indicating that more parameters were sent than the procedure has receptors for...but it is ambiguous. I really wish T-SQL had a function like PARAMETERS() in VFP. I could know for sure.Anyone else have any thoughts? It's surprising that there was only one person replying in a time span of about 23 hours.
Dana
Heh... it IS an SQL Server forum with a large number of people that live in the United states and you've asked an ASP question on a holiday weekend. 😉
Well, it was SQL Server that was throwing the error. I thought it was the best place to start looking...and asking. True, though, it is a holiday weekend and most are probably out firing up the barbie.
Dana
May 25, 2014 at 11:56 pm
Matt Miller (#4) (5/25/2014)
Have you tried look at the profiler session of the parameters being passed in? Also - have you made sure that there is in fact only ONE stored proc with that specific name (e.g. myuser.StoredProc and dbo.StoredProc)? If the ASP is using a specific user account it might not be using the default schema.On profiler: start a session recording, then fire your ASP call at it - you should be able to see what it being passed in.
I don't think I can use profiler. My site and database are on a shared hosting service and concerning SQL Server I am sometimes severely hamstrung. There have been many scripts for querying performance and certain DBA type jobs that I have tried to run only to get the "You do not have permission for that function" message.
Luckily, this issue has been resolved. But thank you!
Dana
May 26, 2014 at 8:26 am
danaanderson (5/25/2014)
Jeff Moden (5/25/2014)
danaanderson (5/25/2014)
I don't think that it matters about the position in the parameter of the dbtype spec, but I'll check it out. It is ASP.NET. Classic ASP is quite a different animal. In any case, from what I know this error is indicating that more parameters were sent than the procedure has receptors for...but it is ambiguous. I really wish T-SQL had a function like PARAMETERS() in VFP. I could know for sure.Anyone else have any thoughts? It's surprising that there was only one person replying in a time span of about 23 hours.
Dana
Heh... it IS an SQL Server forum with a large number of people that live in the United states and you've asked an ASP question on a holiday weekend. 😉
Well, it was SQL Server that was throwing the error. I thought it was the best place to start looking...and asking. True, though, it is a holiday weekend and most are probably out firing up the barbie.
Dana
Ah... understood. I noticed that you marked the post of JLS as the answer so I went and looked at the post he provided a link for. It would seem that some form of "read only field" was the problem and you needed to change the parmeter listing of at least 1 of the fields from "bind" to "eval". If that's true, which of your fields was it and was it an "IDENTITY" or "COMPUTED" column or ???
I'm asking because the code is automatically generated based on the stored procedure parameters and seems like it could be a common problem. Since SQL Server produced the error, I thought I'd ask for more details on what you did for a fix so that folks like me that know little of ASP can lend a helping hand in the future.
Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 26, 2014 at 8:57 am
danaanderson (5/25/2014)
J Livingston SQL (5/25/2014)
danaanderson (5/25/2014)
Anyone else have any thoughts? It's surprising that there was only one person replying in a time span of about 23 hours.
not sure what you expect from a volunteer forum..........???
anyways...I know nothing about ASP but a quick google came up with this and others like it on SO.
it may help...may not.....
Well, what I expected was some responses...I see a lot of helpfulness here. That's why it was my first choice to post the question here.
Thank you, I did Google this issue...and saw some things at stackoverflow...but they weren't that one. many times when I go to that site I see things that aren't helpful, that are about different circumstances entirely, or even often have no replies at all. Once in a while I see something that is actually helpful to me. This one was that...it was exactly the answer! Thank you again!
Dana
Dana...as you say...the people on this forum are very helpful and friendly and I appreciate why you posted as you did.
that said... I am glad you have found a solution....though it was pure luck that my google-fu came up with an answer that matched your requirements........:w00t:
hope we can help again in the future if required....also, suggest you respond to Jeff's question about how you fixed it ....we can then all learn.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 26, 2014 at 1:29 pm
Jeff Moden (5/26/2014)
danaanderson (5/25/2014)
Jeff Moden (5/25/2014)
danaanderson (5/25/2014)
I don't think that it matters about the position in the parameter of the dbtype spec, but I'll check it out. It is ASP.NET. Classic ASP is quite a different animal. In any case, from what I know this error is indicating that more parameters were sent than the procedure has receptors for...but it is ambiguous. I really wish T-SQL had a function like PARAMETERS() in VFP. I could know for sure.Anyone else have any thoughts? It's surprising that there was only one person replying in a time span of about 23 hours.
Dana
Heh... it IS an SQL Server forum with a large number of people that live in the United states and you've asked an ASP question on a holiday weekend. 😉
Well, it was SQL Server that was throwing the error. I thought it was the best place to start looking...and asking. True, though, it is a holiday weekend and most are probably out firing up the barbie.
Dana
Ah... understood. I noticed that you marked the post of JLS as the answer so I went and looked at the post he provided a link for. It would seem that some form of "read only field" was the problem and you needed to change the parmeter listing of at least 1 of the fields from "bind" to "eval". If that's true, which of your fields was it and was it an "IDENTITY" or "COMPUTED" column or ???
I'm asking because the code is automatically generated based on the stored procedure parameters and seems like it could be a common problem. Since SQL Server produced the error, I thought I'd ask for more details on what you did for a fix so that folks like me that know little of ASP can lend a helping hand in the future.
Thanks.
There were several columns that I didn't want the user to edit, a couple of date columns, and couple of Boolean (bit) columns. Things that indicate whether the product is approved, or is active. No IDENTITY or COMPUTED columns were involved. I changed the template values to eval() and that did the trick. I actually changed the table structure since I made that web page, but the new columns were not the ones causing the issue. Like the poster of that solution, I find it odd that you have a parameter list, but ASP.NET still decides on what are truly parameters by looking at the bind() or eval() methods used in the template. Someone dropped the ball there, eh?
The site will be selling digital content for 3D art software such as DAZ|Studio or Poser. I'm a one-man operation, so all this stuff falls on me. I've gone from developing with VFP to having to know VB.NET, sometimes a little C#.NET, SQL Server (which was a little more natural to migrate to for me), a little AJAX, etc. It's amazing how many things we need to know in the field of web development! And they keep changing...I can't keep up, and sometimes I don't want to. I don't hop on every bandwagon just because they say it's the latest and greatest. 😉
Thanks again for all the help and just being there for those who need help!
Dana
May 28, 2014 at 6:28 pm
Thanks, Dana. I appreciate both the info and the thought.
And, yeah... it's amazing what you need to know to get a front-end up and running correctly.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply