locked
Wehere Statement doesn't work properly ! RRS feed

  • 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 Cry

    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 Wink

    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