dropdownlist concatenation

[Paxtonend]

I want to concatenate the values from two columns in the datatextfield of a
drop down list, so that it displays a series of job titles followed by the
count of job titles in brackets, e.g. Job Title 1 (17), Job Title 2 (22)
etc. This is pretty simple to do in classic ASP, but doing it in C# has got
me stumped. I can get one or other value to display, but can't find a way
to get both. Can anyone help? Here's the code from the ascx.cs file
(excluding connection string):

myDataAdapter = new OleDbDataAdapter("SELECT Jobtitle.JobTitle,
Count(Jobtitle.JobTitle) AS CountOfJobTitle, Adverts.JobTitleID FROM Adverts
INNER JOIN Jobtitle ON Adverts.JobTitleID = Jobtitle.JobTitleID WHERE
StartDate <= Date() AND EndDate >= Date() GROUP BY Jobtitle.JobTitle,
Adverts.JobTitleID ORDER BY Jobtitle.JobTitle;", myOleDbConnection);
mydataset = new DataSet();
myDataAdapter.Fill(mydataset);

DropDownList1.DataSource =ds.Tables [0] ;
DropDownList1.DataTextField = //What goes here?
DropDownList1.DataValueField
=ds.Tables[0].Columns["JobTitleId"].ColumnName.ToString();
DropDownList1.DataBind();

 

[Hermit Dave]

you have a list....
id title
1 X
2 Y
3 z

if you want the value to be one that you assign the id to the value field
and the text to text field.. if you want both of them to be the same... then
you assign the same field to each. simple

hth

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)

 

[Paxtonend]

Thanks, but not quite. The columns I have are:

id JobTitle CountOfJobTitle
1 JobTitle1 12
2 JobTitle2 22
3 JobTitle3 6
4 JobTitle4 14 etc.....

I want the text field to read as follows:

JobTitle1 (12)
JobTitle2 (22)
JobTitle3 (6) etc...

So I guess I need to bring both the JobTitle and CountOf JobTitle together,
and include the opening and closing brackets into the concatentation. How
do I do that?

Thanks

Pax

you have a list....
id title
1 X
2 Y
3 z

if you want the value to be one that you assign the id to the value field
and the text to text field.. if you want both of them to be the same... then
you assign the same field to each. simple

hth

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)

I want to concatenate the values from two columns in the datatextfield

of

a
drop down list, so that it displays a series of job titles followed by the
count of job titles in brackets, e.g. Job Title 1 (17), Job Title 2 (22)
etc. This is pretty simple to do in classic ASP, but doing it in C# has got
me stumped. I can get one or other value to display, but can't find a way
to get both. Can anyone help? Here's the code from the ascx.cs file
(excluding connection string):

