Thx guys for replying, I tried getting my sub to show the client full name
Rick, but had trouble getting the value.
I appreciate you help guys I really do.
Dale the client's full name is is the CLIENT table and I have a clientid in
the BOOKING table. My datalist is displaying fine, except I am unsure how
to get at the 'Parent Info' syntax wise.
I also thought that I might be able to easily access this data via the
datarelation but had difficulty doing that as well.
I know I am close, but here goes:
<%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1"
debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<script language="vb" runat="server">
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
Dim ds as DataSet = New DataSet()
curAgentID = Session("sesempId")
curAgencyID = Session("sesempAgencyid")
curAgencyName = Session("sesempAgencyname")
PageTitle = "Agency Bookings"
curSecLevel = Session("sesempSecurity")
curGroupID = Session("sesempGroupid")
'AGENT BOOKINGS QUERY
strSQLa = "SELECT bookingid, clientid, agencyid, empid, arn,
bookingdatetimestamp, status FROM BOOKING WHERE agencyid =" & curAgencyID &
" AND empid=" & curAgentID & " AND status = 'Draft'"
'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("d bConn"))
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")
'response.write (ds.Tables(0).Rows.Count)
'response.write (curAgencyID)
'IF THERE ARENT ANY CLIENTS WITH BOOKINGS DO NOT DISPLAY !!!! SIMPLE MESSAGE
TO USER HERE STATING NO BOOKINGS IE: PANEL
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
dlBookings.DataSource = ds
dlBookings.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
'THIS SUB HANDLES GRABBING CLIENT AND EMPLOYEE NAMES (for now only
clientnames)
Sub dlBookings_OnItemDataBound(sender As Object, e As DataListItemEventArgs)
If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType =
ListItemType.AlternatingItem Then
' Retrieve the Label control in the current DataListItem.
Dim ClientNameLabel As Label = e.Item.FindControl("clientnamelabel")
Dim curClientID as Integer = e.item.FindControl("clientid").Text
' GRAB client name using ID from db here
Dim clientlabel As String
Dim strSQL as String
'SQL STATEMENT
strSQL = "SELECT fullname FROM CLIENT WHERE clientid=" & curClientID
'NEW CONNECTION OBJECT
Dim MyConn as New
SQLConnection(ConfigurationSettings.AppSettings("d bConn"))
'NEW DATAREADER
Dim objDR as SQLDataReader
'NEW COMMAND OBJECT
Dim Cmd as New SQLCommand(strSQL, MyConn)
'OPEN CONNECTION
MyConn.Open()
'EXECUTE QUERY AND RETRIEVE DATA INTO READER
objDR = Cmd.ExecuteReader(system.data.CommandBehavior.Clos eConnection)
WHILE objDR.Read
clientlabel = objDR("fullname")
END While
myConn.close
'redisplay it in the DataList.
ClientNameLabel.Text = clientlabel.ToString()
End If
End Sub
</script>
<html>
<head>
<title>AgentShopper</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="css/todd.css" rel="stylesheet" type="text/css">
<!-- #include file="topsec.aspx" -->
<!--- START OF BOOKINGS PANEL--->
<form runat="server">
<table width="790" border="0" cellpadding="0" cellspacing="0">
<tr valign="top">
<td align="left" colspan="5" class="title"><%= Session("sesempFname") & " "
& Session("sesempLname")%>'s Draft Bookings - <asp:Label ID="mylabel"
runat="server"/></td>
</tr>
<tr height="15">
<td align="left" colspan="5"></td>
</tr>
<asp:panel ID="bookingspanel" runat="server">
<asp:DataList id="dlBookings"
runat="server"
cellpadding="3"
cellspacing="3"
GridLines="Both"
borderstyle="none"
backcolor="#FFFFFF"
width="790px"
headerstyle-font-name="Verdana"
headerstyle-font-size="13pt"
headerstyle-horizontalalign="left"
headerstyle-font-bold="TRUE"
itemstyle-backcolor="#FFFFFF"
itemstyle-forecolor="#000000"
alternatingitemstyle-backcolor="#C6EFF7"
alternatingitemstyle-forecolor="#FFFFFF"
footerstyle-font-size="9pt"
footerstyle-font-italic="true"
OnItemDataBound="dlBookings_OnItemDataBound">
<HeaderTemplate>
<tr valign="top" bgcolor="#CCCCCC">
<td align="left" class="textbox">Agent Reference Number</td>
<td align="left" class="textbox">Status</td>
<td align="left" class="textbox">Booking Date</td>
<td align="left" class="textbox">Agent</td>
<td align="left" class="textbox">Client</td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr valign="top">
<td align="left" class="textbox"><%#Container.DataItem("arn")%></td>
<td align="left" class="textbox"><%#Container.DataItem("status")%></td>
<td align="left"
class="textbox"><%#Container.DataItem("bookingdate timestamp")%></td>
<td align="left" class="textbox"></td>
<td align="left" class="textbox"><asp:Label id="clientnamelabel"
runat="server"/></td>
</tr>
</ItemTemplate>
<AlternatingItemTemplate>
<tr valign="top">
<td align="left" class="textbox"><%#Container.DataItem("arn")%></td>
<td align="left" class="textbox"><%#Container.DataItem("status")%></td>
<td align="left"
class="textbox"><%#Container.DataItem("bookingdate timestamp")%></td>
<td align="left" class="textbox"></td>
<td align="left" class="textbox"><asp:Label id="clientnamelabel"
runat="server"/></td>
</tr>
</AlternatingItemTemplate>
<FooterTemplate>
<tr height="25">
<td align="left" colspan="5"></td>
</tr>
<tr valign="top">
<td align="left" colspan="5" class="textbox"></td>
</tr>
</table>
</FooterTemplate>
</asp:DataList>
<!--- END OF BOOKINGS PANEL --->
</asp:panel>
<!--- START OF NOBOOKINGS PANEL--->
<asp:panel ID="nobookingspanel" runat="server">
<tr valign="top">
<td align="left" class="bodycopy">
<table width="790" cellpadding="5" cellspacing="0" border="0"
bordercolor="#CCCCCC">
<tr valign="top">
<td align="left" class="textbox"><%= Session("sesempFname") & " " &
Session("sesempLname")%> has 0 draft bookings currently.</td>
</tr>
</table>
</td>
</tr>
<!--- END OF NOBOOKINGS PANEL --->
</asp:panel>
</form>
"DalePres" <no****@nomail.com> wrote in message
news:eb**************@TK2MSFTNGP12.phx.gbl...
It isn't clear but it sounds like you need to do one of two things. If
you're already showing the ID column, it's trivial to change to the
company name column if both columns are in the same table. If they are in two
different tables and you're already displaying the company ID from the
child, you would want to use GetParentRow.
Post some sample code or more information.
Dale
"Lerp" <ad***@officience.ca> wrote in message
news:ev**************@TK2MSFTNGP09.phx.gbl... I have a dataset that is made up of info from 2 tables. I have created a
relationship between these 2 and have added it to the dataset.
My datalist is working perfectly minus one little problem, the very last
field in my itemtemplate is an ID field from the child table. I would
like to display the Name of the company as opposed to the ID. Is there a way
to do this? I have read that there is a method called GetChildRows, but I
am unsure if this is what I need. How can I show the name instead?
Thank you, Lerp
