Asp Insert Error

Question

I am new to Asp.net and I am trying to read from a file and store the file form in the database: (reading is a test, and this is 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 program, 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 lost? plz

Answer 1

You run the loop by adding new parameters each time the loop starts. Thus, the second and subsequent calls will occur.

Either set new command parameters every cycle, or create parameters only once, and then reassign them to each cycle.

- - , :

    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();

, 1000 .

Answer 2

, SqlCommand. :

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