OracleDataReader

Question

User1864322503 posted

Hello,

I am attempting to use OracleDataReader to accomplish this task. I want to display a string from my DB in a Textbox on a page. Never have done this before in c#, but just by the name of it, OracleDataReader seems to be fitting. Not using it entirely the right way of course, but so far I have done like this: 

protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                string sqlquery = @"SELECT LOCATION FROM NICK_TEST WHERE EMPID=:EMPID";
                using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
                {
                    connection.Open();
                    using (OracleCommand command = new OracleCommand(sqlquery /*params */ , connection))
                    using(OracleDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            if (Session["empIdSession"] != null)
                        {
                            LocationFill.Text = (reader["LOCATION"].ToString());
                            command.ExecuteReader();
                        }
                             reader.close;
                             connection.close();
                        }
                        
                    }
                }
            }
        }

Essentially I want to show the LOCATION value from my DB, where the EMPID = the employee ID in session. I am getting Oracle Error that there's unbound variables.

Answer 1

User753101303 posted

Hi,

You have to provide a value for the parameters you have in a SQL statement. Try http://www.oracle.com/technetwork/articles/dotnet/williams-sps-089817.html

In particular it uses :

 cmd.CommandText = "select country_name from countries where country_id = :1";
 cmd.Parameters.Add(":1", OracleDbType.Varchar2, CountryID, ParameterDirection.Input);

To define a parameterized query and then to define its actual value from the CountryID variable before running the SQL statement.

 

 

 

Answer 2

User1864322503 posted

Hi Patrice, thank you

I have added in the code like so 

if (Session["empIdSession"] != null)
                            {
                                command.Parameters.Add(":EMPID", Session["empIdSession"]);
                                command.Parameters.Add("LOCATION", LocationFill.Text);
                                LocationFill.Text= (reader["LOCATION"].ToString());

                                reader.Close();
                                connection.Close();
                            }

not sure if that's exactly what you meant, and I am still unfortunately getting Not All Variables Bound error. 

Answer 3

User753101303 posted

:EMPID (and :EMPID only) is a parameter. That is you want to run the SQL statement with some actual value that will "replace" :EMPID.

So :
- you have to move the command.Parameters.Add(":EMPID", Session["empIdSession"]);  line before running the query so that when the query running, the value that should be used for :EMPID is known
- command.Parameters.Add("LOCATION", LocationFill.Text);  should be deleted (LOCATION is a column name that you'll read, not a parameter that will be used to run the SQL statement).

 

Answer 4

User1864322503 posted

Hi Patrice,

Noticed I was missing my { } under one of my using statements as well... haha. For reference my code now looks like: 

using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
                {
                    connection.Open();
                    string sqlquery = @"SELECT LOCATION FROM NICK_TEST WHERE EMPID=:EMPID";
                    using (OracleCommand command = new OracleCommand(sqlquery /*params */ , connection))
                    {

                    command.Parameters.Add(":EMPID", Session["empIdSession"]);
                    using(OracleDataReader reader = command.ExecuteReader())
                        
                    {
                        while (reader.Read())
                            

                        {
                            if (Session["empIdSession"] != null)
                            {

                                
                                LocationFill.Text = reader["LOCATION"].ToString();

                                reader.Close();
                                connection.Close();
                            }
                        }
                    }
                    }
                }

I am getting error 

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

<configuration>
     <appSettings>
    <add key="aspnet:MaxHttpCollectionKeys" value="5000" />
... 
... 
     </appsettings>
</configuration>

I added in the above code to my web.config as a result of a StackOverflow suggestion that had fixed it for many, but unfortunately I am still having the same error even after adding that to the config. Any help is appreciated, thanks for it all up til now!

EDIT: Removing the while loop, and just running the reader.Read(); inside my Session!=null statement fixed the error.