Here is my situation. I am using VB as my programming language.
I am creating a part of a website that will be the admin section. I want the admin to be able to add/edit data in a set of tables that normal users can't. There are 20 lookup tables that I use in the website that I want the admin to be able to manage (i.e. add, edit, delete, insert). I don't want to create 20 separate web pages (one for each table). I have one page that has links to all the tables. Each link has a "table" querystring variable associated with it. When the admin clicks on the link it will send the user to the GridView page. The GridView page will get the "table" querystring variable, then load a GridView based on that variable. I have created the Data Access Layer with DataTables and TableAdapters setup for the 20 Lookup Tables.
Here is the code that I have so far. The main part that I cannot seem to figure out is how to choose the appropriate DataTable based on the "table" querystring variable. I would rather not have to create a huge Case statement.
Imports dsPART_CONVTableAdapters 'Imports the Table Adapters class I created in the DLL
Imports System.Data
Partial Class managing_lookup
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim strTableName As String
'Sets strTableName equal to the passed in querystring variable table
strTableName = Request.QueryString("table")
LoadGrid(strTableName)
If Not IsPostBack Then
AddColumnsToGrid(strTableName)
End If
End Sub
Protected Sub LoadGrid(ByVal strTableName As String)
With Me.ObjectDataSource1
.SelectMethod = "GetData" 'All of my TableAdapters Select Statements are called GetData
.DeleteMethod = "Delete"
.InsertMethod = "Insert"
.UpdateMethod = "Update"
.OldValuesParameterFormatString = "Original_{0}"
End With
'Set Type Name
Me.ObjectDataSource1.TypeName = "dsPart_CONVTableAdapters." & strTableName & "TableAdapter"
'Update Parameters
SetUpdateParameters(strTableName)
'Insert Parameters
SetInsertParameters(strTableName)
'Delete Parameters
Me.ObjectDataSource1.DeleteParameters.Add("Original_{0}", TypeCode.Int32)
End Sub
Protected Sub SetUpdateParameters(ByVal strTableName As String)
Dim strDataTableName As String = "dsPart_CONV." & strTableName & "DataTable"
Dim datTable As New DataTable
'Would Like to set datTable equal to what is in strDataTableName
'Need to find a way to set datTable to the DataTable's set in the DLL
For Each col As DataColumn In datTable.Columns
Me.ObjectDataSource1.InsertParameters.Add(col.ColumnName, col.DataType.ToString) 'Do I Need Data Type
Next
Me.ObjectDataSource1.InsertParameters.Add("Original_{0}", TypeCode.Int32)
End Sub
Protected Sub SetInsertParameters(ByVal strTableName As String)
'same as set update parameters find way to dynamically set datTable based on strTableName
Dim strDataTableName As String
strDataTableName = "dsPart_CONV." & strTableName & "DataTable"
'Sets DataTable equal to passed strTableName
Dim datTable As New DataTable
datTable.TableName = strTableName
For Each col As DataColumn In datTable.Columns
Me.ObjectDataSource1.InsertParameters.Add(col.Caption, col.DataType.ToString)
Next
End Sub
Protected Sub AddColumnsToGrid(ByVal strTableName As String)
'same as set update parameters find way to dynamically set datTable based on strTableName
Dim strDataTableName As String
strDataTableName = "dsPart_CONV." & strTableName & "DataTable"
'Sets DataTable equal to passed strTableName
Dim datTable As New DataTable
datTable.TableName = strTableName
With GridView1
.DataSourceID = "ObjectDataSource1"
.AutoGenerateColumns = False
.DataKeyNames = New String() {"ID"}
.AllowPaging = True
.AllowSorting = True
.PageSize = 10
End With
Dim bf As New BoundField
For Each col As DataColumn In datTable.Columns
With bf
.HeaderText = col.ColumnName
.DataField = col.ColumnName
.SortExpression = col.ColumnName
End With
Me.GridView1.Columns.Add(bf)
Next
Dim cmdField As New CommandField
With cmdField
.ButtonType = ButtonType.Button
.ShowCancelButton = True
.ShowEditButton = True
.ShowDeleteButton = True
End With
End Sub
End Class
![]() |
0 |
![]() |
Hi,
To query table according to the table name and bind, I suggest your TableAdapter take the query string as the table name, just as you do it in AddColumnsToGrid() method.
You can pass the table name to the lower level to make it query from the desired table and then bind it to the grid. However, the TableAdapter wizard might not allow you to do this, so you have to customize it. Add some custom methods to the TableAdapter to wrap the GetData() method.
Here are some articles for your reference.
http://msdn2.microsoft.com/en-us/library/ms233697(VS.80).aspx
HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!
Sincerely,
Kevin Yu
Microsoft Online Community Support
Please remember to click “Mark as Answer” on the post that helps you, and to click “Mark as Not Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
![]() |
0 |
![]() |
I have modified the code and everything works now. I do not understand your recommendation: "I suggest your TableAdapter take the query string as the table name, just as you do it in the AddColumnsToGrid() Method". Could your provide me with a very short working example?
Here is the code that works. I had to modify some of the bound field code and add a CASE statement to pull in the correct DataTable. I wish I wouldn't have to use the table adapter and I could just select the correct one based on the "table" querystring variable.
Imports dsPART_CONVTableAdapters
Imports System.Data
Partial Class managing_lookup
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim strTableName As String
strTableName = Request.QueryString("table")
LoadGrid(strTableName)
If Not IsPostBack Then
AddColumnsToGrid(strTableName)
End If
End Sub
Protected Sub LoadGrid(ByVal strTableName As String)
With Me.ObjectDataSource1
.SelectMethod = "GetData" 'Need to change all table adapters to GetData
.DeleteMethod = "Delete"
.InsertMethod = "Insert"
.UpdateMethod = "Update"
.OldValuesParameterFormatString = "Original_{0}"
End With
'Set Type Name
Me.ObjectDataSource1.TypeName = "dsPart_CONVTableAdapters." & strTableName & "TableAdapter"
'Update Parameters
SetUpdateParameters(strTableName)
'Insert Parameters
SetInsertParameters(strTableName)
'Delete Parameters
Me.ObjectDataSource1.DeleteParameters.Add("Original_{0}", TypeCode.Int32)
End Sub
Protected Sub SetUpdateParameters(ByVal strTableName As String)
'Dim strDataTableName As String
'strDataTableName = "dsPart_CONV." & strTableName & "DataTable"
'Sets DataTable equal to passed strTableName
Dim datTable As DataTable = GetDataTable(strTableName)
For Each col As DataColumn In datTable.Columns
Me.ObjectDataSource1.InsertParameters.Add(col.ColumnName, col.DataType.ToString) 'Do I Need Data Type
Next
Me.ObjectDataSource1.InsertParameters.Add("Original_{0}", TypeCode.Int32)
End Sub
Protected Sub SetInsertParameters(ByVal strTableName As String)
'same as set update parameters find way to dynamically set datTable based on strTableName
'Dim strDataTableName As String
'strDataTableName = "dsPart_CONV." & strTableName & "DataTable"
'Sets DataTable equal to passed strTableName
Dim datTable As DataTable = GetDataTable(strTableName)
For Each col As DataColumn In datTable.Columns
Me.ObjectDataSource1.InsertParameters.Add(col.Caption, col.DataType.ToString)
Next
End Sub
Protected Sub AddColumnsToGrid(ByVal strTableName As String)
'same as set update parameters find way to dynamically set datTable based on strTableName
'Dim strDataTableName As String
'strDataTableName = "dsPart_CONV." & strTableName & "DataTable"
'Sets DataTable equal to passed strTableName
Dim datTable As DataTable = GetDataTable(strTableName)
With GridView1
.DataSourceID = "ObjectDataSource1"
.AutoGenerateColumns = False
.DataKeyNames = New String() {"ID"}
.AllowPaging = True
.AllowSorting = True
.PageSize = 10
End With
For Each col As DataColumn In datTable.Columns
Dim bf As New BoundField
With bf
.HeaderText = col.ColumnName
.DataField = col.ColumnName
.SortExpression = col.ColumnName
End With
Me.GridView1.Columns.Add(bf)
bf = Nothing
Next
Dim cmdField As New CommandField
With cmdField
.ButtonType = ButtonType.Button
.ShowCancelButton = True
.ShowEditButton = True
.ShowDeleteButton = True
End With
Me.GridView1.Columns.Add(cmdField)
End Sub
Public Function GetDataTable(ByVal strTableName As String) As DataTable
Select Case strTableName
Case "tblLookup_BsktSlsCat"
GetDataTable = New dsPART_CONV.tblLookup_BsktSlsCatDataTable
Case "tblLookup_BsktState"
GetDataTable = New dsPART_CONV.tblLookup_BsktStateDataTable
'(around 15 more case statments showing all datatables continue on. I deleted them because the code is so long.)
End Select
End Function
End Class
![]() |
0 |
![]() |