dropdownlist concatenation


P

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();
 
Ad

Advertisements

H

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)
 
P

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

Hermit Dave said:
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 said:
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();
 
H

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)
Paxtonend said:
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 said:
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 said:
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();
 
P

Paxtonend

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

Many thanks

Pax

Hermit Dave said:
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)
Paxtonend said:
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
 
Ad

Advertisements

H

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)
Paxtonend said:
The foreach code worked perfectly. Couldn't get the Select query to work,
though. Still - problem solved :)

Many thanks

Pax

Hermit Dave said:
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)
Paxtonend said:
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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top