datagridview insert blank from the 2nd row onwords

Question

i have a grid view that i fill it with C# and the user edit it and then i insert it on a diffrent table, when i insert it it takes the changes the user done in the first row and ignor all the chagnes in the other rows. the C3 colum is the column the user edit

this is the datagrid genration query

string output = "select distinct [C1],C2 as [Error_Name], ''  as [C3], [C4],[C5] from rejection";
           SqlCommand cmd2 = new SqlCommand();
           cmd2.CommandType = CommandType.Text;
           cmd2.CommandText = output;
           cmd2.Connection = conne;
           conne.Open();
           SqlDataAdapter dscmd = new SqlDataAdapter(output, strconnection);
           DataSet ds = new DataSet();
           dscmd.Fill(ds);
           dataGridView1.DataSource = ds.Tables[0];

this is the inserting query

        for (int i = 0; i < dataGridView1.Rows.Count; i++)
        {
            string StrQuery = @"INSERT INTO [test2] VALUES ('" + dataGridView1.Rows[i].Cells[0].Value + "', '" + dataGridView1.Rows[i].Cells[1].Value + "', '" + dataGridView1.Rows[i].Cells[2].Value + "', '" + dataGridView1.Rows[i].Cells[3].Value + "', '" + dataGridView1.Rows[i].Cells[4].Value + "');";


                SqlConnection conn = new SqlConnection(strconnection);
                conn.Open();

                using (SqlCommand comm = new SqlCommand(StrQuery, conn))
                {
                    comm.ExecuteNonQuery();
                }
                conn.Close();

Note all the rows are inserted on the database, but the first row only have the edit chagnes all other rows dont have the edit changes they are the same.

when i use this connection string :

string strconnection = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\ksa\Documents\Visual Studio 2012\Projects\test\test\test.mdf;Integrated Security=True";

it works ...... but when i use the app.sitting so its :

string strconnection = ConfigurationManager.ConnectionStrings["test"].ConnectionString;

and in the app sitting :

<connectionStrings>
         <add name="test"  connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\test.mdf;Integrated Security=True"   providerName="Microsoft.SqlServerCe.Client.4.0" />
    </connectionStrings>

it doesnt work !!

Obligatory comment about [SQL Injection](http://stackoverflow.com/q/332365/467172). — Anthony, Mar 24 '15 at 16:11
@KhurramAli what i want is to select 3 colums from a table, the user will add the 4th colum manualy row by row then he will clock update button and the will insert all 3colums including the one edited by the user to another table, what happens now is the update or the changes in the first row only come to the table and the reset are the same as it came from the first table ignoring all changes done to them — KAS, Mar 24 '15 at 18:59
what do u mean , the code on the question is the select from the database to the gridview and the insert from the grid view to the database , what other part of the code you need — KAS, Mar 24 '15 at 19:39
@KAS Let me understand You want to select 3 columns from your datagrid or from database? and you said you will add 4th column by manully ?? after that insert all records? — Khurram Ali, Mar 24 '15 at 19:51
"select distinct [C1],C2 as [Error_Name], '' as [C3], [C4],[C5] from rejection" C3 is blank as u can see .... in the grid it comes blank , the user then put manly some words then clieck update this shoud insert all the colums included the updated once to test 2 table — KAS, Mar 24 '15 at 20:01

score 0 · Answer 1 · answered Mar 24 '15 at 16:27

0

You Should use Bulk Insert

 SqlBulkCopy cm = new SqlBulkCopy(con);
            cm.DestinationTableName = "test2";
            cm.ColumnMappings.Add("C1", "C1");
            cm.ColumnMappings.Add("C2", "C2");
            cm.ColumnMappings.Add("C3", "C3");
            cm.ColumnMappings.Add("C4", "C4");
            cm.ColumnMappings.Add("C5", "C5");
            try
            {
                cm.WriteToServer(dataGridView1.DataSource);
                MessageBox.Show("Total Record Inserted");
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }

Note: cm.ColumnMappings.Add(yourdatagridviewcolumnname,"yourcolumnnameindb");

answered Mar 24 '15 at 16:27

Khurram Ali

1,659
4
20
37

still the same thing only the update on the first record is updated on the database all other updates are ignored – KAS Mar 24 '15 at 16:49
also i get this error on the run :Error 13 Argument 1: cannot convert from 'object' to 'System.Data.IDataReader' Error 12 The best overloaded method match for 'System.Data.SqlClient.SqlBulkCopy.WriteToServer(System.Data.IDataReader)' has some invalid arguments – KAS Mar 24 '15 at 16:50
yes am getting the same thing no diffrent, its like any changes done to the 2nd row in the grid view didnt happen – KAS Mar 24 '15 at 19:51

datagridview insert blank from the 2nd row onwords

1 Answers1