Hi there ! I need something which is a bit complicated ! I will create a searc engine which allows the users searc hotel rates. I can create all of its features except the dates interval and the prices of them.
Let me explain it;
In a hotel period 1 starts 01.01.2009 and ends 31.01.2010. its price in this period is $100
period 2 starts 01.02.2010 and ends 28.02.2010. its price in this period is $110.
For example, lets say a person wants to stay this hotel between 05.01.2010 and 10.01.2010. And;
How could I calculate the price and show it in result page ?
For example, lets say a person wants to stay this hotel between 25.01.2010 and 3.01.2010. And;
There is a conflict here. It gets some of the price form period 1 and period 2. How could I calculate them and view it on result page.
Also according to you experties, if it would be easier, if I used MYSQL?
Hi there ! Thanks a lot for your reply ! But as fas as I understand, its a sql code. But I will use ms access for that and say I will use MYSql, and how could I view the total price in a datalist?
There is a conflict here. It gets some of the price form period 1 and period 2. How could I calculate them and view it on result page.
Also according to you experties, if it would be easier, if I used MYSQL?
Although the data is stored in a database, it doesn't mean that you should do the calculation of the total price in SQL. MySQL won't make things easier.
in the above website, there is a search engine on the left side and I want a searc engine just like the same function.
I happen to know something on hotel search engines. Prices are not specified by periods. Usually, prices in weekends are different from prices during weekdays, even on different weekdays prices can be different. Also, when there are events in the region
of a hotel, prices go up. And sometimes, prices for 1 night are different if you stay shorter/longer, sometimes hotels don't allow check in/out on certain dates, or have a different rate for check in/out on certain dates. So to make a search engine like this,
your datamodel should be a lot more complicated than specifying a price for a period...
Hi hans ! Thanks for your reply ! I agree wtih you but I have periods and prices changes priod by period only. So I need to do that. I didn't start to build my database yet and I am still researching. I need to know that Ms Access will be enough for me or
not ? After that I will start it and how could I do this logic. Do you have any clue on that or any source that you can suggest me to have a look at ?
Let say you have a table [period] with the fields [startperiod], [endperiod] and [price]. To calculate the total price for a given daterange, your query would be something like:
SELECT Sum(subtotal) AS total
FROM (SELECT IIf([startperiod]<[@checkin],[@checkin],[startperiod]) AS [start], IIf([endperiod]>[@checkout],[@checkout],[endperiod]) AS [end], [end]-[start]+1 AS [nights], [price]*[nights] AS subtotal
FROM period
WHERE (((period.startperiod)<=[@checkout]) AND ((period.endperiod)>=[@checkin])))
Your requirement is just like calculating income tax slab.
you too can make one master Rate Slab like this(somewhat)
Rateid | StartDate | EndDate | Rate
Now when user enter startdate enddate,you can query it to find in which bracket it fall then calculate it.
Benefit :i) Proper normalisation.
ii) Permanent records of all rates history.
iii) It can be use in search as well as actual transaction(when customer is staying or stayed).
No idea about mysql.
you can calculate the transaction part in programme.
you can store this ratetid in transaction table which will help in calculating stay cost.
thanks a lot dude ! this is really what I thought but here is the problem. how could I do that programatically? also lets say there is two periods and the date interval includes all two of them. Then what ?
thanks a lot dude ! this is really what I thought but here is the problem. how could I do that programatically? also lets say there is two periods and the date interval includes all two of them. Then what ?
Did you test it already? The query will calculate the total price, even if the entered dates (@checkin and @checkout) span multiple periods! You only need to make sure that the periods don't overlap each other, otherwise some dates will be charged multiple
times...
hans to be perfectly honest I have no clue how to do that ! how to calculate it. If you can give me some source link, I will have a look at and write my code. And then I will get back to you.
hans to be perfectly honest I have no clue how to do that ! how to calculate it. If you can give me some source link, I will have a look at and write my code. And then I will get back to you.
Really?
Public Function TotalPrice(ByVal checkIn As DateTime, ByVal checkout As DateTime) As Double
Using conn As New System.Data.OleDb.OleDbConnection("connectionstring")
Dim strSQL As String = "SELECT Sum(subtotal) AS total FROM (SELECT IIf([startperiod]<[@checkin],[@checkin],[startperiod]) AS [start], IIf([endperiod]>[@checkout],[@checkout],[endperiod]) AS [end], [end]-[start]+1 AS [nights], [price]*[nights] AS subtotal FROM(period) WHERE (((period.startperiod)<=[@checkout]) AND ((period.endperiod)>=[@checkin])))"
Using comm As New System.Data.OleDb.OleDbCommand(strSQL, conn)
comm.Parameters.AddWithValue("checkin", checkIn.Date)
comm.Parameters.AddWithValue("checkout", checkout.Date)
conn.Open()
Return comm.ExecuteScalar
End Using
End Using
End Function
I have given the logic to select the records between the range of dates.
I know, but if you look to the problem, there's no range of days! You example has a date in every record, where in this problem, every record holds a range of days (period). That's a completly different problem!
I have given the logic to select the records between the range of dates.
I know, but if you look to the problem, there's no range of days! You example has a date in every record, where in this problem, every record holds a range of days (period). That's a completly different problem!
Hans you are exatly right about that ! Firstly, I need to understand the logic here ! I know it is a big help I require here and it is a bit coplicated problem. So I really need your experties here .!
I have ID, HotelName, Region column as text. and I have Per1Start, Per1End, Per2Start, Per2End, Per3Start, Per3End, Per4Start, Per4End, Per5Start, Per5End as Date Value. And I have Per1Price, Per2Price, Per3Price, Per4Price, Per5Price as Currency Type.
In Searc Engine, Client will select the name of region and hotel and then client will select the date interval he wants to require. An then in my result page it will show the price which is needed to be calculated automatically. My result page datalist's AccessDataSource's select
parameter's will be querystring parameters.
In a hotel period 1 starts 01.01.2009 and ends 31.01.2010. its price in this period is $100
period 2 starts 01.02.2010 and ends 28.02.2010. its price in this period is $110.
For example, lets say a person wants to stay this hotel between 05.01.2010 and 10.01.2010. And he requires 5 nights accommodation. so it needs to be 5 * 100 = 500
How could I calculate the price and show it in result page ?
For example, lets say a person wants to stay this hotel between 25.01.2010 and 3.01.2010. And;
There is a conflict here. It gets some of the price form period 1 and period 2. How could I calculate them and view it on result page.
Hans you are my only hope on this man ! I hope I am clear all alaong ! Thanks again ! I await you response !
I have ID, HotelName, Region column as text. and I have Per1Start, Per1End, Per2Start, Per2End, Per3Start, Per3End, Per4Start, Per4End, Per5Start, Per5End as Date Value. And I have Per1Price, Per2Price, Per3Price, Per4Price, Per5Price as Currency Type.
First of all, What about HotelName and Region?
I think you should have a seperate table Hotel, With HotelID, HotelName, Region (possibly a pointer to a record in a seperate table region) and perhaps some other properties of the hotel (a description, pictures, ....), and in the table Price you've a ForeignKey/Pointer
(hotelID) to a Hotel
and waht do you mean with Per1Start, Per2Start, Per3Start? What does the number indicate?
Hotel name will be the hotel name And region will be the location of the hotel, destination.
hans_v
I think you should have a seperate table Hotel, With HotelID, HotelName, Region (possibly a pointer to a record in a seperate table region) and perhaps some other properties of the hotel (a description, pictures, ....), and in the table Price you've a ForeignKey/Pointer
(hotelID) to a Hotel
I guess you are right about that. I will try to do that.
hans_v
and in the table Price you've a ForeignKey/Pointer (hotelID) to a Hotel
With HotelID, HotelName, Region (possibly a pointer to a record in a seperate table region) and perhaps some other properties of the hotel (a description, pictures, ....), and in the table Price you've a ForeignKey/Pointer (hotelID) to a Hotel
Hans, you mentioned something named foreignKey. How could I set this in access db? I couldn't find it. and what will it refers?
Hans, you mentioned something named foreignKey. How could I set this in access db? I couldn't find it. and what will it refers?
In this example, in the table Price, HotelID is a Foreign Key. It points to a record in the table Hotel. In Access, in the relation panel, you can set a (1 to many) relation between the two tables. You can also do this in SQL:
ALTER TABLE [Price] ADD CONSTRAINT [HotelPrice] FOREIGN KEY ([HotelID]) REFERENCES [Hotel] ([HotelID]) ON UPDATE NO ACTION ON DELETE NO ACTION
If you try to delete a hotel which already have related records (prices) in the table price, an error will occur. You first have to delete all related records, before you can delete the hotel. ALternativly, you can change the relation, so that in you delete
a hotel, all related records in the table Price will also be deleted at the same time
ALTER TABLE [Price] ADD CONSTRAINT [HotelPrice] FOREIGN KEY ([HotelID]) REFERENCES [Hotel] ([HotelID]) ON UPDATE NO ACTION ON DELETE CASCADE
In access, you can do this by rightclicking on the relationship and check the checkbox for delete...
And for the Hotel tabel, I created a lookup column for regionID with lookup wizard. Does It mean that I made it foreignKey?
This is my first question on your lead !
Hi hans, thanks a lot man ! Really for your support and help. As I mentioned in my previous post I did it with Look up wizard and here my relationship of database;
The are key icons on the tabels but I couldn't find anything anmed foreigkey. am I on the right way? Also here my hotel tabel view;
And Here My price tabel view;
I hope I am doing right.
Also My second problem is that;
for the dropdown databind, I cannot do the below distict with two column name;
The are key icons on the tabels but I couldn't find anything anmed foreigkey. am I on the right way? Also here my hotel tabel view
When you're using a Primary Key from a table, in another table, it's called a Foreign Key. So in this example, HotelID in the table Price is a Foreign Key. In Access, you don't find this naming convention!
tugberk_ugurlu_
I hope I am doing right.
It looks good to me...
tugberk_ugurlu_
Also My second problem is that;
for the dropdown databind, I cannot do the below distict with two column name
What is the problem? By the way, you don't need distinct, because every region is unique in this table!
By the way, what datatype are you using for the field Price in table Price? Access offers also the currency type, but I would recommend using a numeric (double precison) datatype...
Hi hans ! Thanks for your reply ! I am using currency for the prices. and I created two pages for demo.
Searc-Engine.aspx
I created My Simple search engine there hans. I didn't put any validations or clender extender because now only I am using it. here is the code of page;
Partial Class Search_Engine
Inherits System.Web.UI.Page
Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
DropDownList2.Items.Clear()
DropDownList2.Items.Add(New ListItem("---Seçim Yapınız---", "-1"))
DropDownList2.DataSourceID = "AccessDataSource2"
DropDownList2.DataBind()
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim TargetURL As String
Dim URL As String = Request.ServerVariables("HTTP_REFERER")
Dim qStringMe As String = Request.ServerVariables("QUERY_STRING")
TargetURL = "Results.aspx?"
'TargetURL &= "HotelName=" & lbl.Text + "&" + "HotelNameID=" & lblID.Text
TargetURL &= "Resort=" & DropDownList1.SelectedValue.ToString() + "&"
TargetURL &= "Hotel=" & DropDownList2.SelectedValue.ToString() + "&"
TargetURL &= "StartFrom=" & StartDateTxt.Text + "&"
TargetURL &= "Till=" & EndDateTxt.Text
Response.Redirect(TargetURL)
Dim Date1 As Date = StartDateTxt.Text.Trim()
Dim Date2 As Date = EndDateTxt.Text.Trim()
Dim resultofit As String = Date1 + Date2
'MsgBox(resultofit)
End Sub
End Class
So as you can see the values will be in querystring so that datasource on the result page can define it. here is one exaple on the page url; "Results.aspx?Resort=4&Hotel=2&StartFrom=10.01.2010&Till=20.01.2010" And my secod page is Results.aspx
I guess I did my best in searc-engine page but here I have no clue how to calculate the price here is my aspx page code;
First, I noticed something in the relationpanel. Doubleclick on both of the relations (the thin line). You will enter the screen "Edit Relations" or someting like that. You'll see three checkboxes. The first one (something named "Enforce Referential Entegrity") should
be checked. When you click OK, the relationship wil be changed to a 1 to many relationship!
In your searchengine page, change the accessDatasource like this:
<asp:AccessDataSource ID="AccessDataSource1" runat="server"
DataFile="~/App_Data/HotelDB.mdb"
SelectCommand="SELECT RegionID, RegionName, HotelID, HotelName, HotelDescription, CheckIn, CheckOut, Sum(nights) AS totalnights, Sum(subtotal) AS totalprice FROM (SELECT Region.RegionID, Region.RegionName, Hotel.HotelID, Hotel.HotelName, Hotel.HotelDescription, IIf([pricestart]<[@checkin],[@checkin],[pricestart]) AS start, IIf([priceend]>=[@checkout],DateAdd('d',-1,[@checkout]),[priceend]) AS [end], DateDiff('d',[start],[end]) AS nights, [price]*[nights] AS subtotal, [@checkin] AS CheckIn, [@checkout] AS CheckOut FROM Region INNER JOIN (Hotel INNER JOIN price ON Hotel.HotelID = price.HotelID) ON Region.RegionID = Hotel.RegionID WHERE price.pricestart<=[@checkout] AND price.priceend>=[@checkin]) WHERE HotelID = [@hotelID] GROUP BY RegionID, RegionName, HotelID, HotelName, HotelDescription, CheckIn, CheckOut">
<SelectParameters>
<asp:QueryStringParameter Name="CheckIn" QueryStringField="StartFrom" Type="DateTime" />
<asp:QueryStringParameter Name="CheckOut" QueryStringField="Till" Type="DateTime" />
<asp:QueryStringParameter Name="HotelID" QueryStringField="Hotel" Type="Int32" />
</SelectParameters>
</asp:AccessDataSource>
SelectCommand="SELECT RegionID, RegionName, HotelID, HotelName, HotelDescription, CheckIn, CheckOut, Sum(nights) AS totalnights, Sum(subtotal) AS totalprice FROM (SELECT Region.RegionID, Region.Regions, Hotel.HotelID, Hotel.HotelName, Hotel.HotelDescription, IIf([pricestart]<[@checkin],[@checkin],[pricestart]) AS start, IIf([priceend]>[@checkout],DateAdd('d',-1,[@checkout]),[priceend]) AS [end], DateDiff('d',[start],[end]) AS nights, [price]*[nights] AS subtotal, [@checkin] AS CheckIn, [@checkout] AS CheckOut FROM Region INNER JOIN (Hotel INNER JOIN price ON Hotel.HotelID = price.HotelID) ON Region.RegionID = Hotel.RegionID WHERE price.pricestart<=[@checkout] AND price.priceend>=[@checkin]) WHERE HotelID = [@hotelID] GROUP BY RegionID, Regions, HotelID, HotelName, HotelDescription, CheckIn, CheckOut">
Hi hans ! I did what you said in relationship panel. thanks for that ! And replace your code with my accessdatasource code on the result.aspx page. but as you can see above, I don't have some the field you entered. So I changed them. So lets come to datalist.
what do I need to do about it?
SelectCommand="SELECT RegionID, Regions, HotelID, HotelName, HotelDescription, CheckIn, CheckOut, Sum(nights) AS totalnights, Sum(subtotal) AS totalprice FROM (SELECT Region.RegionID, Region.Regions, Hotel.HotelID, Hotel.HotelName, Hotel.HotelDescription, IIf([pricestart]<[@checkin],[@checkin],[pricestart]) AS start, IIf([priceend]>=[@checkout],[@checkout],DateAdd('d',1,[priceend])) AS [end], DateDiff('d',[start],[end]) AS nights, [price]*[nights] AS subtotal, [@checkin] AS CheckIn, [@checkout] AS CheckOut FROM Region INNER JOIN (Hotel INNER JOIN price ON Hotel.HotelID = price.HotelID) ON Region.RegionID = Hotel.RegionID WHERE price.pricestart<[@checkout] AND price.priceend>=[@checkin]) WHERE HotelID = [@hotelID] GROUP BY RegionID, Regions, HotelID, HotelName, HotelDescription, CheckIn, CheckOut">
BTW the code you wrote was awesome. It really looks like written by some expert. Is there any source on internet that I can have a look at the logic of this kind of codes? I mean sql codes.
Hans you are wrong ! we are exactly there its looks perfect now
tugberk_ugurlu_
But do not have any price on this period so it shouldn't show any data
Yep, that's the problem if you choose this simple database schema. As I told you before, hotel search engines are much more complicated. You need to validate the entered dates, and make sure that you've all the price information for all hotels for a given
period. But if some data is missing, it will be hard to show the correct prices!
tugberk_ugurlu_
BTW the code you wrote was awesome. It really looks like written by some expert
And who do you think this 'expert' would be?
tugberk_ugurlu_
Is there any source on internet that I can have a look at the logic of this kind of codes? I mean sql codes.
MMM, this is just standard SQL. I don't know any good resources on the internet, maybe you should post this question in another forum.
Ok then for your answers ! I guess it became a cool post what do you think? thanks for your help all along man again. I will
be in touch with you. you are such a godsend !
If you want to come to turkey, promise me I will be the first human beaing that you wanna keep in touch with
I will do my best with my experties for you as you did for me
Participant
1039 Points
1332 Posts
MVP
get the value between the specific dates !
Jan 29, 2010 08:43 AM|tugberk_ugurlu_|LINK
Hi there ! I need something which is a bit complicated ! I will create a searc engine which allows the users searc hotel rates. I can create all of its features except the dates interval and the prices of them.
Let me explain it;
In a hotel period 1 starts 01.01.2009 and ends 31.01.2010. its price in this period is $100
period 2 starts 01.02.2010 and ends 28.02.2010. its price in this period is $110.
For example, lets say a person wants to stay this hotel between 05.01.2010 and 10.01.2010. And;
How could I calculate the price and show it in result page ?
For example, lets say a person wants to stay this hotel between 25.01.2010 and 3.01.2010. And;
There is a conflict here. It gets some of the price form period 1 and period 2. How could I calculate them and view it on result page.
Also according to you experties, if it would be easier, if I used MYSQL?
I really need this guys ! Thanks for your help !
tweets as @tourismgeek
Participant
860 Points
257 Posts
Re: get the value between the specific dates !
Jan 29, 2010 10:32 AM|jsd24|LINK
CREATE TABLE #Hotel ( StartDate DATETIME , EndDate DATETIME , Price FLOAT ) INSERT INTO #Hotel VALUES ('01/01/2009', '01/31/2010', 100) INSERT INTO #Hotel VALUES ('02/01/2010', '02/28/2010', 110) DECLARE @PersonStartDate DATETIME DECLARE @PersonEndDate DATETIME SET @PersonStartDate = '01/05/2010' -- '01/25/2010' -- SET @PersonEndDate = '01/10/2010' -- '02/03/2010' -- SELECT * FROM #Hotel WHERE @PersonStartDate BETWEEN StartDate AND EndDate OR @PersonEndDate BETWEEN StartDate AND EndDate DROP TABLE #HotelMark As Answer
My Blog : http://jsdideas.blogspot.com
Participant
1039 Points
1332 Posts
MVP
Re: get the value between the specific dates !
Jan 29, 2010 01:08 PM|tugberk_ugurlu_|LINK
Hi there ! Thanks a lot for your reply ! But as fas as I understand, its a sql code. But I will use ms access for that and say I will use MYSql, and how could I view the total price in a datalist?
tweets as @tourismgeek
Participant
1039 Points
1332 Posts
MVP
Re: get the value between the specific dates !
Jan 29, 2010 01:24 PM|tugberk_ugurlu_|LINK
http://www.hotels4u.com/
in the above website, there is a search engine on the left side and I want a searc engine just like the same function.
tweets as @tourismgeek
All-Star
25756 Points
7025 Posts
Re: get the value between the specific dates !
Jan 29, 2010 06:26 PM|hans_v|LINK
Although the data is stored in a database, it doesn't mean that you should do the calculation of the total price in SQL. MySQL won't make things easier.
I happen to know something on hotel search engines. Prices are not specified by periods. Usually, prices in weekends are different from prices during weekdays, even on different weekdays prices can be different. Also, when there are events in the region of a hotel, prices go up. And sometimes, prices for 1 night are different if you stay shorter/longer, sometimes hotels don't allow check in/out on certain dates, or have a different rate for check in/out on certain dates. So to make a search engine like this, your datamodel should be a lot more complicated than specifying a price for a period...
Participant
1039 Points
1332 Posts
MVP
Re: get the value between the specific dates !
Jan 29, 2010 06:59 PM|tugberk_ugurlu_|LINK
Hi hans ! Thanks for your reply ! I agree wtih you but I have periods and prices changes priod by period only. So I need to do that. I didn't start to build my database yet and I am still researching. I need to know that Ms Access will be enough for me or not ? After that I will start it and how could I do this logic. Do you have any clue on that or any source that you can suggest me to have a look at ?
tweets as @tourismgeek
All-Star
25756 Points
7025 Posts
Re: get the value between the specific dates !
Jan 30, 2010 05:35 AM|hans_v|LINK
Let say you have a table [period] with the fields [startperiod], [endperiod] and [price]. To calculate the total price for a given daterange, your query would be something like:
Star
9276 Points
3926 Posts
Re: get the value between the specific dates !
Jan 30, 2010 06:20 AM|KumarHarsh|LINK
Your requirement is just like calculating income tax slab.
you too can make one master Rate Slab like this(somewhat)
Rateid | StartDate | EndDate | Rate
Now when user enter startdate enddate,you can query it to find in which bracket it fall then calculate it.
Benefit :i) Proper normalisation.
ii) Permanent records of all rates history.
iii) It can be use in search as well as actual transaction(when customer is staying or stayed).
No idea about mysql.
you can calculate the transaction part in programme.
you can store this ratetid in transaction table which will help in calculating stay cost.
Kumar Harsh
Participant
1039 Points
1332 Posts
MVP
Re: get the value between the specific dates !
Jan 30, 2010 06:31 AM|tugberk_ugurlu_|LINK
thanks a lot dude ! this is really what I thought but here is the problem. how could I do that programatically? also lets say there is two periods and the date interval includes all two of them. Then what ?
tweets as @tourismgeek
All-Star
25756 Points
7025 Posts
Re: get the value between the specific dates !
Jan 30, 2010 06:38 AM|hans_v|LINK
Did you test it already? The query will calculate the total price, even if the entered dates (@checkin and @checkout) span multiple periods! You only need to make sure that the periods don't overlap each other, otherwise some dates will be charged multiple times...
Participant
1039 Points
1332 Posts
MVP
Re: get the value between the specific dates !
Jan 30, 2010 06:43 AM|tugberk_ugurlu_|LINK
hans to be perfectly honest I have no clue how to do that ! how to calculate it. If you can give me some source link, I will have a look at and write my code. And then I will get back to you.
tweets as @tourismgeek
All-Star
22967 Points
5507 Posts
MVP
Re: get the value between the specific dates !
Jan 30, 2010 06:44 AM|raghav_khunger|LINK
You can also look at the:
http://codeasp.net/blogs/raghav_khunger/microsoft-net/633/how-to-select-records-falling-between-two-dates
However I have written in SQL but you can grasp the logic from there.
All-Star
25756 Points
7025 Posts
Re: get the value between the specific dates !
Jan 30, 2010 06:53 AM|hans_v|LINK
Really?
Public Function TotalPrice(ByVal checkIn As DateTime, ByVal checkout As DateTime) As Double Using conn As New System.Data.OleDb.OleDbConnection("connectionstring") Dim strSQL As String = "SELECT Sum(subtotal) AS total FROM (SELECT IIf([startperiod]<[@checkin],[@checkin],[startperiod]) AS [start], IIf([endperiod]>[@checkout],[@checkout],[endperiod]) AS [end], [end]-[start]+1 AS [nights], [price]*[nights] AS subtotal FROM(period) WHERE (((period.startperiod)<=[@checkout]) AND ((period.endperiod)>=[@checkin])))" Using comm As New System.Data.OleDb.OleDbCommand(strSQL, conn) comm.Parameters.AddWithValue("checkin", checkIn.Date) comm.Parameters.AddWithValue("checkout", checkout.Date) conn.Open() Return comm.ExecuteScalar End Using End Using End FunctionAll-Star
25756 Points
7025 Posts
Re: get the value between the specific dates !
Jan 30, 2010 07:00 AM|hans_v|LINK
To grasp the logic, you need to understand the logic first, but I do not see how it applies to this problem?
All-Star
22967 Points
5507 Posts
MVP
Re: get the value between the specific dates !
Jan 30, 2010 07:04 AM|raghav_khunger|LINK
I have given the logic to select the records between the range of dates.
All-Star
25756 Points
7025 Posts
Re: get the value between the specific dates !
Jan 30, 2010 07:10 AM|hans_v|LINK
I know, but if you look to the problem, there's no range of days! You example has a date in every record, where in this problem, every record holds a range of days (period). That's a completly different problem!
Participant
1039 Points
1332 Posts
MVP
Re: get the value between the specific dates !
Jan 30, 2010 07:16 AM|tugberk_ugurlu_|LINK
Hans you are exatly right about that ! Firstly, I need to understand the logic here ! I know it is a big help I require here and it is a bit coplicated problem. So I really need your experties here .!
tweets as @tourismgeek
All-Star
25756 Points
7025 Posts
Re: get the value between the specific dates !
Jan 30, 2010 07:21 AM|hans_v|LINK
Tugberk,
Please tell me how your table looks like?
Participant
1039 Points
1332 Posts
MVP
Re: get the value between the specific dates !
Jan 30, 2010 07:38 AM|tugberk_ugurlu_|LINK
Hi hans,
I have ID, HotelName, Region column as text. and I have Per1Start, Per1End, Per2Start, Per2End, Per3Start, Per3End, Per4Start, Per4End, Per5Start, Per5End as Date Value. And I have Per1Price, Per2Price, Per3Price, Per4Price, Per5Price as Currency Type.
In Searc Engine, Client will select the name of region and hotel and then client will select the date interval he wants to require. An then in my result page it will show the price which is needed to be calculated automatically. My result page datalist's AccessDataSource's select parameter's will be querystring parameters.
In a hotel period 1 starts 01.01.2009 and ends 31.01.2010. its price in this period is $100
period 2 starts 01.02.2010 and ends 28.02.2010. its price in this period is $110.
For example, lets say a person wants to stay this hotel between 05.01.2010 and 10.01.2010. And he requires 5 nights accommodation. so it needs to be 5 * 100 = 500
How could I calculate the price and show it in result page ?
For example, lets say a person wants to stay this hotel between 25.01.2010 and 3.01.2010. And;
There is a conflict here. It gets some of the price form period 1 and period 2. How could I calculate them and view it on result page.
Hans you are my only hope on this man ! I hope I am clear all alaong ! Thanks again ! I await you response !
tweets as @tourismgeek
All-Star
25756 Points
7025 Posts
Re: get the value between the specific dates !
Jan 30, 2010 07:55 AM|hans_v|LINK
First of all, What about HotelName and Region?
I think you should have a seperate table Hotel, With HotelID, HotelName, Region (possibly a pointer to a record in a seperate table region) and perhaps some other properties of the hotel (a description, pictures, ....), and in the table Price you've a ForeignKey/Pointer (hotelID) to a Hotel
and waht do you mean with Per1Start, Per2Start, Per3Start? What does the number indicate?
Participant
1039 Points
1332 Posts
MVP
Re: get the value between the specific dates !
Jan 30, 2010 08:09 AM|tugberk_ugurlu_|LINK
Hotel name will be the hotel name And region will be the location of the hotel, destination.
I guess you are right about that. I will try to do that.
Will it be a query or a seperate table?
tweets as @tourismgeek
All-Star
25756 Points
7025 Posts
Re: get the value between the specific dates !
Jan 30, 2010 08:20 AM|hans_v|LINK
A table
[Region]
RegionID
RegionName
...
[Hotel]
HotelID
HotelName
HotelDescription
RegionID
...
[Price]
PriceID
HotelID
StartPeriod
EndPeriod
Price
Participant
1039 Points
1332 Posts
MVP
Re: get the value between the specific dates !
Jan 30, 2010 10:12 AM|tugberk_ugurlu_|LINK
Hans, you mentioned something named foreignKey. How could I set this in access db? I couldn't find it. and what will it refers?
tweets as @tourismgeek
Participant
1039 Points
1332 Posts
MVP
Re: get the value between the specific dates !
Jan 30, 2010 12:53 PM|tugberk_ugurlu_|LINK
Hi hans again ! I am following your lead step by step ! I created two tabels firstly. The tabels are just Like you said;
[Region]
RegionID
RegionName
...
[Hotel]
HotelID
HotelName
HotelDescription
RegionID
And for the Hotel tabel, I created a lookup column for regionID with lookup wizard. Does It mean that I made it foreignKey?
This is my first question on your lead !
tweets as @tourismgeek
All-Star
25756 Points
7025 Posts
Re: get the value between the specific dates !
Jan 30, 2010 01:04 PM|hans_v|LINK
In this example, in the table Price, HotelID is a Foreign Key. It points to a record in the table Hotel. In Access, in the relation panel, you can set a (1 to many) relation between the two tables. You can also do this in SQL:
ALTER TABLE [Price] ADD CONSTRAINT [HotelPrice] FOREIGN KEY ([HotelID]) REFERENCES [Hotel] ([HotelID]) ON UPDATE NO ACTION ON DELETE NO ACTION
If you try to delete a hotel which already have related records (prices) in the table price, an error will occur. You first have to delete all related records, before you can delete the hotel. ALternativly, you can change the relation, so that in you delete a hotel, all related records in the table Price will also be deleted at the same time
ALTER TABLE [Price] ADD CONSTRAINT [HotelPrice] FOREIGN KEY ([HotelID]) REFERENCES [Hotel] ([HotelID]) ON UPDATE NO ACTION ON DELETE CASCADE
In access, you can do this by rightclicking on the relationship and check the checkbox for delete...
Participant
1039 Points
1332 Posts
MVP
Re: get the value between the specific dates !
Jan 30, 2010 01:27 PM|tugberk_ugurlu_|LINK
Hi hans, thanks a lot man ! Really for your support and help. As I mentioned in my previous post I did it with Look up wizard and here my relationship of database;
The are key icons on the tabels but I couldn't find anything anmed foreigkey. am I on the right way? Also here my hotel tabel view;
And Here My price tabel view;
I hope I am doing right.
Also My second problem is that;
for the dropdown databind, I cannot do the below distict with two column name;
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="AccessDataSource1" DataTextField="Regions" DataValueField="RegionID"></asp:DropDownList> <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/HotelDB.mdb" SelectCommand="SELECT distinct [Regions], [RegionID] FROM [Region]"></asp:AccessDataSource>I await your reply hans. thanks !
tweets as @tourismgeek
All-Star
25756 Points
7025 Posts
Re: get the value between the specific dates !
Jan 30, 2010 01:58 PM|hans_v|LINK
When you're using a Primary Key from a table, in another table, it's called a Foreign Key. So in this example, HotelID in the table Price is a Foreign Key. In Access, you don't find this naming convention!
It looks good to me...
What is the problem? By the way, you don't need distinct, because every region is unique in this table!
All-Star
25756 Points
7025 Posts
Re: get the value between the specific dates !
Jan 30, 2010 02:01 PM|hans_v|LINK
By the way, what datatype are you using for the field Price in table Price? Access offers also the currency type, but I would recommend using a numeric (double precison) datatype...
Participant
1039 Points
1332 Posts
MVP
Re: get the value between the specific dates !
Jan 30, 2010 02:14 PM|tugberk_ugurlu_|LINK
Hi hans ! Thanks for your reply ! I am using currency for the prices. and I created two pages for demo.
Searc-Engine.aspx
I created My Simple search engine there hans. I didn't put any validations or clender extender because now only I am using it. here is the code of page;
aspx code
<h1>Hotel Search Engine</h1> <table> <tr> <td style="width: 100px;">Region</td> <td style="width: 6px;">:</td> <td> <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="AccessDataSource1" AppendDataBoundItems="true" DataTextField="Regions" DataValueField="RegionID"> <asp:ListItem Selected="True" Text="--Seçim Yapınız--" Value="-1"/> </asp:DropDownList> <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/HotelDB.mdb" SelectCommand="SELECT * FROM [Region]"></asp:AccessDataSource> </td> </tr> <tr> <td style="width: 100px;">Hotel</td> <td style="width: 6px;">:</td> <td> <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="AccessDataSource2" DataTextField="HotelName" DataValueField="HotelID" AppendDataBoundItems="true"> <asp:ListItem Selected="True" Text="--Seçim Yapınız--" Value="-1"/> </asp:DropDownList> <asp:AccessDataSource ID="AccessDataSource2" runat="server" DataFile="~/App_Data/HotelDB.mdb" SelectCommand="SELECT * FROM [Hotel] WHERE ([RegionID] = ?) ORDER BY [HotelName]"> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="RegionID" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> </asp:AccessDataSource> </td> </tr> <tr> <td style="width: 100px;">Date 1</td> <td style="width: 6px;">:</td> <td> <asp:TextBox ID="StartDateTxt" runat="server"></asp:TextBox> </td> </tr> <tr> <td style="width: 100px;">Date 2</td> <td style="width: 6px;">:</td> <td> <asp:TextBox ID="EndDateTxt" runat="server"></asp:TextBox> </td> </tr> <tr> <td style="width: 100px;"> <asp:Button ID="Button1" runat="server" Text="Button" /></td> <td style="width: 6px;"></td> <td></td> </tr> </table>aspx.vb Code
Partial Class Search_Engine Inherits System.Web.UI.Page Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged DropDownList2.Items.Clear() DropDownList2.Items.Add(New ListItem("---Seçim Yapınız---", "-1")) DropDownList2.DataSourceID = "AccessDataSource2" DropDownList2.DataBind() End Sub Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim TargetURL As String Dim URL As String = Request.ServerVariables("HTTP_REFERER") Dim qStringMe As String = Request.ServerVariables("QUERY_STRING") TargetURL = "Results.aspx?" 'TargetURL &= "HotelName=" & lbl.Text + "&" + "HotelNameID=" & lblID.Text TargetURL &= "Resort=" & DropDownList1.SelectedValue.ToString() + "&" TargetURL &= "Hotel=" & DropDownList2.SelectedValue.ToString() + "&" TargetURL &= "StartFrom=" & StartDateTxt.Text + "&" TargetURL &= "Till=" & EndDateTxt.Text Response.Redirect(TargetURL) Dim Date1 As Date = StartDateTxt.Text.Trim() Dim Date2 As Date = EndDateTxt.Text.Trim() Dim resultofit As String = Date1 + Date2 'MsgBox(resultofit) End Sub End ClassSo as you can see the values will be in querystring so that datasource on the result page can define it. here is one exaple on the page url; "Results.aspx?Resort=4&Hotel=2&StartFrom=10.01.2010&Till=20.01.2010"
And my secod page is Results.aspx
I guess I did my best in searc-engine page but here I have no clue how to calculate the price
here is my aspx page code;
<div style="width: 100;"> <asp:DataList ID="DataList1" runat="server" DataKeyField="PriceID" DataSourceID="AccessDataSource1" Width="100%"> <ItemTemplate> PriceID: <asp:Label ID="PriceIDLabel" runat="server" Text='<%# Eval("PriceID") %>' /> <br /> HotelID: <asp:Label ID="HotelIDLabel" runat="server" Text='<%# Eval("HotelID") %>' /> <br /> PriceStart: <asp:Label ID="PriceStartLabel" runat="server" Text='<%# Eval("PriceStart") %>' /> <br /> PriceEnd: <asp:Label ID="PriceEndLabel" runat="server" Text='<%# Eval("PriceEnd") %>' /> <br /> Price: <asp:Label ID="PriceLabel" runat="server" Text='<%# Eval("Price") %>' /> <br /> <br /> </ItemTemplate> </asp:DataList> <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/HotelDB.mdb" SelectCommand="SELECT * FROM [Price] WHERE ([HotelID] = ?)"> <SelectParameters> <asp:QueryStringParameter Name="HotelID" QueryStringField="Hotel" Type="Int32" /> </SelectParameters> </asp:AccessDataSource> </div>hans now I need your magic hand here !
tweets as @tourismgeek
All-Star
25756 Points
7025 Posts
Re: get the value between the specific dates !
Jan 30, 2010 03:47 PM|hans_v|LINK
First, I noticed something in the relationpanel. Doubleclick on both of the relations (the thin line). You will enter the screen "Edit Relations" or someting like that. You'll see three checkboxes. The first one (something named "Enforce Referential Entegrity") should be checked. When you click OK, the relationship wil be changed to a 1 to many relationship!
In your searchengine page, change the accessDatasource like this:
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/HotelDB.mdb" SelectCommand="SELECT RegionID, RegionName, HotelID, HotelName, HotelDescription, CheckIn, CheckOut, Sum(nights) AS totalnights, Sum(subtotal) AS totalprice FROM (SELECT Region.RegionID, Region.RegionName, Hotel.HotelID, Hotel.HotelName, Hotel.HotelDescription, IIf([pricestart]<[@checkin],[@checkin],[pricestart]) AS start, IIf([priceend]>=[@checkout],DateAdd('d',-1,[@checkout]),[priceend]) AS [end], DateDiff('d',[start],[end]) AS nights, [price]*[nights] AS subtotal, [@checkin] AS CheckIn, [@checkout] AS CheckOut FROM Region INNER JOIN (Hotel INNER JOIN price ON Hotel.HotelID = price.HotelID) ON Region.RegionID = Hotel.RegionID WHERE price.pricestart<=[@checkout] AND price.priceend>=[@checkin]) WHERE HotelID = [@hotelID] GROUP BY RegionID, RegionName, HotelID, HotelName, HotelDescription, CheckIn, CheckOut"> <SelectParameters> <asp:QueryStringParameter Name="CheckIn" QueryStringField="StartFrom" Type="DateTime" /> <asp:QueryStringParameter Name="CheckOut" QueryStringField="Till" Type="DateTime" /> <asp:QueryStringParameter Name="HotelID" QueryStringField="Hotel" Type="Int32" /> </SelectParameters> </asp:AccessDataSource>Participant
1039 Points
1332 Posts
MVP
Re: get the value between the specific dates !
Jan 30, 2010 04:11 PM|tugberk_ugurlu_|LINK
SelectCommand="SELECT RegionID, RegionName, HotelID, HotelName, HotelDescription, CheckIn, CheckOut, Sum(nights) AS totalnights, Sum(subtotal) AS totalprice FROM (SELECT Region.RegionID, Region.Regions, Hotel.HotelID, Hotel.HotelName, Hotel.HotelDescription, IIf([pricestart]<[@checkin],[@checkin],[pricestart]) AS start, IIf([priceend]>[@checkout],DateAdd('d',-1,[@checkout]),[priceend]) AS [end], DateDiff('d',[start],[end]) AS nights, [price]*[nights] AS subtotal, [@checkin] AS CheckIn, [@checkout] AS CheckOut FROM Region INNER JOIN (Hotel INNER JOIN price ON Hotel.HotelID = price.HotelID) ON Region.RegionID = Hotel.RegionID WHERE price.pricestart<=[@checkout] AND price.priceend>=[@checkin]) WHERE HotelID = [@hotelID] GROUP BY RegionID, Regions, HotelID, HotelName, HotelDescription, CheckIn, CheckOut">Hi hans ! I did what you said in relationship panel. thanks for that ! And replace your code with my accessdatasource code on the result.aspx page. but as you can see above, I don't have some the field you entered. So I changed them. So lets come to datalist. what do I need to do about it?
tweets as @tourismgeek
All-Star
25756 Points
7025 Posts
Re: get the value between the specific dates !
Jan 30, 2010 04:22 PM|hans_v|LINK
I see, don't forget to change RegionName to Regions in the first line!
Also, I made a smaal change in:
IIf([priceend]>[@checkout]
Should be
IIf([priceend]>=[@checkout]
All-Star
25756 Points
7025 Posts
Re: get the value between the specific dates !
Jan 30, 2010 04:24 PM|hans_v|LINK
Just use any of the Selected fields!
The Total price is:
<asp:Label ID="PriceIDLabel" runat="server" Text='<%# Eval("totalprice", "{0:C}") %>' />
Participant
1039 Points
1332 Posts
MVP
Re: get the value between the specific dates !
Jan 30, 2010 04:27 PM|tugberk_ugurlu_|LINK
hi hans ! firts of all you did well in that code ! but there are some errors on that which I cannot repair. I did some datalist coding as follows;
<asp:DataList ID="DataList1" runat="server" DataSourceID="AccessDataSource1"> <ItemTemplate> Region ID: <asp:Label ID="Label1" runat="server" Text='<%# Eval("RegionID") %>'></asp:Label><br /><br /> Hotel Name: <asp:Label ID="Label2" runat="server" Text='<%# Eval("hotelname") %>'></asp:Label><br /><br /> Total Nights: <asp:Label ID="Label3" runat="server" Text='<%# Eval("totalnights") %>'></asp:Label><br /><br /> Total Price: <asp:Label ID="Label4" runat="server" Text='<%# Eval("totalprice") %>'></asp:Label><br /><br /> Check-Out: <asp:Label ID="Label5" runat="server" Text='<%# Eval("checkout") %>'></asp:Label><br /><br /> Check-In: <asp:Label ID="Label6" runat="server" Text='<%# Eval("checkin") %>'></asp:Label><br /><br /> </ItemTemplate> </asp:DataList>But it miscalculates the number of nights wrong ! here and example;
Results.aspx?Resort=4&Hotel=2&StartFrom=25.05.2010&Till=28.05.2010
as you can see on querrystring, it needs to be 3 nights but it shows as 2 nights ! Like below;
Region ID: 4
Hotel Name: Rixos Sungate
Total Nights: 2
Total Price: 170
tweets as @tourismgeek
Participant
1039 Points
1332 Posts
MVP
Re: get the value between the specific dates !
Jan 30, 2010 04:32 PM|tugberk_ugurlu_|LINK
Thanks again hans I did it as well but it still miscalculate the number of nights !
tweets as @tourismgeek
All-Star
25756 Points
7025 Posts
Re: get the value between the specific dates !
Jan 30, 2010 04:48 PM|hans_v|LINK
We're nearly there
SelectCommand="SELECT RegionID, Regions, HotelID, HotelName, HotelDescription, CheckIn, CheckOut, Sum(nights) AS totalnights, Sum(subtotal) AS totalprice FROM (SELECT Region.RegionID, Region.Regions, Hotel.HotelID, Hotel.HotelName, Hotel.HotelDescription, IIf([pricestart]<[@checkin],[@checkin],[pricestart]) AS start, IIf([priceend]>=[@checkout],[@checkout],DateAdd('d',1,[priceend])) AS [end], DateDiff('d',[start],[end]) AS nights, [price]*[nights] AS subtotal, [@checkin] AS CheckIn, [@checkout] AS CheckOut FROM Region INNER JOIN (Hotel INNER JOIN price ON Hotel.HotelID = price.HotelID) ON Region.RegionID = Hotel.RegionID WHERE price.pricestart<[@checkout] AND price.priceend>=[@checkin]) WHERE HotelID = [@hotelID] GROUP BY RegionID, Regions, HotelID, HotelName, HotelDescription, CheckIn, CheckOut">Participant
1039 Points
1332 Posts
MVP
Re: get the value between the specific dates !
Jan 30, 2010 05:08 PM|tugberk_ugurlu_|LINK
Hans you are wrong ! we are exactly there
its looks perfect now. but I have a few thing that I cannot cover yet
:( BTW thanks for putting up with me 
Here some results;
Results.aspx?Resort=4&Hotel=2&StartFrom=26.04.2010&Till=03.05.2010
in this querys string, it starts on 26.04.2010. But do not have any price on this period so it shouldn't show any data. but it show below one;
Region ID: 4
Hotel Name: Rixos Sungate
Total Nights: 2
Total Price: 170
Check-Out:
Check-In:
Results.aspx?Resort=4&Hotel=2&StartFrom=26.06.2010&Till=03.07.2010
Aslo this one as well. it ends on 03.07.2010 but I do not have any price after 31.06.2010. It show below;
Region ID: 4
Hotel Name: Rixos Sungate
Total Nights: 5
Total Price: 635
Check-Out:
Check-In:
Aslo checkout and checkin day doescn't appear;
Check-Out: <asp:Label ID="Label5" runat="server" Text='<%# Eval("checkout") %>'></asp:Label><br /><br /> Check-In: <asp:Label ID="Label6" runat="server" Text='<%# Eval("checkin") %>'></asp:Label><br /><br />BTW the code you wrote was awesome. It really looks like written by some expert. Is there any source on internet that I can have a look at the logic of this kind of codes? I mean sql codes.
tweets as @tourismgeek
All-Star
25756 Points
7025 Posts
Re: get the value between the specific dates !
Jan 30, 2010 05:18 PM|hans_v|LINK
Yep, that's the problem if you choose this simple database schema. As I told you before, hotel search engines are much more complicated. You need to validate the entered dates, and make sure that you've all the price information for all hotels for a given period. But if some data is missing, it will be hard to show the correct prices!
And who do you think this 'expert' would be?
MMM, this is just standard SQL. I don't know any good resources on the internet, maybe you should post this question in another forum.
Participant
1039 Points
1332 Posts
MVP
Re: get the value between the specific dates !
Jan 30, 2010 05:24 PM|tugberk_ugurlu_|LINK
Ok then for your answers ! I guess it became a cool post
what do you think? thanks for your help all along man again. I will
be in touch with you. you are such a godsend !
tweets as @tourismgeek
All-Star
25756 Points
7025 Posts
Re: get the value between the specific dates !
Jan 30, 2010 05:28 PM|hans_v|LINK
When you're finished, you can help me find a cheap hotel
Participant
1039 Points
1332 Posts
MVP
Re: get the value between the specific dates !
Jan 30, 2010 05:31 PM|tugberk_ugurlu_|LINK
If you want to come to turkey, promise me I will be the first human beaing that you wanna keep in touch with
I will do my best with my experties for you as you did for me 
tweets as @tourismgeek
All-Star
25756 Points
7025 Posts
Re: get the value between the specific dates !
Jan 30, 2010 05:35 PM|hans_v|LINK
That's a deal....
Goodnight!
Participant
1039 Points
1332 Posts
MVP
Re: get the value between the specific dates !
Jan 30, 2010 05:36 PM|tugberk_ugurlu_|LINK
good night dude !
tweets as @tourismgeek