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"

This example shows on how to add rows of data in the GridView control.  In this example, I created a simple database table called “Table1” for storing the data.

The Table has the following columns:

Id – PK
Employees
Position
Team

Note that I added some dummy data in table that I have created so that we can display something in the GridView when the page is loaded for the first time.

To get started, let’s grab a GridView Control from the Visual Studio ToolBox and place it in the webform.  Then set up the GridView columns for displaying the data. The Markup should look something like this:

<asp:GridView ID="GridViewEmployee" runat="server" AutoGenerateColumns="false" ShowFooter="true">

    <Columns>

        <asp:TemplateField HeaderText="Employee Name">

            <ItemTemplate>

                <asp:Label ID="Label1" runat="server" Text='<%# Bind("Employees") %>'/>

            </ItemTemplate>

            <FooterTemplate>

                <asp:TextBox ID="TextBoxEmployee" runat="server"/>

            </FooterTemplate>

        </asp:TemplateField >

        <asp:TemplateField HeaderText="Position">

            <ItemTemplate>

                <asp:Label ID="Label2" runat="server" Text='<%# Bind("Position") %>'/>

            </ItemTemplate>

            <FooterTemplate>

                    <asp:TextBox ID="TextBoxPosition" runat="server"/>

            </FooterTemplate>

        </asp:TemplateField>

        <asp:TemplateField HeaderText="Team Name">

            <ItemTemplate>

                <asp:Label ID="Label3" runat="server" Text='<%# Bind("Team") %>'/>

            </ItemTemplate>

            <FooterTemplate>

                    <asp:TextBox ID="TextBoxTeam" runat="server"/>

            </FooterTemplate>

        </asp:TemplateField>

        <asp:TemplateField HeaderText="Employee ID">

            <ItemTemplate>

                <asp:Label ID="Label3" runat="server" Text='<%# Bind("Id") %>'/>

            </ItemTemplate>

            <FooterTemplate>

                <asp:Button ID="Button1" runat="server" Text="Add New" OnClick="Button1_Click" />

            </FooterTemplate>

        </asp:TemplateField>

    </Columns>

    </asp:GridView>

 

As you can see, I used TemplateField Columns and added some Labels in it for displaying the data from the database. I set up TemplateField columns in the Grid so that we can add input fields (TextBox) under the footer of the template for adding new records to database. Note that since we need to add some controls under the footer of the templatefields then be sure that you have set ShowFooter to TRUE in your GridView.

Now let’s switch to source code view (Code Behind) and create a method for binding our GridView.

Here are the code blocks below:

    private string GetConnectionString()

    {

        //Where MyConsString is the connetion string that was set up in the web config file

        return ConfigurationManager.ConnectionStrings["MyConsString"].ConnectionString;

    }

 

    private void BindGridView()

    {

        DataTable dt = new DataTable();

        SqlConnection connection = new SqlConnection(GetConnectionString());

        try

        {

            connection.Open();

            string sqlStatement = "SELECT * FROM Table1";

            SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);

            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

            sqlDa.Fill(dt);

            if (dt.Rows.Count > 0)

            {

                GridViewEmployee.DataSource = dt;

                GridViewEmployee.DataBind();

            }

        }

        catch (System.Data.SqlClient.SqlException ex)

        {

            string msg = "Fetch Error:";

            msg += ex.Message;

            throw new Exception(msg);

        }

        finally

        {

            connection.Close();

        }

    }

 

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!Page.IsPostBack)

        {

            BindGridView();

        }

    }

 

 

For more detail information about binding the GridView Control with data then I would suggest you to refer here.

Running the code above will give us this page output below:

As you noticed, there are three TextBoxes below each columns in the GridView and a Button for adding a new row in the Grid.

Now let’s create the method for adding new records to the database. Here’s the code block below:

    private void AddNewRecord(string employee, string position, string team)

    {

        SqlConnection connection = new SqlConnection(GetConnectionString());

        string sqlStatement = "INSERT INTO Table1" +

                              "(Employees,Position,Team)" +

                               "VALUES (@Employees,@Position,@Team)";

        try

        {

 

            connection.Open();

            SqlCommand cmd = new SqlCommand(sqlStatement, connection);

            cmd.Parameters.AddWithValue("@Employees", employee);

            cmd.Parameters.AddWithValue("@Position", position);

            cmd.Parameters.AddWithValue("@Team", team);

            cmd.CommandType = CommandType.Text;

            cmd.ExecuteNonQuery();

        }

        catch (System.Data.SqlClient.SqlException ex)

        {

            string msg = "Insert/Update Error:";

            msg += ex.Message;

            throw new Exception(msg);

        }

        finally

        {

            connection.Close();

        }

    }

 

Now let’s call that method when clicking on the Button “Add New”. Here’s the code block below:

    protected void Button1_Click(object sender, EventArgs e)

    {

        //Extract the TextBoxes that is located under the footer template

        TextBox tbEmployee = (TextBox)GridViewEmployee.FooterRow.Cells[0].FindControl("TextBoxEmployee");

        TextBox tbPosition = (TextBox)GridViewEmployee.FooterRow.Cells[1].FindControl("TextBoxPosition");

        TextBox tbTeam = (TextBox)GridViewEmployee.FooterRow.Cells[2].FindControl("TextBoxTeam");

 

        //call the method for adding new records to database and pass the necessary parameters

        AddNewRecord(tbEmployee.Text, tbPosition.Text, tbTeam.Text);

        //Re-Bind the GridView to reflect the changes made

        BindGridView();

    }

 

