Dropdownlist prepopulation

  • Thread starter Thread starter Reddy
  • Start date Start date
R

Reddy

I am trying to prepopulate two drop down meus with results of two differnt
SQL queries. I have written the following code. But I am getting errors. Can
anybody please help me how to do this. I also doubt whether this is the most
efficient way to have maximum performance when lots of users access the web
site.

string dsn = ConfigurationSettings.AppSettings["testDsn"];
SqlConnection connection = new SqlConnection (dsn);
SqlDataAdapter adapter = new SqlDataAdapter ("select * from authors; select
* from publishers", connection);
DataSet ds = new DataSet ();
adapter.Fill (ds, "authors");

DropDownList1.DataSource = ds;
DropDownList1.DataTextField = "au_fname";
DropDownList1.DataValueField = "au_id";
DropDownList1.DataBind ();
DropDownList1.SelectedIndex = DropDownList1.Items.IndexOf
(DropDownList1.Items.FindByValue ("672-71-3249"));

DataSet ds2 = new DataSet ();
adapter.Fill (ds2, "publishers");
DropDownList2.DataSource = ds2;
DropDownList2.DataTextField = "pub_name";
DropDownList2.DataValueField = "pub_id";
DropDownList2.DataBind ();
DropDownList2.SelectedIndex = DropDownList2.Items.IndexOf
(DropDownList2.Items.FindByValue ("1389"));



Thanks,

Reddy
 
Reddy,

Your query is returning both recordsets at the same time, but in your code I
don't see where you are moving to the second recordset before binding it to
the second drop down.

Sincerely,

--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche
 
Sorry I couldn't put it much better.

I needed to execute two SQL queries and my idea was to get two tables from
two sql queries.

I don't know whether I can execute SQL queries like this or need to execute
them separately

Reddy


S. Justin Gengo said:
Reddy,

Your query is returning both recordsets at the same time, but in your code I
don't see where you are moving to the second recordset before binding it to
the second drop down.

Sincerely,

--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche


Reddy said:
I am trying to prepopulate two drop down meus with results of two differnt
SQL queries. I have written the following code. But I am getting errors. Can
anybody please help me how to do this. I also doubt whether this is the most
efficient way to have maximum performance when lots of users access the web
site.

string dsn = ConfigurationSettings.AppSettings["testDsn"];
SqlConnection connection = new SqlConnection (dsn);
SqlDataAdapter adapter = new SqlDataAdapter ("select * from authors; select
* from publishers", connection);
DataSet ds = new DataSet ();
adapter.Fill (ds, "authors");

DropDownList1.DataSource = ds;
DropDownList1.DataTextField = "au_fname";
DropDownList1.DataValueField = "au_id";
DropDownList1.DataBind ();
DropDownList1.SelectedIndex = DropDownList1.Items.IndexOf
(DropDownList1.Items.FindByValue ("672-71-3249"));

DataSet ds2 = new DataSet ();
adapter.Fill (ds2, "publishers");
DropDownList2.DataSource = ds2;
DropDownList2.DataTextField = "pub_name";
DropDownList2.DataValueField = "pub_id";
DropDownList2.DataBind ();
DropDownList2.SelectedIndex = DropDownList2.Items.IndexOf
(DropDownList2.Items.FindByValue ("1389"));



Thanks,

Reddy
Click to expand...
Click to expand...
 
You should write as following:
----------------------------------

public DataTable GetDataTable(string sql){
string dsn = ConfigurationSettings.AppSettings["testDsn"];
SqlConnection connection = new SqlConnection (dsn);
SqlDataAdapter adapter = new SqlDataAdapter (sql, connection);
DataSet ds = new DataSet ();
adapter.Fill (ds);
return ds.Tables[0];
}

public void Page_Load()
{
if(!IsPostBack)
{
DropDownList1.DataSource = GetDataTable("select * from authors");
DropDownList1.DataTextField = "au_fname";
DropDownList1.DataValueField = "au_id";
DropDownList1.DataBind ();
DropDownList1.Items.FindByValue ("672-71-3249").Selected = true;

DropDownList2.DataSource = GetDataTable("select * from publishers");
DropDownList2.DataTextField = "pub_name";
DropDownList2.DataValueField = "pub_id";
DropDownList2.DataBind ();
DropDownList2.Items.FindByValue ("1389").Selected = true;
}
}
 
Reddy,

You can certainly do it this way.

Just specify the datamember (the table name) for each drop down list as Jeff
says.

Sincerely,

--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche


Reddy said:
Sorry I couldn't put it much better.

I needed to execute two SQL queries and my idea was to get two tables from
two sql queries.

I don't know whether I can execute SQL queries like this or need to execute
them separately

Reddy


S. Justin Gengo said:
Reddy,

Your query is returning both recordsets at the same time, but in your
Click to expand...
code
I
don't see where you are moving to the second recordset before binding it to
the second drop down.

Sincerely,

--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche


Reddy said:
I am trying to prepopulate two drop down meus with results of two differnt
SQL queries. I have written the following code. But I am getting
Click to expand...
errors.
Can
anybody please help me how to do this. I also doubt whether this is
Click to expand...
the
most
efficient way to have maximum performance when lots of users access
Click to expand...
the
web
site.

string dsn = ConfigurationSettings.AppSettings["testDsn"];
SqlConnection connection = new SqlConnection (dsn);
SqlDataAdapter adapter = new SqlDataAdapter ("select * from authors; select
* from publishers", connection);
DataSet ds = new DataSet ();
adapter.Fill (ds, "authors");

DropDownList1.DataSource = ds;
DropDownList1.DataTextField = "au_fname";
DropDownList1.DataValueField = "au_id";
DropDownList1.DataBind ();
DropDownList1.SelectedIndex = DropDownList1.Items.IndexOf
(DropDownList1.Items.FindByValue ("672-71-3249"));

DataSet ds2 = new DataSet ();
adapter.Fill (ds2, "publishers");
DropDownList2.DataSource = ds2;
DropDownList2.DataTextField = "pub_name";
DropDownList2.DataValueField = "pub_id";
DropDownList2.DataBind ();
DropDownList2.SelectedIndex = DropDownList2.Items.IndexOf
(DropDownList2.Items.FindByValue ("1389"));



Thanks,

Reddy
Click to expand...
Click to expand...
Click to expand...
 
Reddy,

I was just experimenting with some code similar to yours and I noticed that
the tables aren't named. Here's what you need to do:

1) The way you are creating your connection and filling your first dataset
is just fine. The dataset is actually being populated with two tables each
based on one of the two select statements you are using in the SqlCommand.

2) You won't need the second dataset at all.

3) The tables do not have string names. You'll need to refer to them by
index number.

