Asp inserting error

Question

I'm new in Asp.net and I try to read from a file and store to database the file form is:(the reading is test and it's good)

       0101;23/05/2014
       0602;23/05/2014
       0301;23/05/2014
       1103;23/05/2014
       0201;23/05/2014
       1704;23/05/2014
       ***************


        string[] lines = System.IO.File.ReadAllLines(@"C:\Users\user\Desktop\file.txt");
        SqlConnection connexion = new SqlConnection(@"Data Source=               (LocalDB)\v11.0;AttachDbFilename=C:\Users\user\Desktop\controle\miniprojet\miniprojet\App_Data\ariche.mdf;Integrated Security=True"); 
        SqlCommand command = connexion.CreateCommand();
        String requete = "INSERT INTO commande VALUES(@idpr,@date)";
        SqlParameter paramidprod;
        SqlParameter paramdate;
        connexion.Open(); 
        res.Text="Contents of file.txt = ";
        foreach (string line in lines)
        {
           if (line.Contains('*')) break;
           String[] l = line.Split(';');
           paramidprod=new SqlParameter("@idpr", line.Substring(0,2));
           paramdate = new SqlParameter("@date", l[1]);
           command.Parameters.Add(paramidprod);
           command.Parameters.Add(paramdate);
           command.CommandText = requete;
           command.ExecuteNonQuery();
           res.Text += "<br>" + l[1] +"   "+ line.Substring(0, 2);
        }
        connexion.Close();
        connexion.Dispose();

and when I run the programme I got thsi problem:

The variable name '@idpr' has already been declared. Variable names must be unique 
within a query batch or stored procedure.

Could you identify the error I'm lost plz

StuartLC · Accepted Answer · 2014-05-23 12:38:22Z

You are looping, adding new parameters each time the loop runs. The second and subsequent calls will thus fail.

Either set new command parameters each loop, or create the parameters just once and then re-assign them each loop.

Here's the second option - reusing the same parameters - it will be more performant in a loop scenario as you seem to have:

    var connexion = new SqlConnection(@"Data Source=..."); 
    var command = connexion.CreateCommand();
    var requete = "INSERT INTO commande VALUES(@idpr,@date)";
    // I've assumed the types - double check
    var paramidprod = new SqlParameter("@idpr", SqlDbType.VarChar);
    var paramdate = new SqlParameter("@date", SqlDbType.DateTime);
    command.Parameters.Add(paramidprod);
    command.Parameters.Add(paramdate);
    command.CommandText = requete;
    connexion.Open(); 

    foreach (string line in lines)
    {
       if (line.Contains('*')) break;
       String[] l = line.Split(';');
       paramidprod.Value = line.Substring(0,2);
       paramdate.Value = l[1];
       command.ExecuteNonQuery();
    }
    connexion.Close();
    connexion.Dispose();

If you are inserting really large numbers of records, you can also consider committing transactions every ~1000 inserts.

Andrei · Accepted Answer · 2014-05-23 12:33:42Z

2

Note that on each iteration you are inserting the same parameters into the SqlCommand object. You need to clear the previous ones before that:

foreach (string line in lines)
{
    command.Parameters.Clear();

answered May 23, 2014 at 12:33

Andrei

56.8k10 gold badges92 silver badges114 bronze badges

Collectives™ on Stack Overflow

Asp inserting error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related