Procedure or function has too many arguments specified SQL C#

Question

0.00/5 (No votes)

See more:

Hi,

I have a Windows Forms application with a datagridview. The data in the grid has to be inserted into a SQL datatable by means of a stored proc.

I only get the error message, if there are more than one row in the datagridview.

If I enter 1 row of data and press the button SAVE, everything works fine. But as soon as I enter a second row of data and then press the SAVE button, I get the a. m. error message. (I had to create the save button because the client does not want immediate synchronization, and I have to admit several rows, because the sum of one of the columns has to match a certain value)

This is my stored procedure:

SQL

ALTER PROCEDURE [elf].[uspProduktionsDetails_Insert](
		 @FS_Produktion int,
		 @ArtZeitbedarf nchar(50),
		 @Dauer int,
		 @Meter int = 0,
		 @Ausschuss int = 0,
		 @Seite varchar(10)= '',
		 @Bemerkung nvarchar(max) = '')
AS   
    SET NOCOUNT ON; 
INSERT 
	INTO tblProduktionsDetails
        (FS_Produktion
		, Art_des_Zeitbedarfs
		, Dauer_in_Minuten
		, Produzierte_Meter
		, Produzierter_Ausschuss
		, Welche_Seite
		, Bemerkung)
	VALUES (@FS_Produktion
		,@ArtZeitbedarf
		,@Dauer
		,@Meter
		,@Ausschuss
		,@Seite
		,@Bemerkung)
GO

And this is my c# code:

C#

public void Details()
         {
         string spString = "uspProduktionsDetails_Insert"; //Name der Stored Procedure
         SqlConnection con = new SqlConnection(Settings.Default.myConnectionString);
         SqlCommand cmd = new SqlCommand(spString, con);
         cmd.CommandType = CommandType.StoredProcedure;         // Configure command and add parameters
         try
            {
            if (dgvPD.Rows.Count > 0)
               {
               for (int i = 0; i < dgvPD.Rows.Count - 1; i++)
                  {
                  _ = cmd.Parameters.AddWithValue("@FS_Produktion", Convert.ToInt32(textBoxProduktions_ID.Text));
                  _ = cmd.Parameters.AddWithValue("@ArtZeitbedarf", Convert.ToString(dgvPD.Rows[i].Cells[0].Value));
                  _ = cmd.Parameters.AddWithValue("@Dauer", Convert.ToInt32(dgvPD.Rows[i].Cells[1].Value));
                  _ = cmd.Parameters.AddWithValue("@Meter", Convert.ToInt32(dgvPD.Rows[i].Cells[2].Value));
                  _ = cmd.Parameters.AddWithValue("@Ausschuss", Convert.ToInt32(dgvPD.Rows[i].Cells[3].Value));
                  _ = cmd.Parameters.AddWithValue("@Seite", Convert.ToString(dgvPD.Rows[i].Cells[4].Value));
                  _ = cmd.Parameters.AddWithValue("@Bemerkung", Convert.ToString(dgvPD.Rows[i].Cells[5].Value));
                  con.Open();
                  _ = cmd.ExecuteNonQuery();
                  con.Close();
                  }
               }
            }
         catch (Exception e)
            {
            MessageBox.Show(e.Message);
            }
         }

What I have tried:

I provided the last 4 parameters with optional values, so that even if these values were not provided there should not be an error.

I tripplechecked my parameters and the c# parameters but could not find any difference.

The error is thrown after executing the first row, the data from the first row in the grid gets into the SQL data table, while the others don't.

I would be really happy if you saw what I can't.

rgds,
Susanne

Posted 6-May-20 22:51pm

Member 14817939

Updated 6-May-20 23:27pm

RickZeeland

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2020-05-06T23:25:00

You are adding more parameters to the collection each time round the loop. So on the second iteration the Parameters collection will contain 14 items, on the third 21 etc. You need to call Clear on the collection after each call to ExecuteNonQuery to remove the existing items. Alternatively, you can just change the Value fields of the existing individual Parameter items.

You should also be checking the return value from ExecuteNonQuery to check whether the command succeeded or not.

phil.o · Answer 2 · 2020-05-06T23:28:00

You get the error because you add the parameters in the loop.
You should use using blocks with disposable objects (connection, command, ...)
You should not be opening and closing the connection in the loop; better open it at the beginning and leave it open for the lifetime of the method. A using block will ensure it is finally closed.
You should avoid using AddWithValue method; better use Add method which allows specifying the type of the column.
You should avoid using the Convert class, which is a dinosaur herited from early ages of .NET; better use Parse and TryParse methods for common integral types, and ToString method for objects which are not already strings themselves.
You don't need to test for the number of rows before the loop; the loop condition is sufficient.

C#

public void Details()
{
   string spString = "uspProduktionsDetails_Insert"; //Name der Stored Procedure
   using (SqlConnection con = new SqlConnection(Settings.Default.myConnectionString))
   using (SqlCommand cmd = new SqlCommand(spString, con))
   {
      con.Open();
      cmd.CommandType = CommandType.StoredProcedure; // Configure command and add parameters
      var par1 = cmd.Parameters.Add("@FS_Produktion", SqlDbType.Int);
      par1.Value = Int32.Parse(textBoxProduktions_ID.Text);
      var par2 = cmd.Parameters.Add("@ArtZeitbedarf", SqlDbType.NVarChar);
      var par3 = cmd.Parameters.Add("@Dauer", SqlDbType.Int);
      var par4 = cmd.Parameters.Add("@Meter", SqlDbType.Int);
      var par5 = cmd.Parameters.Add("@Ausschuss", SqlDbType.Int);
      var par6 = cmd.Parameters.Add("@Seite", SqlDbType.NVarChar);
      var par7 = cmd.Parameters.Add("@Bemerkung", SqlDbType.NVarChar);
      try
      {
         for (int i = 0; i < dgvPD.Rows.Count; ++i)
         {
            var row = dgvPD.Rows[i];
            par2.Value = row.Cells[0].Value.ToString();
            par3.Value = Int32.Parse(row.Cells[1].Value);
            par4.Value = Int32.Parse(row.Cells[2].Value);
            par5.Value = Int32.Parse(row.Cells[3].Value);
            par6.Value = row.Cells[4].Value.ToString();
            par7.Value = row.Cells[5].Value.ToString();
            _ = cmd.ExecuteNonQuery();
         }
      }
      catch (Exception e)
      {
         MessageBox.Show(e.Message);
      }
   }
}

RickZeeland · Answer 3 · 2020-05-06T23:05:00

Solution 1

The con.Close() should be outside your For loop.

Posted 6-May-20 23:05pm

RickZeeland

Procedure or function has too many arguments specified SQL C#

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0