Saturday, February 25

SAVE DYNAMIC TEXTBOX VALUES FROM GRIDVIEW TO DATABASE


In my previous article, I have demonstrated on how to add dynamic rows in GridView control with TextBoxes. Now, seems that most of the developers are asking if how to save all the data that was entered from the dynamic textbox in the GridView to the database. So in this example, I’m going to show on how to save them all in the database.
To get started then lets create a sample Table in SQL Server. In this example, I named the table as “SampleTable” with the following fields below:
 
Note:
I set the Id to auto increment so that the id will be automatically generated for every new added row in the table. To do this select the Column name “Id” and in the column properties set the “Identity Specification” to yes.
Now let’s go ahead and proceed to ASPX source and add a Button for saving the data to the database. Take a look at the screen shot below:
 
Now let’s create the method for saving the data to the database. The first thing we need here is to set up the connection string so that we can connect to the Sql server from our codes. In this example we are going to use the webconfig file for setting up the connection string. See the mark up below:
<connectionStrings>
            <add name="DBConnection" connectionString="Data Source=SERVERNAME\SQLEXPRESS;Initial Catalog=SampleDB;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>     
</connectionStrings>

Since the connection string is already set up then we now proceed in creating the method for saving the data to the database. Here are the code blocks below:
First, add the following namespaces below:
using System.Collections.Specialized;
using System.Text;
using System.Data.SqlClient;

We need to declare the namespaces above sothat we can use the SqlClient, StrngCollections and StringBuilder built-in methods in our codes later.
Second, create the method for calling the connection strings that was set up in the web.config file.
    //A method that returns a string which calls the connection string from the web.config
    private string GetConnectionString()
    {
        //"DBConnection" is the name of the Connection String
        //that was set up from the web.config file
        returnSystem.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
    }

And here’s the code block for the Insert method:
//A method that Inserts the records to the database
    private void InsertRecords(StringCollection sc)
    {
        SqlConnection conn = new SqlConnection(GetConnectionString());
        StringBuilder sb = new StringBuilder(string.Empty);
        string[] splitItems = null;
        foreach (string item in sc)
        {

            const string sqlStatement = "INSERT INTO SampleTable (Column1,Column2,Column3) VALUES";
            if (item.Contains(","))
            {
                splitItems = item.Split(",".ToCharArray());
                sb.AppendFormat("{0}('{1}','{2}','{3}'); ", sqlStatement, splitItems[0], splitItems[1], splitItems[2]);
            }

        }

        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();

           //Display a popup which indicates that the record was successfully inserted
            Page.ClientScript.RegisterClientScriptBlock(typeof(Page),"Script""alert('Records Successfuly Saved!');"true);

        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Insert Error:";
            msg += ex.Message;
            throw new Exception(msg);

        }
        finally
        {
            conn.Close();
        }
    }

Now, at Button Click event we can the method “InsertRecords” after extracting the dynamic TextBox values. To make it more clear then take a look at this code block below:
protected void Button1_Click(object sender, EventArgs e)
{
        int rowIndex = 0;
        StringCollection sc = new StringCollection();
        if (ViewState["CurrentTable"] != null)
        {
            DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];
            if (dtCurrentTable.Rows.Count > 0)
            {
                for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
                {
                    //extract the TextBox values
                    TextBox box1 = (TextBox)Gridview1.Rows[rowIndex].Cells[1].FindControl("TextBox1");
                    TextBox box2 = (TextBox)Gridview1.Rows[rowIndex].Cells[2].FindControl("TextBox2");
                    TextBox box3 = (TextBox)Gridview1.Rows[rowIndex].Cells[3].FindControl("TextBox3");

                    //get the values from the TextBoxes
                    //then add it to the collections with a comma "," as the delimited values
                    sc.Add(box1.Text + "," + box2.Text + "," + box3.Text);
                    rowIndex++;
                }
                //Call the method for executing inserts
                InsertRecords(sc);
            }
        }
}

Running the code above will show something like below with the entered values in the TextBox:
 
Clicking on the Save Button will insert the data to the database. Table a look at the Table below:
 
add dynamic rows in GridView control with TextBoxes

That’s it! Hope you will find this example useful!

Thanks Shibashish Mohanty

6 comments:

  1. i want to add controls to datatable.I have two buttons add and delete,when pressed on add button in first row i want to add two dropdown's and one button,when click on again add button in second adding the same also and so on.when i pressed on delete button the table rows are deleted on by one.finally i want to store the values on database.Please help me how to do that,Its very urgent in my Project....
    Thanks.

    ReplyDelete
    Replies
    1. Hi jagddeesh just give me 30min i will post it for you.Thanks For Comments

      Delete
  2. Hello Jagadeesh Naidu i have posted Your Requirement in Below Link Just Go for it.Wish you all the best for your Project.
    http://shibashishdotnetocean.blogspot.in/2012/08/adding-and-removing-dynamic-rows-in.html

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. This is the Ausome website I have ever visited..so much to learn..

    ReplyDelete
  5. Hi Shibashish - How can add a new row to gridview when I click on any of the text boxes in the last row (instead of clicking on the add row button)? Thank you soo much for your help!

    ReplyDelete

Please don't spam, spam comments is not allowed here.

.

ShibashishMnty
shibashish mohanty