Last post Jul 02, 2010 08:37 PM by DAmanding
Jul 02, 2010 04:25 PM|shawn.bordeaux|LINK
Basically I want to populate a drop down list with time slots for a user to select. If the time slot is already recorded in the database table I want to skip that one so it doesn't display as a selection in the drop down list.
So my drop down list looks like;
<asp:DropDownList ID="DD_time" Visible="false" runat="server">
<asp:ListItem Value="0800">8:00 AM</asp:ListItem>
<asp:ListItem Value="0900">9:00 AM</asp:ListItem>
<asp:ListItem Value="1000">10:00 AM</asp:ListItem>
<asp:ListItem Value="1100">11:00 AM</asp:ListItem>
<asp:ListItem Value="1200">12:00 PM</asp:ListItem>
<asp:ListItem Value="1300">1:00 PM</asp:ListItem>
<asp:ListItem Value="1400">2:00 PM</asp:ListItem>
<asp:ListItem Value="1500">3:00 PM</asp:ListItem>
<asp:ListItem Value="1600">4:00 PM</asp:ListItem>
<asp:ListItem Value="1700">5:00 PM</asp:ListItem>
And then I have setup a datasource (even though I may get rid of it to bind in the code behind file)
<asp:SqlDataSource ID="SqlDataSource_time" runat="server"
ConnectionString="<%$ ConnectionStrings:justt3ConnectionString %>"
SelectCommand="SELECT [time] FROM [appointment_days] WHERE ([date] = @date)">
<asp:ControlParameter ControlID="txt_calendar" Name="date" PropertyName="Text"
So maybe I could do an If Then statement? Any help on how I should go about skipping the used time slots would be greatly appreciated!
Jul 02, 2010 06:10 PM|viddhi|LINK
Why you want to specify the list items statically??? You can set it to the datasource directly in the page_load event.
DD_time.datasourceId = sqldatasource_time;
Setting it statically and again binding it from DB is timeconsuming. So you directly set it
Jul 02, 2010 06:40 PM|march11|LINK
I would recommend adding a field to the database, say active/inactive, it could be a binary, or just a simple string field Y/N.
Then your where clause could select all times that still have N for NO, not yet assigned. When a selection is made, change said field to Y indicating that it is now used and preventing it from displaying again.
Jul 02, 2010 06:50 PM|shawn.bordeaux|LINK
Thanks. The reason is because not all the times are in that datasource. The statement is just returning times that have been entered into the database table. Not everytime is in there. I don't have a table with ever date and time blocks in it. Just one appointment
table that I insert a date and time into. The datasource then pulls the times used.
For example here is what the data table may look like;
ID| customer_id | date | time
1 | 234 |07/02/2010| 0800
2 | 132 |07/02/2010| 1200
3 | 523 |07/03/2010| 0900
So if I selected 7/2/2010 as the date parameter the data source is going to return "0800, 1200". So I want those times not to be displayed when I use the static list. There is probably an easier way to do this and I am up for suggestions.
Jul 02, 2010 07:03 PM|march11|LINK
SelectCommand="SELECT [time] FROM [appointment_days] WHERE ([date] = @date) AND ([newField] <> "Y")
I take it your currently returned list has multiple days for the same date as you are not using the key word DISTINCT, nor could you if you are looking for multiple times to be returned.
Jul 02, 2010 07:05 PM|march11|LINK
You will need a second data source to query the table. Then populate the dropdown list with the query results.
Jul 02, 2010 07:15 PM|shawn.bordeaux|LINK
Since not every time block is stored in the data table the datasource is just going to return what is stored. Here are the constants that I know;
First there are 10 time blocks for every day's schedule, each in a one hour block. Starting at 0800 and ending at 1700.
I do not have a calendar type table that has everyday of the week with time blocks so I can mark "Y" or NULL if it's been used. I understand that would be easier since i could look up all times on next monday that are NULL in the used column.
What I am doing is inserting a new record for each appointment. All this records includes is what I listed above. ID | customer_id | date | time. So next monday I may have two appointments inserted, one for 0800 and one for 1200. Other than that there is
no other time block listed for that day. So if I added a field for if used or booked or whatever, and then searched by that I would not get any results since the only things there are the two appointments and of course those time blocks are used.
I appreciate the help and suggestions!
Jul 02, 2010 07:42 PM|march11|LINK
I would still build a second table:
Date | 8 | 9 | 10 | 11| 12 | 13 | 14 | ... | 17
8/1/2010 | Y | N | Y | N | Y | ..... | N
8/2/2010 | Y | Y | N |.....etc
query this table, change the value of a given date and hour based on what gets selected and updated to your appoitment_days table.
Jul 02, 2010 08:29 PM|shawn.bordeaux|LINK
Thanks again. I ended up going another route though. I instead just used an arraylist to store the times and if the time matched then I removed it from the arraylist. I then used this array list as my datasource for the dropdown list. Code is below. Thank
you for the help though!
'code for the drop down window to select time
'dim and set are variables
Dim intEight As String = "0800"
Dim intNine As String = "0900"
Dim intTen As String = "1000"
Dim intElleven As String = "1100"
Dim intTweleve As String = "1200"
Dim intThirteen As String = "1300"
Dim intFourteen As String = "1400"
Dim intFifteen As String = "1500"
'read stats for the referral member who is logged in and then add them to come up with their statistic totals
Dim strConnString3 As String = ConfigurationManager.ConnectionStrings("justt3ConnectionString").ConnectionString
Dim queryString3 As String = _
" SELECT [appointment_time] FROM appointment_log WHERE appointment_date=@date ;"
Using connection3 As New SqlConnection(strConnString3)
Dim command3 As New SqlCommand(queryString3, connection3)
'open the sql connection
'start the data reader
Dim reader3 As SqlDataReader = command3.ExecuteReader()
'set up our variable for the array of values for the dorp down list
Dim times As New ArrayList
'set our array list for times. remove as needed
' Call Reader before accessing data.
If txt_calendar.Text.ToString() <> "" Then
If reader3(0) = intEight Then
'if the reader matches a time variable then remove that from the array
ElseIf reader3(0) = intNine Then
ElseIf reader3(0) = intTen Then
' End the loop and then bind the datasource
Me.DD_time.DataSource = times
Jul 02, 2010 08:37 PM|DAmanding|LINK
Please mark this post as answered. :)