Hello everyone. I am a new developer and I am creating a web site connected to an access 2010 db in VS 2010. I have been searching the web but I know I am not asking the right questions because I am not getting a helpful reference especially since i'm only
a beginner. I know someone can point me in the right direction here so let me explain my scenario.
I have a Facilities table with name address and number of the facility which is connected to FacilityTypes table to categorize them (all facilities, urgent care facilities, and emergency facilities). The third table holds the FacilityHours which holds the
day as a double(0-6) and start and end times for the facilities.
My problem is that when I run the query it retrieves each facility 7 times since there are 7 days of the week. I need to run a query to display facilities based on the day of the week so that my results display each facility with hours one time for that
specific day instead of the each facility for each different day. I am using a datalist connected to a sqldatasource to run this query. I cannot think of a way to combine the weekly schedule into a result for each facility so I have been focusing on getting
the result based on the day of the week that at the time.
The following code is the closest I have gotten to a satisfactory result but I have to pass it the day of week manually in the query. I would like for the query to find out what day of the week it is and use that parameter automatically but I'm not sure
if there is an access function that allows for this.
The following code is the closest I have gotten to a satisfactory result but I have to pass it the day of week manually in the query. I would like for the query to find out what day of the week it is and use that parameter automatically but I'm not sure
if there is an access function that allows for this.
the Now() function returns the current DateTime
SelectCommand="SELECT Facilities.ID, Facilities.FacilityName, Facilities.Address1, Facilities.Address2, Facilities.City, Facilities.State, Facilities.Zip, Facilities.Phone, Facilities.[Image], FacilityHours.[Day], FacilityHours.StartTime, FacilityHours.EndTime FROM (Facilities INNER JOIN FacilityHours ON Facilities.ID = FacilityHours.FacilityID) WHERE (FacilityHours.[Day] = Weekday(Now()))"
Marked as answer by mbanavige on Nov 08, 2012 04:13 PM
PeacefulMind
0 Points
7 Posts
Help DayOfWeek with Access DB based on query
Nov 07, 2012 06:28 PM|LINK
Hello everyone. I am a new developer and I am creating a web site connected to an access 2010 db in VS 2010. I have been searching the web but I know I am not asking the right questions because I am not getting a helpful reference especially since i'm only a beginner. I know someone can point me in the right direction here so let me explain my scenario.
I have a Facilities table with name address and number of the facility which is connected to FacilityTypes table to categorize them (all facilities, urgent care facilities, and emergency facilities). The third table holds the FacilityHours which holds the day as a double(0-6) and start and end times for the facilities.
My problem is that when I run the query it retrieves each facility 7 times since there are 7 days of the week. I need to run a query to display facilities based on the day of the week so that my results display each facility with hours one time for that specific day instead of the each facility for each different day. I am using a datalist connected to a sqldatasource to run this query. I cannot think of a way to combine the weekly schedule into a result for each facility so I have been focusing on getting the result based on the day of the week that at the time.
The following code is the closest I have gotten to a satisfactory result but I have to pass it the day of week manually in the query. I would like for the query to find out what day of the week it is and use that parameter automatically but I'm not sure if there is an access function that allows for this.
<asp:SqlDataSource ID="dsFacilities" runat="server"
ConnectionString="<%$ ConnectionStrings:csMCH %>"
ProviderName="<%$ ConnectionStrings:csMCH.ProviderName %>"
SelectCommand="SELECT Facilities.ID, Facilities.FacilityName, Facilities.Address1, Facilities.Address2, Facilities.City, Facilities.State, Facilities.Zip, Facilities.Phone, Facilities.[Image], FacilityHours.[Day], FacilityHours.StartTime, FacilityHours.EndTime FROM (Facilities INNER JOIN FacilityHours ON Facilities.ID = FacilityHours.FacilityID) WHERE (FacilityHours.[Day] = Weekday(1))"
></asp:SqlDataSource>
Any ideas or ways better ways to approach this would be grately appreciated.
edit:sorry this belongs in web forms with data controls I hope somone can move
peace
PeacefulMind
0 Points
7 Posts
Re: Help DayOfWeek with Access DB based on query
Nov 07, 2012 08:37 PM|LINK
bump
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Help DayOfWeek with Access DB based on query
Nov 08, 2012 06:19 AM|LINK
Hello,
If you wanna make a query dynamic parameter. I think you can choose ControlParameter, which will let you search for specific values.
Please have a look at the sample below:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html> <body> <form runat="server"> <p><asp:dropdownlist id="DropDownList1" runat="server" autopostback="True"> <asp:listitem selected>Sales Representative</asp:listitem> <asp:listitem>Sales Manager</asp:listitem> <asp:listitem>Vice President, Sales</asp:listitem> </asp:dropdownlist></p> <asp:AccessDataSource id="AccessDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:MyNorthwind%>" selectcommand="SELECT LastName FROM Employees WHERE Title = ?"> <selectparameters> <asp:controlparameter name="Title" controlid="DropDownList1" propertyname="SelectedValue"/> </selectparameters> </asp:AccessDataSource> <p><asp:listbox id="ListBox1" runat="server" datasourceid="SqlDataSource1" datatextfield="LastName"> </asp:listbox></p> </form> </body> </html>hans_v
All-Star
35986 Points
6550 Posts
Re: Help DayOfWeek with Access DB based on query
Nov 08, 2012 06:48 AM|LINK
the Now() function returns the current DateTime
PeacefulMind
0 Points
7 Posts
Re: Help DayOfWeek with Access DB based on query
Nov 08, 2012 11:03 AM|LINK
Thank you for your replies!! I actually got it working with the Weekday(Date()) function but Now() works too, good to know!! Thanksagain.