Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

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>
0
goodk
Asked:
goodk
  • 3
  • 2
  • 2
  • +1
4 Solutions
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now