Geeks With Blogs

News

Microsoft MVP


DZone MVB


Moderator at CodeASP.NET


Quiz Master







free counters
Free counters
Added on January 19,2012


Follow Me @vmsdurano

A bit About Me



Disclaimer
The opinions expressed herein are my own personal opinions and does not represent the opinions of my employers. Nor does it represent the opinion of my dog, because I don’t have one.


Vinz' Blog (ProudMonkey) "Code, Beer and Music ~ my way of being a programmer"

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

        return System.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:

 

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

 

Technorati Tags: ,,,
Posted on Sunday, August 2, 2009 1:54 PM ADO.NET , ASP.NET , C# , GridView | Back to top


Comments on this post: Save Dynamic TextBox Values from GridView to Database

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
One of Rare article. Perfectly addressed the requirements of a developer.I searched for this logic very long finally i got.
Left by Harikrishnan on Aug 10, 2009 12:47 AM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
Your blog has been a real find. I have enjoyed all of your blogs on gridviews. Outstanding!

Thanks,

Burrell
Left by burrellw on Oct 02, 2009 2:45 AM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
Hello! I have a question here, if a want to add a checkboxlist(populated from a sqldatasource) to the grid, how do I save those values to the database as well?

this doesent really work:

(CheckBoxList)Gridview1.Rows[rowIndex].Cells[1].FindControl("CheckBoxList1");

and I have added another field in the database as well.

Any suggestions?

Regards Henrik
Left by Henrik Rosqvist on Feb 02, 2010 2:37 AM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
Thanx a lot ... finally i got it....
Left by Rohit on Feb 02, 2010 7:23 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
the values that r saved in the database....can we read them thru the same gridview thru which we have added.....
if yes plz do reply......
i'm trying something like this..........but it's showing only one row containing the last record......
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");
TextBox box4 = (TextBox)Gridview1.Rows[rowIndex].Cells[4].FindControl("TextBox4");
TextBox box5 = (TextBox)Gridview1.Rows[rowIndex].Cells[5].FindControl("TextBox5");
TextBox box6 = (TextBox)Gridview1.Rows[rowIndex].Cells[6].FindControl("TextBox6");
DropDownList ddl1 = (DropDownList)Gridview1.Rows[rowIndex].Cells[7].FindControl("DropDownList1");
TextBox box7 = (TextBox)Gridview1.Rows[rowIndex].Cells[8].FindControl("TextBox7");
// drCurrentRow = dtCurrentTable.NewRow();
drCurrentRow["RowNumber"] = i + 1;

dtCurrentTable.Rows[i - 1]["Column1"] = myReader.GetString(1).Trim();
dtCurrentTable.Rows[i - 1]["Column2"] = myReader.GetString(2).Trim();
dtCurrentTable.Rows[i - 1]["Column3"] = myReader.GetString(3).Trim(); ;
dtCurrentTable.Rows[i - 1]["Column4"] = myReader.GetString(4).Trim(); ;
dtCurrentTable.Rows[i - 1]["Column5"] = myReader.GetString(5).Trim(); ;
dtCurrentTable.Rows[i - 1]["Column6"] = myReader.GetString(6).Trim(); ;
dtCurrentTable.Rows[i - 1]["Column7"] = myReader.GetString(7).Trim(); ;
dtCurrentTable.Rows[i - 1]["Column8"] = myReader.GetString(8).Trim(); ;
Left by Mamta on Mar 02, 2010 3:48 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
let me be more clear......the values the u r inserting thru
textboxes into the database....i want to read them from the database thru the same textboxes i've inserted.......
plz help...
it's urgent....
Left by Mamta on Mar 02, 2010 5:03 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
my application has a gridview..which is initially blank...
i'm adding the rows dynamically to it along with the values...
I'm saving it....(This part is working fine) .

I want to view the same values which i have inserted thru the gridview based on some condition in the gridview.........

In the above example ur database column r col1,col2,col3....
so what i want is
select col1,col2,col3 from sample table where *(condition)

