Working with a database

areed24 · February 25th, 2009, 06:53 PM

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

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