Populate .net dropdown

I have 2 tables. One is country and holds a list of countries and the other
is region which holds a list of states/provinces/countys relating to that
country.

How do I populate a combo so that it is indented.

e.g

USA
-Florida
-California
-Utah
Canada
-Ontario
-Quebec
England
-Kent
-Northumberland

TIAYou get real familiar with the dropdownlist.add() method, because this isn't
something you are going to acheive with the standard way of doing things.
First you have to design a query to return
countries/states/provinces/countys sorted by country, then you have to loop
through them, set some variable to the current country, and then pop either
a Country, or a state/province/county in the dropdownlist using the .add
method. If it is a state/province/county you'll probably need to add
something to the beginning to actually indent it. Then you'll have the
problem of finding out if they selected a country or a
state/province/county.

sounds fun!

"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:%23ndPZP32DHA.556@.TK2MSFTNGP11.phx.gbl...
> I have 2 tables. One is country and holds a list of countries and the
other
> is region which holds a list of states/provinces/countys relating to that
> country.
> How do I populate a combo so that it is indented.
> e.g
> USA
> -Florida
> -California
> -Utah
> Canada
> -Ontario
> -Quebec
> England
> -Kent
> -Northumberland
> TIA

populate ddl from two tables

Hello,
I have two Tables: Table1: GroupID1, GroupName1 and Table2:GroupID2,
GroupName2.
I have only one dropdown list (DropDownList1) in my application and through
a stored procedure I want to populate my DropDownList1 from GroupName1 and
GroupName2. How should I write my query?
Thanks,
Jim.You'll probably want to use a union.
select companyname from suppliers union select shipcity from orders
try this on Northwind, it'll return the companynames and shipcity in one
long list.
HTH
Pete
--
http://www.xboxracing.net/
"JIM.H." wrote:

> Hello,
> I have two Tables: Table1: GroupID1, GroupName1 and Table2:GroupID2,
> GroupName2.
> I have only one dropdown list (DropDownList1) in my application and throug
h
> a stored procedure I want to populate my DropDownList1 from GroupName1 and
> GroupName2. How should I write my query?
> Thanks,
> Jim.
>
Jim,
You need to join 2 tables with a UNION query.
Eliyahu
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:318E29E9-6546-43E0-9B65-C0529E58FA96@.microsoft.com...
> Hello,
> I have two Tables: Table1: GroupID1, GroupName1 and Table2:GroupID2,
> GroupName2.
> I have only one dropdown list (DropDownList1) in my application and
through
> a stored procedure I want to populate my DropDownList1 from GroupName1 and
> GroupName2. How should I write my query?
> Thanks,
> Jim.
>
Can you give me an example?
"Eliyahu Goldin" wrote:

> Jim,
> You need to join 2 tables with a UNION query.
> Eliyahu
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:318E29E9-6546-43E0-9B65-C0529E58FA96@.microsoft.com...
> through
>
>

populate ddl from two tables

Hello,
I have two Tables: Table1: GroupID1, GroupName1 and Table2:GroupID2,
GroupName2.

I have only one dropdown list (DropDownList1) in my application and through
a stored procedure I want to populate my DropDownList1 from GroupName1 and
GroupName2. How should I write my query?
Thanks,
Jim.You'll probably want to use a union.

select companyname from suppliers union select shipcity from orders

try this on Northwind, it'll return the companynames and shipcity in one
long list.

HTH

Pete
--
http://www.xboxracing.net/

"JIM.H." wrote:

> Hello,
> I have two Tables: Table1: GroupID1, GroupName1 and Table2:GroupID2,
> GroupName2.
> I have only one dropdown list (DropDownList1) in my application and through
> a stored procedure I want to populate my DropDownList1 from GroupName1 and
> GroupName2. How should I write my query?
> Thanks,
> Jim.
Jim,

You need to join 2 tables with a UNION query.

Eliyahu

"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:318E29E9-6546-43E0-9B65-C0529E58FA96@.microsoft.com...
> Hello,
> I have two Tables: Table1: GroupID1, GroupName1 and Table2:GroupID2,
> GroupName2.
> I have only one dropdown list (DropDownList1) in my application and
through
> a stored procedure I want to populate my DropDownList1 from GroupName1 and
> GroupName2. How should I write my query?
> Thanks,
> Jim.
Can you give me an example?

