How to get database tables names into dropdown list box

[venkateshyeluri]

Hi, I am using an asp.net web application in which I want to retrevie database table names into my dropdown list box. I have wrote code for this there are no errors but I dont get any table name to my DDL box. Kindly help, Thank you.

        MySqlConnection connect = new MySqlConnection();
        connect.ConnectionString = "Data source=localhost;Database=locations;user id=root;password=ietmdb;";

        connect.Open();

        MySqlCommand cmd = new MySqlCommand("show tables", connect);
        MySqlDataReader dr = cmd.ExecuteReader();


        //MySqlDataAdapter da = new MySqlDataAdapter("show tables", connect);
        //DataSet ds = new DataSet();

        //ds.Fill(ds, "tables");
        //DataSet ds = new DataSet();


        while (dr.Read())
        {
            DropDownList1.Items.Add(dr["tables"].ToString());
        }


        //cmd.Parameters.Add(.MySqlDbType.VarChar) = DropDownList1.Text;

[Mitja Bonca]

try using this select query statement:

"select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE = 'BASE TABLE'";

[Mitja Bonca]

Whole code would be:

string connectionString = "Data source=localhost;Database=locations;user id=root;password=ietmdb;";
DataTable tables = new DataTable("Tables");
using (SqlConnection connection = new SqlConnection(connectionString))
{
    using(SqlCommand command = connection.CreateCommand())
    {
        command.CommandText = "select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE = 'BASE TABLE'";
        connection.Open();
        tables.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
    }    
}

foreach(DataRow row in table.Rows)
{
    DropDownList1.Items.Add(row[0].ToString());
}

[venkateshyeluri]

After executing this is the error I got

Could not find specified column in results

Line 56: while (dr.Read())
Line 57: {
Line 58: DropDownList1.Items.Add(dr["tables"].ToString());
Line 59: }
Line 60:

[venkateshyeluri]

Thanks buddy my probelm is solved

[Mitja Bonca]

your should names your table, but else to get them all out, you can use "ALL" witj * simbol:

SELECT * FROM information_schema.tables WHERE table_type = 'BASE TABLE'