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

From: Lerp (admin_at_officience.ca)
Date: 05/21/04 

Date: Fri, 21 May 2004 10:41:34 -0300

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 :)

"Lerp" <admin@officience.ca> wrote in message
news:#NTljAzPEHA.832@TK2MSFTNGP09.phx.gbl...
> 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 --->
>
> <asp:panel 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>
>
>
>
>
>
>
>
> </asp:panel>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>