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:

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();
    }

Open in new window

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

Open in new window

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.

LVL 1

poultarpAsked: 2015-04-20

Who is Participating?

LVL 75

käµfm³d 👽Commented: 2015-04-20

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)

Open in new window

LVL 8

Naitik GamitSoftware DeveloperCommented: 2015-04-20

Please provide your designing code.

LVL 75

käµfm³d 👽Commented: 2015-04-20

P.S.

Shortly, I'll post an explanation of how I approached your problem.

LVL 75

käµfm³d 👽Commented: 2015-04-20

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.

LVL 1

poultarpAuthor Commented: 2015-04-20

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 ;-)