ASP.NET / MySQL

Use asp.net to select a MySQL table by table name
using a drop down list.

This example shows how to use a drop down list to select a MySQL table in order to execute an sql command on the table selected:

Default.aspx
<asp:DropDownList ID="DropDownList1" runat="server" DataTextField="_Desc" DataValueField="_Name" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged" AutoPostBack="True" AppendDataBoundItems="true" DataSourceID="SqlDataSource1"> <asp:ListItem Text="--- Please Select ---" Value="" Selected="True"></asp:ListItem></asp:DropDownList> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand="SELECT _Name, _DESC FROM TABLELIST"></asp:SqlDataSource> <br /><br /> <asp:GridView ID="GridView1" runat="server"> </asp:GridView>

Default.aspx.vb
Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged If (DropDownList1.SelectedValue <> "") Then oConnection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ConnectionString oConnection.Open() Dim oCommand = New MySqlCommand oCommand.CommandType = Data.CommandType.Text oCommand.CommandText = "Select * from _" & DropDownList1.SelectedValue oCommand.Connection = oConnection Dim oDataReader As MySqlDataReader oDataReader = oCommand.ExecuteReader() GridView1.DataSource = oDataReader GridView1.DataBind() oConnection.Close() End If End Sub

This example assumes a MySQL Table called TABLELIST, with two columns in it called _NAME and _DESC. The _NAME column will contain a list of MySQL table names, and the _DESC column will contain a friendly description of the tables to select in the drop down.

Example:

Note: This example assumes there are three MySQL tables named _75340, _40480, and _30490.

How it works:

The dropdown is showing the table "common names" of John's Table, Jay's Table, and Jeff's Table. If "Jay's Table" were selected, a posts back of 40480 will be made. Since all of our tables have an "_" in front of them, we will have to concatenate the "_" and the post back value. In this example the "table name" of 40480 is concatenated with the "_" so that it can select MySQL table "_40480" and execute an sql command on it. The command is a simple SELECT * command that is bound to  GridView1.

The table structure of the example tables isn't really important because all this code is doing is taking a list of tables and executing a "SELECT  * from TABLE" command.

This example is good to show how to concatenate a string in a SQL command that is MySQL friendly.

Tags:  asp.net 3.5, Visual Basic, VB, MySQL 5.1, MySQL .net connector