p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   ASP.NET 3.5 Basics (http://p2p.wrox.com/forumdisplay.php?f=351)
-   -   Working with a database (http://p2p.wrox.com/showthread.php?t=72952)

areed24 February 25th, 2009 06:53 PM

Working with a database
 
I have a database with three tables, CITIES (Id, Name), MEMBERS (Id, Name, Email), COMMENTS (Id, CityId, Comments, MemberId). On the site, there is a drop down which users can select a City. Based on the city seletion, I would like all the comments for that city to appear. How do I output the Member's Name (not Id), Email Address, and Comments in a customizable table for the users to see?


I am new to both ASP.NET and databases, so any information would be greatly appreciated.

dparsons February 26th, 2009 12:08 AM

The SQL Code is trivial really. Something like this would do:

sql Code:
SELECT
       m.Name,
       c.Comments
   FROM Members m
   INNER JOIN Comments c ON m.ID = c.MemberID
   WHERE c.CityID = @CityID

After executing this SQL you could simply populate a data table and then bind it to a gridview something like:

gridView1.DataSource = somedatatable;
gridView1.DataBind();


hth.
-Doug

areed24 February 27th, 2009 11:42 AM

Almost but not quite
 
I have the following code on my page:

<
asp:DropDownListID="DropDownList1"runat="server"AutoPostBack="True"
DataSourceID="SqlDataSource1"DataTextField="Country"DataValueField="Id"AppendDataBoundItems="true">
<asp:ListItemValue="">Please select a county</asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:SiteConnectionString1 %>"SelectCommand="SELECT [Country], [Id] FROM [Countries] ORDER BY [Country]">
</asp:SqlDataSource>
<asp:DropDownListID="DropDownList2"runat="server"DataSourceID="SqlDataSource2"DataTextField="Name"DataValueField="Id"AppendDataBoundItems="true"AutoPostBack="True"Visible="False">
</asp:DropDownList>
<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:SiteConnectionString1 %>"SelectCommand="SELECT [Name], [CountryId], [Id] FROM [Cities] WHERE ([CountryId] = @CountryId) ORDER BY [Name]">
<SelectParameters>
<asp:ControlParameterControlID="DropDownList1"Name="CountryId"PropertyName="SelectedValue"Type="Int32"/>
</SelectParameters>
</asp:SqlDataSource>
<asp:RepeaterID="Repeater1"runat="server">
<ItemTemplate>
<pclass="Contacts"><asp:LiteralID="Literal1"runat="server"Text='<%# Eval("MemberName") %>'></asp:Literal>
<br/><asp:LiteralID="Literal2"runat="server"Text='<%# Eval("Email") %>'></asp:Literal>
<br/><asp:LiteralID="Literal3"runat="server"Text='<%# Eval("Summary") %>'></asp:Literal></p>
</ItemTemplate>
</asp:Repeater>

with this code on the code behind page

If Page.IsPostBack Then
Using myDataContext AsNew SiteDataContext()
Dim allReviews = From r In myDataContext.Reviews _
Where r.CityId = DropDownList2.SelectedValue _
SelectNewWith {r.Member.MemberName, r.Member.email, r.Summary}
Repeater1.DataSource = allReviews
Repeater1.DataBind()
EndUsing
EndIf

I get the results that I want, the User Name, their email address, and the review. However, the review continues off of the page, out of the main content area defined on the other pages. Why is it doing this? How do I fix it? And is using a literal the best way to display the information?

dparsons February 27th, 2009 11:53 AM

Unless you are accessing the Literal in the code behind for something, you dont need to place the text from your database into a control. The following code would suffice:

aspnet Code:
...
<ItemTemplate>
<%# Eval("MemberName") %> <br />
<%# Eval("Email") %> <br />
<%# Eval("Summary") %>
</ItemTemplate>
...

As far as your content breaking the page bounds I am not sure what is going on with that. I have had instances in the past where a literal did this but was able to correct it with something like:

<div style="width:750px"><asp:Literal ... /></div>

hth.
-Doug

areed24 February 27th, 2009 03:12 PM

Thank you. That works great. The review only runs out of the content area if there is no space between the words. So if I were to enter aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa it runs over regardless of the properties that I set int the div tag. Otherwise it wraps just fine. Thanks again.

dparsons February 27th, 2009 03:14 PM

Glad it worked out for you! =]

-Doug


All times are GMT -4. The time now is 03:19 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.