none
C# code to access Oracle view (select statement) RRS feed

  • Question

  • I can access the tables and stored procedures using C#, but i am getting error when trying to use select oracle VIEW.
    Below code i am using. I have access to that view also.

    Error:

    Operation is not valid due to the current state of the object."

    racleConnection con = null;
    OracleCommand cmd = new OracleCommand();
    OracleDataReader dr = null;
    
    try
    {
        con = new OracleConnection("COnstring");
        con.Open();
        string strQuery = string.Empty;
    
        strQuery = "select col1,col2 from VW_test_view where col1 ='" + COL1 + "'";
        cmd.CommandText = strQuery;
        cmd.CommandType = CommandType.Text;
        dr =cmd.ExecuteReader(); 
    
        if (dr.HasRows)
        {
            while (dr.Read())
            {
                statusCode = dr["col2"].ToString();
            }
        } 
    }


    Saturday, November 3, 2018 6:17 PM

All replies

  • Hi sandydv85,

    Since I don't have environment to reproduce the issue, so I am not sure what cause the issue, I would suggest that yo could post your issue on the following forum for suitable support.

    https://community.oracle.com/community/groundbreakers/database/developer-tools/windows_and_.net/odp.net

    In addition, here is similar issue for your reference.

    https://stackoverflow.com/questions/11490855/operation-is-not-valid-due-to-the-current-state-of-the-object-in-c-sharp

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 5, 2018 1:49 AM
  • A little late to reply but here is an example I did against a view.

    If you have not done so yet set a breakpoint at the first line in the code and step through the code, examine objects for valid states.

    public void SelectView(string pCountryCode)
    {
        var selectStatement = "SELECT TR_ALL_DONE  FROM icc_trans_view where tr_ip_country_code = :countryCode ";
        using (OracleConnection cn = new OracleConnection() { ConnectionString = ConnectionString })
        {
            using (OracleCommand cmd = new OracleCommand() { Connection = cn })
            {
                cmd.CommandText = selectStatement;
                cmd.BindByName = true;
                cmd.Parameters.Add(":countryCode", pCountryCode);
    
                cn.Open();
                var reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Console.WriteLine(reader.GetString(0));
                    }
                }
            }
        }
    }


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, November 6, 2018 4:03 PM