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



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 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;
OracleDataReader reader = cmd.ExecuteReader();
dtgOpenOrders.DataSource = reader;
dtgOpenOrders.DataBind();

[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.DataSource = reader;
dtgOpenOrders.DataBind();

[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.NotSupportedException: The data source does not support server-side data paging. at System.Web.UI.DataSourceView.RaiseUnsupportedCapab ilityError(DataSourceCapabilities capability) at System.Web.UI.DataSourceSelectArguments.RaiseUnsup portedCapabilitiesError(DataSourceView view) at System.Web.UI.WebControls.ReadOnlyDataSourceView.E xecuteSelect(DataSourceSelectArguments arguments) at System.Web.UI.DataSourceView.Select(DataSourceSele ctArguments arguments, DataSourceViewSelectCallback callback) at System.Web.UI.WebControls.GridView.CreateChildCont rols(IEnumerable dataSource, Boolean dataBinding) at System.Web.UI.WebControls.CompositeDataBoundContro l.PerformDataBinding(IEnumerable data) at



Any ideas ?
Thanks
Debs

[Plater]

Strange, what is your datasource object?
I use paging on my DataTable (that I have edited with extra columns just like we discussed) just fine?

[saynords]

This is the code I'm using:

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 History 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;
OracleDataReader reader = cmd.ExecuteReader();
dtgOpenOrders.DataSource = reader;
dtgOpenOrders.DataBind();
dtgOpenOrders.AllowPaging = true;
procAddingHyperLink();
procAddingHyperLink2();
}

catch (Exception ex)
{
Response.Write("There was a problem retrieving your data");
Response.Write(ex);
}

finally
{
cmd.Dispose();
con.Close();
con.Dispose();

[Plater]

Ah ha! You're using DataReader as your datasource.
There is no "seek" ability in datareaders. They can effecitvly only be read through "once".
Use a DataSet or DataTable instead.

[saynords]

Ah ha! You're using DataReader as your datasource.
There is no "seek" ability in datareaders. They can effecitvly only be read through "once".
Use a DataSet or DataTable instead.

I don;t seem to have that option in the toolbox, only DataList ? I'm using C# is that right?
Thanks so much for your help

[saynords]

I don;t seem to have that option in the toolbox, only DataList ? I'm using C# is that right?
Thanks so much for your help

oops ignore the last stooopid comment!

[Plater]

System.Data.DataSet

The namespace is usually already included, so I didn't think to specify it fully.

[saynords]

System.Data.DataSet

The namespace is usually already included, so I didn't think to specify it fully.

Sorry to ask this but could you give me an example of how to convert this code to use a dataset as I've only just got my head around readers! I am a sever novice but I learn fast and wont need to ask twice! thank you for any help you could give and sorry to be a nuisance.


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 History 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;
OracleDataReader reader = cmd.ExecuteReader();
dtgOpenOrders.DataSource = reader;
dtgOpenOrders.DataBind();
dtgOpenOrders.AllowPaging = true;
procAddingHyperLink();
}

catch (Exception ex)
{
Response.Write(ex);
}

finally
{
cmd.Dispose();
con.Close();
con.Dispose();
}

}

public void procAddingHyperLink()
{
string msg = "";
string href = "default.aspx?ERID=";
int retdateidx = -1;
if (dtgOpenOrders.HeaderRow != null)
{
for (int i = 0; i < dtgOpenOrders.HeaderRow.Cells.Count; i++)
{
if (dtgOpenOrders.HeaderRow.Cells[i].Text == "EXPEDIOREF")
{//find the column index with that name
retdateidx = i;
}
}
if (retdateidx != -1)
{
for (int i = 0; i < dtgOpenOrders.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("&", "&");
dtgOpenOrders.Rows[i].Cells[retdateidx].Controls.Add(hl);
}//end of forloop
}
}
}

[Plater]

I don't seem to have the Oracle namespace for those objects, but I would imagine it's something like this:

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 History 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;
OracleDataReader reader = cmd.ExecuteReader();
dtgOpenOrders.DataSource = reader;
*/
DataSet retval=new DataSet();
OracleDataAdapter dba = new OracleDataAdapter(cmdQuery, con);
dba.Fill(retval);
 
dtgOpenOrders.DataSource=retval;
dtgOpenOrders.DataBind();
dtgOpenOrders.AllowPaging = true;
procAddingHyperLink();
}
 
catch (Exception ex)
{
Response.Write(ex);
}
 
finally
{
cmd.Dispose();
con.Close();
con.Dispose();
}
 
}
 

[saynords]

I don't seem to have the Oracle namespace for those objects, but I would imagine it's something like this:

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 History 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;
OracleDataReader reader = cmd.ExecuteReader();
dtgOpenOrders.DataSource = reader;
*/
DataSet retval=new DataSet();
OracleDataAdapter dba = new OracleDataAdapter(cmdQuery, con);
dba.Fill(retval);
 
dtgOpenOrders.DataSource=retval;
dtgOpenOrders.DataBind();
dtgOpenOrders.AllowPaging = true;
procAddingHyperLink();
}
 
catch (Exception ex)
{
Response.Write(ex);
}
 
finally
{
cmd.Dispose();
con.Close();
con.Dispose();
}
 
}
 

That's fantastic thank you ! pageing now works, sorting doesn;t but ican fiddle about with that.
The problem now is my method to add a column no longer reads the dtgOpenOrders.HeaderRow.Cells[i].Text . Te project builds but when I response.write this line it is blank where before it would return the column header.

Sorry to pester you, you've been a massive help already but any idea? is it something staring me in the face?!



public void procAddingHyperLink()
{
string msg = "";
string href = "default.aspx?ERID=";
int retdateidx = -1;
if (dtgOpenOrders.HeaderRow != null)
{
for (int i = 0; i < dtgOpenOrders.HeaderRow.Cells.Count; i++)
{

if (dtgOpenOrders.HeaderRow.Cells[i].Text == "EXPEDIOREF")
{//find the column index with that name
retdateidx = i;
}
}
if (retdateidx != -1)
{
for (int i = 0; i < dtgOpenOrders.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("&", "&");
dtgOpenOrders.Rows[i].Cells[retdateidx].Controls.Add(hl);
}//end of forloop
}
}
}

[Plater]

Are your columns labeled when you load the page?
If they are, there should be something in the HeaderRow.
Have you debuged to see what it claims is in there?

[saynords]

Are your columns labeled when you load the page?
If they are, there should be something in the HeaderRow.
Have you debuged to see what it claims is in there?

I am creating the 2 columns (in bold) via SQL query and changing the contents of the data in that field of each row via the 2 methods which was working ok and it iterates through each cell but returns a blank string when I response.write the line dtgOpenOrders.HeaderRow.Cells[i].Text which is the line that should bring back the columen headers ;O(

string cmdQuery = "SELECT R_EXPEDIOREFERENCE ExpedioRef,R_CUSTOMERREFERENCE CustomerRef,R_REQUESTSTATUS Status, R_REQUESTTASK Task, R_EXPEDIOREFERENCE History FROM aradmin.exp__request WHERE (r_source = 'KANA' AND BFG_CONTRACTID='" + Session["BFGCONTRACTID"].ToString() + "')";