Save each row in gridview that was built manually

Question

I'm trying to save multiple rows from a gridview into sql database. The gridview is manually built in code, thus it is not databind... The reason for this is because this is a grid for parts that has to be added to a quote... Okey so I done the saving part, the only thing is, is that I have to set a foreach to get the data from that row (which I done), but the variables will be set constantly and only getting the last rows data... Can anybody please help me out? This is what I've done:

    conn = new SqlConnection(GetConnectionString());

    string sql = "INSERT INTO CC_ItemsRequested (Item,Quantity, Price, ModelID, DateCreatedOn ) VALUES (@Item, @Quantity, @Price, @ModelID, @DateCreatedOn)";

    try
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand(sql, conn);
        int row = GridView1.Rows.Count;

        cmd.Parameters.AddWithValue("@Item", myPart);
        cmd.Parameters.AddWithValue("@Quantity", myQuantity);
        cmd.Parameters.AddWithValue("@Price", myPrice);
        cmd.Parameters.AddWithValue("@ModelID", methodID);
        cmd.Parameters.AddWithValue("@DateCreatedOn", DateTime.Now);

        for (int i = 0; i < row; i++)
        {
            cmd.Parameters["@Item"].Value = myPart;
            cmd.Parameters["@Quantity"].Value = myQuantity;
            cmd.Parameters["@Price"].Value = myPrice;
            cmd.Parameters["@ModelID"].Value = methodID;
        }

        cmd.CommandType = CommandType.Text;
        cmd.ExecuteNonQuery();

This will save the data, but I need to do something so that each row will be saved...This is the foreach that I wrote:

     //foreach (GridViewRow rows in GridView1.Rows)
        //{
        //    if (rows.RowType == DataControlRowType.DataRow)
        //    {
        //        string myPart = rows.Cells[0].Text;
        //        string myQuantity = rows.Cells[1].Text;
        //        string myPrice = rows.Cells[2].Text; 
        //    }

But the problem is, it is not going to get all the rows in the grid's data, it will basically only save the last row then....

Any ideas please? Thanks in advance!

I need little clarification. In `if` condition of `foreach` loop you initialized and assigned some variables. Where are you using those values?? — Shaharyar, Mar 07 '13 at 07:19
Thanks for replying! At first I only had a foreach statement with the those variable and the cmd.Paramaters.AddWithValue() in that exact foreach statement, but I reaceived a error message of "The variable name '@Item' has already been declared. Variable names must be unique within a query batch or stored procedure" meaning that I was trying to initialize those cmd variable over and over again with all the rows. So I had to change it to the above code not to get the eroor message. All that I have to do now is to get those variables in the if statement some other way to use them for the values. — Kerieks, Mar 07 '13 at 07:47
perhaps just place your ExecuteNonQuery within the for loop and not at the end? — AardVark71, Mar 07 '13 at 09:59
Hi.. Thanks for your reply... Did try that, but the error was given because the same paramaters were beign initialized more than once thus it didn't matter where the ExecuteNonQuery is... Thanks for your help though! — Kerieks, Mar 07 '13 at 11:34

score 0 · Accepted Answer · answered Mar 07 '13 at 11:07

I refactored your code a little bit: the SqlCommand has to be executed for each rows, and the parameters have to be retrieve from the GridView before.

conn.Open();

using(SqlCommand cmd = new SqlCommand(sql, conn))
{
    var now = DateTime.Now;

    foreach (GridViewRow row in GridView1.Rows)
    {
        string myPart = row.Cells[0].Text;
        string myQuantity = row.Cells[1].Text;
        string myPrice = row.Cells[2].Text;
        //... etc

        cmd.Parameters.Clear();
        cmd.Parameters.AddWithValue("@Item", myPart);
        cmd.Parameters.AddWithValue("@Quantity", myQuantity);
        cmd.Parameters.AddWithValue("@Price", myPrice);
        cmd.Parameters.AddWithValue("@ModelID, methodID);
        cmd.Parameters.AddWithValue("@DateCreatedOn, now);

        cmd.ExecuteNonQuery();
    }
}

conn.Close();

I am not sure you want to store Price, Quantity and numeric values as text. You probably want to do some validation first, and do some conversions instead of picking the Text property. What not take the .Value of the cell (if their CellValueType is defined correctly) ?

Hi... Thanks for your reply! Yes the fields are correct, conversions will be done after saving on this form has taken place and are populated in the next step of the process as it will be then needed to be in double/decimal value (its saving the decimal value without any conversion necessary now...) And thanks so much the clear of paramaters works perfect! thanks so much! — Kerieks, Mar 07 '13 at 11:32

Save each row in gridview that was built manually

1 Answers1

Linked