This discussion is archived
1 Reply Latest reply: Mar 23, 2012 10:17 AM by gdarling - oracle RSS

Union select, OracleDataAdapter, Gridview

user545194 Newbie
Currently Being Moderated
Hi,

We have a Web form that has some text boxes, dropdown controls and a submit button. When clicking the button
a database connection is established and a union select query is executed. Finally, the result output should be shown
in the grid view. There is a minor problem: Only the data from the 1.select statement is returned.

The query has been tested in the database and works.

OS: Windows XP SP3, Oracle Database 11gR2, Visual Web Developer 2010 Express, .NET 4 FW

Here is the button event code:
using System;
using System.Data;
using System.Data.OracleClient;
using System.Configuration;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Script.Services;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace AeroPortal
{
    public partial class panels : System.Web.UI.Page
    {
       
        System.Data.DataSet FindFlights(string pleaving, string parriving, string pclass, string poutbound, string pinbound)
        {

            OracleConnection con = new OracleConnection("Data Source=DEV01;Persist Security Info=True;User ID=xxx;Password=xxx;Unicode=True");

            string queryString = "";

            queryString = "SELECT to_char(FD.FLIGHT_DATE, 'DY')||'.'|| FD.FLIGHT_DATE AS DATE_HEADER, " +
                          "FD.FLIGHT_DATE AS TRAVEL_DATE, " +
                          "LM.STD AS DEPART, " +
                          "LM.FNR AS FLIGHT, " +
                          "LM.STD ||' '|| LM.DEP ||' - '|| LM.STA ||' '|| LM.ARR AS SCHEDULE, " +
                          "B.CITY ||' - '|| C.CITY AS ROUTE, " +
                          "MAX(CASE WHEN FA.BOOKING_CLASS = 'S' THEN (ROUND(FA.LH_FARE_EUR, 2)) END) AS ECONOMY_SAVER, " +
                          "MAX(CASE WHEN FA.BOOKING_CLASS = 'H' THEN (ROUND(FA.LH_FARE_EUR, 2)) END) AS ECONOMY, " +
                          "MAX(CASE WHEN FA.BOOKING_CLASS = 'B' THEN (ROUND(FA.LH_FARE_EUR, 2)) END) AS ECONOMY_FLEXIBLE, " +
                          "MAX(CASE WHEN FA.BOOKING_CLASS = 'Z' THEN (ROUND(FA.LH_FARE_EUR, 2)) END) AS BUSINESS, " +
                          "MAX(CASE WHEN FA.BOOKING_CLASS = 'D' THEN (ROUND(FA.LH_FARE_EUR, 2)) END) AS BUSINESS_FLEXIBLE, " +
                          "MAX(CASE WHEN FA.BOOKING_CLASS = 'F' THEN (ROUND(FA.LH_FARE_EUR, 2)) END) AS FIRST_CLASS " +
                          "FROM LH_MASTER_Q LM, LH_FLIGHT_DATE FD, AIRPORTS_LOOKUP B, AIRPORTS_LOOKUP C, CARRIERS E, LH_FARES FA, LH_SERVICE_CLASS SC, LH_CABIN_CLASS CC " +
                          "WHERE LM.ROWNR = FD.FLIGHT_LEG_ID " +
                          "AND LM.DEP = FA.FROM_AP " +
                          "AND LM.ARR = FA.TO_AP " +
                          "AND LM.DEP = B.IATA_CODE " +
                          "AND LM.ARR = C.IATA_CODE " +
                          "AND LM.AL = E.CARRIER_CODE " +
                          "AND FA.BOOKING_CLASS = SC.BKG_CLASS_ID " +
                          "AND SC.CABIN_CLASS_ID = CC.CABIN_CLASS_ID " +
                          "AND LM.ACTYPE = FD.AIRCRAFT_TYPE " +
                          "AND B.CITY_CODE = :p_leaving " +
                          "AND C.CITY_CODE = :p_arriving " +
                          "AND CC.CABIN_CLASS_NAME = :p_class " +
                          "AND FD.FLIGHT_DATE = :p_outbound " +
                          "GROUP BY TO_CHAR(FD.FLIGHT_DATE, 'DY')|| '.' || FD.FLIGHT_DATE, " +
                          "FD.FLIGHT_DATE, " +
                          "LM.STD, " +
                          "LM.FNR, " +
                          "LM.STD ||' '|| LM.DEP || ' - ' || LM.STA || ' ' || LM.ARR, " +
                          "B.CITY|| ' - '|| C.CITY " +
                          "UNION " +
                          "SELECT to_char(FD.FLIGHT_DATE, 'DY')||'.'|| FD.FLIGHT_DATE AS DATE_HEADER, " +
                          "FD.FLIGHT_DATE AS TRAVEL_DATE, " +
                          "LM.STD AS DEPART, " +
                          "LM.FNR AS FLIGHT, " +
                          "LM.STD ||' '|| LM.DEP ||' - '|| LM.STA ||' '|| LM.ARR AS SCHEDULE, " +
                          "B.CITY ||' - '|| C.CITY AS ROUTE, " +
                          "MAX(CASE WHEN FA.BOOKING_CLASS = 'S' THEN (ROUND(FA.LH_FARE_EUR, 2)) END) AS ECONOMY_SAVER, " +
                          "MAX(CASE WHEN FA.BOOKING_CLASS = 'H' THEN (ROUND(FA.LH_FARE_EUR, 2)) END) AS ECONOMY, " +
                          "MAX(CASE WHEN FA.BOOKING_CLASS = 'B' THEN (ROUND(FA.LH_FARE_EUR, 2)) END) AS ECONOMY_FLEXIBLE, " +
                          "MAX(CASE WHEN FA.BOOKING_CLASS = 'Z' THEN (ROUND(FA.LH_FARE_EUR, 2)) END) AS BUSINESS, " +
                          "MAX(CASE WHEN FA.BOOKING_CLASS = 'D' THEN (ROUND(FA.LH_FARE_EUR, 2)) END) AS BUSINESS_FLEXIBLE, " +
                          "MAX(CASE WHEN FA.BOOKING_CLASS = 'F' THEN (ROUND(FA.LH_FARE_EUR, 2)) END) AS FIRST_CLASS " +
                          "FROM LH_MASTER_Q LM, LH_FLIGHT_DATE FD, AIRPORTS_LOOKUP B, AIRPORTS_LOOKUP C, CARRIERS E, LH_FARES FA, LH_SERVICE_CLASS SC, LH_CABIN_CLASS CC " +
                          "WHERE LM.ROWNR = FD.FLIGHT_LEG_ID " +
                          "AND LM.DEP = FA.FROM_AP " +
                          "AND LM.ARR = FA.TO_AP " +
                          "AND LM.DEP = B.IATA_CODE " +
                          "AND LM.ARR = C.IATA_CODE " +
                          "AND LM.AL = E.CARRIER_CODE " +
                          "AND FA.BOOKING_CLASS = SC.BKG_CLASS_ID " +
                          "AND SC.CABIN_CLASS_ID = CC.CABIN_CLASS_ID " +
                          "AND LM.ACTYPE = FD.AIRCRAFT_TYPE " +
                          "AND B.CITY_CODE = :p_arriving " +
                          "AND C.CITY_CODE = :p_leaving " +
                          "AND CC.CABIN_CLASS_NAME = :p_class " +
                          "AND FD.FLIGHT_DATE = :p_inbound " +
                          "GROUP BY TO_CHAR(FD.FLIGHT_DATE, 'DY')|| '.' || FD.FLIGHT_DATE, " +
                          "FD.FLIGHT_DATE, " +
                          "LM.STD, " +
                          "LM.FNR, " +
                          "LM.STD ||' '|| LM.DEP || ' - ' || LM.STA || ' ' || LM.ARR, " +
                          "B.CITY|| ' - '|| C.CITY " +
                          "ORDER BY TRAVEL_DATE, DEPART";

            System.Data.OracleClient.OracleCommand sqlCmd = new System.Data.OracleClient.OracleCommand(queryString, con);

            {

                sqlCmd.Parameters.Add("p_leaving", System.Data.OracleClient.OracleType.VarChar).Value = pleaving;
                sqlCmd.Parameters.Add("p_arriving", System.Data.OracleClient.OracleType.VarChar).Value = parriving;
                sqlCmd.Parameters.Add("p_class", System.Data.OracleClient.OracleType.VarChar).Value = pclass;
                sqlCmd.Parameters.Add("p_outbound", System.Data.OracleClient.OracleType.VarChar).Value = poutbound;
                sqlCmd.Parameters.Add("p_inbound", System.Data.OracleClient.OracleType.VarChar).Value = pinbound;
            }
            
            System.Data.OracleClient.OracleDataAdapter dataAdapter = new System.Data.OracleClient.OracleDataAdapter(sqlCmd);

            System.Data.DataSet dataSet = new System.Data.DataSet();

            dataAdapter.Fill(dataSet);

            return dataSet;
   
        }

        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {

            string pleaving = DropDownList3.SelectedItem.Text;
            string parriving = DropDownList4.SelectedItem.Text;
            string pclass = DropDownList2.SelectedItem.Text;
            string poutbound = TextBox3.Text;
            string pinbound = TextBox4.Text;

            System.Threading.Thread.Sleep(3000);
            
            GridView1.DataSource = FindFlights(pleaving, parriving, pclass, poutbound, pinbound);

            GridView1.DataBind();
        }
    }
}
What could be the problem? Any help greatly appreciated!
  • 1. Re: Union select, OracleDataAdapter, Gridview
    gdarling - oracle Expert
    Currently Being Moderated
    Hi,

    Your question would be better posed in the ODP.NET forum as the Oracle Providers for ASP.NET are used for Membership, Roles, etc. However, you're not actually using Oracle's ODP.NET either and are using Microsoft's System.Data.OracleClient.

    However, some generic troubleshooting advice:

    1) Does the behavior occur if using Oracle's ODP as well? Note that ODP binds by position rather than by name by default though so may take some tweaks to test.
    2) When you tested the db directly, did you use bind variables there as well, or hard code the values?
    3) If you hard coded the values when you tested directly in the db, I assume if you hard code the values in the system.data.oracleclient as well then it also returns the rows you're expecting?
    4) You're using UNION not UNION all, so I assume you want duplicates removed, and the rows that you think should be coming from the second query are not duplicates.
    5) Does the second part of the query return rows if you execute it all by itself (with the bind variables)?
    6) It looks like you're using the same bind variable multiple times (ie, in both the first part and second part of the query). If the second part works all by itself, it could be related to that.
    7) Short of that, my best suggestion is that you double check and make sure that pleaving, parriving,pclass, poutbound, pinbound are actually what you think they are.

    Greg

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points