Refreshing Data

Box

Hi

im having problems keeping some data up to date. every 60 minutes data is input into a database which works fine but when the data is refreshed it always comes back with the original data. if i open and close the program then it will refresh.

I have these 2 functions that are run below

Pulse is working fine and a new line is inserted correctly. Refresh values is ran on open which works fine and then again after every time pulse is ran but the information it gets is always the same as the first time it is ran. I'm gueesing it because the information is somehow cache'd somewhere and not being refreshed.

  public void Pulse()
  {
   string istring = @"INSERT INTO tblUsers (username, distance, pulsetime) VALUES ('" + Environment.UserName + "'," + this.labSeshDistance.Text + ",#" + DateTime.Now + "#);";
   //@"INSERT into tblUsers (username, distance) VALUES ('" + Environment.UserName + "',0);";
   conn.Open();
   OleDbCommand cmd = new OleDbCommand(istring, conn);
   cmd.ExecuteNonQuery();
   conn.Close();
   this.labSeshDistance.Text="0";
  }

  public void RefreshValues()
  {
   conn.Open();
   adap = new OleDbDataAdapter("SELECT SUM(distance) AS tdistance FROM tblUsers WHERE username='" + Environment.UserName + "'",conn);
   adap.Fill(datat);

DataRow myline = datat.Rows[0];
this.labPrevDistance.Text=myline["tdistance"].ToString();

   adap.Dispose();
   datat.Dispose();
   conn.Close();
  }

At the start of the class I have these 3 lines

  OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=L:\BDSU\Archive\Personal - TO BE SORTED\Scott Thurlow\InTheDistance.mdb; User Id=admin; Password=;");
  OleDbDataAdapter adap = new OleDbDataAdapter();
  DataTable datat = new DataTable();

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Zoran Horvat15y ago
Accepted answer
From MSDN:

OleDbDataAdapter.Fill (DataTable) Adds or refreshes rows in a specified range in the DataSet to match those in the data source using the DataSet, DataTable, and IDataReader names.

Looks like Fill(DataTable) will only append rows to the table, since you don't have any identity column, etc. to force it update data.

To verify this is the problem, in line DAtaRow myline = datat.Rows[0] try counting rows in datat. I expect that there will be more than one row after refresh.

To resolve the problem, execute datat.Clear() before adap.Fill(datat).

Zoran
Box15y ago
awesome :) Thanks a lot this works great!
Box15y ago
  private void button1_Click(object sender, System.EventArgs e)
  {
   Pulse();
   RefreshValues();
  }

and

  private void timer2_Tick(object sender, System.EventArgs e)
  {
   Pulse();
   RefreshValues();
  }
Zoran Horvat15y ago
Can you paste the code which invokes RefreshValues and Pulse methods?

Zoran