Hi,
Is the datalist meant for one table or can I use it to display information
from 2 different tables? Or should I be looking at a datagrid instead?
I began my code and kinda ran into a snag where my last two columns I am
displaying are ID fields, one for clientid and the other for empid.
What I would really like is to display their actual names instead of the
ids.
I have a dataset that is filled with 3 tables and I have created relations
between CLIENT and BOOKING (clientid) and EMPLOYEE and BOOKING (empid)
tables.
I have bound the dataset to my datalist and it is displaying correctly.
Thank you in advance! Joe
Here's my code:
SUB getdata()
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
</script>
<!--- START OF BOOKING PANEL --->
<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">
<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">*****</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">*****</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> 4 2462
Put code into your itemdatabound event, and in it pick up the id's from your
controls, retrieve the data you want, and substitute it. Or, more
efficiently, construct your SQL query with appropriate joins, etc, to
retrieve the columns you want and just bind to them.
"Joe Van Meer" <va*****@accesscable.net> wrote in message
news:un**************@TK2MSFTNGP10.phx.gbl... Hi,
Is the datalist meant for one table or can I use it to display information from 2 different tables? Or should I be looking at a datagrid instead?
I began my code and kinda ran into a snag where my last two columns I am displaying are ID fields, one for clientid and the other for empid.
What I would really like is to display their actual names instead of the ids.
I have a dataset that is filled with 3 tables and I have created relations between CLIENT and BOOKING (clientid) and EMPLOYEE and BOOKING (empid) tables.
I have bound the dataset to my datalist and it is displaying correctly.
Thank you in advance! Joe
Here's my code:
SUB getdata()
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 </script> <!--- START OF BOOKING PANEL ---> <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"> <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">*****</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">*****</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>
Hi Rick thanks for replying.
I had one query in the beginning that had table aliases and column aliases
but was told to use the relations collection and create my relationships
that way...so which way should I go? And how do I reference/bind data in
the second table to appear alongside info in the first table in my datalist
<%#Container.DataItem("SECOND TABLE COLUMN ")%>
Thanks, Joe
"Rick Spiewak" <ri*********@mindspring.com> wrote in message
news:eO**************@TK2MSFTNGP11.phx.gbl... Put code into your itemdatabound event, and in it pick up the id's from
your controls, retrieve the data you want, and substitute it. Or, more efficiently, construct your SQL query with appropriate joins, etc, to retrieve the columns you want and just bind to them. "Joe Van Meer" <va*****@accesscable.net> wrote in message news:un**************@TK2MSFTNGP10.phx.gbl... Hi,
Is the datalist meant for one table or can I use it to display
information from 2 different tables? Or should I be looking at a datagrid instead?
I began my code and kinda ran into a snag where my last two columns I am displaying are ID fields, one for clientid and the other for empid.
What I would really like is to display their actual names instead of the ids.
I have a dataset that is filled with 3 tables and I have created
relations between CLIENT and BOOKING (clientid) and EMPLOYEE and BOOKING (empid) tables.
I have bound the dataset to my datalist and it is displaying correctly.
Thank you in advance! Joe
Here's my code:
SUB getdata()
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 </script> <!--- START OF BOOKING PANEL ---> <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"> <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">*****</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">*****</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>
Instead of binding declaratively, put code in the itemdatabound event.
Go directly into your tables with whatever querie(s) you need to
retrieve the data you want, and put it directly into the relevant
controls (which should be templated so you can find them).
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Thx Rick, I will give that a go. I am migrating from asp to .net. I have
experience in vb in past way back...so hopefully it won't be too bad.
I appreciate you help.
Anyways, I got started on the sub that will be called onItemdataBound but am
having problems getting it...I know I am close, but here goes:
'THIS SUB HANDLES GRABBING CLIENT NAMES
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
********************************************** ERROR HERE I AM TRYING TO
GET THE VALUE OF 'CLIENTID'
' 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
"Rick Spiewak" <ri*********@mindspring.com> wrote in message
news:uo**************@TK2MSFTNGP09.phx.gbl... Instead of binding declaratively, put code in the itemdatabound event. Go directly into your tables with whatever querie(s) you need to retrieve the data you want, and put it directly into the relevant controls (which should be templated so you can find them).
*** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: marslee |
last post by:
I want to change a webpage so that when i move the mouse to certain
area of the page, the info tip tell what the content is about.
It's like moving the mouse in the desktop's my documment, when you...
|
by: olle |
last post by:
Hi everyone.
Beging a newbee to asp.net and used to work with traditional asp I have
one problem working with a datalist.
1/ I make a dataset as as session variable like
session("employees")
2/...
|
by: kscdavefl |
last post by:
How do I display the tables of a database in a list box?
Thanks,
dave
|
by: Roberto López |
last post by:
Hello all,
I have a page that in the top display a tree with Folders and Images into
the folders.
When the user click in a image, in the botton of the page i load the Image
properties into a DIV...
|
by: Demetri |
last post by:
I have two tables in sql server. Customer and State. Of
course the Customer table has data about the customer
such as name, address, and phone number. It also has a
field called state_id. The...
|
by: mkd1919 |
last post by:
I have a website that performs a search on an indexing service and returns
the results. During the initial load, I get the recordcount through a DS,
then bind a DataList using a second DS with...
|
by: beachboy |
last post by:
hello,
I have set "RepeatColumns" on myDataList = 5, if the reminder of itemdata is
not multiple of 5.. e.g: only have 3 items. then the display will break...
any method i can solive this and...
|
by: beachboy |
last post by:
hello,
I have set "RepeatColumns" on myDataList = 5, if the reminder of itemdata is
not multiple of 5.. e.g: only have 3 items. then the display will break...
any method i can solive this and...
|
by: poojak |
last post by:
Hi Everybody,
I need some help. I want that when user click on a hyperlink (say Add Aditional Info) it display a table with 11 row and 2 cols just above the link and link moves down. and at this...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |