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="te xt/html" ResponseEncodin g="iso-8859-1"
debug="true" %>
<%@ Import Namespace="Syst em.Data" %>
<%@ Import Namespace="Syst em.Data.SQLClie nt" %>
<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("sesemp Id")
curAgencyID = Session("sesemp Agencyid")
curAgencyName = Session("sesemp Agencyname")
PageTitle = "Agency Bookings"
curSecLevel = Session("sesemp Security")
curGroupID = Session("sesemp Groupid")
'AGENT BOOKINGS QUERY
strSQLa = "SELECT bookingid, clientid, agencyid, empid, arn,
bookingdatetime stamp, 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(C onfigurationSet tings.AppSettin gs("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")
'response.write (ds.Tables(0).R ows.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).Ro ws.Count > 0 THEN
'SET UP TABLE RELATIONS HERE
Dim datrela as New DataRelation("C lientBookings",
ds.Tables("CLIE NT").Columns("c lientid"),
ds.Tables("BOOK ING").Columns(" clientid"))
'add relation to collection
ds.Relations.Ad d(datrela)
Dim datrelb as New DataRelation("E mployeeBookings ",
ds.Tables("EMPL OYEE").Columns( "empid"),
ds.Tables("BOOK ING").Columns(" empid"))
'add relation to collection
ds.Relations.Ad d(datrelb)
bookingspanel.v isible = true
nobookingspanel .visible = false
'BIND DATA TO DATALIST
dlBookings.Data Source = ds
dlBookings.Data Bind()
myConn.close
mylabel.Text = ds.Tables(0).Ro ws.Count
mylabel.visible = true
ELSE
'SET LABEL MESSAGE HERE - NO BOOKINGS CURRENTLY, ETC...
mylabel.Text = "0"
mylabel.visible = true
bookingspanel.v isible = false
nobookingspanel .visible = true
END IF
END SUB
'THIS SUB HANDLES GRABBING CLIENT AND EMPLOYEE NAMES (for now only
clientnames)
Sub dlBookings_OnIt emDataBound(sen der As Object, e As DataListItemEve ntArgs)
If e.Item.ItemType = ListItemType.It em Or e.Item.ItemType =
ListItemType.Al ternatingItem Then
' Retrieve the Label control in the current DataListItem.
Dim ClientNameLabel As Label = e.Item.FindCont rol("clientname label")
Dim curClientID as Integer = e.item.FindCont rol("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(C onfigurationSet tings.AppSettin gs("dbConn"))
'NEW DATAREADER
Dim objDR as SQLDataReader
'NEW COMMAND OBJECT
Dim Cmd as New SQLCommand(strS QL, MyConn)
'OPEN CONNECTION
MyConn.Open()
'EXECUTE QUERY AND RETRIEVE DATA INTO READER
objDR = Cmd.ExecuteRead er(system.data. CommandBehavior .CloseConnectio n)
WHILE objDR.Read
clientlabel = objDR("fullname ")
END While
myConn.close
'redisplay it in the DataList.
ClientNameLabel .Text = clientlabel.ToS tring()
End If
End Sub
</script>
<html>
<head>
<title>AgentSho pper</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.as px" -->
<!--- 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("sesemp Fname") & " "
& Session("sesemp Lname")%>'s Draft Bookings - <asp:Label ID="mylabel"
runat="server"/></td>
</tr>
<tr height="15">
<td align="left" colspan="5"></td>
</tr>
<asp:panel ID="bookingspan el" runat="server">
<asp:DataList id="dlBookings "
runat="server"
cellpadding="3"
cellspacing="3"
GridLines="Both "
borderstyle="no ne"
backcolor="#FFF FFF"
width="790px"
headerstyle-font-name="Verdana"
headerstyle-font-size="13pt"
headerstyle-horizontalalign ="left"
headerstyle-font-bold="TRUE"
itemstyle-backcolor="#FFF FFF"
itemstyle-forecolor="#000 000"
alternatingitem style-backcolor="#C6E FF7"
alternatingitem style-forecolor="#FFF FFF"
footerstyle-font-size="9pt"
footerstyle-font-italic="true"
OnItemDataBound ="dlBookings_On ItemDataBound">
<HeaderTemplate >
<tr valign="top" bgcolor="#CCCCC C">
<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.D ataItem("arn")% ></td>
<td align="left" class="textbox" ><%#Container.D ataItem("status ")%></td>
<td align="left"
class="textbox" ><%#Container.D ataItem("bookin gdatetimestamp" )%></td>
<td align="left" class="textbox" ></td>
<td align="left" class="textbox" ><asp:Label id="clientnamel abel"
runat="server"/></td>
</tr>
</ItemTemplate>
<AlternatingIte mTemplate>
<tr valign="top">
<td align="left" class="textbox" ><%#Container.D ataItem("arn")% ></td>
<td align="left" class="textbox" ><%#Container.D ataItem("status ")%></td>
<td align="left"
class="textbox" ><%#Container.D ataItem("bookin gdatetimestamp" )%></td>
<td align="left" class="textbox" ></td>
<td align="left" class="textbox" ><asp:Label id="clientnamel abel"
runat="server"/></td>
</tr>
</AlternatingItem Template>
<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="nobookingsp anel" runat="server">
<tr valign="top">
<td align="left" class="bodycopy ">
<table width="790" cellpadding="5" cellspacing="0" border="0"
bordercolor="#C CCCCC">
<tr valign="top">
<td align="left" class="textbox" ><%= Session("sesemp Fname") & " " &
Session("sesemp Lname")%> 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******** ******@TK2MSFTN GP12.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***@officien ce.ca> wrote in message
news:ev******** ******@TK2MSFTN GP09.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
