Retriving data with same ID into single row

Hi,
I have a problem with retriving data from one table.
Table looks like this:
ID CONTACT EMPLOYEE_ID
And now, one employee can how more than one contact, like this
ID CONTACT EMPLOYEE_ID
15 e-mail 553
16 phone 553
..and so on.
How can I retrive this contacts, for same employee into single row, and divi
ded with 'coma'?
Now a can retrive them but I recieve multiple rows?
Thanks for helpHi
Do such reports on the client side.
It you want to do that by T-SQL be aware that it is not relyable solution
create table w
(
id int,
t varchar(50)
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+coalesce(t,'')+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"s3v3n" <s3v3n.1yuclt@.mail.codecomments.com> wrote in message
news:s3v3n.1yuclt@.mail.codecomments.com...
> Hi,
> I have a problem with retriving data from one table.
> Table looks like this:
> ID CONTACT EMPLOYEE_ID
> And now, one employee can how more than one contact, like this
> ID CONTACT EMPLOYEE_ID
> 15 e-mail 553
> 16 phone 553
> ..and so on.
> How can I retrive this contacts, for same employee into single row, and
> divided with 'coma'?
> Now a can retrive them but I recieve multiple rows?
> Thanks for help
>
> --
> s3v3n
> ---
> Posted via http://www.codecomments.com
> ---
>|||Check this good article with source code "Returning a Comma-Delimited List o
f
Related Records"
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"s3v3n" wrote:
> Hi,
> I have a problem with retriving data from one table.
> Table looks like this:
> ID CONTACT EMPLOYEE_ID
> And now, one employee can how more than one contact, like this
> ID CONTACT EMPLOYEE_ID
> 15 e-mail 553
> 16 phone 553
> ...and so on.
> How can I retrive this contacts, for same employee into single row, and
> divided with 'coma'?
> Now a can retrive them but I recieve multiple rows?
> Thanks for help
>
> --
> s3v3n
> ---
> Posted via http://www.codecomments.com
> ---
>|||[Based on the 4guysrolla article .. ]
Create table empTest
(
[ID] int identity,
Contact varchar(100),
Employee_Id int
)
Go
Insert into empTest values ( 'abc@.email.com', 554)
Insert into empTest values ( '090909090', 554)
Go
Create function dbo.udf_GetEmpDetails(@.EmpID int)
Returns Varchar(1000) as
BEGIN
DECLARE @.Contact varchar(1000)
SELECT @.Contact = COALESCE(@.Contact + ', ', '') + s.Contact
FROM empTest s
WHERE s.EMPLOYEE_ID = @.EmpID
RETURN @.Contact
END
Go
Select distinct Employee_ID,
dbo.udf_GetEmpDetails(EMPLOYEE_ID) as ListOfContacts
From empTest f
Go
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"s3v3n" wrote:

> Hi,
> I have a problem with retriving data from one table.
> Table looks like this:
> ID CONTACT EMPLOYEE_ID
> And now, one employee can how more than one contact, like this
> ID CONTACT EMPLOYEE_ID
> 15 e-mail 553
> 16 phone 553
> ...and so on.
> How can I retrive this contacts, for same employee into single row, and
> divided with 'coma'?
> Now a can retrive them but I recieve multiple rows?
> Thanks for help
>
> --
> s3v3n
> ---
> Posted via http://www.codecomments.com
> ---
>

Retrieving multiple rows from data base.

OK here's my question. I want to retrieve from my database employee table all those employees with the name eg. Smith and display them in a list. Can anyone give me any pointers please. I'm using VB 2005 Express Edition. So far this is what I have but it only seems to return 1 row when I know there are more than one entries with the name I am inputting

PrivateSub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim searchString AsString

searchString = Me.SearchStringBox.Text

Try

Dim filter AsString

filter = "LastName LIKE '" & searchString & "'"

Dim search() As System.Data.DataRow

search = myCDDataSEt.ClientData.Select(filter)

If search.Length > 0 Then

'no code as yet

Else

MessageBox.Show("The client " & searchString & "is not in the database")

EndIf

Catch ex As Exception

MessageBox.Show(ex.Message)

EndTry

EndSub

All of the SQL that I see in this is LastName LIKE... If you want help with the SQL, you need to print it out and let us look at it. Otherwise, we need to move the thread over to a VB programming group to look at.|||Sorry thought I was in another forum. !

Retrieving an image from SQL, test for null

I have an employee directory application that displays employees in a gridview. When a record is selected, a new page opens and displays all info about the employee, including their photo. I have the code working that displays the photos, however, when no photo is present an exception is thrown that "Unable to cast object of type System.DbNull to System.Byte[]". I'm not sure how to test for no photo before trying to write it out.

My code is as follows (with no error trapping):

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load,Me.Load

Dim tempAsString

Dim connPhotoAs System.Data.SqlClient.SqlConnection

Dim connstringAsString

connstring = Web.Configuration.WebConfigurationManager.ConnectionStrings("connPhoto").ConnectionString

connPhoto =New System.Data.SqlClient.SqlConnection(connstring)

temp = Request.QueryString("id")

Dim SqlSelectCommand2As System.Data.SqlClient.SqlCommand

Dim sqlstringAsString

sqlstring ="Select * from dbo.PhotoDir WHERE (CMS_ID = " + temp +")"

SqlSelectCommand2 =New System.Data.SqlClient.SqlCommand(sqlstring, connPhoto)

Try

connPhoto.Open()

Dim myDataReaderAs System.Data.SqlClient.SqlDataReader

myDataReader = SqlSelectCommand2.ExecuteReader

DoWhile (myDataReader.Read())

Response.BinaryWrite(myDataReader.Item("ImportedPhoto"))

Loop

connPhoto.Close()

Catch SQLexecAs System.Data.SqlClient.SqlException

Response.Write("Read Failed : " & SQLexec.ToString())

EndTry

EndSub

EndClass

If you could point me in the right direction I would appreciate it.

lwhalen618:

when no photo is present an exception isthrown that "Unable to cast object of type System.DbNull toSystem.Byte[]

lwhalen618:

DoWhile (myDataReader.Read())

Response.BinaryWrite(myDataReader.Item("ImportedPhoto"))

Loop

did you try to check for nulls ??

DoWhile (myDataReader.Read())
if Not IsDBNull(myDataReader.Item("ImportedPhoto")) then
Response.BinaryWrite(myDataReader.Item("ImportedPhoto"))
End if
Loop

hope it works... pls let me know

Good Luck./.

|||

I did try testing for null but was doing it incorrectly. Your code worked fine. Thanks!