Display columns from 2 tables in datagrid HELP!!!!!!!!!!!

[Lerp]

Hi all,

I have been trying to get this to work for over a week now and have not been
able to get it I am attempting to display data from 2 tables in one
datagrid using my ClientBookings relation ship but am having trouble
extracting the data and replacing the id field with the fname value. How
do you reference a parent row in every iteration of the grid...do I need to
create a function to replace the values????

Thank you for your help, Lerp


The problem area below is marked with ************************

I am getting the following error ; 'DataRow' is a type in 'Data' and cannot
be used as an expression.



Here's My Code:

<script language="vb" runat="server">
Dim ds as DataSet = New DataSet()

SUB Page_Load(Sender As Object, E As EventArgs)

IF NOT Page.IsPostBack THEN
BindData()
END IF

END SUB




SUB BindData()

Dim curAgentID as Integer
Dim curAgencyID as Integer
Dim curAgencyName as String
Dim curAgentName as String
Dim PageTitle as String
Dim curSecLevel as String
Dim curGroupID as Integer
Dim curStatus as String
Dim strSQLa as String
Dim strSQLb as String
Dim strSQLc as String


curAgentID = Session("sesempId")
curAgencyID = Session("sesempAgencyid")
curAgencyName = Session("sesempAgencyname")
PageTitle = "Agency Bookings"
curSecLevel = Session("sesempSecurity")
curGroupID = Session("sesempGroupid")



'AGENCY BOOKINGS QUERY
strSQLa = "SELECT bookingid, clientid, agencyid, empid, arn,
bookingdatetimestamp, status FROM BOOKING WHERE agencyid =" & curAgencyID &
" AND status = 'Final'"
'CLIENT NAME QUERY
strSQLb = "SELECT clientid, fname, lname FROM CLIENT WHERE agencyid=" &
curAgencyID
'EMPLOYEE NAME QUERY
strSQLc = "SELECT empid, fname, lname FROM EMPLOYEE WHERE agencyid =" &
curAgencyID


'FILL BOOKINGS
Dim MyConn as New SQLConnection(ConfigurationSettings.AppSettings("dbConn"))
Dim myCmd as New SqlDataAdapter(strSQLa, MyConn)
myCmd.fill(ds, "BOOKING")

'FILL CLIENTS
Dim myCmdb as New SqlDataAdapter(strSQLb, MyConn)
myCmdb.fill(ds, "CLIENT")

'FILL EMPLOYEES
Dim myCmdc as New SqlDataAdapter(strSQLc, MyConn)
myCmdc.fill(ds, "EMPLOYEE")



IF ds.Tables(1).Rows.Count > 0 THEN

'SET UP TABLE RELATIONS HERE
Dim datrela as New DataRelation("ClientBookings",
ds.Tables("CLIENT").Columns("clientid"),
ds.Tables("BOOKING").Columns("clientid"))
'add relation to collection
ds.Relations.Add(datrela)


Dim datrelb as New DataRelation("EmployeeBookings",
ds.Tables("EMPLOYEE").Columns("empid"),
ds.Tables("BOOKING").Columns("empid"))
'add relation to collection
ds.Relations.Add(datrelb)


bookingspanel.visible = true
nobookingspanel.visible = false


'BIND DATA TO DATALIST
dgBookings.DataSource = ds
dgBookings.DataBind()
myConn.close


mylabel.Text = ds.Tables(0).Rows.Count
mylabel.visible = true

ELSE


'SET LABEL MESSAGE HERE - NO BOOKINGS CURRENTLY, ETC...
mylabel.Text = "0"
mylabel.visible = true
bookingspanel.visible = false
nobookingspanel.visible = true

END IF


END SUB



'HANDLER FOR PAGING
Sub dgBookings_PageIndexChanged(sender as Object, e as
DataGridPageChangedEventArgs)
dgBookings.CurrentPageIndex = e.NewPageIndex
BindData()
End Sub

</script>









<!--- PANEL --->

<aspanel ID="bookingspanel" runat="server">


<asp:datagrid ID="dgBookings" runat="server"
DataKeyfield="bookingid"
BorderColor="#CCCCCC"
GridLines="Horizontal"
Cellpadding="2"
cellspacing="0"
width="790"
Font-Names="Arial"
Font-Size="8pt"
ShowFooter="true"
HeaderStyle-forecolor="#000000"
HeaderStyle-backcolor="#FFFFFF"
ItemStyle-forecolor="#000000"
ItemStyle-backcolor="#C6EFF7"
AlternatingItemStyle-backcolor="#FFFFFF"
Autogeneratecolumns="false"
AllowPaging="true"
PageSize="25"
PagerStyle-Mode="NumericPages"
PagerStyle-PageButtonCount="2"
OnPageIndexChanged="dgBookings_PageIndexChanged">

<Columns>




<asp:boundcolumn HeaderText="AgentReferencrNumber" DataField="arn"/>
<asp:boundcolumn HeaderText="Status" DataField="status"/>
<asp:boundcolumn HeaderText="Booking Date"
DataField="bookingdatetimestamp"/>


<asp:templatecolumn HeaderText="Client">
<itemtemplate>


<asp:label ID="clientfname" text='<%#
Container.DataItem,System.Data.DataRow).GetParentRow("ClientBookings")("fnam
e") %>' runat="server"/> *************************


</itemtemplate>
</asp:templatecolumn>


<asp:TemplateColumn HeaderText="Booking Actions">
<ItemTemplate>
<asp:Hyperlink runat="server" ImageUrl="graphic/edit.gif" ToolTip="Edit
Booking" NavigateUrl='<%# "bookingedit.aspx?bookingid=" &
Server.UrlEncode(Container.DataItem("bookingid"))%>'/>&nbsp;
<asp:Hyperlink runat="server" ImageUrl="graphic/details.gif"
ToolTip="Booking Details" NavigateUrl='<%# "bookingdetails.aspx?bookingid="
& Server.UrlEncode(Container.DataItem("bookingid"))%>'/>&nbsp;
<asp:Hyperlink runat="server" ImageUrl="graphic/delete.gif"
ToolTip="Delete Booking" NavigateUrl='<%# "bookdel.aspx?bookingid=" &
Server.UrlEncode(Container.DataItem("bookingid"))%>'/>&nbsp;
</ItemTemplate>
</asp:TemplateColumn>



</Columns>

</asp:datagrid>







</aspanel>

 

[Lerp]

Nm, I figured it out....

I created a sub that runs onitemdatabound event of the grid and used the
relations to replace the existing labels with the fields I needed.

Cheers, Joe