Last inserted record not displaying in listview until I close and re-run the app

Question

This is my first post in this type of forum so please forgive my ignorance.

I have a WPF application with a SQL Server 2008 mdf database.

I use the following code to insert a new record into a table called Sales_TBL.

SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\SalesDB.mdf;Integrated Security=True;User Instance=True");

con.Open();
SqlTransaction tran = con.BeginTransaction();

SqlCommand cmd =
      new SqlCommand("INSERT INTO  Sales_TBL (Date, Sale_ID,Cust_Fname, Cust_Lname,  Comments, Total_Sale_Price) VALUES (@salesDate, @SalesID, @CFname, @CLname, @Comms, @TotSalePrice)", con);
cmd.Parameters.AddWithValue("@salesDate", saleDate_DB);
cmd.Parameters.AddWithValue("@SalesID", salesID_DB);
cmd.Parameters.AddWithValue("@CFname", custFname_DB);
cmd.Parameters.AddWithValue("@CLname", custSname_DB);
cmd.Parameters.AddWithValue("@Comms", comments_DB);
cmd.Parameters.AddWithValue("@TotSalePrice", totSalesPrice_DB);

cmd.Transaction = tran;
cmd.ExecuteNonQuery();
tran.Commit();
con.Close();

//navigate to another page to display the contents of the sales_tbl
recPage printRecPage = new recPage();
this.NavigationService.Navigate(printRecPage);

I then navigate to another page (recPage) and populate a listview (SoldItesmListView) with all the contents of the Sales_TBL code below:

DataClasses1DataContext dc = new DataClasses1DataContext();

var q =
from a in dc.GetTable<Sales_TBL>()
select new RecData 
{
   salesID = (string)a.Sale_ID,
   salesDate = (DateTime)a.Date,
   custFname = (string)a.Cust_Fname,
   custSname = (string)a.Cust_Lname,
   saleComms = (string) a.Comments,
   totPrice = (double) a.Total_Sale_Price,
};
SoldItesmListView.DataContext = q;

Now all the above works fine, however the only issue I am having is the last record that was entered through the insert query will not show in the listview above (previously inserted data does show up in the list view), the record is inserted into the Database successfully, as when I close and re-run the application the record then shows up in the listview.

I hope this makes sense and I have no idea why it’s doing this, I have searched a few forums and not found a similar issue.

Any help or advice will be much appreciated.

score 0 · Accepted Answer · answered Aug 03 '12 at 11:53

0

please specify which ORM you are using (entity Framework, Linq2Sql, ...). Probably it caches the results of previous query and doesn't know that table was updated, because you update it using regular Ado.Net command.

You should either update table using same ORM or find a way to clear its cache before retrieving table data.

answered Aug 03 '12 at 11:53

Snowbear

16,924
3
43
67

Thanks Snowbear, as you suggested the issue was with using a different ORM to insert\query the table. I ended up using ADO.net to insert into the table as in my original code above, then to query the table after the insert I also used ADO.net instead of Linq2SQL. I populated the listview using a DataTable, and it was successful in showing the latest inserted record. Thanks again for your help! It much appreciated. – user1573896 Aug 04 '12 at 03:57

Last inserted record not displaying in listview until I close and re-run the app

1 Answers1