title: asp.net inserting data into SQL Server db in while loop
body: <p>I want to import data from Excel worksheet into SQL Server database (2008). I have successfully tested code for getting data from xls file. However, I am not able insert these data into db table. Here is the part of the code:</p>
  
  <pre><code>        while (ctecka.Read())
          {
              exJmeno = ctecka[0].ToString();
              exPrijmeni = ctecka[1].ToString();
              Response.Write(exJmeno + " " + exPrijmeni + " ");
              pridano = vlozSQL.ExecuteNonQuery();
          }
  </code></pre>
  
  <p><strong>ctecka</strong> is DbDataReader object and it contains data from excel worksheet</p>
  
  <p><strong>exJmeno</strong> and <strong>exPrijmeni</strong> are strings and data from columns are inserted there</p>
  
  <p>Problem:</p>
  
  <p><em>Response.write</em> writes onto page the data I need</p>
  
  <p><em>vlozSQL.ExecuteNonQuery</em> ignores data in (exJmeno and exPrijmeni) and inserts empty          columns into table in database</p>
  
  <p>here are some definitions:</p>
  
  <pre><code>string InsertSQL = "INSERT INTO users (name, surname) VALUES (@name,@surname)";
  vlozSQL.Parameters.AddWithValue("@name", exJmeno);
  vlozSQL.Parameters.AddWithValue("@surname", exPrijmeni);
  </code></pre>
  
  <p><strong>EDIT</strong>    </p>
  
  <pre><code>    string exJmeno = "";
      string exPrijmeni = "";
      string InsertSQL = "INSERT INTO users (name, surname) VALUES (@name,@surname)";
      SqlConnection sqlconn = new SqlConnection(SQLpripoj);
      SqlCommand vlozSQL = new SqlCommand(InsertSQL, sqlconn);
      vlozSQL.Parameters.AddWithValue("@name", exJmeno);
      vlozSQL.Parameters.AddWithValue("@surname", exPrijmeni);
  
      try
      {
          spojeni.Open();
          DbDataReader ctecka = prikaz.ExecuteReader();
  
          sqlconn.Open();
          while (ctecka.Read())
          {
              exJmeno = ctecka[0].ToString();
              exPrijmeni = ctecka[1].ToString();
              Response.Write(exJmeno + " " + exPrijmeni + " ");
              pridano = vlozSQL.ExecuteNonQuery();
          }
      }
      finally
      {
          spojeni.Close();
          sqlconn.Close();
          Label2.Text = pridano.ToString();
      }
  </code></pre>