4) When you assign a datatable to the datalist, quick way to specify the
correct datasource would be to specify the correct datatable directly:

DropDownList1.DataSource = ds.Tables(0);

DropDownList2.DataSource = ds.Tables(1);

Sincerely,

--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche


S. Justin Gengo said:
Reddy,

Your query is returning both recordsets at the same time, but in your code I
don't see where you are moving to the second recordset before binding it to
the second drop down.

Sincerely,

--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche


Reddy said:
I am trying to prepopulate two drop down meus with results of two differnt
SQL queries. I have written the following code. But I am getting errors. Can
anybody please help me how to do this. I also doubt whether this is the most
efficient way to have maximum performance when lots of users access the web
site.

string dsn = ConfigurationSettings.AppSettings["testDsn"];
SqlConnection connection = new SqlConnection (dsn);
SqlDataAdapter adapter = new SqlDataAdapter ("select * from authors; select
* from publishers", connection);
DataSet ds = new DataSet ();
adapter.Fill (ds, "authors");

DropDownList1.DataSource = ds;
DropDownList1.DataTextField = "au_fname";
DropDownList1.DataValueField = "au_id";
DropDownList1.DataBind ();
DropDownList1.SelectedIndex = DropDownList1.Items.IndexOf
(DropDownList1.Items.FindByValue ("672-71-3249"));

DataSet ds2 = new DataSet ();
adapter.Fill (ds2, "publishers");
DropDownList2.DataSource = ds2;
DropDownList2.DataTextField = "pub_name";
DropDownList2.DataValueField = "pub_id";
DropDownList2.DataBind ();
DropDownList2.SelectedIndex = DropDownList2.Items.IndexOf
(DropDownList2.Items.FindByValue ("1389"));



Thanks,

Reddy
Click to expand...
Click to expand...
 
Reddy,

Ok, I've switched from coffee to mountain dew and my brain is functioning
better. I realized I gave you bad code here.

You need to eliminate "authors" from your fill command if you populate the
datasets the way I suggested.

Change it from this:

adapter.Fill (ds, "authors");

to this:

adapter.Fill(ds);

Sorry about that!

--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche


S. Justin Gengo said:
Reddy,

I was just experimenting with some code similar to yours and I noticed that
the tables aren't named. Here's what you need to do:

1) The way you are creating your connection and filling your first dataset
is just fine. The dataset is actually being populated with two tables each
based on one of the two select statements you are using in the SqlCommand.

2) You won't need the second dataset at all.

3) The tables do not have string names. You'll need to refer to them by
index number.

4) When you assign a datatable to the datalist, quick way to specify the
correct datasource would be to specify the correct datatable directly:

DropDownList1.DataSource = ds.Tables(0);

DropDownList2.DataSource = ds.Tables(1);

Sincerely,

--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche


S. Justin Gengo said:
Reddy,

Your query is returning both recordsets at the same time, but in your
Click to expand...
code
I
don't see where you are moving to the second recordset before binding it to
the second drop down.

Sincerely,

--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche


Reddy said:
I am trying to prepopulate two drop down meus with results of two differnt
SQL queries. I have written the following code. But I am getting
Click to expand...
errors.
Can
anybody please help me how to do this. I also doubt whether this is
Click to expand...
the
most
efficient way to have maximum performance when lots of users access
Click to expand...
the
web
site.

