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/9/2007 Subject: using table adapters
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
