0
votes

I got 2 comboboxes on my form(in the form load event). First combobox gets a value from a select statement once the form loads. I want to use that value in my second combobox. Here is my code:

1ste Combobox = cbDelivery

        OracleConnection conn = new OracleConnection();
        conn.ConnectionString = "User Id=christob;Password=CHRISTOB;Host=poseidon;Pooling=true;Min Pool    Size=0;Max Pool Size=100;Connection Lifetime=0;Port=1522;Sid=GLODCD";

        conn.Open();

        string query;
        query = "select distinct dd.delivery_bay_code from dc_delivery dd, dc_grv dg where delivery_complete_datetime is null and dd.dc_delivery_id_no = dg.dc_delivery_id_no and dd.delivery_announce_datetime is null";
        OracleCommand cmd = new OracleCommand(query, conn);
        OracleDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            cbDelivery.Items.Add(dr["delivery_bay_code"]);
        }
        dr.Close();
        conn.Close();

2de Combobox = cbOrderNo

This combobox is in:

private void cbDelivery_SelectedIndexChanged(object sender, EventArgs e)

so as soon as I select a value from 1ste combobox my 2nd combobox query must populate the 2nd combobox. See code:

        OracleConnection conn = new OracleConnection();
        conn.ConnectionString = "User Id=christob;Password=CHRISTOB;Host=poseidon;Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0;Port=1522;Sid=GLODCD";
        conn.Open();
        string query1;
        query1 = "select distinct dg.order_no from dc_delivery dd, dc_grv dg where delivery_complete_datetime is null and dd.dc_delivery_id_no = dg.dc_delivery_id_no and dd.delivery_announce_datetime is null and dd.delivery_bay_code = " + cbDelivery.Text;
        OracleCommand cmd1 = new OracleCommand(query1, conn);
        OracleDataReader dr1 = cmd1.ExecuteReader();
        while (dr1.Read())
        {
            cbOderNo.Items.Add(dr1["order_no"]);

        }
        dr1.Close();
        conn.Close();

Note I'm using cbDelivery combobox in my second Select query.

Problem is: As soon as I select a value from my first combobox the second gives an exception ""ORA-00904: "BAY1": Invalid Identifier.

Please help me sort this out or suggest a different method.

Thanks in Advance.

2
Did you try cbDelivery.SelectedValue instead of cbDelivery.Text. Also you are missing single quotes around your sql query. So this line: query1 = "select distinct dg.order_no from dc_delivery dd, dc_grv dg where delivery_complete_datetime is null and dd.dc_delivery_id_no = dg.dc_delivery_id_no and dd.delivery_announce_datetime is null and dd.delivery_bay_code = '" + cbDelivery.Text + "'"; - Azhar Khorasany
@AzharKhorasany cbDelivery.SelectedValue doesn't do it. Also "'" doesn't work. Don't think there is something wrong with the query. It works like that in my other one's. - Werner van den Heever
Put a breakpoint on your query variable and see what string you get when you execute it. Then run that query in Oracle database to see whether you can get any results? - Azhar Khorasany
@AzharKhorasany Yes, that query works in Oracle. Im getting a exception at OracleDataReader dr1 = cmd1.ExecuteReader(); -- ""ORA-00904: "BAY1": Invalid Identifier. - Werner van den Heever
Have a look at this. There must be something wrong in your query: dba-oracle.com/t_ora_00904_string_invalid_identifier.htm - Azhar Khorasany

2 Answers

0
votes

May be your query using some keyword of sql or oracle as column name or at some invalid place. in such cases this type of errors are possible. I am not sure about this solution. but atleast we can try once. so that we can make it sure if its correct or not?

0
votes

FIXED!! This is how i did it:

private void populateDeliveryBayCodes()
    {
        conn.Open();
        string query;
        query = "select distinct dd.delivery_bay_code from dc_delivery dd, dc_grv dg where dd.delivery_complete_datetime is null and dd.dc_delivery_id_no = dg.dc_delivery_id_no and dd.delivery_announce_datetime is null";
        OracleCommand cmd = new OracleCommand(query, conn);
        OracleDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            cbDelivery.Items.Add(dr["delivery_bay_code"]);
        }
        dr.Close();
        conn.Close();
    }

    private void populateOrderNumbers()
    {
        conn.Open();
        string query;
        query = "select distinct dg.order_no from dc_delivery dd, dc_grv dg where dd.delivery_complete_datetime is null and dd.dc_delivery_id_no = dg.dc_delivery_id_no and dd.delivery_announce_datetime is null and dd.delivery_bay_code ='" + cbDelivery.Text + "' order by order_no";
        OracleCommand cmd = new OracleCommand(query, conn);
        OracleDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            cbOderNo.Items.Add(dr["order_no"]);
        }
        dr.Close();
        conn.Close();
    }

and

private void frmBuiltPallet_Load(object sender, EventArgs e)
    {
        populateDeliveryBayCodes();
    {

private void cbDelivery_SelectedIndexChanged(object sender, EventArgs e)
    {
        populateOrderNumbers();
    }