vb / sql help needed

keithroby · June 23rd, 2009, 04:06 PM

Several months ago I posted to this forum asking for assistance on a particular issue...Imar graciously helped me out...

Anyway, same project, but I've got a new(ish) problem.

I have a likert score database that pipes instructors, courses, dates, and a series of scores on varied performance metrics to a web front end.

User modifies web controls to receive desired date range, instructor and course(s).

Here's my problem. Everything works great...When user only wants a range defined to include ONE course. When the checkboxlist is asked to send two (or more) courses, only the first course in the list makes it through the SQL statement.

So...I need to cycle through the checkboxlist to form a string of values....A For each list item statement that is tied to either the page load(?) or btnSubmit click event. (???)

I don't know how to configure that. I need help there...hence the VB part of my title.

Now, once that is configured, I presume I need some type of tweaking to my SQL statement so that it will accept a string into the WHERE clause. Or not. I really don't know.

Here's the code.

Code:


<formid="form1"runat="server">
<asp:ScriptManagerrunat="server"id="ScriptManager1"></asp:ScriptManager>
<br/>
<asp:UpdatePanelid="UpdatePanel1"runat="server">
<ContentTemplate>
<br/>
<spanclass="style2">
<asp:labelid="lblCblCourse"runat="server"style="font-weight: 700"Text="Courses"></asp:label>
</span>
<brclass="style2"/>
<asp:CheckBoxListid="cblCourse"runat="server"DataSourceID="CoursesDataSource"DataTextField="Course"DataValueField="Course"Font-Names="Calibri"Font-Size="Small"RepeatColumns="4">
</asp:CheckBoxList>
<brclass="style2"/>
<brclass="style2"/>
<spanclass="style2">
<asp:labelid="lblInstructor"runat="server"style="font-weight: 700"Text="Instructor"></asp:label>
</span>
<brclass="style2"/>
<spanclass="style1"><spanclass="style3">
<asp:dropdownlistid="ddlInstructor"runat="server"DataSourceID="InstructorsDataSource"DataTextField="Instructor"DataValueField="Instructor">
</asp:dropdownlist>
</span></span>
<brclass="style2"/>
<brclass="style2"/>
<brclass="style2"/>
<tablestyle="width: 100%">
<tr>
<tdstyle="width: 240px"><spanclass="style2">
<asp:labelid="lblStartDate"runat="server"style="font-weight: 700"Text="Start Date"></asp:label>
</span>
<asp:calendarid="calStartDate"runat="server"Font-Names="Calibri"Font-Size="Small"SelectedDate="2008-01-01">
<TitleStyleBackColor="#336699"Font-Bold="True"Font-Names="Calibri"/>
</asp:calendar>
</td>
<td><spanclass="style2">
<asp:labelid="lblEndDate"runat="server"style="font-weight: 700"Text="End Date"></asp:label>
</span>
<asp:calendarid="calEndDate"runat="server"Font-Names="Calibri"Font-Size="Small"SelectedDate="2009-05-26">
<TitleStyleBackColor="#336699"Font-Bold="True"/>
</asp:calendar>
</td>
</tr>
</table>
<brclass="style2"/>
<brclass="style2"/>
<asp:ButtonID="btnSubmit"runat="server"Text="Submit"/>
<brclass="style2"/>
<brclass="style2"/>
<asp:GridViewid="gvData"runat="server"AutoGenerateColumns="False"DataSourceID="ScoresDataSource"Font-Names="Calibri"Font-Size="Small">
<Columns>
<asp:boundfieldDataField="SessionFlow"HeaderText="SessionFlow"ReadOnly="True"SortExpression="SessionFlow">
</asp:boundfield>
<asp:boundfieldDataField="Materials"HeaderText="Materials"ReadOnly="True"SortExpression="Materials">
</asp:boundfield>
<asp:boundfieldDataField="CBT"HeaderText="CBT"ReadOnly="True"SortExpression="CBT">
</asp:boundfield>
<asp:boundfieldDataField="Activities"HeaderText="Activities"ReadOnly="True"SortExpression="Activities">
</asp:boundfield>
<asp:boundfieldDataField="ClarityOfInfo"HeaderText="ClarityOfInfo"ReadOnly="True"SortExpression="ClarityOfInfo">
</asp:boundfield>
<asp:boundfieldDataField="ClassLength"HeaderText="ClassLength"ReadOnly="True"SortExpression="ClassLength">
</asp:boundfield>
<asp:boundfieldDataField="AnswerQs"HeaderText="AnswerQs"ReadOnly="True"SortExpression="AnswerQs">
</asp:boundfield>
<asp:boundfieldDataField="Patience"HeaderText="Patience"ReadOnly="True"SortExpression="Patience">
</asp:boundfield>
<asp:boundfieldDataField="Presentation"HeaderText="Presentation"ReadOnly="True"SortExpression="Presentation">
</asp:boundfield>
<asp:boundfieldDataField="Knowledge"HeaderText="Knowledge"ReadOnly="True"SortExpression="Knowledge">
</asp:boundfield>
<asp:boundfieldDataField="Timeliness"HeaderText="Timeliness"ReadOnly="True"SortExpression="Timeliness">
</asp:boundfield>
<asp:boundfieldDataField="Preparedness"HeaderText="Preparedness"ReadOnly="True"SortExpression="Preparedness">
</asp:boundfield>
<asp:boundfieldDataField="Motivation"HeaderText="Motivation"ReadOnly="True"SortExpression="Motivation">
</asp:boundfield>
<asp:boundfieldDataField="Participation"HeaderText="Participation"ReadOnly="True"SortExpression="Participation">
</asp:boundfield>
</Columns>
</asp:GridView>
<brclass="style2"/>
<brclass="style2"/>
<asp:sqldatasourceid="ScoresDataSource"runat="server"ConnectionString="<%$ ConnectionStrings:OpsCourseEvaluationsConnectionString %>"SelectCommand="SELECT AVG(SessionFlow) AS SessionFlow, AVG(Materials) AS Materials, AVG(CBT) AS CBT, AVG(Activities) AS Activities, AVG(ClarityOfInfo) AS ClarityOfInfo, AVG(ClassLength) AS ClassLength, AVG(AnswerQs) AS AnswerQs, AVG(Patience) AS Patience, AVG(Presentation) AS Presentation, AVG(Knowledge) AS Knowledge, AVG(Timeliness) AS Timeliness, AVG(Preparedness) AS Preparedness, AVG(Motivation) AS Motivation, AVG(Participation) AS Participation FROM View_3 WHERE (Course = @Course) AND (Instructor = @Instructor) AND (CourseDate &gt;= @CalStartDate) AND (CourseDate &lt;= @CalEndDate)">
<SelectParameters>
<asp:ControlParameterControlID="cblCourse"Name="Course"PropertyName="SelectedValue"Type="String"/>
<asp:controlparameterControlID="ddlInstructor"Name="Instructor"PropertyName="SelectedValue"/>
<asp:controlparameterControlID="calStartDate"Name="CalStartDate"PropertyName="SelectedDate"/>
<asp:controlparameterControlID="calEndDate"Name="CalEndDate"PropertyName="SelectedDate"/>
</SelectParameters>
</asp:sqldatasource>
<asp:sqldatasourceid="CoursesDataSource"runat="server"ConnectionString="<%$ ConnectionStrings:CoursesConnectionString %>"SelectCommand="SELECT DISTINCT [CourseID], [Course] FROM [View_3] ORDER BY [Course]">
</asp:sqldatasource>
<asp:sqldatasourceid="InstructorsDataSource"runat="server"ConnectionString="<%$ ConnectionStrings:InstructorsConnectionString %>"SelectCommand="SELECT DISTINCT [InstructorID], [Instructor] FROM [View_3]">
</asp:sqldatasource>
<asp:sqldatasourceid="CourseDatesDataSource"runat="server"ConnectionString="<%$ ConnectionStrings:CourseDatesConnectionString %>"SelectCommand="SELECT [CourseDate] FROM [View_3]">
</asp:sqldatasource>
<brclass="style2"/>
</ContentTemplate>
</asp:UpdatePanel>