this data shud be displayed in above gridview...i am accesing this page from otherpage and so i want to retain the values of the grid view....
Left by Mamta on Mar 02, 2010 7:35 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
Hi thr...ur above code in working fine....it really helped me..
can u plz tell how can i use pop up calendar with the dynamically added textboxes.....on a textbox box click i want a calendar ..and on its(CALENDAR) click it filters the textbox....
Left by Mamta on Mar 15, 2010 3:13 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
Could you please write the same code in vb.net... Iam trying to translate your code into the vb.net but if you can post them I would appreciate more.
Left by Burundian on Jun 16, 2010 6:51 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
It is not working for me as it should be. I do not see anything after I run the code.
Left by Happy beginner on Jun 16, 2010 10:03 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
Burundian, this site (http://www.developerfusion.com/tools/convert/csharp-to-vb/) should help. It translates from C# to VB.net.

VINZ, you are a genius Bro! I find examples on the net quite often, and rarely do they work without tweaking. Yours did! Thanx for this and all your other gridview examples!
Left by mike vytal on Jul 02, 2010 7:12 AM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
Any thoughts on how I can accomplish this with an access database?

Everything works great but when I try to save the mutliple rows I get the message: Characters found after end of SQL statement

Because Access doesn't allow multiple insert statements?
Left by Eli on Jul 10, 2010 3:10 AM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
Hi,

I have implemented the above code in one of my project and i hve a new requirement of getting a unique id once the value gets inserted to the database. I am using scope_identity to accomplish the task but somehow i am not able to make it work.
Could you please help me to resolve the problem
Left by Deepa on Jul 21, 2010 3:32 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
Vinz...is there a way to change the following piece of your code to an update statement? I want to update existing records from the gridview. Thanks in advance for your help.

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]);
}

}
Left by mike vytal on Jul 28, 2010 4:34 AM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
@Deepa

Have you tried using a datareader?
Left by mike vytal on Jul 28, 2010 4:47 AM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
HI I HAVE A PROBLEM IN THIS
WHEN I RUN THE CODE IT SHOWS ONLY THE BUTTON NOT THE GRID VIEW
WHY SO?
AN U HELP ME ?????
Left by MANI on Aug 06, 2010 11:56 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
can u help me in developing the same technique using java?
Left by nutan on Aug 19, 2010 10:59 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
tell me the source code for dynamic add ,edit and delete
Left by suraj kumar pothal on Nov 10, 2010 7:42 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
In my insert query, i want to insert an additional field(PKey Value) which is not related to my grid columns.
Since when i run insert command it shows me error, that primary key can't be null.
on same webform i have one label which have that primary key value, now how can i insert this value using code above.

Please help.
Left by rohit on Jan 13, 2011 5:11 AM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
Hi Vinz! I am very apprecite for this coding. It works! But when i added one more column (from 3 to 4 column) it's error. It keep saying the same error : Index was outside the bounds of the array.
Can u explain why it's happen? Thanks in advance :)
Left by SU on Apr 25, 2011 11:55 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
@SU,
The error means that you are trying to reference a column that doesn't exist. Please debug your codes and step into it sothat you will figure out what's going on there.
Left by Vinz on Apr 26, 2011 1:10 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
Nice, Thank u
Left by Dhivya on Apr 26, 2011 6:08 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
how can i insert same using Linq to sql queries please help
Left by Paras on May 21, 2011 1:58 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
How can i insert same thing using Linq to Sql queries.
Left by Paras on May 26, 2011 2:09 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
How to do this for Oracle database ...I get a bunch of errors.. PLease help!!!!
I am on a tight deadline
Left by asp.net on Jul 19, 2011 4:08 AM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
can u please tel how to get the saved data back into the grid.. i mean for the update purpose..
Left by ekon on Aug 17, 2011 11:56 AM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
tell me how to create dynamic textbox ,it insert to database
Left by Niro on Sep 03, 2011 7:30 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
while saving the data there is an coming i.e the
ViewState["CurrentTable"] is becoming null so the control is not going inside the loop and execution is getting stopped..plz solve sir
Left by swagatika on Sep 22, 2011 7:43 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
the values that r saved in the database....can we read them thru the same gridview thru which we have added.....
Left by rupali on Nov 01, 2011 6:31 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
hi sir thank you it more helped me and i have one requirement is "How to insert dynamic textbox values from gridview with remaining values (this values are not from gridview) to database" at a time .

