Populating a DropDownList

[p.mc]

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

 

[Anthony Merante]

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]

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

 

[Joey]

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

 

[p.mc]

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

 

[Joey]

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

 

[p.mc]

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