Adding a row in GridView for SqlDataSource 1

[kamfl610]

Hi all,

Thought I'd pass along a solution that I did for my GridView that uses SqlDataSource. Here was my problem:
I was using the Footer Template to create an Add and Cancel button along with my fields so I could Add new records. But then came the problem, what if I had no data in that table or filtering the data and it returned no rows. Well then my header and footer wouldn't show. So I did some searching throught the internet and forums to find a solution. Gary Dryden had a good solution about just silently adding a row and using that row to do the add but it wasn't quite what I wanted. Andrew Robinson had a another good solution but it was for an objectdatasource. So what I ended up doing was taking a part of each for my sqldatasource solution.

In Andrew Robinson's solution, objectdatasource returns a table and what he did was add a row in that datatable to fool the gridview into thinking there was truly data. And then the next step was to basically hide that row so that your header and footer will show up on your gridview. In sqldatasource, there really isn't a table per say and I could never get it right.

In Gary Dryden's version, what he did was use the UNION statement in the SQL statement to silently insert a blank row. Awesome solution.

The final solution is this, I used Gary Dryden's UNION statement in the SqlDataSource and then used Andrew Robinson's 2nd part to actually hide the row.

Here's how it works.

On the actually aspx page in the GridView part, I added this :

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4" OnRowUpdating="GridView1_RowAdding"
                DataSourceID="SqlDataSource3" ForeColor="#333333" DataKeyNames="ID" BorderColor="Transparent" ShowFooter="True" OnRowCreated="GridView1_RowCreated">

Notice the two events: OnRowUpdating and OnRowCreated .
OnRowUpdating event has the GridView1_RowAdding (which Gary Dryden's solution) which is on the aspx.cs page. Here's the code for it:

protected void GridView1_RowAdding(object sender,
                    GridViewUpdateEventArgs e)
   {
        if (e.RowIndex > 0)
        {
            return; // RowIndex=0 is the row we want to insert
        }
        System.Collections.Hashtable h = new System.Collections.Hashtable();
        foreach (System.Collections.DictionaryEntry x in e.NewValues)
        {
            h[x.Key] = x.Value;
        }
        // you now have the data to insert in a hashtable
        // get it into the database using your
        // usual Data Access Layer methods

 }

You need to make sure that you're SqlDataSource SelectCommand Statement in your aspx looks similar to this:

 <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:Budget_ManagementConnectionString %>" 
SelectCommand="SELECT ' ' AS ID, ' ' AS BE, ' ' AS FISCAL_YEAR, ' ' AS NOTES, ' ' AS LONGTITLE UNION ALL SELECT REF_BE.ID, REF_BE.BE, REF_BE.FISCAL_YEAR, REF_BE.NOTES, BE.LONGTITLE FROM REF_BE INNER JOIN BE ON REF_BE.BE = BE.BE WHERE (REF_BE.FISCAL_YEAR = @FISCAL_YEAR) ORDER BY FISCAL_YEAR, BE"

Notice I'm doing some filtering in my sql statement and ordering by.

Anyway, here's the code for the OnRowCreated event:

protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
    {
//  when binding a row, look for a zero row condition based on the flag.
//  if we have zero data rows (but a dummy row), hide the grid view row
//  and clear the controls off of that row so they don't cause binding errors

        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            count = count + 1;
            if (count == 1)
            {
                e.Row.Visible = false;
                e.Row.Controls.Clear();
            }
        }
    }

You need to put somewhere before the two codes a variable called int count. This variable is necessary because the first row because I'm using an identity seed will be 0 and that is the blank row. I know it's the first record so I need to just count the first row. Example of int count, where to put it:

public partial class Budget_Entity : System.Web.UI.Page
{
    int count = 0;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
public void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        count = 0;
    }

My dropdownlist1 is really a variable I'm using to filter the list so everytime the list changes, I need to reset count and the same thing will apply when you do adds and inserts. Just make sure that you code these to the correct events in your aspx page. i.e. OnSelectedIndexChanged on the your DropDownList.

Once you have these coded, it works beautifully with SqlDataSource and it only took very little lines to code.

I want to thank Gary Dryden and Andrew Robinson for probably many hours of hard work.

Here's the link's to their sites:

http://www.codeproject.com/aspnet/addupdate.asp

http://blog.binaryocean.com/PermaLink,guid,a1ff6cab-dc2d-441c-8557-7dce920d4075.aspx

I hope this helps you. Let me know if you don't understand something. Thanks!