Member Avatar for datapham

Hi,
i have tried to insert all rows at one time from datagridview c# into a table in database , although the table gets all the rows from datagridview but it always gets the values of the last row from datagridview for all the other rows , so all the rows in the table have the same values . Could anyone help ?

//class Presentation
for(int i=0; i<datagridview1.Rows.Count - 1; i++)
{
ClassPro.ProductA = datagridview1.Row.Cells[0].Value.ToString();
ClassPro.ProductB = datagridview1.Row.Cells[1].Value.ToString();
MyArray.Add(ClassPro);
}

//class Database Access Object
for(int i=0; i<Class.MyArray.Count; i++)
{
string InsertTable = "insert into table_name values('" + Class.MyArray.ProductA + "', '" + Class.MyArray.ProductB + "' )" ;
}

  • 2 Contributors
  • 3 Replies
  • 340 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by finito

Dani AI

Generated

Most likely cause: the same model instance (or static properties) is being reused for every row, so the list holds multiple references to one object and all entries end up with the last-set values. was right that the INSERT must run per row, but the root problem usually sits earlier—how the objects are created and collected.

A safe pattern (avoid reusing one object, handle nulls, skip the new-row) is to create a fresh model for each DataGridView row, then insert with a parameterized command. Example: 

var items = new List<ProductModel>();
foreach (DataGridViewRow r in dataGridView1.Rows)
{
    if (r.IsNewRow) continue;
    items.Add(new ProductModel {
        ProductA = Convert.ToString(r.Cells[0].Value),
        ProductB = Convert.ToString(r.Cells[1].Value)
    });
}

Insert using a single prepared command (better for performance and safe from SQL injection): 

using (var cn = new SqlConnection(connString))
{
    cn.Open();
    using (var cmd = new SqlCommand("INSERT INTO table_name (ColA,ColB) VALUES (@a,@b)", cn))
    {
        cmd.Parameters.Add("@a", SqlDbType.NVarChar, 200);
        cmd.Parameters.Add("@b", SqlDbType.NVarChar, 200);
        foreach (var it in items)
        {
            cmd.Parameters["@a"].Value = it.ProductA ?? "";
            cmd.Parameters["@b"].Value = it.ProductB ?? "";
            cmd.ExecuteNonQuery();
        }
    }
}

Extra checks/troubleshooting: verify that the model properties are not declared static; call dataGridView1.EndEdit() or bindingSource.EndEdit() before reading to commit any in‑cell edits; inspect the list in the debugger to confirm each element is a distinct object (Object.ReferenceEquals). For bulk imports, build a DataTable and use SqlBulkCopy inside a transaction for speed.

Recommended Answers

All 3 Replies

Member Avatar for finito

Where is you insert command?

I only see the string.

You need to move the ExecuteNonQuery() in to

for(int i=0; i<Class.MyArray.Count; i++)
{
string InsertTable = "insert into table_name values('" + Class.MyArray[i].ProductA + "', '" + Class.MyArray[i].ProductB + "' )" ;
}

Cause if you run Execute once on the string InsertTable it will only do the last thing where i = Class.MyArray.Count-1

You see what I am saying?

it runs through this code making InsertTable = to the i^th Product A and B.

What you could/should do is.

MyConnection.Open();
for(int i=0; i<Class.MyArray.Count; i++)
{
      new SqlCommand("insert into table_name values('" + Class.MyArray[i].ProductA + "', '" + Class.MyArray[i].ProductB + "' )", MyConnection).ExecuteNonQuery();
}
MyConnection.Close();

or

MyConnection.Open();
for(int i=0; i<Class.MyArray.Count; i++)
{
      string InsertTable = "insert into table_name values('" + Class.MyArray[i].ProductA + "', '" + Class.MyArray[i].ProductB + "' )" ;
      SqlCommand cmd = new SqlCommand();
      cmd.Connection = MyConnection;
      cmd.CommandText= InsertTable;
      cmd.ExecuteNonQuery();
}
MyConnection.Close();

Enjoy.

Edited by finito because: n/a
Member Avatar for datapham

Hi Finito ,
I coded exactly as you did , but it does not work ( of course i understand the code snippet ). However , thank you for your coding . I will try .

Member Avatar for finito

Are you getting an error?

Are the symptoms different?

What is the output?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.