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])
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 !
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 tugberk_ugurlu_ on May 08, 2010 02:35 PM
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])
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])
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.
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])
tugberk_ugur...
Participant
1944 Points
1344 Posts
MVP
Wehere Statement doesn't work properly !
May 08, 2010 06:21 AM|LINK
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;
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
tweets as @tourismgeek
tugberk_ugur...
Participant
1944 Points
1344 Posts
MVP
Re: Wehere Statement doesn't work properly !
May 08, 2010 07:55 AM|LINK
I also tried it as ;
(ADTSayi + CHDSayi) As Deger
Instead Of
(ADTSayi ++ CHDSayi) As Deger,
But didn't work either.
tweets as @tourismgeek
tugberk_ugur...
Participant
1944 Points
1344 Posts
MVP
Re: Wehere Statement doesn't work properly !
May 08, 2010 10:07 AM|LINK
Don't have an idea :(
tweets as @tourismgeek
hans_v
All-Star
35998 Points
6551 Posts
Re: Wehere Statement doesn't work properly !
May 08, 2010 01:39 PM|LINK
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?
tugberk_ugur...
Participant
1944 Points
1344 Posts
MVP
Re: Wehere Statement doesn't work properly !
May 08, 2010 01:43 PM|LINK
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" />
tweets as @tourismgeek
tugberk_ugur...
Participant
1944 Points
1344 Posts
MVP
Re: Wehere Statement doesn't work properly !
May 08, 2010 01:48 PM|LINK
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 !
tweets as @tourismgeek
tugberk_ugur...
Participant
1944 Points
1344 Posts
MVP
Re: Wehere Statement doesn't work properly !
May 08, 2010 01:49 PM|LINK
'/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.
tweets as @tourismgeek
tugberk_ugur...
Participant
1944 Points
1344 Posts
MVP
Re: Wehere Statement doesn't work properly !
May 08, 2010 01:51 PM|LINK
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 :)
tweets as @tourismgeek
hans_v
All-Star
35998 Points
6551 Posts
Re: Wehere Statement doesn't work properly !
May 08, 2010 01:52 PM|LINK
My Turkish isn't that good
Good you try to translate it?
tugberk_ugur...
Participant
1944 Points
1344 Posts
MVP
Re: Wehere Statement doesn't work properly !
May 08, 2010 01:57 PM|LINK
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 !
tweets as @tourismgeek