ASP.NET

column value from multiple rows as one value

26-Aug-08 23:56

Check this article out it might be quite useful

http://msdn.microsoft.com/en-us/library/ms972974.aspx

vjvjvjvj26-Aug-08 23:40

26-Aug-08 23:40

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

Tripathi Swati26-Aug-08 23:59

26-Aug-08 23:59

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

vjvjvjvj27-Aug-08 0:44

27-Aug-08 0:44

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

Tripathi Swati27-Aug-08 1:12

27-Aug-08 1:12

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

vjvjvjvj27-Aug-08 1:28

27-Aug-08 1:28

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 +' ' 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

Tripathi Swati27-Aug-08 2:33

27-Aug-08 2:33

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

vjvjvjvj27-Aug-08 3:00

27-Aug-08 3:00

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

Tripathi Swati27-Aug-08 19:30

27-Aug-08 19:30

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

vjvjvjvj27-Aug-08 22:51

Authorization roles & IIS

27-Aug-08 22:51

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(" Leased "))
origStreet = origStreet.Substring(origStreet.IndexOf(" Street ") + " Street ".Length())

origTown = rw(1)
origTown = origTown.Remove(origTown.IndexOf(" Street "))
origTown = origTown.Substring(origTown.IndexOf(" Townland ") + " Townland ".Length())

For Each rw1 As DataRow In rowcoll1
Dim str As String = rw1(1)
Dim st As String = str

str = str.Remove(str.IndexOf(" Leased "))
str = str.Substring(str.IndexOf(" Street ") + " Street ".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(" Leased "), " " + str)
origStreet = origStreet + " " + str
End If

End If
End If

str = st

str = str.Remove(str.IndexOf(" Street "))
str = str.Substring(str.IndexOf(" Townland ") + " Townland ".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(" Street "), " " + str)
origTown = origTown + " " + 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()

laserbaronen26-Aug-08 23:39

laserbaronen

26-Aug-08 23:39

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

Re: Authorization roles & IIS

eyeseetee26-Aug-08 23:58

Re: Authorization roles & IIS

26-Aug-08 23:58

Check you have only one version of the web.config file and that it is in the root directory

laserbaronen27-Aug-08 0:48

laserbaronen

27-Aug-08 0:48

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

sending Email in C#

kvsreedhar26-Aug-08 23:33

26-Aug-08 23:33

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]

Thanking in advance Rose | [Rose]

Kovuru Sreedhar

N a v a n e e t h26-Aug-08 23:36

N a v a n e e t h

26-Aug-08 23:36

Have you checked in the spam/bulk ?

All C# applications should call Application.Quit(); in the beginning to avoid any .NET problems.- Unclyclopedia

How to use google | Ask smart questions

kvsreedhar26-Aug-08 23:52

26-Aug-08 23:52

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

eyeseetee26-Aug-08 23:55

26-Aug-08 23:55

kvsreedhar wrote:
All C# applications should call Application.Quit();

I dont think he like c#

kvsreedhar27-Aug-08 0:08

27-Aug-08 0:08

literally I don't understand ur words .
if u have ans to Application.Quit()
pl. tell me.

Kovuru Sreedhar

eyeseetee27-Aug-08 0:11

27-Aug-08 0:11

Its a joke.
Calling Application.Quit() means the app will never run.

kvsreedhar27-Aug-08 0:20

27-Aug-08 0:20

really I laughed on My Ignorance of the subject(App.Quit()). Laugh | :laugh:

Any how please help me with email .

Kovuru Sreedhar

eyeseetee27-Aug-08 1:29

27-Aug-08 1:29

I suggest you create a simple page and just use the code from this site to test.
Try using different e-mail address as well with this code

http://www.aspheute.com/english/20000918.asp

Manas Bhardwaj26-Aug-08 23:59

Manas Bhardwaj

26-Aug-08 23:59

kvsreedhar wrote:
bye the bye please tell me what does this mean

By the way... Poke tongue | ;-P

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. Big Grin | :-D

Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

kvsreedhar27-Aug-08 0:12