I need some help with my SELECT statement bellow and comma separated values.
"SELECT * FROM userProfilesTbl LEFT JOIN stationsTbl ON userProfilesTbl.st_id = stationsTbl.st_id LEFT JOIN watchTbl ON userProfilesTbl.wa_id = watchTbl.wa_id LEFT JOIN userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId LEFT JOIN trainingTbl ON userAssessmentTbl.tt_id = trainingTbl.tt_id LEFT JOIN setsTbl ON trainingTbl.s_id = setsTbl.s_id WHERE userProfilesTbl.st_id=@st_id AND userProfilesTbl.wa_id=@wa_id AND DATEPART(mm,t_date) = @m_date AND DATEPART(yyyy,t_date) = @y_date ORDER BY surname ASC"
The field in the database that i need to do the comma seperated value on is userAssessmentTbl.o_id
select STUFF(
(SELECT ',' + userAssessmentTbl.o_id
FROM userProfilesTbl
LEFT JOIN stationsTbl ON userProfilesTbl.st_id = stationsTbl.st_id
LEFT JOIN watchTbl ON userProfilesTbl.wa_id = watchTbl.wa_id
LEFT JOIN userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId
LEFT JOIN trainingTbl ON userAssessmentTbl.tt_id = trainingTbl.tt_id
LEFT JOIN setsTbl ON trainingTbl.s_id = setsTbl.s_id
WHERE userProfilesTbl.st_id=@st_id AND userProfilesTbl.wa_id=@wa_id
AND DATEPART(mm,t_date) = @m_date AND DATEPART(yyyy,t_date) = @y_date
ORDER BY surname ASC
for xml path('')),1,1,'')
I've had a look at it and this is what my string looks like at present.
Dim query As String = "SELECT *, STUFF ((SELECT ',' + userAssessmentTbl.o_id FROM userProfilesTbl LEFT JOIN stationsTbl ON userProfilesTbl.st_id = stationsTbl.st_id LEFT JOIN watchTbl ON userProfilesTbl.wa_id = watchTbl.wa_id LEFT JOIN userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId LEFT JOIN trainingTbl ON userAssessmentTbl.tt_id = trainingTbl.tt_id LEFT JOIN setsTbl ON trainingTbl.s_id = setsTbl.s_id WHERE userProfilesTbl.st_id=@st_id AND userProfilesTbl.wa_id=@wa_id AND DATEPART(mm,t_date) = @m_date AND DATEPART(yyyy,t_date) = @y_date ORDER BY surname ASC FOR XML PATH ('')), 1, 1, '')"
I'm getting no errors however i'm getting no output to my GridView either!
I'm getting no errors however i'm getting no output to my GridView either!
In order to resolve your issue, please try to test the SQL statement in SSMS and see whether it returns result at first. When it returns nothing, there will be no output in the Gridview either.
Best wishes,
Please mark the replies as answers if they help or unmark if not.
Feedback to us
The original SQL statement I'm using returns results. When I use the concatenate method provided it still returns no results an no errors?
Here's a breakdown of the tables being used.
PK tt_id (int)
FK s_id (int)
t_date (datetime)
EditorId (varchar)
st_id (int)
wa_id (int)
The userProfilesTbl This table stores additional user information from the ASP.NET membership, roles and profiles tables.
PK UserId (int)
StaffNo (varchar)
forename (varchar)
surname (varchar)
FK st_id (int)
FK wa_id (int)
ss_id (int)
The storeTbl This table references all of the stores in the group
PK st_id (int)
store (varchar)
storeNo (varchar)
FK d_id (int)
The shiftTbl this table references the differing shift patterns
PK wa_id (int)
Shift (varchar)
The userAssessmentTbl this table holds information relating to the user, the training which was undertaken and the outcome of that training.
PK ti_id (int)
FK UserId (UID)
FK tt_id (int)
o_id (int)
n_id (int)
The setTbl this table is the names of each of the training courses
PK s_id (int)
setName (varchar)
instruction (varchar)
link (varchar)
setNo (int)
as you can see the userAssessmentTbl.o_id field is not 'FROM' userProfilesTbl but is erived from a 'LEFT JOIN' userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId in the string!
I-Weedy
Member
67 Points
238 Posts
Join Select Statement with Comma Seperated Value field
Jan 01, 2013 04:32 PM|LINK
Hi
I need some help with my SELECT statement bellow and comma separated values.
The field in the database that i need to do the comma seperated value on is userAssessmentTbl.o_id
The question stems from a previous unanswered forum question which can be found here: http://forums.asp.net/t/1867125.aspx/1?Seperate+values+in+gridview+cell
I have since found out that this needs to be donr through the select statement.
0ramramram0
Member
305 Points
73 Posts
Re: Join Select Statement with Comma Seperated Value field
Jan 01, 2013 05:01 PM|LINK
Refer this
http://blog.sqlauthority.com/2008/06/04/sql-server-create-a-comma-delimited-list-using-select-clause-from-table-column/
sandeepmitta...
Contributor
6767 Points
1057 Posts
Re: Join Select Statement with Comma Seperated Value field
Jan 02, 2013 10:32 AM|LINK
select STUFF( (SELECT ',' + userAssessmentTbl.o_id FROM userProfilesTbl LEFT JOIN stationsTbl ON userProfilesTbl.st_id = stationsTbl.st_id LEFT JOIN watchTbl ON userProfilesTbl.wa_id = watchTbl.wa_id LEFT JOIN userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId LEFT JOIN trainingTbl ON userAssessmentTbl.tt_id = trainingTbl.tt_id LEFT JOIN setsTbl ON trainingTbl.s_id = setsTbl.s_id WHERE userProfilesTbl.st_id=@st_id AND userProfilesTbl.wa_id=@wa_id AND DATEPART(mm,t_date) = @m_date AND DATEPART(yyyy,t_date) = @y_date ORDER BY surname ASC for xml path('')),1,1,'')Sandeep Mittal | My Blog - IT Developer Zone
I-Weedy
Member
67 Points
238 Posts
Re: Join Select Statement with Comma Seperated Value field
Jan 02, 2013 11:25 AM|LINK
Hi Can you explain a little more how this works! as I'm finding it difficult to impliment into my string and get it to output the correct data?
eralper
Contributor
6048 Points
971 Posts
Re: Join Select Statement with Comma Seperated Value field
Jan 02, 2013 12:33 PM|LINK
Hi weedy,
SQL XML commands can be used to split or concatenate values in T-SQL
You can find samples and explanation for SQL split function with XML and SQL string concatenation with For XML Path
SQL Server 2012
I-Weedy
Member
67 Points
238 Posts
Re: Join Select Statement with Comma Seperated Value field
Jan 02, 2013 01:22 PM|LINK
Hi eralper
I've had a look at it and this is what my string looks like at present.
Dim query As String = "SELECT *, STUFF ((SELECT ',' + userAssessmentTbl.o_id FROM userProfilesTbl LEFT JOIN stationsTbl ON userProfilesTbl.st_id = stationsTbl.st_id LEFT JOIN watchTbl ON userProfilesTbl.wa_id = watchTbl.wa_id LEFT JOIN userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId LEFT JOIN trainingTbl ON userAssessmentTbl.tt_id = trainingTbl.tt_id LEFT JOIN setsTbl ON trainingTbl.s_id = setsTbl.s_id WHERE userProfilesTbl.st_id=@st_id AND userProfilesTbl.wa_id=@wa_id AND DATEPART(mm,t_date) = @m_date AND DATEPART(yyyy,t_date) = @y_date ORDER BY surname ASC FOR XML PATH ('')), 1, 1, '')"I'm getting no errors however i'm getting no output to my GridView either!
What am i doing wrong?
Catherine Sh...
All-Star
23373 Points
2490 Posts
Microsoft
Re: Join Select Statement with Comma Seperated Value field
Jan 04, 2013 07:58 AM|LINK
Hi,
In order to resolve your issue, please try to test the SQL statement in SSMS and see whether it returns result at first. When it returns nothing, there will be no output in the Gridview either.
Best wishes,
Feedback to us
Develop and promote your apps in Windows Store
I-Weedy
Member
67 Points
238 Posts
Re: Join Select Statement with Comma Seperated Value field
Jan 15, 2013 09:56 AM|LINK
The original SQL statement I'm using returns results. When I use the concatenate method provided it still returns no results an no errors?
Here's a breakdown of the tables being used.
The userProfilesTbl This table stores additional user information from the ASP.NET membership, roles and profiles tables.
The storeTbl This table references all of the stores in the group
The shiftTbl this table references the differing shift patterns
The userAssessmentTbl this table holds information relating to the user, the training which was undertaken and the outcome of that training.
The setTbl this table is the names of each of the training courses
as you can see the userAssessmentTbl.o_id field is not 'FROM' userProfilesTbl but is erived from a 'LEFT JOIN' userAssessmentTbl ON userProfilesTbl.UserId = userAssessmentTbl.UserId in the string!
Hope this is clear?
Do you have any idea's?