</form>

Somebody earlier had helped me configure this as a For Each statement...Would I execute this on a button click? or on page load?

Code:

ProtectedSub btnSubmit_Click(ByVal sender AsObject, ByVal e As System.EventArgs) Handles btnSubmit.Click
Dim inExpression AsNew Text.StringBuilder()
ForEach li As ListItem In cblCourse.Items
If li.Selected Then
inExpression.Append(li.Text + ", ")
EndIf
Next li
If inExpression.Length > 0 Then
inExpression.Remove(inExpression.Length - 2, 1)
EndIf
ScoresDataSource.SelectParameters("Course").DefaultValue = inExpression.ToString().TrimEnd(","c, " ")
EndSub

Imar · June 24th, 2009, 03:50 PM

Hi keithroby,

I think you've gotten to a point where the Drag and Drop controls no longer cut it. To make this work (and build it in a reasonable time), you probably want to use the ADO.NET API instead and directly talk to the database.

To get started, take a look here:

http://quickstarts.asp.net/QuickStar...wcontents.aspx

or one of the Wrox books on ADO.NET (http://www.wrox.com/WileyCDA/Section...?query=ado.net) like Pro ADO.NET 2 or Beginning C# 2005 Databases

Cheers,

Imar

keithroby · June 26th, 2009, 11:40 AM

I'm confused.

The controls on the page seem simple enough in that they assist the user in filtering the dataset which is provided through the SQLDataSource...

It actually works, but it's just that the CheckBoxList is not set up to push multiple values through to the 'Course' parameter. That's where the For Each statement comes in.

Won't I have the exact same issue if I go about the back-end connections a different way?

Seems to me the snag I'm up against is how to appropriately configure the web control, specifically the CheckBoxList so that it passes multiple values (string into the Course parameter)...

keithroby · June 26th, 2009, 12:12 PM

something like....

Code:

 
protected sub btnSubmit_click....
 
Dim li as ListItem
For each li in cblCourse.Items
If li.Selected = True Then

(build string here so parameterized query can utilize it)

 End If
Next
 
 
end sub

Imar · June 27th, 2009, 06:24 AM

Hi keithroby,

Maybe I misunderstood your problem. I thought you were saving the selected items in a separate table; each with its own row. Since the datasource controls typically work with a single record, this won't work.

But you're right; if you want to store a string with the items, you can assign a value to one of the parameters of the data source control. However, you shouldn't do this in the Button's Click event, but in the data source's Inserting and Updating events. They fire right before the data is sent to the database and as such are a perfect place to modify parameters:

http://msdn.microsoft.com/en-us/libr...inserting.aspx
http://msdn.microsoft.com/en-us/libr....updating.aspx

Cheers,

Imar