using Gridview add.column and AccessDataSource

all I want to do was use Access DataSource with this msdn example and ability to add.column
AccessDataSource Access_DS1 = new AccessDataSource(
"myDB.mdb","Select * from MyTable");


code location
http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.sorting.aspx
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

  protected void TaskGridView_Sorting(object sender, GridViewSortEventArgs e)
  {

    //Retrieve the table from the session object.
    DataTable dt = Session["TaskTable"] as DataTable;

    if (dt != null)
    {

      //Sort the data.
      dt.DefaultView.Sort = e.SortExpression + " " + GetSortDirection(e.SortExpression);
      TaskGridView.DataSource = Session["TaskTable"];
      TaskGridView.DataBind();
    }

  }

  private string GetSortDirection(string column)
  {

    // By default, set the sort direction to ascending.
    string sortDirection = "ASC";

    // Retrieve the last column that was sorted.
    string sortExpression = ViewState["SortExpression"] as string;

    if (sortExpression != null)
    {
      // Check if the same column is being sorted.
      // Otherwise, the default value can be returned.
      if (sortExpression == column)
      {
        string lastDirection = ViewState["SortDirection"] as string;
        if ((lastDirection != null) && (lastDirection == "ASC"))
        {
          sortDirection = "DESC";
        }
      }
    }

    // Save new values in ViewState.
    ViewState["SortDirection"] = sortDirection;
    ViewState["SortExpression"] = column;

    return sortDirection;
  }

  protected void Page_Load(object sender, EventArgs e)
  {

    if (!Page.IsPostBack)
    {

      // Create a new table.
      DataTable taskTable = new DataTable("TaskList");

      // Create the columns.
      taskTable.Columns.Add("Id", typeof(int));
      taskTable.Columns.Add("Description", typeof(string));

      //Add data to the new table.
      for (int i = 0; i < 10; i++)
      {
        DataRow tableRow = taskTable.NewRow();
        tableRow["Id"] = i;
        tableRow["Description"] = "Task " + (10 - i).ToString();
        taskTable.Rows.Add(tableRow);
      }

      //Persist the table in the Session object.
      Session["TaskTable"] = taskTable;

      //Bind the GridView control to the data source.
      TaskGridView.DataSource = Session["TaskTable"];
      TaskGridView.DataBind();

    }

  }

</script>

<html >
<head runat="server">
    <title>Sorting example</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

      <asp:GridView ID="TaskGridView" runat="server"
        AllowSorting="true"
        OnSorting="TaskGridView_Sorting" >
      </asp:GridView>

    </div>
    </form>
</body>
</html>
goodkAsked:
Who is Participating?
 
goodkAuthor Commented:

 
I tried the following and it works except sort - please help! thanks. bye

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data.OleDb" %>

<%@ Page AutoEventWireup="true" Culture="auto" ErrorPage="~/Prod/Problem.htm" Language="C#"
MasterPageFile="~/MasterPage.master" Theme="GridView" Title="Sure Accounting" UICulture="auto" %>


<script runat="server">



void Page_Load()
{
myGridView.PageIndexChanging += myGridView_PageIndexChanging;
myGridView.Sorting += myGridView_Sorting;
if (IsPostBack)
{
Session.Add("PageName", "AnyTable");
}
else
{
SetSQLOnChange("PostBack");

}
}
protected void myGridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
myGridView.PageIndex = e.NewPageIndex;
myGridView.DataSource = Session["mGridView"];
myGridView.DataBind();
}

protected void myGridView_Sorting(object sender, GridViewSortEventArgs e)
{
//Retrieve the table from the session object.
DataTable dt = Session["mGridView"] as DataTable;
if (dt != null)
{
//Sort the data.
dt.DefaultView.Sort = e.SortExpression + " " + GetSortDirection(e.SortExpression);
myGridView.DataSource = Session["mGridView"];
myGridView.DataBind();
}
}
private string GetSortDirection(string column)
{
// By default, set the sort direction to ascending.
string sortDirection = "ASC";
// Retrieve the last column that was sorted.
string sortExpression = ViewState["SortExpression"] as string;
if (sortExpression != null)
{
// Check if the same column is being sorted.
// Otherwise, the default value can be returned.
if (sortExpression == column)
{
string lastDirection = ViewState["SortDirection"] as string;
if ((lastDirection != null) && (lastDirection == "ASC"))
{
sortDirection = "DESC";
}
}
}
// Save new values in ViewState.
ViewState["SortDirection"] = sortDirection;
ViewState["SortExpression"] = column;
return sortDirection;
}

void SetSQLOnChange(string xPostBack)
{
// Response.Write(" In SetSQLOnChange tempStringSelected " + tempStringSelected);
// AccessDataSource xAccountDataSource = new AccessDataSource();
string connString = AceClasses.GetConnString();


AccessDataSource Access_Data_Source1 = new AccessDataSource(
"~/App_Data/AccountInfo_Nov14_2009.mdb","Select * from AceData");

myGridView.DataSource = Access_Data_Source1;
Session["mGridView"] = myGridView.DataSource;

myGridView.AutoGenerateColumns = false;

myGridView.Columns.Add(CodeClass.CreateBoundField("OutDebit", "OutDebit", " OutDebit ", "money", ""));
myGridView.Columns.Add(CodeClass.CreateBoundField("FinancialEntity", "FinancialEntity", " FinancialEntity ", "", ""));

myGridView.DataBind();
}


</script>


<asp:Content ID="Content1" runat="Server" ContentPlaceHolderID="ContentPlaceHolder1">




<div>

