About Chris Expertise I can answer pretty much any question relating to VB.NET and its use in a Windows environment. I specialize in ASP.NET web development and MSSQL database access.
Experience I have over 5 years of industry experience using VB.NET and other .NET technologies for web and database development.
Education/Credentials I have some college education, but does it really matter in this field of work?
Expert: Chris Date: 8/13/2007 Subject: using table adapters
Question QUESTION: I have a combobox list of datatable names that I want the user to be able to select from and at the press of a button view the data (possibly in a datagridview table) on a separate form.
In order to 'fill' datagridview you need the table adapter for the specific table you would like to view, but since the user is selecting this it isn't known in advance (the list also contains tables that they might have created and named while using the application)
so far this is what I have for the form where they can preview the data:
Private Sub Preview_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
previewtable.DataSource = LobsterDataSetBindingSource ' preview table is the name of the datagridview table
previewtable.DataMember = CStr(IBM.tablelist.SelectedItem) 'this is the combobox name
previewtable.AutoGenerateColumns = True
previewtable.ReadOnly = True
End Sub
any help would be greatly appreciated
Amanda
ANSWER: Since you don't know the details of the table whose data you want to display, you can't ask Visual Studio to generate a data access component for you to use as a data source. You'll need to access the database through code, but this is actually a good thing. Direct access using SqlDataAdapter is faster than through those auto generated thingies anyway.
So, the short answer is something like this:
- Add an import for the System.Data.SqlClient namespace to your class by adding this statement to the top of your class:
Imports System.Data.SqlClient
- Create an SqlConnection to access the database where the table resides. You'll have to pass it a connection string, telling it which server (eg: 127.0.0.1) and database (eg: Northwind) to work with. You might also need to provide a username and password - this example assumes your windows user account has the proper rights to access the database:
Dim conn As New SqlConnection("data source=YOUR_SERVER_NAME;initial catalog=YOUR_DATABASE_NAME;integrated security=SSPI;persist security info=False;packet size=4096")
- Create an SqlCommand to access the table and retrieve its data:
Dim cmd as New SqlCommand("SELECT * FROM some_table_name", conn)
- Create a DataTable to hold the retrieved data, and set its TableName property to the name of the table:
Dim dt as New DataTable
dt.TableName = "some_table_name"
- Create and use an SqlDataAdapter to put the data into the DataTable:
Dim da as New SqlDataAdapter(cmd)
conn.Open()
da.Fill(dt)
conn.Close()
- Set your BindingContainer's DataSource property to this DataTable, then set your DataGridView's DataSource to the BindingContainer (if this wasn't already set in design mode). Do not set the DataMember property to anything here, or on your DataGridView:
LobsterDataSetBindingSource.DataSource = dt
previewtable.DataSource = LobsterDataSetBindingSource
QUESTION: Thanks for the link, however even when I created a user account I wasn't able to access the gallery section of your website so I haven't been able to view the code.
I tried to do it from the short answer and it didn't work. (my ignorance of VB will show through here). The new tables when/if they are created are saved to the dataset, wouldn't there be an easier way to access them within the application rather then going to the database? If I am saving them on the dataset are they even accessible through the database?
I am also using Microsoft Access so I think I used the wrong server name...where would I find the server name?
thanks
Amanda
(username on your website:vacuit)
ANSWER: I've fixed the example I posted - you should be able to download it now at the link I gave you - but hold off on that for a minute.
First, I think I should explain that you can not save data into a DataSet, per se. You can save a DataSet, and its data, to a database (or an XML file, etc.), however. This is probably what is happening behind your back - I'm assuming this DataSet you're working with was automatically generated by Visual Studio for you, correct?
DataSets are like miniature databases. You can create tables, add records, modify them, and so forth. But at some point, you'll have to save this data. If you're using auto-generated DataSets, they'll only work with the original tables. Once a table is modified, the DataSet's inner workings which you've been shielded from no longer correspond, and adding or modifying records will probably fail. You also won't be able to see newly added columns.
Since you're working with tables which the user can apparently create and modify for themselves, automatically generated DataSets (a.k.a. Components, if you're using Visual Studio 2003 or earlier) are no longer an option. But that's okay, it's always faster executing and easier maintenance to do it manually, believe it or not.
Also, since you're using Microsoft Access, and not MS SQL Server, I should warn you that that example I posted does not completely apply - the method I used to get a list of tables in the database will not work in Access. Also, some of the code is different - for example you import System.Data.OleDb instead of System.Data.SqlClient to gain access to the database classes. Instead of using SqlCommand, SqlConnection and SqlDataAdapter, you should use OleDbCommand, OleDbConnection and OleDbDataAdapter. Fortunately, both use the same SQL queries for inserting, updating and deleting data, so you just need to rename a few things. Finally, since you're using Access instead of SQL Server, when you create an OleDbConnection, you'll have to pass it a totally different connection string. It would look something like:
Dim cmd As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyDatabaseFilename)
Now, I said that you can not save data into a DataSet, but you can save a DataSet and its data into a database. How? Change the data in code or in a control bound to something in the DataSet (such as a DataGridView), then call the DataAdapter's .Update() command and pass it your DataSet. But you're going to need to manually instantiate a new DataSet, DataTable and DataAdapter yourself because the user might have created or changed the table themselves. And to support updating, it has to be done in the correct order, using an OleDbCommandBuilder. You should also be aware, that since you're not getting those automatically generated DataSets here, you won't be able to access fields from each record as properties directly. For example if you had a User table with a field called UserName, you'd have to get the DataTable from the DataSet's .Tables property [eg: Dim dt As DataTable = ds.Tables.Item(0)], then get a row to work with, and modify a column [eg: dr.Rows(0).Item("UserName") = "new username"]. But since you'll be working with a DataGridView, it'll handle all of that for you as far as accessing individual rows and the data within.
So, basically you want to:
Create a small class to retrieve a DataSet for any table given in your database. You'll create a new instance of this, it'll create a DataAdapter, fill a DataTable and put it in a DataSet. You'll get its result, and bind that DataSet to your BindingContainer, which in turn is bound to your DataGridView. You'll also want to provide a way to commit changes made to the DataSet to the database, if the DataGridView (or any other bound control) is set to be editable, so that the next time you load data into this DataSet from the database those changes will remain.
So, as you can see, this is not easy. I'd put the entire resulting class here, but it would look horrible :). So, I'll update the example - take a look at the TableLoader.vb file in there - you can use it to provide all the synchronization between MS Access and a DataSet.
In case you can't access that, I'll put the key parts here:
' create a connection to your Access .mdb file
Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & c_strAccessMDBFile)
' this command will retrieve all of the columns from all of the rows of the table specified
Dim cmd As New OleDbCommand("SELECT * FROM " & strTableName, conn)
' this will create a DataAdapter, which synchronizes between DataTables/DataSets and a database
Dim da As New OleDbDataAdapter(cmd)
' create a dataset and a datatable, give the table a name, and add the table to the dataset
Dim ds As New DataSet
Dim dt As New DataTable
dt.TableName = strTableName
ds.Tables.Add(dt)
' open the connection and fill the specified table in the dataset
conn.Open()
da.Fill(ds, strTableName)
' this CommandBuilder will create the necessary Update/Insert/Delete queries for the DataAdapter
Dim cb As New OleDbCommandBuilder(da)
da.InsertCommand = cb.GetInsertCommand()
da.DeleteCommand = cb.GetDeleteCommand()
da.UpdateCommand = cb.GetUpdateCommand()
' always close your connection when done
conn.Close()
' this will allow you to commit changes made to the DataSet back into the database
If ds.HasChanges() Then da.Update(ds)
But like I said, get the updated example I posted, and look at TableLoader.vb - it takes the code from above and wraps it in a class that will keep track of your DataAdapter and DataSet, they're needed together to save changes.
To use it from your code, you'd do something like:
Private Sub Preview_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim objLoader As New TableLoader(CStr(IBM.tablelist.SelectedItem))
previewtable.DataSource = LobsterDataSetBindingSource
previewtable.AutoGenerateColumns = True
previewtable.ReadOnly = True
End Sub
Let me know if this works...
---------- FOLLOW-UP ----------
QUESTION: That code is great, thanks it works perfectly.
Last question: I was wondering can I recycle this Class when accessing the chosen datatable in my model (module) as well?
i.e. Something like this:
Sub CrustaceanIBM() 'my model
Dim HLandings(0 To MAXYR - MINYR) As Single 'historic landings (kg)
'SET HISTORIC LANDINGS (in kg)
Dim objLoader As New TableLoader(CStr(IBM.CBLandingsList.SelectedItem))
Dim dt = The_Landings_Table_Here
For i = 0 To MAXYR - MINYR
HLandings(i) = dt.Rows(i+1).Item(1) 'row i+1,column 2
Next i
...
Thanks again
Amanda
Answer Hi,
There's no reason why you shouldn't be able to reuse it for most of your data access needs. It may not be the best solution (I use a more advanced home-grown code generator for those tables that I know of in advance), but it will get the job done. You should download the latest update to the example that I just posted. It adds a new constructor, to allow you to execute any query you want instead of a blind SELECT *. It also adds a utility function to execute DELETE statements and such. And a new property accessor, .Table, to get just the DataTable holding the rows returned.
You could use it in your code like this:
Dim objLoader As New TableLoader(CStr IBM.CBLandingsList.SelectedItem))
Dim dt as DataTable = objLoader.Table
For i = 0 To MAXYR - MINYR
' you do not want to reference .Rows(i+1), since the rows array starts at index 0
' you could also reference .Item("columnName") instead of using its index
HLandings(i) = dt.Rows(i).Item(1)
Next i
If you wanted to make changes to any of the data, you can do so directly against the rows, then call objLoader.Save(), and it should update the database accordingly.
This should work for any table, using any query you want, provided that the data it returns comes from only one table. Otherwise, the DataSet object that controls all of this will fail to update the database. If you have a query that loads data from multiple tables, the DataSet will usually handle that correctly for read-only purposes, but represent it as only one DataTable, due to the simplicity of this code.
