We help IT Professionals succeed at work.

ASP.NET C# Stored Procedure has too many arguments

poultarp
poultarp asked
on
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.
Comment
Watch Question

View Solution Only

Naitik GamitSoftware Developer
Top Expert 2015

Commented:
Please provide your designing code.
Most Valuable Expert 2011
Top Expert 2015
Commented:
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

kaufmed
Most Valuable Expert 2011
Top Expert 2015

Commented:
P.S.

Shortly, I'll post an explanation of how I approached your problem.
kaufmed
Most Valuable Expert 2011
Top Expert 2015

Commented:
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:

Screenshot
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:

Screenshot
Then from the Edit menu, I selected "Delete"->"Bookmarked Lines". This leaves me with only your parameter additions:

Screenshot
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:

Screenshot
...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:

Screenshot
Then I remove all the extraneous bits by hand, leaving only parameter names:

Screenshot
...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:

 Screenshot
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:

Screenshot
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.
poultarp

Author

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