"Eliyahu Goldin" wrote:

> Jim,
> You need to join 2 tables with a UNION query.
> Eliyahu
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:318E29E9-6546-43E0-9B65-C0529E58FA96@.microsoft.com...
> > Hello,
> > I have two Tables: Table1: GroupID1, GroupName1 and Table2:GroupID2,
> > GroupName2.
> > I have only one dropdown list (DropDownList1) in my application and
> through
> > a stored procedure I want to populate my DropDownList1 from GroupName1 and
> > GroupName2. How should I write my query?
> > Thanks,
> > Jim.
>

Populating a DropDownList

Hi all,
I have a MS SQLServer database which has two tables, 'images' and
'photographers'. The photographer table contains a field for
PhotographerID and a one for PhotographerName. The image table also
contains a field for photographerID which is used to join the two
tables.
I'm having problems with populating a dropDownList using the complete
set of photographerNames and IDs from the 'photographer' table but
binding the selected value to the Image table.
I've set up a DataSet and added both tables to it and now have the list
populated but can't bind the list to the photographerID field in the
'image' table.
Anyone done anything like this before? if so any help would be much
appreciated,
thanks in advance,
PaulSo if i understand, you have a Dataset with 2 DataTables.
The DataMember property of the Dropdownlist object will allow you to specify
which dataTable to use from the datasource which is in this case the DataSet
So for example
DropDownList dll = new DropDownList();
ddl.DataSource = MyDataSetWith2Tables;
ddl.DataMember = "Photographers"
...
ddl.DataBind()
HTH,
Tony
"p.mc" <paul.mcmanus.uk@.googlemail.com> wrote in message
news:1142616298.580083.168470@.j33g2000cwa.googlegroups.com...
> Hi all,
> I have a MS SQLServer database which has two tables, 'images' and
> 'photographers'. The photographer table contains a field for
> PhotographerID and a one for PhotographerName. The image table also
> contains a field for photographerID which is used to join the two
> tables.
> I'm having problems with populating a dropDownList using the complete
> set of photographerNames and IDs from the 'photographer' table but
> binding the selected value to the Image table.
> I've set up a DataSet and added both tables to it and now have the list
> populated but can't bind the list to the photographerID field in the
> 'image' table.
> Anyone done anything like this before? if so any help would be much
> appreciated,
> thanks in advance,
> Paul
>
Hi Tony,
thanks for your help, however i'm still unable to get it working as i
would like. Using the method you suggested i've got the ddl displaying
the complete list of photographers from the photographers table. After
i select this i would like to store the result (photog ID) in the
photographerID field in the 'imageTable'. So i guess i need to bind the
DataTextField and DataValueField to the photographer Table but bind the
result to the image table.
I'm sure i must be missing something simple here, but can't think what
it is. Any further help would be appreciated.
thanks, paul
Anthony Merante wrote:
> So if i understand, you have a Dataset with 2 DataTables.
> The DataMember property of the Dropdownlist object will allow you to speci
fy
> which dataTable to use from the datasource which is in this case the DataS
et
> So for example
> DropDownList dll = new DropDownList();
> ddl.DataSource = MyDataSetWith2Tables;
> ddl.DataMember = "Photographers"
> ...
> ddl.DataBind()
> HTH,
> Tony
> "p.mc" <paul.mcmanus.uk@.googlemail.com> wrote in message
> news:1142616298.580083.168470@.j33g2000cwa.googlegroups.com...
Assuming that the image field that you want to place in selected value
is of type System.String, you need to create a SQL statement or
(preferably) a stored procedure that will do the join. Then bind the
dropdown list to that...
using (SqlConnection conPhotographerAndImage = new
SqlConnection([PUT CONNECTION STRING HERE])
{
SqlCommand cmdGet = new
SqlCommand("GetPhotographersAndImages", conPhotographerAndImage);
cmdGet.CommandType = CommandType.StoredProcedure;
//You could also omit the above line and set command with
text instead...
//SELECT p.PhotographerName,i.ImageName FROM Photographer
p INNER JOIN Image i ON p.PhotographerId=i.PhotographerId;
conPhotographerAndImage.Open();
SqlDataReader drdPhotographerAndImage =
cmdGet.ExecuteReader();
this.ddlMyDropDownList.DataSource =
drdPhotographerAndImage;
this.ddlMyDropDownList.DataValueField = "ImageName";
this.ddlMyDropDownList.DataTextField =
"PhotographerName";
this.ddlMyDropDownList.DataBind();
drdPhotographerAndImage.Close();
conPhotographerAndImage.Close();
ListItem lstAll = new ListItem("[all]", "999999");
this.ddlEventType.Items.Insert(this.ddlEventType.Items.Count, lstAll);
this.ddlEventType.SelectedIndex =
this.ddlEventType.Items.Count - 1;
}
HTH,
JP
JP
thanks for your help, just to clarify - i've included a table in the
dataSet which is a SQL view that contains the INNER JOIN you suggest.
Is it necessary to explicitly create this join for the ddl?
The webForm i'm working on will contain 15 to 20 of these foreign-key
joins bound to ddls.
I'm sure that what i'm trying to do must be a very common technique (is
it not one of the basic principles of relational databases?), surely
using foreign keys to link to other tables can be handled more
efficiently than this?
thanks again
The point is to set the datasource up as *one* entity (not two tables).
This where the join comes in. You configure the query with a join to
return a result set that has all of your data. You then simply set
DataTextField to the name of the field that you want to show in the
dropdown, set DataValue field to the name of the field that you want to
be in the corresponding values, and then bind the DDL to the one
entity. At least that's the way I have always done it.
JP
Thanks for your help, much appreciated.
I'll have a go and see where i get,
thanks again

Populating a DropDownList

Hi all,

I have a MS SQLServer database which has two tables, 'images' and
'photographers'. The photographer table contains a field for
PhotographerID and a one for PhotographerName. The image table also
contains a field for photographerID which is used to join the two
tables.

I'm having problems with populating a dropDownList using the complete
set of photographerNames and IDs from the 'photographer' table but
binding the selected value to the Image table.

I've set up a DataSet and added both tables to it and now have the list
populated but can't bind the list to the photographerID field in the
'image' table.

Anyone done anything like this before? if so any help would be much
appreciated,

thanks in advance,

PaulSo if i understand, you have a Dataset with 2 DataTables.

The DataMember property of the Dropdownlist object will allow you to specify
which dataTable to use from the datasource which is in this case the DataSet

So for example

DropDownList dll = new DropDownList();
ddl.DataSource = MyDataSetWith2Tables;
ddl.DataMember = "Photographers"
...
ddl.DataBind()

HTH,
Tony

"p.mc" <paul.mcmanus.uk@.googlemail.com> wrote in message
news:1142616298.580083.168470@.j33g2000cwa.googlegr oups.com...
> Hi all,
> I have a MS SQLServer database which has two tables, 'images' and
> 'photographers'. The photographer table contains a field for
> PhotographerID and a one for PhotographerName. The image table also
> contains a field for photographerID which is used to join the two
> tables.
> I'm having problems with populating a dropDownList using the complete
> set of photographerNames and IDs from the 'photographer' table but
> binding the selected value to the Image table.
> I've set up a DataSet and added both tables to it and now have the list
> populated but can't bind the list to the photographerID field in the
> 'image' table.
> Anyone done anything like this before? if so any help would be much
> appreciated,
> thanks in advance,
> Paul
Hi Tony,

thanks for your help, however i'm still unable to get it working as i
would like. Using the method you suggested i've got the ddl displaying
the complete list of photographers from the photographers table. After
i select this i would like to store the result (photog ID) in the
photographerID field in the 'imageTable'. So i guess i need to bind the
DataTextField and DataValueField to the photographer Table but bind the
result to the image table.

I'm sure i must be missing something simple here, but can't think what
it is. Any further help would be appreciated.

thanks, paul

Anthony Merante wrote:
> So if i understand, you have a Dataset with 2 DataTables.
> The DataMember property of the Dropdownlist object will allow you to specify
> which dataTable to use from the datasource which is in this case the DataSet
> So for example
> DropDownList dll = new DropDownList();
> ddl.DataSource = MyDataSetWith2Tables;
> ddl.DataMember = "Photographers"
> ...
> ddl.DataBind()
> HTH,
> Tony
> "p.mc" <paul.mcmanus.uk@.googlemail.com> wrote in message
> news:1142616298.580083.168470@.j33g2000cwa.googlegr oups.com...
> > Hi all,
> > I have a MS SQLServer database which has two tables, 'images' and
> > 'photographers'. The photographer table contains a field for
> > PhotographerID and a one for PhotographerName. The image table also
> > contains a field for photographerID which is used to join the two
> > tables.
> > I'm having problems with populating a dropDownList using the complete
> > set of photographerNames and IDs from the 'photographer' table but
> > binding the selected value to the Image table.
> > I've set up a DataSet and added both tables to it and now have the list
> > populated but can't bind the list to the photographerID field in the
> > 'image' table.
> > Anyone done anything like this before? if so any help would be much
> > appreciated,
> > thanks in advance,
> > Paul
Assuming that the image field that you want to place in selected value
is of type System.String, you need to create a SQL statement or
(preferably) a stored procedure that will do the join. Then bind the
dropdown list to that...

using (SqlConnection conPhotographerAndImage = new
SqlConnection([PUT CONNECTION STRING HERE])
{
SqlCommand cmdGet = new
SqlCommand("GetPhotographersAndImages", conPhotographerAndImage);
cmdGet.CommandType = CommandType.StoredProcedure;

//You could also omit the above line and set command with
text instead...
//SELECT p.PhotographerName,i.ImageName FROM Photographer
p INNER JOIN Image i ON p.PhotographerId=i.PhotographerId;

conPhotographerAndImage.Open();

SqlDataReader drdPhotographerAndImage =
cmdGet.ExecuteReader();

this.ddlMyDropDownList.DataSource =
drdPhotographerAndImage;
this.ddlMyDropDownList.DataValueField = "ImageName";
this.ddlMyDropDownList.DataTextField =
"PhotographerName";
this.ddlMyDropDownList.DataBind();

drdPhotographerAndImage.Close();
conPhotographerAndImage.Close();

ListItem lstAll = new ListItem("[all]", "999999");

this.ddlEventType.Items.Insert(this.ddlEventType.I tems.Count, lstAll);
this.ddlEventType.SelectedIndex =
this.ddlEventType.Items.Count - 1;
}

HTH,
JP
JP

thanks for your help, just to clarify - i've included a table in the
dataSet which is a SQL view that contains the INNER JOIN you suggest.
Is it necessary to explicitly create this join for the ddl?

The webForm i'm working on will contain 15 to 20 of these foreign-key
joins bound to ddls.

I'm sure that what i'm trying to do must be a very common technique (is
it not one of the basic principles of relational databases?), surely
using foreign keys to link to other tables can be handled more
efficiently than this?

thanks again
The point is to set the datasource up as *one* entity (not two tables).
This where the join comes in. You configure the query with a join to
return a result set that has all of your data. You then simply set
DataTextField to the name of the field that you want to show in the
dropdown, set DataValue field to the name of the field that you want to
be in the corresponding values, and then bind the DDL to the one
entity. At least that's the way I have always done it.

JP
Thanks for your help, much appreciated.
I'll have a go and see where i get,
thanks again

Populating datagrid with columns with multiple tables.

Using ASP.NET (vb), I have two tables and joining on ID. I am having some issues populating my datagrid with a dataset.

Here is what I have so far:

Dim conPubs as SqlConnection
Dim dsSearch1 as DataSet
Dim dsSearch2 as DataSet
Dim adSearch1 as SqlDataAdapter
Dim adSearch2 as SqlDataAdapter
Dim iRecordsFound as Integer
Dim sSearch as String

conPubs = New SqlConnection(...)

sSearch = txtSearch.Text("'", "''")
If sSearch.Length > 0 Then
sSQL1 = "SELECT PRD_ID, BRAND, MODEL FROM t_products "
sSQL1 &= "WHERE BRAND like '%" & sSearch & "' "
sSQL1 &= "ORDER BY BRAND"

sSQL2 = "SELECT RATING, RECOMMEND FROM t_details"
Else
sSQL1 = "SELECT PRD_ID, BRAND, MODEL FROM t_products "
sSQL1 &= "ORDER BY BRAND"

sSQL2 = "SELECT RATING, RECOMMEND FROM t_details"
End If

adSearch1 = New SqlDataAdapter(sSQL1, conPubs)
adSearch2 = New SqlDataAdapter(sSQL2, conPubs)

dsSearch1 = New DataSet()
dsSearch2 = New DataSet()

adSearch1.Fill(dsSearch1, "t_products")
adSearch2.Fill(dsSearch2, "t_products")

Dim pk1(0) as DataColumn
Dim pk2(0) as DataColumn

pk1(0) = dsSearch1.Tables(0).Columns("PRD_ID")
dsSearch1.Tables(0).PrimaryKey = pk1

pk2(0) = dsSearch2.Tables(0).Columns("PRD_ID")
dsSearch2.Tables(0).PrimaryKey = pk2

dsSearch1.Merge(dsSearch2, false, MissingSchemaAction.Add)

conPubs.Open()
iRecordsFound = dsSearch1.Tables("t_products").Rows.Count.ToString()
lblRowCount.Text = iRecordsFound

datagridOutput.DataSource = dsSearch1
datagridOutput.DataBind()
conPubs.Close()


The error I get now is:
System.ArgumentNullException: 'column' argument cannot be null. Parameter name: column

Any help would be great or better way of doing this is welcome as well.
Thanks.Probably because sSQL2 isn't selecting PRD_ID.
Is there a reason your not joining the tables in your sql statment?
Yeah, your right. I needed the PRD_ID in the sSQL2. I don't get errors but my datagrid says I have over 6 pages (of 25) but I can only see 2.

You suggestion why not putting the sql all in one. I did try this way but could not get to work properly. This is how I would like to do it but couldn't get my code to work.

Any ideas?
Where you using a JOIN or just WHERE X=Y?
TPM - Using x = y.

Update: I tried again and worked so not sure what I did differently but not complaining. This is what I have:

Dim conPubs as SqlConnection
Dim cmdSearch as SqlCommand
Dim dsSearch as DataSet
Dim SadSearch as SqlDataAdapter
Dim sSQL as String
Dim iRecordsFound as Integer
Dim sSearch as String

'Open connection with connection object
conPubs = New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("MS_SQL_CONN"))

sSearch = txtSearch.Text.Replace("'", "''")
sSearch = txtSearch.Text.Replace(";", vbNullString)

'form sql
If sSearch.Length > 0 Then
sSQL = "SELECT p.PRD_ID, BRAND, MODEL, NAME, RECOMMENDED_CD FROM products p, products_details1 pd "
sSQL &= "WHERE p.PRD_ID = pd.PRD_ID "
sSQL &= "AND BRAND Like '%" & sSearch & "%' OR PMODEL Like '%" & sSearch & "%' "
sSQL &= "ORDER BY BRAND, MODEL"
Else
sSQL = "SELECT p.PRD_ID, BRAND, MODEL, NAME, RECOMMENDED_CD FROM products p, products_details1 pd WHERE p.PRD_ID = pd.PRD_ID ORDER BY BRAND"
End If

'To execute sql statement and provide active connection
SadSearch = New SqlDataAdapter(sSQL, conPubs)

'Create instance of dataset object
dsSearch = New DataSet()

'fill datagrid
SadSearch.Fill(dsSearch, "t_products")

conPubs.Open()
iRecordsFound = dsSearch.Tables("t_products").Rows.Count.ToString()
lblRowCount.Text = iRecordsFound

dgrdResults.DataSource = dsSearch
dgrdResults.DataBind()
conPubs.Close()

Do you see any advantage to using the JOIN?
Thanks