Hi all,
I have a dataset made up of 3 tables that is bound to a datalist.
On the itemdataBound event I call a sub that grabs a value (an id value)
from the current row being outputted, queries the database using this id
value to return a string value that I replace the id with in the datalist.
My question is this: instead of re-querying the database can I use the
dataset to replace this value using the id?
And what would the syntax be for accessing such a relation something like:
mylabel = mydataset.Relat ion("myrelation name").Columns( 6)....? Or is
there a method I can use to access this based on the id? getParentRows
maybe? Where can I can such syntax examples? I am assuming this has to be
called in the same Sub.
I know there must be a way to access this data via the relations collection,
I am just unsure of the syntax of how to do so.
Cheers, and thx for any help! Lerp :)
Entire code is below:
<%@ 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">
SUB Page_Load(Sende r As Object, E As EventArgs)
Dim curAgentID as Long
Dim curAgencyID as Long
Dim curAgencyName as String
Dim curAgentName as String
Dim PageTitle as String
Dim curSecLevel as String
Dim curGroupID as Long
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
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")
'GRAB CURRENT ID IN LOOP
Dim drv As DataRowView = CType(e.Item.Da taItem, DataRowView)
Dim curClientID As Long = Long.Parse(drv. Row("clientid") .ToString())
' 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
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>
</table>
<br>
<!-- #include file="footer.as px" //-->
</div>
</body>
</html>
