Saving multiple rows of gridview into database

Petro Tiburcio

Hi there, i have a gridview which populated via selected item on my drop down list which I don't have any problem with that. My problem is I am trying to save multiple rows on my gridview into my database, for example I have added 3 items on my gridview which looks like this:

Id | Name
111 | Jack
222 | Nicole
333 | John

Now, I want all that under column Id which is 111, 222 and 333 will be save on my database once I click Save button I am trying the code below but it gives me an error saying "system.data.sqlclient.sqlparameter does not contain a definition for 'Values' and no extension method 'Values' accepting a first argument of type..." :

SqlConnection con = new SqlConnection("Data Source=GATE-PC\\SQLEXPRESS;Initial Catalog=dbProfile;Integrated Security=True");
SqlCommand cmdd = new SqlCommand("Insert into profile (Id, profile_id)VALUES(@id, @profile_id", con);
foreach (GridViewRow row in GridView1.Rows)
{
cmdd.Parameters.Add("@id", SqlDbType.Char, 20, "Id").Values = row.Cells["Id"].Value; }
cmdd.CommandType = System.Data.CommandType.Text; cmdd.Parameters.AddWithValue("@profile_id", txtID.Text); con.Open(); cmdd.ExecuteNonQuery();

My table should be look like this once I am able to save multiple rows from my gridview into my database:

auto_id | Id | profile_id
1 |111 | 101
2 |222 | 101
3 |333 | 101

what am I missing? please help. Im using asp.net with c#. Thanks and God bless

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes13y ago
I've just looked up the Item property (indexer in C#) of the TableCellCollection class on MSDN and it's not overloaded to take the column name like many of these collections are - you can only use the zero based index of the column.

Also the TableCell class has a Text property rather than a Value property.

So, assuming the id column will have an index of 0, I'd try instead:

cmdd.Parameters.Add("@id", SqlDbType.Char, 20, "Id").Value = row.Cells[0].Text;
Petro Tiburcio13y ago
Hi thanks for replying, I have tried your code but it gives me an error at row.Cells["Id"]. It says "the best overloaded method match for 'System.Web.UI.WebControls.TableCellCollection.this[int]' has some invalid arguments". Thanks again
Vulpes13y ago
Try it like this - note that Values should be Value:

SqlConnection con = new SqlConnection("Data Source=GATE-PC\\SQLEXPRESS;Initial Catalog=dbProfile;Integrated Security=True");
con.Open();

foreach (GridViewRow row in GridView1.Rows)
{
SqlCommand cmdd = new SqlCommand("Insert into profile (Id, profile_id) VALUES(@id, @profile_id)", con); // inserted space before VALUES and added closing bracket to it
cmdd.CommandType = System.Data.CommandType.Text;
cmdd.Parameters.Add("@id", SqlDbType.Char, 20, "Id").Value = row.Cells["Id"].Value;
cmdd.Parameters.AddWithValue("@profile_id", txtID.Text);
cmdd.ExecuteNonQuery();
}

con.Close();