Inserting more than one record to sql server

Question

Guys i have a grid and need to retrieve text inputs from and then insert to the the database . The grid looks like below

From the above it permits user to pass as many rows to the database as he so desired. i use the method below.

   private void insert()
        {
            connection.Open();

            for(int i=0; i< gvAdditionalDetails.Rows.Count ; i++)
            {
                string sql = "insert into [CONTACT_DETAILS] (type,description,contactID) VAlUES (@row1,@row2,@contactID )";

                SqlCommand cmd = new SqlCommand(sql, connection);
                cmd.CommandType = CommandType.Text;

                cmd.Parameters.AddWithValue("@row1", gvAdditionalDetails.Rows[i].Cells[1].Text.Trim());
                cmd.Parameters.AddWithValue("@row2", gvAdditionalDetails.Rows[i].Cells[2].Text.Trim());
                cmd.Parameters.AddWithValue("@contactID", 39);
                cmd.ExecuteNonQuery();
            }
            connection.Close();
        }

The above method loop throw my grid , how ever is returning null for the columns type which has a dropdown selection and description which has a tetxbox control. I cant call this controls invidually because they are declared in a grid. How do i retrieve the text the selected item from the dropdown and the inserted text from the textbox. the code gvAdditionalDetails.Rows[i].Cells[1].Text.Trim() returns null.

@ Mike sure. Sorry was busy trying didn't notice your comment earlier. Can you see the update now please? — Nuru Salihu, Sep 24 '13 at 04:31
Thanks i get it going now, not sure if its the best way through. — Nuru Salihu, Sep 24 '13 at 04:50

score 3 · Answer 1 · answered Sep 24 '13 at 04:01

3

INSERT INTO Table ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 )

answered Sep 24 '13 at 04:01

x2.

9,554
6
41
62

though you couldn't do that in MSSQL 2005, http://stackoverflow.com/questions/2624713/how-do-i-insert-multiple-rows-without-repeating-the-insert-into-dbo-blah-part – Levin Magruder Sep 24 '13 at 04:24
You can't in 2005 or earlier, but I don't think 2005 is specified in the question. I think 2008/2012 are both ok with this syntax – Greg Sep 24 '13 at 04:28
Thanks guys. But i loop through and insert one row at time . Please see the above update. However i couldnt retrieve the input from the two fields. – Nuru Salihu Sep 24 '13 at 04:37

score 3 · Accepted Answer · answered Sep 24 '13 at 05:12

You need to refer to the control inside the gridview row.

private void insert()
        {
            connection.Open();

            for(int i=0; i< gvAdditionalDetails.Rows.Count ; i++)
            {
                string sql = "insert into [CONTACT_DETAILS] (type,description,contactID) VAlUES (@row1,@row2,@contactID )";

                SqlCommand cmd = new SqlCommand(sql, connection);
                cmd.CommandType = CommandType.Text;

                cmd.Parameters.AddWithValue("@row1", ((DropdownList)gvAdditionalDetails.Rows[i].FindControl("DropDownListType")).SelectedItem.Text.Trim());
                cmd.Parameters.AddWithValue("@row2", ((TextBox)gvAdditionalDetails.Rows[i].FindControl("txtDescription")).Text.Trim());
                cmd.Parameters.AddWithValue("@contactID", 39);
                cmd.ExecuteNonQuery();
            }
            connection.Close();
        }

score 0 · Answer 3 · answered Sep 24 '13 at 04:29

0

INSERT INTO Table (Column1, Column2)
select value1, value2
union all
select value1, value2
union all
select value1, value2

answered Sep 24 '13 at 04:29

Greg

3,442
3
29
50

score 0 · Answer 4 · answered Sep 24 '13 at 04:49

Thanks all. I loop through and retrieve the inputs as follows . I am not sure it its the best way to do it , but it did work for me.

  private void insert()
        {
            connection.Open();


            for(int i=0; i< gvAdditionalDetails.Rows.Count ; i++)
            {
                DropDownList DropDownListType =
                         (DropDownList)gvAdditionalDetails.Rows[i].Cells[1].FindControl("DropDownListType");



                TextBox TextBoxDescription =
                (TextBox)gvAdditionalDetails.Rows[i].Cells[i].FindControl("txtDescription");

                string sql = "insert into [CONTACT_DETAILS] (type,description,contactID) VAlUES (@row1,@row2,@contactID )";

                SqlCommand cmd = new SqlCommand(sql, connection);
                cmd.CommandType = CommandType.Text;

                cmd.Parameters.AddWithValue("@row1", DropDownListType.SelectedItem.Text.Trim());
                cmd.Parameters.AddWithValue("@row2", TextBoxDescription.Text.Trim());
                cmd.Parameters.AddWithValue("@contactID", 39);
                cmd.ExecuteNonQuery();
            }
            connection.Close();
        }

Inserting more than one record to sql server

4 Answers4