Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 3.5 > ASP.NET 3.5 Basics
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 23rd, 2009, 04:06 PM
Authorized User
 
Join Date: Mar 2008
Location: baltimore, md, USA.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default vb / sql help needed

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
  #2 (permalink)  
Old June 24th, 2009, 03:50 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
  #3 (permalink)  
Old June 26th, 2009, 11:40 AM
Authorized User
 
Join Date: Mar 2008
Location: baltimore, md, USA.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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)...
  #4 (permalink)  
Old June 26th, 2009, 12:12 PM
Authorized User
 
Join Date: Mar 2008
Location: baltimore, md, USA.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #5 (permalink)  
Old June 27th, 2009, 06:24 AM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Aurgent needed(VB convert to Java) debasisha Java GUI 1 December 5th, 2008 10:01 PM
SQL help needed frank.svs SQL Server 2005 1 November 21st, 2007 11:22 AM
Tutor needed for VB 2005 application edx VB How-To 9 May 13th, 2007 04:26 PM
Good VB interview questions are needed. BigDude Access VBA 1 August 30th, 2005 07:40 AM
help needed on how to use COM in vb.net iceman1188 General .NET 8 June 9th, 2004 09:10 AM



All times are GMT -4. The time now is 02:20 AM.


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