locked
OracleDataReader RRS feed

  • 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.

    Thursday, August 1, 2013 10:45 AM

Answers

  • 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).

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 1, 2013 11:28 AM

All replies

  • 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.

     

     

     

    Thursday, August 1, 2013 11:01 AM
  • 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. 

    Thursday, August 1, 2013 11:22 AM
  • 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).

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 1, 2013 11:28 AM
  • 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.

    Thursday, August 1, 2013 11:51 AM