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

Answer 1

User-706647060 posted

I also tried it as ;

(ADTSayi + CHDSayi) As Deger 

Instead Of

(ADTSayi ++ CHDSayi) As Deger,

But didn't work either.

Answer 2

User-706647060 posted

Don't have an idea :(

Answer 3

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?

Answer 4

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" />

Answer 5

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 !

Answer 6

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.

Answer 7

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 :)

Answer 8

User-1199946673 posted

My Turkish isn't that good

Good you try to translate it? 

Answer 9

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 !

Answer 10

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?

Answer 11

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 !

Answer 12

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

Answer 13

User-706647060 posted

Exactly ! This function is what I need for may project actually ! Thanks A lot man ;)