We help IT Professionals succeed at work.
Get Started

ASP.NET C# Stored Procedure has too many arguments

poultarp
poultarp asked
on
373 Views
Last Modified: 2015-04-20
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
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE