Wrox Programmer Forums
|
ASP.NET 3.5 Basics If you are new to ASP or ASP.NET programming with version 3.5, this is the forum to begin asking questions. Please also see the Visual Web Developer 2008 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 3.5 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 12th, 2008, 10:29 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Do you want to use the DDL as a filter for some other data? In that case, you need two SqlDataSource controls: one for the DDL and one for the other.

Typically, you set up filters like this:

1. Create a SqlDataSource for, say, categories and call it dsCategories

2. You add a DLL called lstCategories and bind it to dsCategories. When the page loads, the DDL gets populated with the categories

3. You set AutoPostback on the DDL to true

4. You add a data bound control, like a ListView to the page.

5. You hook up the ListView to a new DataSource like dsCourses.

6. You define a ControlParameter in dsCourses to look at the SelectedValue of lstCategories.

Then, when the page loads, it loads the categories in the DDL. When you choose a new item, the page posts back. Then dsCourses looks at the new SelectedValue of the DDL and filters its list accordingly. The results are then displayed on the page with the ListView.

Does that help? If not, can you post the relevant parts of the source and describe what it is you're trying to accomplish exactly?

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004
Want to be my colleague? Then check out this post.
 
Old November 12th, 2008, 11:09 AM
Authorized User
 
Join Date: Mar 2008
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, it does help in that you've exposed what I suspected: that I'd need multiple datasources.

Here's what I'm trying to do.

I have a SQL table that is effectively a log of every Course Evaluation. The Evaluation includes Course Date, Instructor, Course Name, and a series of Responses (numeric 1-5 asking a range of questions about their opinion on different aspects of class - 'likerts').

I have a SQL View that returns AVG scores for the responses. I think we've already worked out that some variation of this is what has to pass to the GridView.


The aspx page has web controls that would allow the user to set a date range, select the instructor (or leave blank), and select the course (or leave blank) - in order to view AVGs based on the criteria they set.

That would then populate the Grid View with the relevant data.


So the DropDownLists' items are Course and Instructor.

The Calendar Controls' items are CourseDate (two for BeginDate and EndDate).

Thanks!

 
Old November 13th, 2008, 12:19 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Can you post the code of what you have so far? You seem to understand the global design, so it now comes down to code. I can't write it for you, so the best I can do is give you tips based on your current code.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004
Want to be my colleague? Then check out this post.
 
Old November 13th, 2008, 01:37 PM
Authorized User
 
Join Date: Mar 2008
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks and of course! The goal here is to essentially learn and I can't thank you enough - you confirmed for me the multiple datasource approach was (if you can believe it) a breakthrough on my end..

Here is the code I have so far. The drop downs do populate, but i'm having an issue now with the gridview even showing up.



code below:


Code:
<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="Ops_Course_Evaluations._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Course Evaluations</title>
    <style type="text/css">
        .style3
        {
            font-family: Calibri;
            font-size: small;
        }
        </style>
</head>
<body>
    <form id="form1" runat="server">
    <div class="style3">

    </div>


        <br />
 <asp:Label ID="lblCourse" runat="server" Text="Course"></asp:Label>
                                                 
    <asp:Label ID="lblInstructor" runat="server" Text="Instructor"></asp:Label>
    <br />
        <asp:DropDownList ID="ddlCourse" runat="server" 
        AutoPostBack="True" Width="130px"
        DataSourceID="SqlDataSource1" 
        DataTextField="Course" 
        DataValueField="CourseID">
        </asp:DropDownList>
                               
    <asp:DropDownList ID="ddlInstructor" runat="server" AutoPostBack="True" 
        DataSourceID="SqlDataSource2" DataTextField="Instructor" 
        DataValueField="InstructorID" Height="17px" Width="145px">
    </asp:DropDownList>
        <br />
        <br />
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        DataSourceID="SqlDataSource3" Height="198px" Width="450px">
            <Columns>
                <asp:BoundField DataField="CourseDate" HeaderText="CourseDate" 
                    SortExpression="CourseDate" />
                <asp:BoundField DataField="CourseID" HeaderText="CourseID" 
                    SortExpression="CourseID" />
                <asp:BoundField DataField="Course" HeaderText="Course" 
                    SortExpression="Course" />
                <asp:BoundField DataField="InstructorID" HeaderText="InstructorID" 
                    SortExpression="InstructorID" />
                <asp:BoundField DataField="Instructor" HeaderText="Instructor" 
                    SortExpression="Instructor" />
                <asp:BoundField DataField="Expr1" HeaderText="SessionFlow" 
                    SortExpression="Expr1" />
            </Columns>
    </asp:GridView>
        <br />
        <br />
        <br />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:dsCourse %>" 
        EnableCaching="True" 


        SelectCommand="SELECT DISTINCT [CourseID], [Course] FROM [View_2] ORDER BY [Course]">
    </asp:SqlDataSource>
        <br />
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
        ConnectionString="<%$ ConnectionStrings:dsInstructors %>" 
        SelectCommand="SELECT DISTINCT [InstructorID], [Instructor] FROM [View_2]"></asp:SqlDataSource>
    <br />
    <asp:SqlDataSource ID="SqlDataSource3" runat="server" 
        ConnectionString="<%$ ConnectionStrings:Ops_Course_Evals_withIDsConnectionString4 %>" 

        SelectCommand="SELECT * FROM [View_2] WHERE (([Course] = @Course) AND ([Instructor] = @Instructor))">


        <SelectParameters>
            <asp:ControlParameter ControlID="ddlCourse" Name="Course" 
                PropertyName="SelectedValue" Type="String" />
            <asp:ControlParameter ControlID="ddlInstructor" Name="Instructor" 
                PropertyName="SelectedValue" Type="String" />


        </SelectParameters>
    </asp:SqlDataSource>
    <br />


    </form>
</body>
</html>
 
Old November 13th, 2008, 04:11 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

>> but i'm having an issue now with the gridview even showing up.

And what is the issue exactly? Do you get an error? Does it show up if moth DDLs have a valid value?

The code looks OK, although it's hard to tell for sure since I can't run / test it. However, because of the way you set things up, the GridView won't display records until both DDls have a valid value.

Just to test things out, change the T-SQL to this:

SELECT * FROM [View_2] WHERE (([Course] = @Course) <s>AND</s> OR ([Instructor] = @Instructor))

This way, you may get results when only one item is selected.

Also: are you sure the database contains records whose values match those of the IDs in the DDLs?

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004
Want to be my colleague? Then check out this post.
 
Old November 14th, 2008, 09:48 AM
Authorized User
 
Join Date: Mar 2008
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That was it! I pointed the values towards the columns themselves (I had them pointed at the ID field).

I was able to create Start/End Date ranges...

Questions: How can I return AVG scores against the date ranges the user inputs?

As of now the code below will return AVGs for each date, not a cumulative AVG for a date range.

My guess is to undo the AVG function in the SQL view, and instead do the AVG in the SQLDataSource.

Thoughts?

Code:
<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="Ops_Course_Evaluations._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Course Evaluations</title>
    <style type="text/css">
        .style3
        {
            font-family: Calibri;
            font-size: small;
        }
        .style4
        {
            font-family: Calibri;
        }
        .style5
        {
            font-family: Calibri;
            font-size: medium;
        }
        .style6
        {
            font-size: medium;
        }
        .style7
        {
            font-family: Calibri;
            font-size: medium;
            font-weight: bold;
        }
        .style8
        {
            font-family: Calibri;
            font-size: small;
            margin-top: 10px;
        }
        </style>
</head>
<body>
    <form id="form1" runat="server">
    <div class="style3">

    </div>


        <span class="style7">&nbsp;<br />
    <br />
    </span><b>
    <br class="style5" />
    </b><span class="style4"><span class="style6"><b><asp:Label ID="lblCourse" runat="server" Text="Course"></asp:Label>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    <span lang="en-us">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    </span>
    <asp:Label ID="lblInstructor" runat="server" Text="Instructor"></asp:Label>
    </b></span></span><b>
    <br class="style5" />
        </b><span class="style4"><span class="style6"><b>
        <asp:DropDownList ID="ddlCourse" runat="server" 
        AutoPostBack="True" Width="273px"
        DataSourceID="SqlDataSource1" 
        DataTextField="Course" 
        DataValueField="Course" Height="34px">
        </asp:DropDownList>
        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    <asp:DropDownList ID="ddlInstructor" runat="server" AutoPostBack="True" 
        DataSourceID="SqlDataSource2" DataTextField="Instructor" 
        DataValueField="Instructor" Height="34px" Width="201px">
    </asp:DropDownList>
        </b></span></span><b>
        <br class="style5" />
        </b>
    <br />
    <br />
    <asp:Label ID="lblStartDate" runat="server" CssClass="style7" Text="StartDate"></asp:Label>
    <asp:Calendar ID="calStartDate" runat="server" CssClass="style3"></asp:Calendar>
    <br />
    <asp:Label ID="lblEndDate" runat="server" CssClass="style7" Text="EndDate"></asp:Label>
    <asp:Calendar ID="calEndDate" runat="server" CssClass="style3"></asp:Calendar>
    <br />
    <br />
        <br />
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        DataSourceID="SqlDataSource3" AllowPaging="True" AllowSorting="True" 
        BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" 
        CellPadding="4" CssClass="style8" ForeColor="Black" GridLines="Horizontal" 
        Height="198px" Width="818px">
            <FooterStyle BackColor="#CCCC99" ForeColor="Black" />
            <Columns>
                <asp:BoundField DataField="CourseDate" HeaderText="CourseDate" 
                    SortExpression="CourseDate" DataFormatString="{0:MM-dd-yyyy}" />
                <asp:BoundField DataField="Course" HeaderText="Course" 
                    SortExpression="Course" />
                <asp:BoundField DataField="Instructor" HeaderText="Instructor" 
                    SortExpression="Instructor" />
                <asp:BoundField DataField="Expr1" HeaderText="SessionFlow" 
                    SortExpression="Expr1" />
                <asp:BoundField DataField="Expr2" HeaderText="Materials" 
                    SortExpression="Expr2" />
                <asp:BoundField DataField="Expr3" HeaderText="CBT" SortExpression="Expr3" />
            </Columns>
            <PagerStyle BackColor="White" ForeColor="Black" HorizontalAlign="Right" />
            <SelectedRowStyle BackColor="#CC3333" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#333333" Font-Bold="True" ForeColor="White" />
    </asp:GridView>
        <br />
        <br />
        <br />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:dsCourse %>" 
        EnableCaching="True" 


        SelectCommand="SELECT DISTINCT [CourseID], [Course] FROM [View_2] ORDER BY [Course]">
    </asp:SqlDataSource>
        <br />
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
        ConnectionString="<%$ ConnectionStrings:dsInstructors %>" 
        SelectCommand="SELECT DISTINCT [InstructorID], [Instructor] FROM [View_2]"></asp:SqlDataSource>
    <br />
    <asp:SqlDataSource ID="SqlDataSource3" runat="server" 
        ConnectionString="<%$ ConnectionStrings:Ops_Course_Evals_withIDsConnectionString4 %>" 


        SelectCommand="SELECT * FROM [View_2] WHERE (([Course] = @Course) AND ([Instructor] = @Instructor) AND ([CourseDate] &gt;= @CourseDate) AND ([CourseDate] &lt;= @CourseDate2))">

     
        <SelectParameters>
            <asp:ControlParameter ControlID="ddlCourse" Name="Course" 
                PropertyName="SelectedValue" Type="String" />
            <asp:ControlParameter ControlID="ddlInstructor" Name="Instructor" 
                PropertyName="SelectedValue" Type="String" />


            <asp:ControlParameter ControlID="calStartDate" Name="CourseDate" 
                PropertyName="SelectedDate" />
            <asp:ControlParameter ControlID="calEndDate" Name="CourseDate2" 
                PropertyName="SelectedDate" />


        </SelectParameters>
    </asp:SqlDataSource>
    <br />
    <asp:SqlDataSource ID="SqlDataSource4" runat="server" 
        ConnectionString="<%$ ConnectionStrings:dsCourseDate %>" 
        SelectCommand="SELECT [CourseDate] FROM [View_2]"></asp:SqlDataSource>
    <br />
    <br />
    <br />
    <br />
    <br />
    <br />


    </form>
</body>
</html>
 
Old November 14th, 2008, 04:29 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

That's pretty difficult to answer as I don't see any references to avg in your code so I have no idea what you're trying to take an average of, what exactly the View returns or what your desired output is.

My advice: start a new thread in this forum, explain the problem in detail, and provide a lot more information, including the desired output and the structure of the database. That way, others can join the discussion as well.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004
Want to be my colleague? Then check out this post.





Similar Threads
Thread Thread Starter Forum Replies Last Post
ASP.NET 1.1,VB.NET,crystal reports, SQl server gvi Crystal Reports 1 September 11th, 2008 02:55 AM
ASP.Net Development Engineer needed lashan ASP.NET 2.0 Professional 0 January 7th, 2008 01:10 PM
Files needed to run ASP.NET? RAM req'd? Ron Howerton ADO.NET 1 February 15th, 2005 09:08 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.