none
OLEDBdatareader cannot capture the latest data‏ RRS feed

  • Question

  • i facing a big problem, check out and debug my source code whole day, still cannot get solution, i have a text box and gridview on a page.  and when user enter a value in the textbox (employee#), the sql results from OledbDataReader will be dump into a datatable and then bind the datatable into gridview datasource....

    For example, let say i change my data (the field is employee name) for a particular employee in the table, initially when i run the page, it is working, the page can show the updated data(employee name) ...after that, i goto change the name again in the table, and run the page again, it is not the latest name for this employee, it show the previous name on the grid because on the Data Reader it is not updated still capture the previous data although i re-run the sql...why?
    unless i wait for 30 mins or so then run the page again only it shows the latest data ...

    the problem is why the oledbdatareader cannot capture the latest data?? is there anyway to clear the previous data in the oledbdatareader before re-run the sql query?  Below is the code: 



    OleDbConnection Connect = new OleDbConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
    DataTable dt = new DataTable("ltempc");


    protected void Page_Load(object sender, EventArgs e)
        {
            muser = Request.QueryString["user"];
            mctrl = Request.QueryString["ctrl"];

            this.ClientScript.GetPostBackEventReference(this, string.Empty);
                    
            BindGrid();    
        }



    private void BindGrid()
        {
            Connect.Open();
            dt.Columns.Add("emno");
            dt.Columns.Add("emname");
           
            string sqlQuery = "SELECT * FROM lv where emno='" + txtEmno.Text + "' order by emno";
            OleDbCommand aCommand = new OleDbCommand(sqlQuery, Connect);
            OleDbDataReader rdlvtemp = aCommand.ExecuteReader();
           
            while (rdlvtemp.Read())
            {
                 DataRow nrow = dt.NewRow();
                 nrow["emno"] = rdlvtemp["emno"].ToString();
                 nrow["emname"] = rdlvtemp["emname"].ToString();
                 dt.Rows.Add(nrow);
            }
            rdlvtemp.Close();
           
            Connect.Close();
        
            GridView1.DataSource = dt;
            GridView1.DataBind();
            dt.Clear();
            dt.Dispose();
        }

    protected void txtEmno_TextChanged(object sender, EventArgs e)
        {
            //some code here to chk the employee#, if valid, gridview visible is true,else false      
        }

         

    • Moved by Chris Robinson- MSFT Thursday, July 23, 2009 4:01 PM Oledbreader issue (From:ADO.NET DataSet)
    Thursday, July 23, 2009 1:09 PM

Answers

  • DataReader does not use any caching, so I would suspect it is caching of the ASPX page that returns you same result. When you are in development environment then it is different, since you are using your local development web server to execute the code and it is got reset locally, so you the environments are different.
    Val Mazur (MVP) http://www.xporttools.net
    Wednesday, July 29, 2009 10:23 AM
    Moderator

All replies

  • Where is the code that does the update to the data? Are the changes being saved via an user click event or saved as entered etc? If you are using datatables or datasets you may not be committing the changes before you requery.
    Thursday, July 23, 2009 1:48 PM
  • no code to update the data, i update it manually in the table, and confirm it is the updated data ...
    the problem is the oledb datareader itself doesn't capture the latest data...so datatable also not update cos i dump the data from data reader to data table...
    the table i use is  .dbf  file
    Friday, July 24, 2009 12:33 AM
  • I guess what you are facing here is not a problem with the DataReader, but caching of the ASP.NET web pages. Most likely your code does not even query data again (until cached version expires), but IIS provides you with the cached web page that was generated on the first run. Try to play with the web page instructions to disable caching. You could run SQL Profiler to see if actual SQL statement is actually executed each time when you query the web page
    Val Mazur (MVP) http://www.xporttools.net
    Friday, July 24, 2009 10:20 AM
    Moderator
  • i try running the page remotely, datareader can capture the latest data, but when i run locally, it can't, don't know why
    Monday, July 27, 2009 6:48 AM
  • What does that mean "remotely" and "locally" in your case?

    Val Mazur (MVP) http://www.xporttools.net
    Monday, July 27, 2009 10:33 AM
    Moderator
  • locally means i run the webpage on my pc (localhost) which i use it to develop the web application, the datareader sometimes cannot capture the latest data.. remotely means i use my pc to run the webpage located in the web server, means when access the page i need to include the ip address of the webserver, and this situation has no problem with the datareader captured
    Tuesday, July 28, 2009 2:03 AM
  • Did you check caching? Did you run SQL Server profiler to see if query for the DataReader really executed?
    Val Mazur (MVP) http://www.xporttools.net
    Tuesday, July 28, 2009 10:36 AM
    Moderator
  • ya, the sql datareader only executed when i use my pc to access the page in the web server, i can't understand if it is a datareader or caching problem, why is it there's no problem running the page in web server that accessing on my pc? meaning same source code in the webserver and my development pc, the page running on webserver using my pc no problem, but running the page located on my pc has problem
    Wednesday, July 29, 2009 1:19 AM
  • DataReader does not use any caching, so I would suspect it is caching of the ASPX page that returns you same result. When you are in development environment then it is different, since you are using your local development web server to execute the code and it is got reset locally, so you the environments are different.
    Val Mazur (MVP) http://www.xporttools.net
    Wednesday, July 29, 2009 10:23 AM
    Moderator