Saving multiple rows of gridview into database

[PetroTiburcio]

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

[Oralloy]

Hello Petro!

Kudos on using parameters to insert values into your SQL!

1. They prevents SQL injection attacks.
2. They eliminate the value encoding problems that dynamic SQL has.

First: I see is that your SQL in line 2 is malformed, it should probably be something like this:

  SqlCommand cmdd = new SqlCommand("INSERT INTO profile(Id, profile_id) VALUES(@id, @profile_id)", con);  

(Added spacing)
(Added a closing parenthesis to the VALUES clause.)
(Did not add a semicolon (";"), this should not affect your work, either way.)

Since it's pretty obvious that you're new to this, I'm going to approach helping you from a teaching perspective, not just writing the code and leaving you ignorant.

Second: It looks like you are a little confused in your logic - it looks like you loop around each of your gridview's rows and try to add a query parameter for the ID, then you try to add the profile-id once at the end.

Rather, you might want to do a complete SQL INSERT command for each row in your gridview. NOTE: some databases allow INSERT command bundling, but that is overkill for what we are doing.

This SqlCommand class, Parameters property manual page has good examples of parameter use. The example uses UPDATE, rather than INSERT, which is immaterial to our needs.

Reading the manual page, you will see that it gives two distinct ways of adding a parameter value to the collection - the first adds parameter @ID, and uses code to provide explicit typing information - the second adds the parameter @demographics, using implicit typing based on the supplied variable.

I suggest that you use the second method of adding parameters as the model for your code.

Third: now that you have a simple example, and a method of manipulating records, write the code to INSERT just the first row from your gridview.

Then, test your code and see how the pieces work together, and what the results are. This will help you become familiar with the class library and its use. Don't forget to include your error detectinon and reporting. People hate silent failures, and you will hate how they report problems - almost all information from system error messages is either ignored, lost, or simply misstated. But, I digress.

Fourth: once you have a single insert working, I think that wrapping a loop around the code will be straightforward.

Make sure to test your code and review the results. Did you test at the boundary conditions (e.g. blank ID values, and repeated ID values)?

Fifth: do you know what transactioning is? If so, you might want to consider whether it is valuable in this circumstance or not.

Ok, before we make a life's project out of this, I'll let you take over.

Good luck!

If you have any questions, or run into any more problems, let me know.

Kind Regards,
Oralloy