Now, compile your application and run it. Then try input something in the TextBox and hit the Button "Add New" to add a new data to the Database. The page output should look something like this:

 

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

Technorati Tags: ,,,
Posted on Tuesday, June 9, 2009 10:37 PM ADO.NET , ASP.NET , C# , GridView | Back to top


Comments on this post: Adding Rows in GridView

# Re
Requesting Gravatar...
Cool, I've learn a lot that formerly unknown. A suggestion, you'd better use ListView, since ListView provide a better performance.
Left by Jack on Jun 11, 2009 8:02 PM

# re: Adding Rows in GridView
Requesting Gravatar...
I've accomplished this in other ways, but I never thought to use the FooterTemplate - thanks.
BTW, you don't need to use a SqlDataAdapter, and the "@" in the AddWithValue() call in unnecessary.
Left by M on Jun 14, 2009 9:58 PM

# re: Adding Rows in GridView
Requesting Gravatar...
i am getting an error. Colomn id cannot be null in insert/update statement. Ca
n u tell the solution for this.

Actually the table is having four colomns. But in our insert statement we are sending only 3 parameters. How can we send the value of id.
Left by nilu on Jul 13, 2009 6:33 AM

# re: Adding Rows in GridView
Requesting Gravatar...
how to insert a passing value from the previous page into the same table as above. the value do not appear in the gridview. it is just an id to differentiate department.
Left by sakinah hawa on Aug 12, 2009 7:45 PM

# re: Adding Rows in GridView
Requesting Gravatar...
oh..it's okay..i've already got the solution...my senior help me do some alteration to your code..

private void AddNewRecord(int id_masjid, string senaraiTaman, string senaraiSurau)
{
//conn = new SqlConnection(connString);
string sqlStatement = "INSERT INTO tbl_sempadanKariah" +
"(id_masjid,senaraiTaman,senaraiSurau)" +
"VALUES (@id_masjid,@senaraiTaman,@senaraiSurau)";

try
{
conn.Open();
SqlCommand cmd = new SqlCommand(sqlStatement, conn);
cmd.Parameters.AddWithValue("@senaraiTaman", senaraiTaman);
cmd.Parameters.AddWithValue("@senaraiSurau", senaraiSurau);
cmd.Parameters.AddWithValue("@id_masjid", id_masjid);

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

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

finally
{
conn.Close();
}

}

id_masjid do not need to appear in the gridview..
Left by sakinah hawa on Aug 18, 2009 9:46 PM

# re: Adding Rows in GridView
Requesting Gravatar...
How to allow paging in this intergation?

Left by Algo on Dec 28, 2009 5:46 AM

# re: Adding Rows in GridView
Requesting Gravatar...
when i use ur given link code Mr.Vinz there give error at
connection.open() line so plz help me
Left by amarnath matia on Feb 09, 2010 6:42 PM

# re: Adding Rows in GridView
Requesting Gravatar...
its not able to add data when the data in the database is null for the first time....plz help me...
Left by Ravish S on Feb 18, 2010 10:35 PM

# re: Adding Rows in GridView
Requesting Gravatar...
Hi, I hav 2 tables of AccountGroups and Accounts.Under a AccountGroup, there are many accounts.So the accounts table consists of a foreign key i.e. AccountGroupID from the AccountGroup Table. While adding rows it gives me an error "Object Reference not set to an instant" and nullreference pointer exception....Plz help me.
Left by Trupti on Apr 07, 2010 8:58 PM

# re: Adding Rows in GridView
Requesting Gravatar...
can we use paging in this
Left by moto on Aug 05, 2011 8:47 PM

# re: Adding Rows in GridView
Requesting Gravatar...
Yes.. just google for the term to get more examples.
Left by Vinz on Aug 05, 2011 9:42 PM

# good article
Requesting Gravatar...

Nice post.Thank you for taking the time to publish this information very useful!I’m still waiting for some interesting thoughts from your side in your next post thanks.
Personsøk
Left by yashika on Aug 17, 2011 1:22 PM

# re: Adding Rows in GridView
Requesting Gravatar...
error at
connection.open() line so plz help me
Left by swetha on Aug 18, 2011 7:35 PM

# re: Adding Rows in GridView
Requesting Gravatar...
Check your connectionstring
Left by Vinz on Aug 18, 2011 9:02 PM

# re: Adding Rows in GridView
Requesting Gravatar...
I have read everything on the shortlist and I know its quality is incredible. Every writer has aspects of style I genuinely covet. They are extraordinary women and extraordinary writers.Over 350 Nail Art samples to download
Left by mariya on Aug 19, 2011 3:37 PM

# re: Adding Rows in GridView
Requesting Gravatar...
Left by Jorge on Aug 19, 2011 8:08 PM

# re: Adding Rows in GridView
Requesting Gravatar...
upadate,add are not working
Left by veerendra on Sep 01, 2011 12:10 AM

# re: Adding Rows in GridView
Requesting Gravatar...
my requirement is,In gridview first row must be empty and in that row one textbox is searchable i:e if i enter my agentid it must add the record below the empty row and so on....
Left by ghouse on Jan 23, 2012 6:56 PM

comments powered by Disqus

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