ASP.NET C# Stored Procedure has too many arguments
Hello
I have an asp.net website in C# created in Visual Studio 2013, where I call a stored procedure in an SQL database. However, when I call the stored procedure, I get the error: "Procedure or function TurTotal_v6 has too many arguments specified". I have checked the .net code and the sql code about a million times now, and I am certain that I only specify the parameters that are needed to call the stored procedure.
This is my asp.net C# code which calls the stored procedure:
And this is the parameter section of the stored procedure I am calling:
So far I have tried:
Does anyone have any suggestions to what the problem can be? I really can't figure this one out (and if it turns out I really am adding too many parameters I am going to cry because I have checked like a million times!),
If more code is needed or you have any questions please feel free to ask.
Thanks in advance.
I have an asp.net website in C# created in Visual Studio 2013, where I call a stored procedure in an SQL database. However, when I call the stored procedure, I get the error: "Procedure or function TurTotal_v6 has too many arguments specified". I have checked the .net code and the sql code about a million times now, and I am certain that I only specify the parameters that are needed to call the stored procedure.
This is my asp.net C# code which calls the stored procedure:
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["webmtc"].ConnectionString))
{
con.Open();
SqlCommand sqlCom;
SqlDataReader sqlReader;
sqlCom = new SqlCommand("TurTotal_v6", con);
sqlCom.CommandType = CommandType.StoredProcedure;
sqlCom.Parameters.Add("@TurId", SqlDbType.Int).Value = DBNull.Value;
sqlCom.Parameters.Add("@AflvMejeriNr", SqlDbType.VarChar, 10).Value = ddlNewTripDestinationNo.SelectedValue;
if (tbNewTripStartSiteNo.Text.Trim() != "")
sqlCom.Parameters.Add("@StartSiteNo", SqlDbType.VarChar, 10).Value = tbNewTripStartSiteNo.Text;
else
sqlCom.Parameters.Add("@StartSiteNo", SqlDbType.VarChar, 10).Value = DBNull.Value;
sqlCom.Parameters.Add("@AflvAnhNr", SqlDbType.VarChar, 10).Value = DBNull.Value;
sqlCom.Parameters.Add("@AflvSted", SqlDbType.Int).Value = ddlNewTripDestinationType.SelectedValue;
sqlCom.Parameters.Add("@ChaufforNr", SqlDbType.VarChar, 10).Value = ddlNewTripDriverNo.SelectedValue;
sqlCom.Parameters.Add("@BilNr", SqlDbType.VarChar, 10).Value = ddlNewTripTruckNo.SelectedValue;
sqlCom.Parameters.Add("@LinieTurNr", SqlDbType.VarChar, 10).Value = tbNewTripTripNo.Text.Trim();
sqlCom.Parameters.Add("@RouteNo", SqlDbType.VarChar, 10).Value = tbNewTripRouteNo.Text.Trim();
sqlCom.Parameters.Add("@Produkt1", SqlDbType.Decimal).Value = tbNewTripVolumeVolume.Text.Trim();
sqlCom.Parameters.Add("@Produkt2", SqlDbType.Decimal).Value = DBNull.Value;
sqlCom.Parameters.Add("@Produkt3", SqlDbType.Decimal).Value = DBNull.Value;
sqlCom.Parameters.Add("@Produkt4", SqlDbType.Decimal).Value = DBNull.Value;
sqlCom.Parameters.Add("@Produkt5", SqlDbType.Decimal).Value = DBNull.Value;
sqlCom.Parameters.Add("@StartTid", SqlDbType.DateTime).Value = tbNewTripStartTimeDate.Text.Trim() + ' ' + tbStartTimeTime.Text.Trim() + ":00";
sqlCom.Parameters.Add("@FoersteHentning", SqlDbType.DateTime).Value = DBNull.Value;
sqlCom.Parameters.Add("@SlutTid", SqlDbType.DateTime).Value = tbNewTripFinishTimeDate.Text.Trim() + " " + tbNewTripFinishTimeTime.Text.Trim() + ":00";
sqlCom.Parameters.Add("@Temperatur", SqlDbType.Decimal).Value = tbNewTripVolumeTemp.Text;
sqlCom.Parameters.Add("@AntalBesoeg", SqlDbType.Int).Value = DBNull.Value;
if (tbNewTripKilometreKM1.Text.Trim() != "")
sqlCom.Parameters.Add("@Km1", SqlDbType.Decimal).Value = tbNewTripKilometreKM1.Text.Trim();
else
sqlCom.Parameters.Add("@Km1", SqlDbType.Decimal).Value = 0;
if (tbNewTripKilometreKM2.Text.Trim() != "")
sqlCom.Parameters.Add("@Km2", SqlDbType.Decimal).Value = tbNewTripKilometreKM2.Text.Trim();
else
sqlCom.Parameters.Add("@Km2", SqlDbType.Decimal).Value = 0;
if (tbNewTripKilometreKM3.Text.Trim() != "")
sqlCom.Parameters.Add("@Km3", SqlDbType.Decimal).Value = tbNewTripKilometreKM3.Text.Trim();
else
sqlCom.Parameters.Add("@Km3", SqlDbType.Decimal).Value = 0;
if (tbNewTripKilometreKM4.Text.Trim() != "")
sqlCom.Parameters.Add("@Km4", SqlDbType.Decimal).Value = tbNewTripKilometreKM4.Text.Trim();
else
sqlCom.Parameters.Add("@Km4", SqlDbType.Decimal).Value = 0;
if (tbNewTripKilometreKM5.Text.Trim() != "")
sqlCom.Parameters.Add("@Km5", SqlDbType.Decimal).Value = tbNewTripKilometreKM5.Text.Trim();
else
sqlCom.Parameters.Add("@Km5", SqlDbType.Decimal).Value = 0;
if (tbNewTripKilometreKM6.Text.Trim() != "")
sqlCom.Parameters.Add("@Km6", SqlDbType.Decimal).Value = tbNewTripKilometreKM6.Text.Trim();
else
sqlCom.Parameters.Add("@Km6", SqlDbType.Decimal).Value = 0;
sqlCom.Parameters.Add("@S12FilNavn", SqlDbType.VarChar, 50).Value = "WEB_MTC_" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
sqlCom.Parameters.Add("@TurLaesNr", SqlDbType.Int).Value = tbNewTripLoadNo.Text.Trim();
sqlCom.Parameters.Add("@Tid", SqlDbType.VarChar, 5).Value = tbNewTripStartTime.Text;
sqlCom.Parameters.Add("@FejlLevering", SqlDbType.Int).Value = DBNull.Value;
sqlCom.Parameters.Add("@VejeTidPolen", SqlDbType.DateTime).Value = DBNull.Value;
sqlCom.Parameters.Add("@LoadId", SqlDbType.Char, 13).Value = DBNull.Value;
sqlCom.Parameters.Add("@SettelmentDate", SqlDbType.DateTime).Value = tbNewTripSettelmentDate.Text.Trim();
sqlCom.Parameters.Add("@RouteStartTime", SqlDbType.DateTime).Value = tbNewTripRouteStartDate.Text.Trim() + ' ' + tbNewTripStartTime.Text.Trim() + ":00";
sqlCom.Parameters.Add("@Debug", SqlDbType.Bit).Value = DBNull.Value;
sqlCom.Parameters.Add("@ProductId", SqlDbType.Int).Value = ddlNewTripVolumeProductNo.SelectedValue;
sqlCom.Parameters.Add("@SourceNo", SqlDbType.VarChar, 10).Value = ddlNewTripSourceNo.SelectedValue;
sqlCom.Parameters.Add("@SourceType", SqlDbType.Int).Value = ddlNewTripSourceType.SelectedValue;
sqlCom.Parameters.Add("@UnloadSession", SqlDbType.DateTime).Value = tbNewTripFinishTimeTime.Text;
sqlCom.Parameters.Add("@VehicleLoadTruckId", SqlDbType.Int).Value = DBNull.Value; //kig på disse
sqlCom.Parameters.Add("@VehicleLoadTrailerId", SqlDbType.Int).Value = DBNull.Value; //Kig på disse
sqlCom.Parameters.Add("@WeighFirstNo", SqlDbType.VarChar, 10).Value = DBNull.Value;
sqlCom.Parameters.Add("@WeighFirstTime", SqlDbType.DateTime).Value = DBNull.Value;
sqlCom.Parameters.Add("@WeighFirstWeight", SqlDbType.Int).Value = DBNull.Value;
sqlCom.Parameters.Add("@WeighSecondNo", SqlDbType.VarChar, 10).Value = DBNull.Value;
sqlCom.Parameters.Add("@WeighSecondTime", SqlDbType.DateTime).Value = DBNull.Value;
sqlCom.Parameters.Add("@WeighSecondWeight", SqlDbType.Int).Value = DBNull.Value;
sqlCom.Parameters.Add("@WeightDiff", SqlDbType.Int).Value = DBNull.Value;
sqlCom.Parameters.Add("@WeighSession", SqlDbType.DateTime).Value = DBNull.Value;
sqlCom.Parameters.Add("@WeighTruckNo", SqlDbType.VarChar, 10).Value = DBNull.Value;
sqlReader = sqlCom.ExecuteReader();
if (sqlReader.HasRows)
{
sqlReader.Read();
Session["NewTripTripId"] = sqlReader["@TurId"].ToString(); //Henter det nye TurId ud
}
sqlReader.Close();
sqlReader.Dispose();
sqlCom.Dispose();
con.Close();
con.Dispose();
}
And this is the parameter section of the stored procedure I am calling:
ALTER Procedure [dbo].[TurTotal_v6] (@TurId int,
@AflvMejeriNr varchar(10),
@StartSiteNo varchar(10),
@AflvAnhNr varchar(10),
@AflvSted int,
@ChaufforNr varchar(10),
@BilNr varchar(10),
@LinieTurNr varchar(10),
@RouteNo varchar(10),
@Produkt1 decimal(10,2),
@Produkt2 decimal(10,2),
@Produkt3 decimal(10,2),
@Produkt4 decimal(10,2),
@Produkt5 decimal(10,2),
@StartTid datetime,
@FoersteHentning datetime,
@SlutTid datetime,
@Temperatur decimal(4,2),
@AntalBesoeg int,
@Km1 decimal(6,2),
@Km2 decimal(6,2),
@Km4 decimal(6,2),
@Km5 decimal(6,2),
@Km6 decimal(6,2),
@S12FilNavn varchar(50),
@TurLaesNr int ,
@Tid varchar(5),
@FejlLevering int,
@VejeTidPolen datetime,
@LoadId char(13),
@SettelmentDate datetime,
@RouteStartTime datetime,
@Debug bit,
@ProductId int,
@SourceNo varchar(10),
@SourceType int,
@UnloadSession datetime,
@VehicleLoadTruckId int,
@VehicleLoadTrailerId int,
@WeighFirstNo varchar(10),
@WeighFirstTime datetime,
@WeighFirstWeight int,
@WeighSecondNo varchar(10),
@WeighSecondTime datetime,
@WeighSecondWeight int,
@WeightDiff int,
@WeighSession datetime,
@WeighTruckNo varchar(10)
) AS
So far I have tried:
Checking the number and names of the parameters I add to the SqlCommand
Checking the type of parameter I add to the SqlCommand
Re-checking the name and number of parameters added to the SqlCommand
Checked that it is the correct database I am connecting to
Checked that the connection to the database is open
tripled-checked the names and number of parameters added to the SqlCommand
Rebuild solution and web site
Hit my head repeatedly against the wall
Searched the internet for a solution and ended up empty handed
Does anyone have any suggestions to what the problem can be? I really can't figure this one out (and if it turns out I really am adding too many parameters I am going to cry because I have checked like a million times!),
If more code is needed or you have any questions please feel free to ask.
Thanks in advance.
Asked:
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Here are the parameters from your code and from your proc side by side--scroll down until you see the problem ; )
Code Proc
======================================================
@TurId @TurId int,
@AflvMejeriNr @AflvMejeriNr varchar(10),
@StartSiteNo @StartSiteNo varchar(10),
@AflvAnhNr @AflvAnhNr varchar(10),
@AflvSted @AflvSted int,
@ChaufforNr @ChaufforNr varchar(10),
@BilNr @BilNr varchar(10),
@LinieTurNr @LinieTurNr varchar(10),
@RouteNo @RouteNo varchar(10),
@Produkt1 @Produkt1 decimal(10,2),
@Produkt2 @Produkt2 decimal(10,2),
@Produkt3 @Produkt3 decimal(10,2),
@Produkt4 @Produkt4 decimal(10,2),
@Produkt5 @Produkt5 decimal(10,2),
@StartTid @StartTid datetime,
@FoersteHentning @FoersteHentning datetime,
@SlutTid @SlutTid datetime,
@Temperatur @Temperatur decimal(4,2),
@AntalBesoeg @AntalBesoeg int,
@Km1 @Km1 decimal(6,2),
@Km2 @Km2 decimal(6,2),
@Km3
@Km4 @Km4 decimal(6,2),
@Km5 @Km5 decimal(6,2),
@Km6 @Km6 decimal(6,2),
@S12FilNavn @S12FilNavn varchar(50),
@TurLaesNr @TurLaesNr int ,
@Tid @Tid varchar(5),
@FejlLevering @FejlLevering int,
@VejeTidPolen @VejeTidPolen datetime,
@LoadId @LoadId char(13),
@SettelmentDate @SettelmentDate datetime,
@RouteStartTime @RouteStartTime datetime,
@Debug @Debug bit,
@ProductId @ProductId int,
@SourceNo @SourceNo varchar(10),
@SourceType @SourceType int,
@UnloadSession @UnloadSession datetime,
@VehicleLoadTruckId @VehicleLoadTruckId int,
@VehicleLoadTrailerId @VehicleLoadTrailerId int,
@WeighFirstNo @WeighFirstNo varchar(10),
@WeighFirstTime @WeighFirstTime datetime,
@WeighFirstWeight @WeighFirstWeight int,,
@WeighSecondNo @WeighSecondNo varchar(10)
@WeighSecondTime @WeighSecondTime datetime,
@WeighSecondWeight @WeighSecondWeight int,
@WeightDiff @WeightDiff int,
@WeighSession @WeighSession datetime,
@WeighTruckNo @WeighTruckNo varchar(10)
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
So I use a Text Editor named "TextPad". A nice feature of this text editor is that I can paste text as entire blocks into any position within a document. There's also a feature that when you find/replace, you can "bookmark" lines that match your find criteria. Further, you can invert your bookmarked lines to bookmark the lines that didn't match your find criteria. Finally, you can delete your bookmarked lines. Really, you could use any text editor that you fancy, but these features are very handy.
I first copied your C# code into TextPad. I then did a find for "Parameters.Add". I bookmarked all matched lines:
Those blue arrows on the left side of the editor are the bookmarks. From the search menu, I then selected the "Invert All Bookmarks" option. Now the un-bookmarked lines become bookmaked, clearing the original bookmarks:
Then from the Edit menu, I selected "Delete"->"Bookmarked Lines". This leaves me with only your parameter additions:
Now it's simply a matter of scanning through those lines and removing any duplicate additions. I do this manually. For my own sanity, I select all the text, and then shift-Tab until all of the text lines up with the left margin--but this isn't required. I then do a find/replace to isolate only the parameter name. I do this using a regular expression:
...which basically means: Find anything up to an @ symbol, then find some letters, then find anything up to the end of the line. The parentheses save the parameter name into a hidden variable that I then use in the replace (the $1 thing). You don't have to use regular expressions--the point here is to isolate the parameter name. Next, I open up a new tab, and then copy/paste your proc code:
Then I remove all the extraneous bits by hand, leaving only parameter names:
...along with another round of select all, shift-tab to line everything up on the left margin. Then I select all of that text again and copy it. Now I flip back to the original tab, and I go to the end of the first line. Add enough spaces such that the cursor shall be well past the longest string in the whole list:
Then simply paste the text you copied from the other tab as a block. You do this by going to "Edit"->"Insert"->"Past as Block". Now you'll have everything side by side:
Then it's simply a matter of scanning down the list for the first two parameters (side-by-side) that don't match.
Now this process described above is really more TextPad-centric. Again, you don't have to use TextPad. The gist of this post is that you want to line up the parameters you defined in C# with the parameters you defined in your proc, and see which one is missing.
I first copied your C# code into TextPad. I then did a find for "Parameters.Add". I bookmarked all matched lines:
Those blue arrows on the left side of the editor are the bookmarks. From the search menu, I then selected the "Invert All Bookmarks" option. Now the un-bookmarked lines become bookmaked, clearing the original bookmarks:
Then from the Edit menu, I selected "Delete"->"Bookmarked Lines". This leaves me with only your parameter additions:
Now it's simply a matter of scanning through those lines and removing any duplicate additions. I do this manually. For my own sanity, I select all the text, and then shift-Tab until all of the text lines up with the left margin--but this isn't required. I then do a find/replace to isolate only the parameter name. I do this using a regular expression:
...which basically means: Find anything up to an @ symbol, then find some letters, then find anything up to the end of the line. The parentheses save the parameter name into a hidden variable that I then use in the replace (the $1 thing). You don't have to use regular expressions--the point here is to isolate the parameter name. Next, I open up a new tab, and then copy/paste your proc code:
Then I remove all the extraneous bits by hand, leaving only parameter names:
...along with another round of select all, shift-tab to line everything up on the left margin. Then I select all of that text again and copy it. Now I flip back to the original tab, and I go to the end of the first line. Add enough spaces such that the cursor shall be well past the longest string in the whole list:
Then simply paste the text you copied from the other tab as a block. You do this by going to "Edit"->"Insert"->"Past as Block". Now you'll have everything side by side:
Then it's simply a matter of scanning down the list for the first two parameters (side-by-side) that don't match.
Now this process described above is really more TextPad-centric. Again, you don't have to use TextPad. The gist of this post is that you want to line up the parameters you defined in C# with the parameters you defined in your proc, and see which one is missing.
Oh god do I feel dumb now :P
I did something similar to what you have done and I still manage to miss that the stored procedure didn't have a "@Km3" parameter! Guess that's what happens when you stare at a problem for too long :P
Anyway thank you so much for your excellent solution to my stupid problem!! I'm gonna have a word with the guy how made the stored procedure ;-)
I did something similar to what you have done and I still manage to miss that the stored procedure didn't have a "@Km3" parameter! Guess that's what happens when you stare at a problem for too long :P
Anyway thank you so much for your excellent solution to my stupid problem!! I'm gonna have a word with the guy how made the stored procedure ;-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.