Datagrid Hyperlink

skywalker1981 · April 2nd, 2004, 11:32 AM

Hi I am using the datagrid control to display data from my sql server db. The sql i am using to place data into the datagrid is like so:

----
select
LTRIM(RTRIM(wis_quarter))
, substring(wis_group, 1,2)
, round(avg(wis_cases_owned),2)
, round(avg(wis_perc_closed),2)
, round(avg(wis_perc_close_24),2)
, round(avg(wis_perc_reopen),2)
, round(avg(wis_ave_days_resolv),2)
, round(avg(wis_ave_days_status),2)
, round(avg(wis_ia),2)
, round(avg(wis_gap),2)
, round(avg(wis_ipi_score),2)

FROM web_indi_scores

WHERE SUBSTRING(wis_group,1,2) in ('NE','CE','SE')
--AND wis_from_date != (SELECT max(wis_from_date) FROM web_indi_scores)

GROUP BY wis_quarter, substring(wis_group, 1,2)

ORDER BY substring(wis_group, 1,2)
----

Now this data fills the datagrid with stats data based on region and quarter. I want to be able to use the lefthand column as a hyperlink to lower level data, which displays teams within a certain region by quarter. If i use property builder on visual studio.net to create such a hyperlink, i know you can pass a variable into another page so that i use it in my select statement on that page.

The problem I am having is that i want to pass more than one variable. I have heard something regarding a template column to pass more than one variable but i am a beginner with visual studio.net and require some help.

It would be very much appreciated if someone to could shed some light on this matter.

Thanks in advance, Luke

Yeeeeeha cowboy!

skywalker1981 · April 4th, 2004, 06:37 AM

Ok, well I have been fiddling around with the template column and have now got this:

<html>
<head><title>DataGridTemplate.aspx</title></head>
<body>
<form Runat="Server" ID="Form1">

<asp:DataGrid
  ID="dgrdAuthors"
  AutoGenerateColumns="False"
  EnableViewState="False"
  ShowHeader="False"
  CellPadding="10"
  Runat="Server">

<Columns>
  <asp:TemplateColumn>
    <itemTemplate>
    <table>
    <tr>
     <td><a href='mainindex.aspx?id=<%# DataBinder.Eval(Container.DataItem, "wis_group")%>&Other=<%# DataBinder.Eval(Container.DataItem, "wis_group") %>'><%# DataBinder.Eval(Container.DataItem, "wis_group") %></a></td>
    </tr>
    </table>
    </itemTemplate>
  </asp:TemplateColumn>

    <asp:BoundColumn
    DataField="wis_quarter" />

</Columns>

</asp:DataGrid>

</form>
</body>
</html>

Ok this allows me to pass more than one variable over to the maindex.aspx page as you can see from the code. Now in this example i am using a simple "select * from web_indi_scores" and the output produces a table with a hyperlink column which passes over the intended variables.

However, I now have the problem that when I change the select statement to more complex sql I generate an error. The select statement i want to use is :

select
LTRIM(RTRIM(wis_quarter)) + ' ' + substring(wis_group, 1,2)
, round(avg(wis_cases_owned),2)
, round(avg(wis_perc_closed),2)
, round(avg(wis_perc_close_24),2)
, round(avg(wis_perc_reopen),2)
, round(avg(wis_ave_days_resolv),2)
, round(avg(wis_ave_days_status),2)
, round(avg(wis_ia),2)
, round(avg(wis_gap),2)
, round(avg(wis_ipi_score),2)

FROM web_indi_scores

WHERE SUBSTRING(wis_group,1,2) in ('NE','CE','SE')

GROUP BY LTRIM(RTRIM(wis_quarter)) + ' ' + substring(wis_group, 1,2)

ORDER BY LTRIM(RTRIM(wis_quarter)) + ' ' + substring(wis_group, 1,2)

This sql runs fine in sql query analyser. Ok im pretty sure the problem relates to how i have joined the columns from the sql statement. So lets say I change the select statement to the more complex one, and I want to pass over the variable of the joined columns in the select statement ( LTRIM(RTRIM(wis_quarter)) + ' ' + substring(wis_group, 1,2) ) - my question is how do i pass over a variable of a joined column in the code. I am doing this for a project and trying to learn ASP.NET and VB.NET in the process, so i am completely new to all this. For testing if i leave "wis_group" as the dataitem then i generate the error:

DataBinder.Eval: 'System.Data.Common.DbDataRecord' does not contain a property with the name wis_group.

Of course, I dont want to pass the variable "wis_group" but I wanted to see the result. Now obviously because i have joined wis_group with wis_quarter to produce a single column im guessing that vb cant find wis_group as a single dataitem. So again how do i successfully pass over the variable of the joined data items of :

LTRIM(RTRIM(wis_quarter)) + ' ' + substring(wis_group, 1,2)

I hope im being clear enough.

Many thanks in advance, Luke

Yeeeeeha cowboy!

planoie · April 5th, 2004, 06:22 AM

I think the problem is simply due to column aliases. Any time you have a computed column, you loose the field name so just provide them:

Select field1+field as Column1, field2+field3 as column2

Now in your template you need only reference the column aliases...

<td><a href='mainindex.aspx?id=<%# DataBinder.Eval(Container.DataItem, "column1")%>&Other=<%# DataBinder.Eval(Container.DataItem, "column2") %>'><%# DataBinder.Eval(Container.DataItem, "column3") %></a></td>