string dsn = ConfigurationSettings.AppSettings["testDsn"];
SqlConnection connection = new SqlConnection (dsn);
SqlDataAdapter adapter = new SqlDataAdapter ("select * from authors; select
* from publishers", connection);
DataSet ds = new DataSet ();
adapter.Fill (ds, "authors");

DropDownList1.DataSource = ds;
DropDownList1.DataTextField = "au_fname";
DropDownList1.DataValueField = "au_id";
DropDownList1.DataBind ();
DropDownList1.SelectedIndex = DropDownList1.Items.IndexOf
(DropDownList1.Items.FindByValue ("672-71-3249"));

DataSet ds2 = new DataSet ();
adapter.Fill (ds2, "publishers");
DropDownList2.DataSource = ds2;
DropDownList2.DataTextField = "pub_name";
DropDownList2.DataValueField = "pub_id";
DropDownList2.DataBind ();
DropDownList2.SelectedIndex = DropDownList2.Items.IndexOf
(DropDownList2.Items.FindByValue ("1389"));



Thanks,

Reddy
Click to expand...
Click to expand...
Click to expand...
 
Reddy,

The only real way to tell is to try each.

Put them both in a loop and run it say, 10,000 times. Get the start time
before the loop and the end time after and see which is faster.

Please post the results here. I'm sure we're all curious which will be
faster.

Sincerely,

--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche


Reddy said:
Thanks Everyone.

Both the processes work fantastically

I am in a dilemma now. Which of the two processes is the most efficient way
performance wise(when more number of users access the same page). Becuase I
want to use some more controls on the same page.


private void Page_Load(object sender, System.EventArgs e)
{
DropDownList1.DataSource = GetDataTable("select * from authors");
DropDownList1.DataTextField = "au_fname";
DropDownList1.DataValueField = "au_id";
DropDownList1.DataBind ();
DropDownList1.SelectedIndex = DropDownList1.Items.IndexOf
(DropDownList1.Items.FindByValue ("672-71-3249"));

DropDownList2.DataSource = GetDataTable("select * from publishers");
DropDownList2.DataTextField = "pub_name";
DropDownList2.DataValueField = "pub_id";
DropDownList2.DataBind ();
DropDownList2.SelectedIndex = DropDownList2.Items.IndexOf
(DropDownList2.Items.FindByValue ("1389"));
}
public DataTable GetDataTable(string sql)
{
string dsn = ConfigurationSettings.AppSettings["testDsn"];
SqlConnection connection = new SqlConnection (dsn);
SqlDataAdapter adapter = new SqlDataAdapter (sql, connection);
DataSet ds = new DataSet ();
adapter.Fill (ds);
return ds.Tables[0];
}





or




private void Page_Load(object sender, System.EventArgs e)
{
string dsn = ConfigurationSettings.AppSettings["testDsn"];
SqlConnection connection = new SqlConnection (dsn);
SqlDataAdapter adapter = new SqlDataAdapter ("select * from authors; select
* from publishers", connection);
DataSet ds = new DataSet ();
adapter.Fill (ds);

DropDownList1.DataSource = ds.Tables[0];
DropDownList1.DataTextField = "au_fname";
DropDownList1.DataValueField = "au_id";
DropDownList1.DataBind ();
DropDownList1.SelectedIndex = DropDownList1.Items.IndexOf
(DropDownList1.Items.FindByValue ("672-71-3249"));

DropDownList2.DataSource = ds.Tables[1];
DropDownList2.DataTextField = "pub_name";
DropDownList2.DataValueField = "pub_id";
DropDownList2.DataBind ();
DropDownList2.SelectedIndex = DropDownList2.Items.IndexOf
(DropDownList2.Items.FindByValue ("1389"));
}


Reddy





Reddy said:
I am trying to prepopulate two drop down meus with results of two differnt
SQL queries. I have written the following code. But I am getting errors. Can
anybody please help me how to do this. I also doubt whether this is the most
efficient way to have maximum performance when lots of users access the web
site.

string dsn = ConfigurationSettings.AppSettings["testDsn"];
SqlConnection connection = new SqlConnection (dsn);
SqlDataAdapter adapter = new SqlDataAdapter ("select * from authors; select
* from publishers", connection);
DataSet ds = new DataSet ();
adapter.Fill (ds, "authors");

DropDownList1.DataSource = ds;
DropDownList1.DataTextField = "au_fname";
DropDownList1.DataValueField = "au_id";
DropDownList1.DataBind ();
DropDownList1.SelectedIndex = DropDownList1.Items.IndexOf
(DropDownList1.Items.FindByValue ("672-71-3249"));

DataSet ds2 = new DataSet ();
adapter.Fill (ds2, "publishers");
DropDownList2.DataSource = ds2;
DropDownList2.DataTextField = "pub_name";
DropDownList2.DataValueField = "pub_id";
DropDownList2.DataBind ();
DropDownList2.SelectedIndex = DropDownList2.Items.IndexOf
(DropDownList2.Items.FindByValue ("1389"));



Thanks,

Reddy
Click to expand...
Click to expand...
 
Back
Top