asp.net dropdownlist and gridview

4
votes

I have created 2 DropdownList and 1 GridView

1st DropdownList Loads and Displays DataBase Names Dynamically

2nd DropdownList Loads and Displays Table Names, Based on Database name Which selected in 1st drop down list

Based on Table Name Data has to be displayed in GridView........

I have written code which displays Database Names and working fine

private void populateDatabasename() {

        SqlConnection con = new SqlConnection(@"Data Source=SAI-           PC\SQLEXPRESS;Integrated Security=True");
        con.Open();
        SqlDataAdapter da = new SqlDataAdapter("select name,collation_name from sys.databases order by name", con);
        DataSet ds = new DataSet();
        da.Fill(ds, "dbname");
        DropDownList1.DataSource = ds.Tables["dbname"];
        DropDownList1.DataTextField = "name";
        DropDownList1.DataValueField = "name";
        DropDownList1.DataBind();
}

Based on Database name tables have to be displayed..... how to pass Database name(Which is selected in 1st drop down list) in the following code..... Is This a correct way to pass database name

private void populateTableName() {                

        SqlConnection con = new SqlConnection(@"Data Source=SAI-PC\SQLEXPRESS;Integrated Security=True");
        con.Open();
        SqlDataAdapter da = new SqlDataAdapter("select name from "+"@Dbname"+".sys.tables", con);

        da.SelectCommand.Parameters.Add("@dbname", SqlDbType.VarChar);
        da.SelectCommand.Parameters["@dbname"].Value = DropDownList1.SelectedValue;
        DataSet ds = new DataSet();
        da.Fill(ds, "dbname1");
        DropDownList2.DataSource = ds.Tables["dbname1"];
        DropDownList2.DataTextField = "name";
        DropDownList2.DataValueField = "name";
        DropDownList2.DataBind();
}
3
asp.net
It may be by accident but in the SqlDataAdapter you used @Dbname with capital D and when you assign the parameter you used @dbname with a small d. Don't know if it's case sensitive. - Robin V.
Thank You....... I got the answer................................... cmd.CommandText = "select name as TableName from " + drddatabase.SelectedValue + "." + "sys" + "." + "tables - arun369

3 Answers

0
votes

I am not sure if it required or not but you should give the db name in connection string. Database=Northwind; in this case, using System.Data.SqlClient.SqlConnectionStringBuilder class even better. And then you can query the table names if the user have correct rights.

0
votes

i think you should include DataBase name in connection string that you use to populate table

use something like this

SqlConnection con = new SqlConnection(@"Data Source=SAI-PC\SQLEXPRESS;Database=" + Dropdownlist1.selecteditem.text + ";Integrated Security=True");

then Read the tables and continue

0
votes

Try this:

        string dbName = ddlDbName.SelectedValue;
        string strcon = "server=SAI-PC\\SQLEXPRESS;database= " + dbName + ";providerName=\"System.Data.SqlClient\"";
        SqlConnection con = new SqlConnection(strcon);
        con.Open();