Left by prasad on Dec 03, 2011 5:18 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
hI,
I tried your code and when I debug Iam getting always ViewState["CurrentTable"] = "". Do I need to set some values in my aspx file to store the values of textbox to viewstate. Please help.

Thanks
Left by shri on Dec 07, 2011 10:29 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
can we also have a button to delete any row from the grid view
Left by Varun on Dec 21, 2011 11:45 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
when i click on save button it is showing as insatance failure in conn.Open();
Left by prashanth on Jan 05, 2012 6:43 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
if (ViewState["CurrentTable"] != null)

{

DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];

if (dtCurrentTable.Rows.Count > 0)

{

for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)

{

these lines are not woking when i remove these lines then it saves all rows. i have 100 rows. i want in which rows i insert data that rows should be add in database
Left by veenu on Jan 25, 2012 5:08 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
if (ViewState["CurrentTable"] != null)

{

DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];

if (dtCurrentTable.Rows.Count > 0)

{

for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)

{

these lines are not woking when i remove these lines then it saves all rows. i have 100 rows. i want in which rows i insert data that rows should be add in database
Left by veenu on Jan 25, 2012 5:08 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
thanku so much for this code
Left by veenu on Jan 27, 2012 3:56 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
very useful thanku
Left by meena on Jan 31, 2012 5:59 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
very useful thanku expected answer was received thanku for ur post
Left by meena on Jan 31, 2012 5:59 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
i want to apply query on datagrid view acc to user need..
for eg: user enter name in textbox and corresponding to dis textbox value related rows are selected and data is fetch in grid view..
pls help.
its urgent.
Left by Ankit on Feb 14, 2012 12:09 AM

# Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
Very Nice
Left by Anonymous on Mar 07, 2012 6:04 PM

# Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
Very nice code.... :)
Left by Anonymous on Mar 07, 2012 6:04 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
How can i convert the splitItems into a DateTime datatype so i can store it into the database??
Left by melG on Aug 02, 2012 3:14 PM

# Use this very simple and handy version of above code
Requesting Gravatar...
protected void btnSave_Click(object sender, EventArgs e)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
con.Open();
string qry;
qry = "insert into tbmrk(mrkstdcod, mrksubcod, mrkint,mrkssncod,mrkcdt,mrkisdelt) values";
foreach (GridViewRow row in grdmrk.Rows)
{
if (row.RowType == DataControlRowType.DataRow)
{
qry = qry + " (" + grdmrk.DataKeys[row.RowIndex].Values[0].ToString() + "," + ddlsubject.SelectedValue + ",";
qry = qry + ((TextBox)(grdmrk.Rows[row.RowIndex].FindControl("txtmrk"))).Text + "," + DdlSession.SelectedValue + ", GETDATE() "+ ",0),";
}
}
qry=qry.Substring(0,(qry.Length-1));



cmd.CommandText = qry;
cmd.ExecuteNonQuery();

cmd.Dispose();

con.Close();
}
catch (Exception ex)
{
throw ex;
}

}
Left by Gourav Mahipal on Dec 30, 2012 1:57 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
Thank you, this was very helpful, Just 1 question, if you want the gridviews rows to be added basing on a selected value from a dropdownlist control.How do you do that? Thanx.
Left by Emmy on Feb 08, 2013 2:36 PM

# re: Save Dynamic TextBox Values from GridView to Database
Requesting Gravatar...
thanks .its really helpfull.
Left by faty on Feb 18, 2013 9:14 PM

comments powered by Disqus

Copyright © Vincent Maverick Durano | Powered by: GeeksWithBlogs.net