myDataAdapter = new OleDbDataAdapter("SELECT Jobtitle.JobTitle,
Count(Jobtitle.JobTitle) AS CountOfJobTitle, Adverts.JobTitleID FROM Adverts
INNER JOIN Jobtitle ON Adverts.JobTitleID = Jobtitle.JobTitleID WHERE
StartDate <= Date() AND EndDate >= Date() GROUP BY Jobtitle.JobTitle,
Adverts.JobTitleID ORDER BY Jobtitle.JobTitle;", myOleDbConnection);
mydataset = new DataSet();
myDataAdapter.Fill(mydataset);

DropDownList1.DataSource =ds.Tables [0] ;
DropDownList1.DataTextField = //What goes here?
DropDownList1.DataValueField
=ds.Tables[0].Columns["JobTitleId"].ColumnName.ToString();
DropDownList1.DataBind();

 

[Hermit Dave]

you could either do it this way

foreach(DataRow dr in mydataset.Tables[0].Rows)
{
ListItem li = new ListItem(dr["JobTitle"].ToString() + " (" +
dr["CountOfJobTitle"] + ")", dr["id].ToString() );
DropDownList1.Items.Add(li);
}

or you could use this query instead
SELECT
Adverts.JobTitleID,
Jobtitle.JobTitle + ' (' + Cast(Count(Jobtitle.JobTitle) as VARCHAR(5) )
+ ')' as Title
FROM
Adverts INNER JOIN Jobtitle ON Adverts.JobTitleID = Jobtitle.JobTitleID
WHERE
StartDate <= Date()
AND EndDate >= Date()
GROUP BY
Jobtitle.JobTitle,
Adverts.JobTitleID
ORDER BY
Jobtitle.JobTitle
--

Regards,

Hermit Dave
(http://hdave.blogspot.com)

Thanks, but not quite. The columns I have are:

id JobTitle CountOfJobTitle
1 JobTitle1 12
2 JobTitle2 22
3 JobTitle3 6
4 JobTitle4 14 etc.....

I want the text field to read as follows:

JobTitle1 (12)
JobTitle2 (22)
JobTitle3 (6) etc...

So I guess I need to bring both the JobTitle and CountOf JobTitle together,
and include the opening and closing brackets into the concatentation. How
do I do that?

Thanks

Pax

you have a list....
id title
1 X
2 Y
3 z

if you want the value to be one that you assign the id to the value field
and the text to text field.. if you want both of them to be the same... then
you assign the same field to each. simple

hth

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)

I want to concatenate the values from two columns in the datatextfield

of

a
drop down list, so that it displays a series of job titles followed by the
count of job titles in brackets, e.g. Job Title 1 (17), Job Title 2 (22)
etc. This is pretty simple to do in classic ASP, but doing it in C#

has
got

me stumped. I can get one or other value to display, but can't find a way
to get both. Can anyone help? Here's the code from the ascx.cs file
(excluding connection string):

myDataAdapter = new OleDbDataAdapter("SELECT Jobtitle.JobTitle,
Count(Jobtitle.JobTitle) AS CountOfJobTitle, Adverts.JobTitleID FROM Adverts
INNER JOIN Jobtitle ON Adverts.JobTitleID = Jobtitle.JobTitleID WHERE
StartDate <= Date() AND EndDate >= Date() GROUP BY Jobtitle.JobTitle,
Adverts.JobTitleID ORDER BY Jobtitle.JobTitle;", myOleDbConnection);
mydataset = new DataSet();
myDataAdapter.Fill(mydataset);

DropDownList1.DataSource =ds.Tables [0] ;
DropDownList1.DataTextField = //What goes here?
DropDownList1.DataValueField
=ds.Tables[0].Columns["JobTitleId"].ColumnName.ToString();
DropDownList1.DataBind();

 

[Paxtonend]

The foreach code worked perfectly. Couldn't get the Select query to work,
though. Still - problem solved

Many thanks

Pax

you could either do it this way

foreach(DataRow dr in mydataset.Tables[0].Rows)
{
ListItem li = new ListItem(dr["JobTitle"].ToString() + " (" +
dr["CountOfJobTitle"] + ")", dr["id].ToString() );
DropDownList1.Items.Add(li);
}

or you could use this query instead
SELECT
Adverts.JobTitleID,
Jobtitle.JobTitle + ' (' + Cast(Count(Jobtitle.JobTitle) as VARCHAR(5) )
+ ')' as Title
FROM
Adverts INNER JOIN Jobtitle ON Adverts.JobTitleID = Jobtitle.JobTitleID
WHERE
StartDate <= Date()
AND EndDate >= Date()
GROUP BY
Jobtitle.JobTitle,
Adverts.JobTitleID
ORDER BY
Jobtitle.JobTitle
--

Regards,

Hermit Dave
(http://hdave.blogspot.com)

Thanks, but not quite. The columns I have are:

id JobTitle CountOfJobTitle
1 JobTitle1 12
2 JobTitle2 22
3 JobTitle3 6
4 JobTitle4 14 etc.....

I want the text field to read as follows:

JobTitle1 (12)
JobTitle2 (22)
JobTitle3 (6) etc...

So I guess I need to bring both the JobTitle and CountOf JobTitle together,
and include the opening and closing brackets into the concatentation. How
do I do that?

Thanks

Pax

 

[Hermit Dave]

you are welcome.

the query would run correctly on sql server database. Cast is a sql server
funtion. but what the heck.

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)

The foreach code worked perfectly. Couldn't get the Select query to work,
though. Still - problem solved

Many thanks

Pax

you could either do it this way

foreach(DataRow dr in mydataset.Tables[0].Rows)
{
ListItem li = new ListItem(dr["JobTitle"].ToString() + " (" +
dr["CountOfJobTitle"] + ")", dr["id].ToString() );
DropDownList1.Items.Add(li);
}

or you could use this query instead
SELECT
Adverts.JobTitleID,
Jobtitle.JobTitle + ' (' + Cast(Count(Jobtitle.JobTitle) as VARCHAR(5) )
+ ')' as Title
FROM
Adverts INNER JOIN Jobtitle ON Adverts.JobTitleID = Jobtitle.JobTitleID
WHERE
StartDate <= Date()
AND EndDate >= Date()
GROUP BY
Jobtitle.JobTitle,
Adverts.JobTitleID
ORDER BY
Jobtitle.JobTitle
--

Regards,

Hermit Dave
(http://hdave.blogspot.com)

Thanks, but not quite. The columns I have are:

id JobTitle CountOfJobTitle
1 JobTitle1 12
2 JobTitle2 22
3 JobTitle3 6
4 JobTitle4 14 etc.....

I want the text field to read as follows:

JobTitle1 (12)
JobTitle2 (22)
JobTitle3 (6) etc...

So I guess I need to bring both the JobTitle and CountOf JobTitle together,
and include the opening and closing brackets into the concatentation. How
do I do that?

Thanks

Pax