Answered by:
Wehere Statement doesn't work properly !

Question
-
User-706647060 posted
Hi everybody ! I have written a bunch of T-sql code to my accessdatasource selectcommand. But I am having problem with where statement ! here is my code;
SELECT OWGBP, RTGBP, OWEUR, RTEUR, OWYTL, RTYTL, (SELECT Resort FROM Resort RF WHERE (View = True) AND (RF.ResID = TypePrices.ResID)) As Resortİsmi, (SELECT Destination + ' - ' + CodeOfDest FROM Destination TD WHERE (View = True) AND (TD.DestID = @DestID)) As DestIsmi, (SELECT TypeOFTrf FROM TRFTypes WHERE (TRFTypes.TRFTypeID = TypePrices.TypeOfTRF)) As TrasferTürü, @QADT As ADTSayi, @QCHD As CHDSayi, ([CHDPerCent]/100) As PerCHDCent, (ADTSayi ++ CHDSayi) As Deger, ResID FROM TypePrices WHERE ([View] = True) And (ResID = @QRes) AND (Deger >= [MinReq]) AND (Deger <= [MaxCap])
And here is my select parameters;
<SelectParameters> <asp:QueryStringParameter Name="QDestID" QueryStringField="DestIDQ" Type="Int32" /> <asp:QueryStringParameter Name="QADT" QueryStringField="ADTQ" Type="Int32" /> <asp:QueryStringParameter Name="QCHD" QueryStringField="CHDQ" Type="Int32" /> <asp:QueryStringParameter Name="QRes" QueryStringField="ResortQ" Type="Int32" /> <asp:QueryStringParameter Name="QTyp" QueryStringField="TypeQ" Type="Int32" /> </SelectParameters>
It is a bit long ! I am having problem on last two where declaration. here ;
(Deger >= [MinReq]) AND (Deger <= [MaxCap])
those MinReq and MaxCap columns are int32 columns. But when I run the page it doesnt work properly ! It display the valuse it shouldn't be ! weird thing is, when I display this 'deger', I get the value properly.
With this;
Değer : <b><asp:Label ID="Label10" runat="server" Text='<%# Eval("Deger")%>'/></b>
for example I get 8 with this but it dosnt work on where statement !
help me out please
Saturday, May 8, 2010 2:21 AM
Answers
-
User-1199946673 posted
When looking to your command, I notice that Deger is a calculated field (ADTSayi + CHDSayi) (NOT ++) , but later you're using Deger like it was a normal field! Can you try this:
SELECT OWGBP, RTGBP, OWEUR, RTEUR, OWYTL, RTYTL,
(SELECT Resort FROM Resort RF WHERE (View = True) AND (RF.ResID = TypePrices.ResID)) As Resortİsmi,
(SELECT Destination + ' - ' + CodeOfDest FROM Destination TD WHERE (View = True) AND (TD.DestID = @DestID)) As DestIsmi,
(SELECT TypeOFTrf FROM TRFTypes WHERE (TRFTypes.TRFTypeID = TypePrices.TypeOfTRF)) As TrasferTürü,
@QADT As ADTSayi, @QCHD As CHDSayi, ([CHDPerCent]/100) As PerCHDCent, (ADTSayi + CHDSayi) As Deger, ResID FROM TypePrices
WHERE ([View] = True) And (ResID = @QRes) AND ((ADTSayi + CHDSayi) >= [MinReq]) AND ((ADTSayi + CHDSayi) <= [MaxCap])By the way, I don't see a parameter QTyp?
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Saturday, May 8, 2010 9:39 AM
All replies
-
User-706647060 posted
I also tried it as ;
(ADTSayi + CHDSayi) As Deger
Instead Of
(ADTSayi ++ CHDSayi) As Deger,
But didn't work either.
Saturday, May 8, 2010 3:55 AM -
User-706647060 posted
Don't have an idea :(
Saturday, May 8, 2010 6:07 AM -
User-1199946673 posted
When looking to your command, I notice that Deger is a calculated field (ADTSayi + CHDSayi) (NOT ++) , but later you're using Deger like it was a normal field! Can you try this:
SELECT OWGBP, RTGBP, OWEUR, RTEUR, OWYTL, RTYTL,
(SELECT Resort FROM Resort RF WHERE (View = True) AND (RF.ResID = TypePrices.ResID)) As Resortİsmi,
(SELECT Destination + ' - ' + CodeOfDest FROM Destination TD WHERE (View = True) AND (TD.DestID = @DestID)) As DestIsmi,
(SELECT TypeOFTrf FROM TRFTypes WHERE (TRFTypes.TRFTypeID = TypePrices.TypeOfTRF)) As TrasferTürü,
@QADT As ADTSayi, @QCHD As CHDSayi, ([CHDPerCent]/100) As PerCHDCent, (ADTSayi + CHDSayi) As Deger, ResID FROM TypePrices
WHERE ([View] = True) And (ResID = @QRes) AND ((ADTSayi + CHDSayi) >= [MinReq]) AND ((ADTSayi + CHDSayi) <= [MaxCap])By the way, I don't see a parameter QTyp?
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Saturday, May 8, 2010 9:39 AM -
User-706647060 posted
hi hans_v !
Thanks a lot for your reply ! I really appreciate it !
I am gonna try your code now. I now Qtyp is no longer in my code. I updated my code. I can do anything I want except that idiotic problem of mine :D
Here is my new code. I will let you know man.
SELECT OWGBP, RTGBP, OWEUR, RTEUR, OWYTL, RTYTL, (SELECT Resort FROM Resort RF WHERE (View = True) AND (RF.ResID = TypePrices.ResID)) As Resortİsmi, (SELECT Destination + ' - ' + CodeOfDest FROM Destination TD WHERE (View = True) AND (TD.DestID = @DestID)) As DestIsmi, (SELECT TypeOFTrf FROM TRFTypes WHERE (TRFTypes.TRFTypeID = TypePrices.TypeOfTRF)) As TrasferTürü, @QADT As ADTSayi, @QCHD As CHDSayi, ([CHDPerCent]/100) As PerCHDCent, ([ADTSayi] ++ [CHDSayi]) As Deger, IIF([PriceTypeUnit]=True,[OWGBP],(OWGBP*[ADTSayi])+(([CHDPerCent]*OWGBP)/100)*[CHDSayi]) As OneWayGBP, ResID FROM TypePrices WHERE ([View] = True) And (ResID = @QRes) AND ([@QADT] >= [MinReq]) AND ([@QADT] <= [MaxCap]) AND ([@QArr] BETWEEN [StartDate] AND [EndDate])
<asp:QueryStringParameter Name="QDestID" QueryStringField="DestIDQ" Type="Int32" />
<asp:QueryStringParameter Name="QADT" QueryStringField="ADTQ" Type="Int32" />
<asp:QueryStringParameter Name="QCHD" QueryStringField="CHDQ" Type="Int32" />
<asp:QueryStringParameter Name="QRes" QueryStringField="ResortQ" Type="Int32" />
<asp:QueryStringParameter Name="QArr" QueryStringField="ArrivalQ" Type="DateTime" />
Saturday, May 8, 2010 9:43 AM -
User-706647060 posted
Hans !
With this URL:
Result.aspx?CurQ=1&DestIDQ=1&ResortQ=3&TypeQ=-1&ADTQ=2&CHDQ=1&INFQ=2&TotalQ=5&ArrivalQ=5/12/2010&DepQ=5/20/2010
With this SQL Code :
SELECT OWGBP, RTGBP, OWEUR, RTEUR, OWYTL, RTYTL, (SELECT Resort FROM Resort RF WHERE (View = True) AND (RF.ResID = TypePrices.ResID)) As Resortİsmi, (SELECT Destination + ' - ' + CodeOfDest FROM Destination TD WHERE (View = True) AND (TD.DestID = @DestID)) As DestIsmi, (SELECT TypeOFTrf FROM TRFTypes WHERE (TRFTypes.TRFTypeID = TypePrices.TypeOfTRF)) As TrasferTürü, @QADT As ADTSayi, @QCHD As CHDSayi, ([CHDPerCent]/100) As PerCHDCent, ([ADTSayi] ++ [CHDSayi]) As Deger, IIF([PriceTypeUnit]=True,[OWGBP],(OWGBP*[ADTSayi])+(([CHDPerCent]*OWGBP)/100)*[CHDSayi]) As OneWayGBP, ResID FROM TypePrices WHERE ([View] = True) And (ResID = @QRes) AND ([@QADT] >= [MinReq]) AND ((ADTSayi + CHDSayi) <= [MaxCap]) AND ([@QArr] BETWEEN [StartDate] AND [EndDate])
And with those parameter:
<asp:QueryStringParameter Name="QDestID" QueryStringField="DestIDQ" Type="Int32" />
<asp:QueryStringParameter Name="QADT" QueryStringField="ADTQ" Type="Int32" />
<asp:QueryStringParameter Name="QCHD" QueryStringField="CHDQ" Type="Int32" />
<asp:QueryStringParameter Name="QRes" QueryStringField="ResortQ" Type="Int32" />
<asp:QueryStringParameter Name="QArr" QueryStringField="ArrivalQ" Type="DateTime" />I am getting a error ! It is in turkish but it says that there isn't a or more value which is required is missing !
What do you think !
Saturday, May 8, 2010 9:48 AM -
User-706647060 posted
<title>Gerekli bir veya daha fazla parametre için girilen değer yok.</title> <style><!-- body {font-family:"Verdana";font-weight:normal;font-size: .7em;color:black;} p {font-family:"Verdana";font-weight:normal;color:black;margin-top: -5px} b {font-family:"Verdana";font-weight:bold;color:black;margin-top: -5px} H1 { font-family:"Verdana";font-weight:normal;font-size:18pt;color:red } H2 { font-family:"Verdana";font-weight:normal;font-size:14pt;color:maroon } pre {font-family:"Lucida Console";font-size: .9em} .marker {font-weight: bold; color: black;text-decoration: none;} .version {color: gray;} .error {margin-bottom: 10px;} .expandable { text-decoration:underline; font-weight:bold; color:navy; cursor:hand; } --></style>
'/TransferModule' Uygulamasında Sunucu Hatası.
Gerekli bir veya daha fazla parametre için girilen değer yok.
Açıklama: Geçerli web isteği yürütülürken işlenmemiş özel durum oluştu. Lütfen hata ve kod içinde kaynaklandığı yer hakkında daha fazla bilgi almak için yığın izlemesini gözden geçirin.
Özel Durum Ayrıntıları: System.Data.OleDb.OleDbException: Gerekli bir veya daha fazla parametre için girilen değer yok.
Saturday, May 8, 2010 9:49 AM -
User-706647060 posted
Buddy this worked :)
SELECT OWGBP, RTGBP, OWEUR, RTEUR, OWYTL, RTYTL, (SELECT Resort FROM Resort RF WHERE (View = True) AND (RF.ResID = TypePrices.ResID)) As Resortİsmi, (SELECT Destination + ' - ' + CodeOfDest FROM Destination TD WHERE (View = True) AND (TD.DestID = @DestID)) As DestIsmi, (SELECT TypeOFTrf FROM TRFTypes WHERE (TRFTypes.TRFTypeID = TypePrices.TypeOfTRF)) As TrasferTürü, @QADT As ADTSayi, @QCHD As CHDSayi, ([CHDPerCent]/100) As PerCHDCent, ([ADTSayi] ++ [CHDSayi]) As Deger, IIF([PriceTypeUnit]=True,[OWGBP],(OWGBP*[ADTSayi])+(([CHDPerCent]*OWGBP)/100)*[CHDSayi]) As OneWayGBP, ResID FROM TypePrices WHERE ([View] = True) And (ResID = @QRes) AND ([@QADT] >= [MinReq]) AND ((@QADT + @QCHD) <= [MaxCap]) AND ([@QArr] BETWEEN [StartDate] AND [EndDate])
you know I learned a lot from you :)
Saturday, May 8, 2010 9:51 AM -
User-1199946673 posted
My Turkish isn't that good
Good you try to translate it?
Saturday, May 8, 2010 9:52 AM -
User-706647060 posted
I am not getting it anymore so don't need to translate :)
One more thing man !
What should I do about that : arrival is 11.11.2010 and departure is 03.11.2010 and There are two price for those date;
11.11.2010 is 20 GBP and 03.11.2010 35 GBP. how could I get it to SQL Code !
Saturday, May 8, 2010 9:57 AM -
User-1199946673 posted
What should I do about that : arrival is 11.11.2010 and departure is 03.11.2010 and There are two price for those date;
11.11.2010 is 20 GBP and 03.11.2010 35 GBP. how could I get it to SQL Code !
I think you should be more clear on this?
Saturday, May 8, 2010 10:00 AM -
User-706647060 posted
Hmm lets say that !
Result.aspx?CurQ=1&DestIDQ=1&ResortQ=3&TypeQ=-1&ADTQ=2&CHDQ=1&INFQ=2&TotalQ=5&ArrivalQ=5/12/2010&DepQ=5/20/2010
here is the search result. In my database I am entering prices and I also Have Startdate and enddate fields. so price is valid between those dates.
In my T-SQL code, It only gets the arrival date value for where statement. I couldn't figured out how to use departure value on this. And Also if there is gonna be two value for a calculation, I need to use group by I guess !
Saturday, May 8, 2010 10:05 AM -
User-1199946673 posted
I couldn't figured out how to use departure value on this. And Also if there is gonna be two value for a calculation, I need to use group by I guess !I don't think so. I think your logic to calculate the price is what you need to look at. Perhaps he easiest way is to have a price for both trips, and calculate the 2 together
Saturday, May 8, 2010 10:18 AM -
User-706647060 posted
Exactly ! This function is what I need for may project actually ! Thanks A lot man ;)
Saturday, May 8, 2010 10:34 AM