Order Columns in bound Datagrid

[saynords]

Hi, I have a bound datagrid in c# using the code below. My problem is that I need to add an extra column as the last column presented which creates a hyperlinkfield using the value of ExpedioRef attached to a URL.

I have tried t do this in the design view of the datagrid using the wizard to add the new column 'History' and field 'view' with value of expedioref but it places the column at the beginning every time. Please could you tell me how to get this column to appear on the left of the other columns, not before.

Alternatively if would be better to use the already ato generated ExpedioRef field i'm returning and add a hyperlink to it, but don't know how to do this.

I can't use the normal datagrid because I am passing in a variable as parameter in the SQL string.

Thanks
Debs



OracleConnectio n con = new OracleConnectio n();
con.ConnectionS tring = ConfigurationMa nager.Connectio nStrings["NewExpedioDevS tring"].ConnectionStri ng;
string cmdQuery = "SELECT R_EXPEDIOREFERE NCE ExpedioRef,R_CU STOMERREFERENCE CustomerRef,R_R EQUESTSTATUS Status, R_REQUESTTASK Task FROM aradmin.exp__re quest WHERE (r_source = 'KANA' AND BFG_CONTRACTID= '" + Session["BFGCONTRAC TID"].ToString() + "')";

OracleCommand cmd = new OracleCommand(c mdQuery);
try
{
con.Open();
cmd.Connection = con;
cmd.CommandType = CommandType.Tex t;
OracleDataReade r reader = cmd.ExecuteRead er();
dtgOpenOrders.D ataSource = reader;
dtgOpenOrders.D ataBind();

[Plater]

In your SQL statement, have the SELECT return an extra column. That extra column will be your scratch space column and you can then play with/overwrite the data in it.

PS: Unless you're using .NET older then 2.0, you should be using the DataGridView object.

[saynords]

In your SQL statement, have the SELECT return an extra column. That extra column will be your scratch space column and you can then play with/overwrite the data in it.

PS: Unless you're using .NET older then 2.0, you should be using the DataGridView object.

Hi thanks for such a quick response, yes sorry I am using the gridview object. Could you give me an example of how to do the above (sorry to be a pain - new to all this)
thanks
Debs

[Plater]

You have an SQL statement that selects say X number of columns (in your case it appears to be 4), just request one of the columns a 2nd time.
I named it SpareColumn below, but you could name it something more suitable to your needs.

OracleConnection con = new OracleConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings["NewExpedioDevString"].ConnectionString;
string cmdQuery = "SELECT 
R_EXPEDIOREFERENCE ExpedioRef,
R_CUSTOMERREFERENCE CustomerRef,
R_REQUESTSTATUS Status, 
R_REQUESTTASK Task,
R_EXPEDIOREFERENCE SpareColumn,
FROM aradmin.exp__request WHERE (r_source = 'KANA' AND BFG_CONTRACTID='" + Session["BFGCONTRACTID"].ToString() + "')";
 
OracleCommand cmd = new OracleCommand(cmdQuery);
try
{
con.Open();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
 

Now you will have an extra column to use as scratch space.
I recomend you transfer into a DataSet (or DataTable) instead of using a datareader.
That way you can manipulate your DataSet before setting it as the datasource for your gridview object.

Alternatively, you can edit the gridview object after the datasource has been applied. You will have that extra column in there to toy with. (I use this in my own project to a pretty good success)

[saynords]

You have an SQL statement that selects say X number of columns (in your case it appears to be 4), just request one of the columns a 2nd time.
I named it SpareColumn below, but you could name it something more suitable to your needs.

OracleConnection con = new OracleConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings["NewExpedioDevString"].ConnectionString;
string cmdQuery = "SELECT 
R_EXPEDIOREFERENCE ExpedioRef,
R_CUSTOMERREFERENCE CustomerRef,
R_REQUESTSTATUS Status, 
R_REQUESTTASK Task,
R_EXPEDIOREFERENCE SpareColumn,
FROM aradmin.exp__request WHERE (r_source = 'KANA' AND BFG_CONTRACTID='" + Session["BFGCONTRACTID"].ToString() + "')";
 
OracleCommand cmd = new OracleCommand(cmdQuery);
try
{
con.Open();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
 

Now you will have an extra column to use as scratch space.
I recomend you transfer into a DataSet (or DataTable) instead of using a datareader.
That way you can manipulate your DataSet before setting it as the datasource for your gridview object.

Alternatively, you can edit the gridview object after the datasource has been applied. You will have that extra column in there to toy with. (I use this in my own project to a pretty good success)

OK sounds like the second option for me,
the following is my gridview object & binding, could you give me an example of how to edit this object to add a hyerlink to the new SpareColumn fields please, sorry to ask for more but really am stuck on this one. Got some training booked for next week so will leave you alone then ;O)

dtgOpenOrders.D ataSource = reader;
dtgOpenOrders.D ataBind();

[Plater]

Well I am sure there is a cleaner way to do this, try something like this:

protected void procAddingHyperLink()
{
    string msg = "";
    string href = "ManagerSiteInfo.aspx?siteid=";
    int retdateidx = -1;
    if (dtgOpenOrders.HeaderRow != null)
    {
        for (int i = 0; i < gvOverdueSites.HeaderRow.Cells.Count; i++)
        {
            if (dtgOpenOrders.HeaderRow.Cells[i].Text == "Spare Column")
            {//find the column index with that name
                retdateidx = i;
            }
        }
        if (retdateidx!=-1)
        {
          for (int i = 0; i < gvOverdueSites.Rows.Count; i++)
          {
                //this would pull the text from the first column
                msg = dtgOpenOrders.Rows[i].Cells[0].Text;
                //create a hyperlink object
                HyperLink hl = new HyperLink();
                hl.Text = msg;
                hl.NavigateUrl = href + msg.Replace("&amp;", "&");
                dtgOpenOrders.Rows[i].Cells[retdateidx].Controls.Add(hl);
          }//end of forloop
      }
    }
}
 

[saynords]

Well I am sure there is a cleaner way to do this, try something like this:

protected void procAddingHyperLink()
{
    string msg = "";
    string href = "ManagerSiteInfo.aspx?siteid=";
    int retdateidx = -1;
    if (dtgOpenOrders.HeaderRow != null)
    {
        for (int i = 0; i < gvOverdueSites.HeaderRow.Cells.Count; i++)
        {
            if (dtgOpenOrders.HeaderRow.Cells[i].Text == "Spare Column")
            {//find the column index with that name
                retdateidx = i;
            }
        }
        if (retdateidx!=-1)
        {
          for (int i = 0; i < gvOverdueSites.Rows.Count; i++)
          {
                //this would pull the text from the first column
                msg = dtgOpenOrders.Rows[i].Cells[0].Text;
                //create a hyperlink object
                HyperLink hl = new HyperLink();
                hl.Text = msg;
                hl.NavigateUrl = href + msg.Replace("&amp;", "&");
                dtgOpenOrders.Rows[i].Cells[retdateidx].Controls.Add(hl);
          }//end of forloop
      }
    }
}
 

You are an absolute superstar thank you so much, works a treat - think I might have to marry you.
;O)

[saynords]

Next question - how do i add pageing & Sorting to this? (it's nto available int he design view wizard mode of gridview.

Thanks
Debs

[Plater]

There is a property in the properies window where you turn this on.
You will need to assign an event for it (it will help you with it)

[saynords]

There is a property in the properies window where you turn this on.
You will need to assign an event for it (it will help you with it)

I get a system error when i use properties to add paging:

dataSystem.NotS upportedExcepti on: The data source does not support server-side data paging. at System.Web.UI.D ataSourceView.R aiseUnsupported CapabilityError (DataSourceCapa bilities capability) at System.Web.UI.D ataSourceSelect Arguments.Raise UnsupportedCapa bilitiesError(D ataSourceView view) at System.Web.UI.W ebControls.Read OnlyDataSourceV iew.ExecuteSele ct(DataSourceSe lectArguments arguments) at System.Web.UI.D ataSourceView.S elect(DataSourc eSelectArgument s arguments, DataSourceViewS electCallback callback) at System.Web.UI.W ebControls.Grid View.CreateChil dControls(IEnum erable dataSource, Boolean dataBinding) at System.Web.UI.W ebControls.Comp ositeDataBoundC ontrol.PerformD ataBinding(IEnu merable data) at



Any ideas ?
Thanks
Debs