how concat multiple values in sql

Question

2.00/5 (1 vote)

See more:

ASP.NET

database

how can i concat multiple values in single label control?

Posted 17-May-12 7:35am

Jignesh J patel

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2012-05-17T07:53:00

Solution 1

If you concatenate string values in C#, you can use + The same applies to SQL Server if you concatenate varchar values in an SQL statement

Addition, example of concatenating in SQL

If you want to concatenate twu strings in the SQL statement, lets say col1 and col2 (both varchar) then your statement could look something like:

SQL

SELECT Col1 + Col2, ... other columns ...
FROM TIMETABLE
WHERE DEPARTMENT_CODE = @depcodeparameter
AND TIME = @sometimeparameter
AND  SEMESTER = @semesterparame

Posted 17-May-12 7:53am

Wendelius

Updated 17-May-12 8:51am

v2

Comments

Monjurul Habib 17-May-12 14:05pm

nice advice :), 5+

Wendelius 17-May-12 14:10pm

Thank you Monjurul :)

Jignesh J patel 17-May-12 14:21pm

though this solution is correct but i have a problem that i have fire this query for select values SELECT * FROM TIMETABLE WHERE DEPARTMENT_CODE ='" + lbldepartment.SelectedValue + "' AND TIME = '" + "05:00-06:00" + "' AND SEMESTER = '" + lblsemester.SelectedItem + "' " and i want to select multiple values and display on label .... please replay...

Wendelius 17-May-12 14:26pm

Ok, so you're talking about querying and conditions. For this you should always use parameters. Don't concatenate literal text to an SQL statement. For additional information, see http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.aspx[^]

So your statement should look something like the following:

SELECT * FROM TIMETABLE
WHERE DEPARTMENT_CODE = @depcodeparameter
AND TIME = @sometimeparameter
AND SEMESTER = @semesterparame

Jignesh J patel 17-May-12 14:43pm

in that case how can i concate values in single label..

Wendelius 17-May-12 14:51pm

See the updated answer

Monjurul Habib 17-May-12 14:59pm

nice update but are you sure the query will return a single row?

Wendelius 17-May-12 15:07pm

No but should it? If the query returns several rows, each row will have it's own concatenated value which can be placed somewhere. For example if the result set is shown in a grid, the concatenated calue could be shown on a label based on the selection.

Actually I'm not sure since OP hasn't provided very much information :)

Monjurul Habib 17-May-12 15:12pm

yes you are right. If the label is inside grid, repeater etc then your solution will work perfectly :)

Wendelius 17-May-12 15:19pm

Hopefully the OP gives more information if the problem persists :)

Monjurul Habib 17-May-12 15:30pm

yes i also hope so, anyway your instant solution was great :)

Jignesh J patel 17-May-12 15:39pm

there is only on label control. it's not in grid view.problem is that i have develop timetable. i that for displaying i have used label. in perticular slots there will be more than one labs allocated to each slots. so i have to display more than one slots in asp:label control.

Wendelius 17-May-12 15:57pm

Thanks, and yes, you were absolutely correct, the problem was several rows...

Jignesh J patel 17-May-12 15:52pm

there is only on label control. it's not in grid view.problem is that i have develop timetable. i that for displaying i have used label. in perticular slots there will be more than one labs allocated to each slots. so i have to display more than one slots in asp:label control.

Wendelius 17-May-12 15:56pm

This can be done in SQL but I think the easiest way is to fetch all the rows from the database into a datatable and then loop through the rows in the datatable to build the complete string.

If you want to do it in SQL, one way is to use custom aggregates. See the concatenation example in Custom Aggregates in SQL Server[^]

Monjurul Habib 17-May-12 16:06pm

:thumbsup: :)

Jignesh J patel 17-May-12 16:31pm

i used like this..
"SELECT SUBJECT_CODE + ' ,' AS [text()] FROM TIMETABLE WHERE DEPARTMENT_CODE ='" + lbldepartment.SelectedValue + "' AND TIME = '" + "04:00-05:00" + "' AND SEMESTER = '" + lblsemester.SelectedItem + "' FOR XML PATH('') "
but it generates en error of Index was outside the bounds of the array.how can i soleved this.

Wendelius 17-May-12 16:42pm

The SQL statement looks fine. Are you sure that the SelectedItem is set. If you use the debugger, check if SelectedItem returns a proper value.

Jignesh J patel 17-May-12 16:47pm

ya.this works fine. when i used this in query builder it will return proper value but when put into code it will generate this error.

Jignesh J patel 18-May-12 4:15am

may i used this query with datatable ? how ?

Wendelius 18-May-12 13:20pm

Sure, you can use data table if you like. To fill the datatable see for example DbDataAdapter.Fill Method (DataTable)[^]

Jignesh J patel 18-May-12 13:42pm

this is code for fetch single value
string str0 = "SELECT * FROM TIMETABLE WHERE DEPARTMENT_CODE ='" + lbldepartment.SelectedValue + "' AND TIME = '" + "02:00-03:00" + "' AND SEMESTER = '" + lblsemester.SelectedItem + "'";
SqlDataAdapter ada = new SqlDataAdapter(str0, con);
DataTable dt = new DataTable();
ada.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i].ItemArray[5].Equals("Monday"))
{
Response.Write(dt.Rows[i].ItemArray[5].Equals("Monday"));
Label35.Text = dt.Rows[i].ItemArray[1].ToString();

Jignesh J patel 18-May-12 13:43pm

but for fetching multiple data it's not working.

Jignesh J patel 20-May-12 14:11pm

sir, can you help me for above problem please..

Wendelius 20-May-12 14:23pm

If I'm interpreting the code correctly, you loop through all the rows, that's correct and what you want?

So if yo uwant to add data from all of the rows to the label you can use the concatenation of C# which is + For example

string concatenated = "A" + "B" + "C"

So loop through the rows, concatenate the desired data and then place the result on the label.

Jignesh J patel 22-May-12 14:42pm

thanx for ur support i got my answer....

Wendelius 22-May-12 14:45pm

You're welcome.

Monjurul Habib · Answer 2 · 2012-05-17T08:03:00

Solution 2

Mika already provided you the solution. You can also try another solution if you are looking for concatenates two specified instances of String.

Try String.Concat Method (String, String)

For better detail please navigate the following link:
How to: Concatenate Multiple Strings (C# Programming Guide)

Please let us know if you have further requirement.

Posted 17-May-12 8:03am

Monjurul Habib

Updated 17-May-12 8:04am

v2

Comments

Wendelius 17-May-12 14:10pm

Yes, that would also be feasible!

Monjurul Habib 17-May-12 14:13pm

thank you :)

Jignesh J patel 17-May-12 14:26pm

though this solution is correct but i have a problem that i have fire this query for select values SELECT * FROM TIMETABLE WHERE DEPARTMENT_CODE ='" + lbldepartment.SelectedValue + "' AND TIME = '" + "05:00-06:00" + "' AND SEMESTER = '" + lblsemester.SelectedItem + "' " and i want to select multiple values and display on label .... please replay...

Monjurul Habib 17-May-12 14:49pm

what did you try? share you detail code. we need your code sample to guide you properly. I guess you are getting a list using the SELECT query. If i am correct then let me know.

how concat multiple values in sql

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

how concat multiple values in sql

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us