<asp:SqlDataSource
id="SqlDS"
runat="server" />
<asp:AccessDataSource ID="AccessDS" runat="server"></asp:AccessDataSource>

<asp:GridView ID="myGridView" AllowSorting = "true" AllowPaging = "true" runat="server">
</asp:GridView>

</div>

</asp:Content>

0
 
madhevan_pillaiCommented:
hi try this


aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridViewCustom].aspx.cs" Inherits="GridViewCustom_" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
   
    <asp:GridView ID="myGridView" runat="server">
    </asp:GridView>
    </div>
    </form>
</body>
</html>



codebehind

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
public partial class GridViewCustom_ : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        myGridView.PageIndexChanging += myGridView_PageIndexChanging;
        myGridView.Sorting += myGridView_Sorting;
        if (!this.IsPostBack)
        {
            SetGridData();
            myGridView.DataSource = Data(null);
            myGridView.DataBind();
        }



    }
    protected AccessDataSource Data(string order)
    {
        string sql = "select * from person ";

            if(order != null)
                sql= sql + order;
            else if (ViewState["data"] != null)
            {
                sql = sql + ViewState["data"].ToString();
            }


        AccessDataSource asc = new AccessDataSource("E:\\Temp\\Test.mdb",sql);
        //DataTable dt= new DataTable();
        //dt.Columns.Add("Id");
        //dt.Columns.Add("Name");

        //dt.Rows.Add(new object[] {1,"a" });
        //dt.Rows.Add(new object[] { 2, "b" });
        //dt.Rows.Add(new object[] { 3, "c" });
        //dt.Rows.Add(new object[] { 4, "d" });
        //dt.Rows.Add(new object[] { 5, "e" });
        //dt.Rows.Add(new object[] { 6, "f" });
        //dt.Rows.Add(new object[] { 7, "g" });
        //dt.Rows.Add(new object[] { 8, "h" });
        //dt.Rows.Add(new object[] { 9, "i" });
         
       return asc;
    }






    private void SetGridData()
    {
     
        myGridView.AllowSorting = true;
        myGridView.AllowPaging = true;
        myGridView.PageSize = 3;
        myGridView.AutoGenerateColumns = false;


        BoundField one = new BoundField();
        BoundField two = new BoundField();

        one.DataField = "Id";
        one.HeaderText = "Id";
        one.SortExpression = "Id";
       
        two.DataField = "Name";
        two.HeaderText = "Name";
        two.SortExpression = "Name";

       
         
        myGridView.Columns.Add(one);
        myGridView.Columns.Add(two);
       
    }

    protected void myGridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        myGridView.PageIndex = e.NewPageIndex;
         myGridView.DataSource = Data(null);
        myGridView.DataBind();
    }
    protected void myGridView_Sorting(object sender, GridViewSortEventArgs e)
    {
          ViewState["data"] ="order by " + e.SortExpression + "  " + GetSortDirection(e.SortExpression) ;
          myGridView.DataSource = Data(ViewState["data"].ToString() );
        myGridView.DataBind();
         
    }

    private string GetSortDirection(string column)
    {

       
        string sortDirection = "ASC";

         string sortExpression = ViewState["SortExpression"] as string;

        if (sortExpression != null)
        {
             if (sortExpression == column)
            {
                string lastDirection = ViewState["SortDirection"] as string;
                if ((lastDirection != null) && (lastDirection == "ASC"))
                {
                    sortDirection = "DESC";
                }
            }
        }

         ViewState["SortDirection"] = sortDirection;
        ViewState["SortExpression"] = column;

        return sortDirection;
    }

}
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
madhevan_pillaiCommented:
please go thru this

    protected AccessDataSource Data(string order)
    {
        string sql = "select * from person ";

            if(order != null)
                sql= sql + order;
            else if (ViewState["data"] != null)
            {
                sql = sql + ViewState["data"].ToString();
            }


        AccessDataSource asc = new AccessDataSource("E:\\Temp\\Test.mdb",sql);
        //DataTable dt= new DataTable();
        //dt.Columns.Add("Id");
        //dt.Columns.Add("Name");

        //dt.Rows.Add(new object[] {1,"a" });
        //dt.Rows.Add(new object[] { 2, "b" });
        //dt.Rows.Add(new object[] { 3, "c" });
        //dt.Rows.Add(new object[] { 4, "d" });
        //dt.Rows.Add(new object[] { 5, "e" });
        //dt.Rows.Add(new object[] { 6, "f" });
        //dt.Rows.Add(new object[] { 7, "g" });
        //dt.Rows.Add(new object[] { 8, "h" });
        //dt.Rows.Add(new object[] { 9, "i" });
         
       return asc;
    }


    protected void myGridView_Sorting(object sender, GridViewSortEventArgs e)
    {
          ViewState["data"] ="order by " + e.SortExpression + "  " + GetSortDirection(e.SortExpression) ;
          myGridView.DataSource = Data(ViewState["data"].ToString() );
        myGridView.DataBind();
         
    }
0
 
Bob LearnedCommented:
If you have a GridView and an AccessDataSource, and you set GridView.AllowSorting, then the GridView and the AccessDataSource should automatically handle the sorting, so that you don't have to add an event handler for the Sorting event.
0
 
goodkAuthor Commented:
Hi TheLearnedOne,
That is how it should work but when you do add.column (programatically ) then it stops working that way and you have to handle sort and pagination.  I was having trouble with handling sort.  The pagination is working fine.
 
 
0
 
Bob LearnedCommented:
Do you need dynamic columns?  Do you need to define them in a specific way, or can you use auto-generated columns?
0
 
goodkAuthor Commented:
thanks,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.