Last post Jan 03, 2010 04:02 AM by Mikesdotnetting
Dec 29, 2009 04:50 PM|shrubberr|LINK
I'm using the following code in the Page_load Sub of my site. I'm using a DataTable drawn from my database to populate a drop down list. The problem I'm having is that I'm getting duplicates in the Datatable each time I reload the page. I think I've tracked
this down to when I use the Fill method to fill the datatable, it appends the data to the DataTable, instead of replacing the data.
I'm pulling the data in from an Access Database using ADO.NET. That might not be the best way to go, but I couldn't get the AccessDataSource and LINQ to work. If you can offer a way to use the AccessDataSource and LINQ, I would be open to that, but right
now, I've got the data working this way, so don't flame me for it. Also the string path is an actual string path, but it was really long and annoying, so I didn't include it. Thanks for your help!
Here's the code:
Public Shared InnDataSet As New DataSet
Public Shared Sub LoadData()
Dim stAdapter, yearAdapter As New OleDb.OleDbDataAdapter()
'Dim stDataTable, yearDataTable As New DataTable
Dim dtConn As OleDb.OleDbConnection
Dim myPath As String = "<string path here>"
dtConn = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & myPath & ";Persist Security Info=False;")
.SelectCommand = New OleDb.OleDbCommand()
.SelectCommand.Connection = dtConn
.SelectCommand.CommandText = "SELECT * FROM States WHERE (StateID Between 1 AND 51) ORDER BY StateName"
Dec 29, 2009 04:58 PM|chrisbarba|LINK
Check if Page.IsPostBack before you call the LoadData method.
Dec 30, 2009 12:51 PM|shrubberr|LINK
Thanks, I think I got it worked out. The problem seemed to be with declaring the Datatable as a public Dim. I changed it to a private dim inside a function, then returned that function, and it eliminated the problem. Basically it is now creating a new DataTable
each time the page loads.
I'm still somewhat baffled about this though. According to the documentation, the Fill method should merge the data with new data, as long as there are primary keys, which there are in this case, because it's pulling from an Access Database. I guess I'll
keep looking into it.
One more thing though, does anyone have suggestions on what is the most resource efficient way to access a database? This page will do about 20 queries from the data I'm pulling into the DataTable to fill a template. I figured querying the DataTable would
be more efficient than going back and querying the database each time I needed to grab the information. But if I have to re-create the table each time the page loads, it may be more efficient to go directly to the source.
Anyway, thatnks for the help!
Jan 03, 2010 04:02 AM|Mikesdotnetting|LINK
With Access, whether you populate a dataset from 20 queries or not makes little difference. You still need to make 20 queries. I wouldn't bother with the dataset approach until I had performance problems to deal with. Premature optimisation is the root of
many evils. It seems to be causing you problems that you wouldn't otherwise have to contend with. I would also guess that you are not using all the data that you pull into the datatable.
Going back to your first point about LINQ, I believe that someone started work on a LINQ to Access provider, but it was not completed. Microsoft are unlikely to ever produce one. They prefer you to use SQL Server.