Passing parameters to a view from ASP.NET -- sql server data mining

sql server data mining : Passing parameters to a view from ASP.NET

James
12/22/2004 5:19:31 PM

Hia,

I have 2 tables, and i would like to create a view which shows all of
the names that are stored in one table, and next to each name (stored in
the first table TBL_TECHNICIANS) shows what job the person named has to
do on a specific day (stored in the second table TBL_SCHEDULE). I need
to filter the results to show only jobs for a specific day, but still
listing all of the people from the first table, whether they have a job
scheduled for that day or not.

I have managed to do this, but i can't specify the date as a parameter
for the view. Has anyone any suggestions on how i might be able to do
this?!

Any help would be hugely appreciated!!!

If i have been too vague please let me know!

Cheers,

James.

*** Sent via Developersdex http://www.developersdex.com ***

Jeff Block
12/28/2004 12:42:54 PM

Views do not accept parameters, if you MUST use a parameter, you have to
restort to a UDF or SP.

However, unless I am missing something here, this looks like a simple left
join

select tech.name, isnull(sch.jobname,'no job scheduled for day')
from tbl_technicians tech
left join tbl_schedule sch on tech.key1= sch.key1
where sch.jobdate = <somedate>

[quoted text, click to view]

James
12/28/2004 3:31:10 PM

Hia,

Thanks very much for that advice! I have tried it and it works until I
insert the WHERE line. If i use it without the WHERE line i see all the
technicians names and all of the scheduled jobs for them.

However, if i use the WHERE date = somedate line it only shows up the
technicians who have jobs scheduled. The SELECT statement is below :

SELECT TBL_TECHNICIANS.sName, ISNULL(TBL_SCHEDULE.sDescription, 'no job
scheduled for day'), TBL_SCHEDULE.dDate
FROM dbo.TBL_TECHNICIANS
LEFT JOIN dbo.TBL_SCHEDULE ON dbo.TBL_TECHNICIANS.nTechnicianPK =
dbo.TBL_SCHEDULE.nTechnicianFK
WHERE TBL_SCHEDULE.dDate = '2004-12-10 00:00:00.000'

Can you suggest anything else i could try?? I would like to be able to
show all of the technicians if that is possible, but only the jobs for a
specific day.

Thank you very much.

James.

*** Sent via Developersdex http://www.developersdex.com ***

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server data mining

View Other Months
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007

home · blog · groups

Questions? Comments? Contact the dn