|
Hi,
I need to have a column value from multiple rows as one value. My datagrid is binded to a record set and this record set column should have this value
e.g. select name from employee
returns> name
-----
david
malcom
rave
I need to have this value as
name
----
david, malcom,rave (in one row)
Please note that I am on sql server 2000
Can anyone help in generating such sql script.
My datagrid will show the result as
------------------
dept names
------------------
dept1 david, malcom,rave
-----------------------------
dept2 david1, malcom1,rave1
------------------------------
regards
Vijay
|
|
|
|
|
HI,
This forum is basically for .net
then also
1> Use Cursor for this ---
2>
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL ON
DECLARE @list VARCHAR(8000)
DECLARE @delm VARCHAR(2)
SET @delm = ','
CREATE TABLE #TableToList (value varchar(20))
INSERT #TableToList VALUES ('swati')
INSERT #TableToList VALUES ('ABC')
INSERT #TableToList VALUES ('NIDHI')
INSERT #TableToList VALUES ('XYZ')
SELECT @list = ISNULL(
@list + @delm + CAST(value AS VARCHAR(8000)),
CAST(value AS VARCHAR(8000))
)
FROM #TableToList
ORDER BY value
DROP TABLE #TableToList
SELECT list = @list
Reasons are not Important but Results are Important.
Swati Tripathi
|
|
|
|
|
Hi Swati,
Thanks.. but I still have some doubts
My data grid is dg_SearchResults
and I add a following column in it
Dim Details As New BoundColumn
Details.HeaderText = "Details"
Details.DataField = "DETAILS"
dg_SearchResults.Columns.AddAt(0, Details) (details should have those values in one row)
Now can I bind the out put of stored procedure as you have mentioned to my datagrid. i guess its not
dataset just a flat string . will it consider it as the dataset to get binded with my datagrid.
what changes do I have to make to my stored procedure?
regards
vijay
|
|
|
|
|
yes, you can bind these value with your data grid
this output can be used in stored proc just merge/add this variable(@list) with your select statement.
i am really not getting where are you not getting????
you can use cursor also for this output ...
Reasons are not Important but Results are Important.
Swati Tripathi
|
|
|
|
|
Hi Swati,
My query is in my asp.net code is as (which is not a stored procedure i am implementing it in my vb code.
select assets.asset_pk as UNIQUEID,
assets.asset_ID ,
assets.[name] collate database_default ,
asset_types.ASSET_TYPE_NAME ,
(CASE when IsNull(townland.townland, '') = '' THEN '' eLSE townland.townland END) ,
(CASE when IsNull(thoroughfares_fme1.thorfare_name, '') = '' THEN '' eLSE thoroughfares_fme1.thorfare_name END) ,
(CASE assets.LEASED WHEN 1 THEN 'YES' eLSE 'NO' END) ,
(CASE assets.DISPOSED WHEN 1 THEN 'YES' eLSE 'NO' END) ,
(CASE assets.ASSET_REGISTERED WHEN 1 THEN 'YES' eLSE 'NO' END) ,
assets.ASSET_FOLIO_NUMBER ,
assets.FLAGGED_NOTE collate database_default +'<br>' AS DETAILS
from assets
INNER JOIN asset_types on assets.type_id = asset_types.asset_type_id
left join asset_streets on assets.asset_pk =asset_streets.asset_ref_key
left join thoroughfares_fme1 on asset_streets.THORFARE_REF_KEY = thoroughfares_fme1.primaryindex
left join asset_townlands on assets.asset_pk=asset_townlands.asset_ref_key
left join TOWNLAND on asset_townlands.TOWNLAND_KEY = TOWNLAND.primaryindex
where
assets.asset_ID like '%L%' or
assets.[name] collate database_default like '%L%' or
asset_types.ASSET_TYPE_NAME like '%L%' or
townland.townland like '%L%' or
thoroughfares_fme1.thorfare_name like '%L%' or
assets.leased_to like '%L%' or
assets.LEASED_CUSTOMER_ID like '%L%' or
assets.LEASED_OCCUPIER like '%L%' or
assets.DISPOSED_TO_NAME like '%L%' or
assets.ASSET_FOLIO_NUMBER like '%L%' or
assets.FLAGGED_NOTE collate database_default like '%L%'
order by assets.asset_ID
here because of thoroughfares_fme1.thorfare_name field I am getting rows for the asset_id
e.g
62 BLD10001 ffa-234 LA BUILDING RATHQUARTER HOLBORN STREET
62 BLD10001 ffa-234 LA BUILDING RATHQUARTER CONNAUGHTON ROAD
I want this records as one record
e.g.
62 BLD10001 ffa-234 LA BUILDING RATHQUARTER HOLBORN STREET, CONNAUGHTON ROAD
Now my datagrid is binded to thi record set.
When I tried to have your list concept in my code I get the error as it can be implemented in other data retrieval fields.
Now please tell me how I can proceed from here?
regards
Vijay
|
|
|
|
|
sorry i thought you are doing stored procedure.
hey over here you have to do programming and use dynamic column for that.
where you need to check prev and current asset id and if it is same then add column or add string to ur current string.
Hope this will help you.
Reasons are not Important but Results are Important.
Swati Tripathi
|
|
|
|
|
Hi Swati,
even with stored procedure how i can proceed with as there are some other fields also, can it return
list as well as other fields also? and how can i then bind it with such a flat data as its not a dataset, My datagrid has columns which are linked with other fields from the existing querry? with the stored procedure how i can have this linkage?
regards
Vijay
|
|
|
|
|
please look at below
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL ON
DECLARE @list VARCHAR(8000)
DECLARE @delm VARCHAR(2)
SET @delm = ','
CREATE TABLE #TableToList (
Columns.....
)
Insert into #TableToList
select assets.asset_pk as UNIQUEID,
assets.asset_ID ,
assets.[name] collate database_default ,
asset_types.ASSET_TYPE_NAME ,
(CASE when IsNull(townland.townland, '') = '' THEN '' eLSE townland.townland END) ,
(CASE when IsNull(thoroughfares_fme1.thorfare_name, '') = '' THEN '' eLSE thoroughfares_fme1.thorfare_name END) ,
(CASE assets.LEASED WHEN 1 THEN 'YES' eLSE 'NO' END) ,
(CASE assets.DISPOSED WHEN 1 THEN 'YES' eLSE 'NO' END) ,
(CASE assets.ASSET_REGISTERED WHEN 1 THEN 'YES' eLSE 'NO' END) ,
assets.ASSET_FOLIO_NUMBER ,
assets.FLAGGED_NOTE collate database_default +'
' AS DETAILS
from assets
INNER JOIN asset_types on assets.type_id = asset_types.asset_type_id
left join asset_streets on assets.asset_pk =asset_streets.asset_ref_key
left join thoroughfares_fme1 on asset_streets.THORFARE_REF_KEY = thoroughfares_fme1.primaryindex
left join asset_townlands on assets.asset_pk=asset_townlands.asset_ref_key
left join TOWNLAND on asset_townlands.TOWNLAND_KEY = TOWNLAND.primaryindex
where
assets.asset_ID like '%L%' or
assets.[name] collate database_default like '%L%' or
asset_types.ASSET_TYPE_NAME like '%L%' or
townland.townland like '%L%' or
thoroughfares_fme1.thorfare_name like '%L%' or
assets.leased_to like '%L%' or
assets.LEASED_CUSTOMER_ID like '%L%' or
assets.LEASED_OCCUPIER like '%L%' or
assets.DISPOSED_TO_NAME like '%L%' or
assets.ASSET_FOLIO_NUMBER like '%L%' or
assets.FLAGGED_NOTE collate database_default like '%L%'
order by assets.asset_ID
SELECT UNIQUEID ,asset_ID,ASSET_TYPE_NAME, @list = ISNULL(
@list + @delm + CAST(value AS VARCHAR(8000)),
CAST(value AS VARCHAR(8000))
)
FROM #TableToList
ORDER BY value
Reasons are not Important but Results are Important.
Swati Tripathi
|
|
|
|
|
Thanks Swati for your time.
Yesterday i tried following which worked. after getting the dataset from my original sql script, I tried to check in the returned dataset and manupulated it to have the result I wanted for thorfare names and townlands
here it is..
Dim rowcoll As DataRow() = dsTemp.Tables(0).Select()
If rowcoll.Length > 0 Then
For Each rw As DataRow In rowcoll
If rw.RowState = DataRowState.Unchanged Then
Dim rowcoll1 As DataRow() = dsTemp.Tables(0).Select("UNIQUEID=" + rw(0).ToString() + " and DETAILS <> '" + rw(1) + "'")
If rowcoll1.Length > 0 Then
Dim str1 As String = rw(1)
Dim origStreet, origTown As String
origStreet = rw(1)
origStreet = origStreet.Remove(origStreet.IndexOf("<BR><b>Leased</b><BR>"))
origStreet = origStreet.Substring(origStreet.IndexOf("<BR><b>Street</b><BR>") + "<BR><b>Street</b><BR>".Length())
origTown = rw(1)
origTown = origTown.Remove(origTown.IndexOf("<BR><b>Street</b><BR>"))
origTown = origTown.Substring(origTown.IndexOf("<BR><b>Townland</b><BR>") + "<BR><b>Townland</b><BR>".Length())
For Each rw1 As DataRow In rowcoll1
Dim str As String = rw1(1)
Dim st As String = str
str = str.Remove(str.IndexOf("<BR><b>Leased</b><BR>"))
str = str.Substring(str.IndexOf("<BR><b>Street</b><BR>") + "<BR><b>Street</b><BR>".Length())
If String.IsNullOrEmpty(str) = False Then
If str.Length > 0 Then
If IsNothing(origStreet) = True Or (origStreet.Contains(str) = False) Then
str1 = str1.Insert(str1.IndexOf("<BR><b>Leased</b><BR>"), "<br>" + str)
origStreet = origStreet + "<br>" + str
End If
End If
End If
str = st
str = str.Remove(str.IndexOf("<BR><b>Street</b><BR>"))
str = str.Substring(str.IndexOf("<BR><b>Townland</b><BR>") + "<BR><b>Townland</b><BR>".Length())
If String.IsNullOrEmpty(str) = False Then
If str.Length > 0 Then
If IsNothing(origTown) = True Or (origTown.Contains(str) = False) Then
str1 = str1.Insert(str1.IndexOf("<BR><b>Street</b><BR>"), "<br>" + str)
origTown = origTown + "<br>" + str
End If
End If
End If
dsTemp.Tables(0).Rows.Remove(rw1)
Next
rw(1) = str1
rw.AcceptChanges()
End If
End If
Next
End If
dsTemp.AcceptChanges()
|
|
|
|
|
I have created a virtual directory in IIS for my application. The problem is that it completely disregards the authorization settings in the web.config:
<location path="Admin">
<system.web>
<authorization>
<allow users="domain\Administrator" />
<deny users="*" />
</authorization>
</system.web>
</location>
If i run the application as a web site, it works perfectly.. Any ideas of why this can be? As far as I can see, the settings are the same
betonglasermur.FeedDwarf(pur_is, 17);
ProcessStartupInfo.AintNotCreateNoWindow = (false && !true) != (true || false) ? false == true ? true : false : (true != false && false);
Morgonen är tröttmans mecka
|
|
|
|
|
Check you have only one version of the web.config file and that it is in the root directory
|
|
|
|
|
There is only one, and it is in the root directory
betonglasermur.FeedDwarf(pur_is, 17);
ProcessStartupInfo.AintNotCreateNoWindow = (false && !true) != (true || false) ? false == true ? true : false : (true != false && false);
Morgonen är tröttmans mecka
|
|
|
|
|
anyone over there please help
I was trying sending Email in C#
the code goes this way...
string smtp = "localhost";
System.Net.Mail.SmtpClient smtpClient = new System.Net.Mail.SmtpClient(smtp);
System.Net.Mail.MailMessage objEmail = new System.Net.Mail.MailMessage();
objEmail.To.Add(txtEmail.Text); //to@domain.com
objEmail.From = new System.Net.Mail.MailAddress(txtFrom.Text, "kv"); //from@domain.com
objEmail.Subject = "Test Email For DOT NET";
objEmail.Body = txtMessage.Text; //some body
objEmail.Priority = System.Net.Mail.MailPriority.High;
//SmtpMail.SmtpServer = "localhost";
try
{
smtpClient.Send(objEmail);
Response.Write("Your Email has been sent sucessfully -Thank You");
}
catch (Exception exc)
{
Response.Write("Send failure: " + exc.ToString());
}
----but my doubt is When the code is run I am getting a message
"Your Email has been sent sucessfully -Thank You"
but when checked the mail I don't find the mail sent in inbox. I am using localhost as SMTP server .
May please help me in this .
Also I have configured the SMTP server to 127.0.0.1 (AdminTools->IIS->Default SMTP Virtual Server->properties->access->relay->127.0.0.1). I found the SMTP working in my system.But the mail can't be found in the Inbox of the recepient(it's my own mail)
![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif) Thanking in advance
Kovuru Sreedhar
|
|
|
|
|
Have you checked in the spam/bulk ?
|
|
|
|
|
yes I did checked in spam/bulk also.
bye the bye please tell me what does this mean and why
"All C# applications should call Application.Quit(); in the beginning to avoid any .NET problems"
in the beginning means where to write the code ,is it load event?
Kovuru Sreedhar
|
|
|
|
|
kvsreedhar wrote: All C# applications should call Application.Quit();
I dont think he like c#
|
|
|
|
|
literally I don't understand ur words .
if u have ans to Application.Quit()
pl. tell me.
Kovuru Sreedhar
|
|
|
|
|
Its a joke.
Calling Application.Quit() means the app will never run.
|
|
|
|
|
 really I laughed on My Ignorance of the subject(App.Quit()).
Any how please help me with email .
Kovuru Sreedhar
|
|
|
|
|
|
kvsreedhar wrote: bye the bye please tell me what does this mean
By the way... 
kvsreedhar wrote: All C# applications should call Application.Quit(); in the beginning to avoid any .NET problems
I really do not know what adjective to use for you. In between, that was a joke. 
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
I would be more happy with your answer for Application.quit() 
Kovuru Sreedhar
|
|
|
|
|
Looks like I have to put a joke smiley next to my sig
|
|
|
|
|
N a v a n e e t h wrote: Looks like I have to put a joke smiley next to my sig
Well, actually not. You rarely get to see these type of icons
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
you are Too Smart MB
Kovuru Sreedhar
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
