CODEWITHSUNDEEP
c#sqlsql-server
Marek
Marek

Reputation: 3575

SqlCommand INSERT INTO from datagridview to SQL DB table

I have this code below which should save data from datagridview dtg_ksluzby to sql table KLISLUZ, but it says that:

Embedded statement cannot be declarition or labeled statemnt.

for(int i=0; i< dtg_ksluzby.Rows.Count;i++)
    SqlCommand prikaz2 = new SqlCommand("INSERT INTO klisluz'" + dtg_ksluzby.Rows[i].Cells["text"].Value +"', '" + dtg_ksluzby.Rows[i].Cells["pocet"].Value +"'",spojeni);
  prikaz2.ExecuteNonQuery();

Upvotes: 1

Views: 2976

Answers (2)

gzaxx
gzaxx

Reputation: 17590

Your query is wrong (also use Parametrized Queries)

Fixed query:

"INSERT INTO klisluz values('" + dtg_ksluzby.Rows[i].Cells["text"].Value +"', '" + dtg_ksluzby.Rows[i].Cells["pocet"].Value +"')"

Fixed code:

using (SqlCommand prikaz2 = new SqlCommand("INSERT INTO klisluz values('@val1', '@val2')",spojeni))
{
  for (int i = 0; i < dtg_ksluzby.Rows.Count; i++)
  {
    prikaz2.Parameters.Clear();
    prikaz2.Parameters.AddWithValue("@val1", dtg_ksluzby.Rows[i].Cells["text"].Value);
    prikaz2.Parameters.AddWithValue("@val2", dtg_ksluzby.Rows[i].Cells["pocet"].Value);
    prikaz2.ExecuteNonQuery();
  }
}

Upvotes: 2

Soner G&#246;n&#252;l
Soner G&#246;n&#252;l

Reputation: 98750

First of all, you should always use parameterized queries, this kind of string concatenations are open for SQL Injection attacks.

Try like this;

for(int i=0; i< dtg_ksluzby.Rows.Count;i++)
{
    using(SqlCommand prikaz2 = new SqlCommand("INSERT INTO klisluz VALUES(@p1, @p2)",spojeni))
    {
      prikaz2.Parameters.AddWithValue("@p1", dtg_ksluzby.Rows[i].Cells["text"].Value);
      prikaz2.Parameters.AddWithValue("@p2", dtg_ksluzby.Rows[i].Cells["pocet"].Value);
      prikaz2.ExecuteNonQuery();
    }
}

As an alternative which Tim pointed, you can reuse the same SqlCommand for your all values which you just need to use SqlParameterCollection.Clear() method after you execute your command.

Like;

using(SqlCommand prikaz2 = new SqlCommand("INSERT INTO klisluz VALUES(@p1, @p2)",spojeni))
{
    for(int i=0; i< dtg_ksluzby.Rows.Count;i++)
    {
          prikaz2.Parameters.AddWithValue("@p1", dtg_ksluzby.Rows[i].Cells["text"].Value);
          prikaz2.Parameters.AddWithValue("@p2", dtg_ksluzby.Rows[i].Cells["pocet"].Value);
          prikaz2.ExecuteNonQuery();
          prikaz2.Parameters.Clear();
    }
}

Upvotes: 4

Related Questions