Procedure or function insert_data has too many arguments specified

Question

0.00/5 (No votes)

See more:

Hi, i have a little problem with a SP in C#, i trying to insert data from a array but fails and gime the next error "Procedure or function insert_data has too many arguments specified."

using System.IO;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Configuration;
using System.Data;

namespace ReadHugeTxt
{
    class Program
    {
        static void Main(string[] args)
        {
            StreamReader sr = new StreamReader(@"C:\file2.txt");
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CAS"].ConnectionString);
            SqlCommand cmd = new SqlCommand("insert_data", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            string line, lastline = " ";

            while ((line = sr.ReadLine()) != null)
            {
                string[] L = Regex.Split(line, ",");
                conn.Open();
                if (line != lastline)
                {
                    cmd.Parameters.AddWithValue("@sm", line[0]);
                    cmd.Parameters.AddWithValue("@st", line[1]);
                    cmd.Parameters.AddWithValue("@est", line[3]);

                    cmd.ExecuteNonQuery();
                    lastline = line;
                }
                conn.Close();
            }
        }
    }
}

Stored Procedure

SQL

create procedure insert_data 
@sm nvarchar(50),
@st nvarchar(50),
@est nvarchar(50)

as 

insert into report (sma,st,est)
values(@sm,@st,(upper(@est)))

Thanks

Posted 28-Jan-15 6:12am

Laxmax1

Add a Solution

Comments

CHill60 28-Jan-15 12:16pm

You're adding extra parameters with each loop through that "where" - add the parameters to the command outside the loop and just change the values within the loop

Laxmax1 28-Jan-15 12:37pm

ok! :) I understand my mistake, thanks bro for your help!

CHill60 28-Jan-15 12:46pm

My pleasure!

Simon_Whale 28-Jan-15 12:16pm

does the error happen for the first execution of the stored procedure or does it happen on other executions of the stored procedure?

TechJosh 28-Jan-15 12:40pm

You also have 3 IDisposable objects which you should consider wrapping in a using() statement.

1 solution

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)

CHill60 · Accepted Answer · 2015-01-28T06:21:00

Solution 1

Try this (warning not tested)

C#

cmd.Parameters.Add("@sm", SqlDbType.NVarChar);
cmd.Parameters.Add("@st", SqlDbType.NVarChar);
cmd.Parameters.Add("@est", SqlDbType.NVarChar);

while ((line = sr.ReadLine()) != null)
{
    string[] L = Regex.Split(line, ",");
    conn.Open();
    if (line != lastline)
    {
        cmd.Parameters["@sm"].Value = line[0];
        cmd.Parameters["@st"].Value = line[1];
        cmd.Parameters["@est"].Value = line[3];

        cmd.ExecuteNonQuery();
        lastline = line;
    }
    conn.Close();
}

Posted 28-Jan-15 6:21am

CHill60

Comments

CHill60 28-Jan-15 12:29pm

No - it was the fact that you kept on adding new parameters with each loop.
First time through you had the 3 parameters you were expecting, then 2nd time through you had those 3 PLUS another 3. By moving the Add to outside the loop they only get added once and only the value changes as you go through the loop

Laxmax1 28-Jan-15 12:38pm

ok! :) I understand my mistake, thanks for the help!