asked on

C# code to access oracle view

HI,
I can access the oracle 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.
I don't even understand what mistake i did, please do the needful, very urgent.
If you have any piece of code to connect oracle view, please post it.

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

OracleConnection 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();
        }
    } 
}

Open in new window

Geert G

does the user you login with have access to that view ?
and is in the schema (same name as user) ?

if you have oracle client installed, you might also have sqlplus to test this with:

sqlplus login/pw@db
select owner, view_name
from all_views
where upper(view_name) = 'VW_TEST_VIEW';

Open in new window

if there is a record, check the owner ...
if the owner is different:

select col1,col2 from owner.VW_test_view where col1 = :param1

Open in new window

work with bind variables from the start ...
you won't have to rewrite your code later on and your dba will like you a little more for it

cmd.Parameters.Clear();
cmd.Parameters.Add("param1", OracleDbType.Varchar2, ParameterDirection.Input).Value = Col1;

Open in new window

slightwv (䄆 Netminder)

Is that all your code? My .Net is rusty but I didn't think you could have a only a try.

I'm thinking the error is being generated somewhere else.

I 100% agree with Bind variables. Using string concatenation opens things up to SQL Injection.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.