Each dropdown filter is dependent on each other including the text boxes.
For example if RaftID 000001 is selected it only occurs on certain Nodes and
it Only has certain MessageIDs. So each one of these has to be rebuilt to
reflect the selection. I would like to do this however using a filterRow
for a datatable however so I can cache the data for 15 minutes yet have it
look dynamic. Can I do this if I alter my query to return everything in one
table? If so does anyone have an example?
Filter Options:
Drop Downs:
Raft ID:
Node:
Message ID:
Server:
Service:
Type:
Sub-Service:
Text Boxes:
Search:
Date Start
Time Start
Date End
Time End
Stored Procedure For Populating Menus:
CREATE PROCEDURE [dbo].[PROC_getMenus]
@Node varchar(50)=null,
@Server varchar(50)=null,
@Service varchar(50)=null,
@SubService varchar(50)=null,
@RaftID varchar(10)=null,
@Type varchar(1)=null,
@MessageID varchar(10)=null,
@startDateTime datetime = null,
@endDateTime datetime = null
AS
DECLARE @queryString varchar(1000)
DECLARE @selectFromList varchar(1000)
DECLARE @JobIDView varchar(1000)
DECLARE @NodeView varchar(1000)
DECLARE @ServerView varchar(1000)
DECLARE @ServiceView varchar(1000)
DECLARE @SubServiceView varchar(1000)
DECLARE @TypeView varchar(1000)
DECLARE @MessIDView varchar(1000)
SET DATEFORMAT mdy
SET NOCOUNT ON
SET @queryString = ''
SET @selectFromList = ''
SET @Node = RTRIM(@Node)
SET @Server = RTRIM(@Server)
SET @Service = RTRIM(@Service)
SET @SubService = RTRIM(@SubService)
SET @Type = RTRIM(@Type)
SET @RaftID = RTRIM(@RaftID)
SET @messageID = RTRIM(@MessageID)
if (@Node is not null)and(RTRIM(@Node) <> '')
BEGIN
Set @[EMAIL PROTECTED] + ' AND NODE =' + "'" + @Node +
"'"
Set @selectFromList [EMAIL PROTECTED] + ',NODE'
END
if (@Server is not null)and(RTRIM(@Server) <>'')
BEGIN
Set @[EMAIL PROTECTED]' AND SERVER = '+ "'" + @Server
+"'"
Set @selectFromList [EMAIL PROTECTED] + ',SERVER'
END
if (@Service is not null)and(RTRIM(@Service) <>'')
BEGIN
Set @[EMAIL PROTECTED]' AND SERVICE =' +"'"+ @Service
+"'"
Set @selectFromList [EMAIL PROTECTED] + ',Service'
END
if (@SubService is not null)and(RTRIM(@SubService) <>'')
BEGIN
Set @[EMAIL PROTECTED]' AND SUBSERVICE ='+
"'"[EMAIL PROTECTED] +"'"
Set @selectFromList [EMAIL PROTECTED] + ',SubService'
END
if (@RaftID is not null)and(RTRIM(@RaftID) <>'')
BEGIN
Set @[EMAIL PROTECTED] 'AND JOB_ID ='+"'"+ @RaftID +"'"
Set @selectFromList [EMAIL PROTECTED] + ',JOB_ID'
END
if (@Type is not null)and(RTRIM(@Type) <>'')
BEGIN
Set @[EMAIL PROTECTED]' AND TYPE ='+"'"+ @Type+"'"
Set @selectFromList [EMAIL PROTECTED] + ',TYPE'
END
if (@MessageID is not null)and(RTRIM(@MessageID) <>'')
BEGIN
Set @[EMAIL PROTECTED]' AND MESSAGE_ID ='+"'"+
@MessageID +"'"
Set @selectFromList [EMAIL PROTECTED] + ',MESSAGE_ID'
END
if ((@startDateTime <> null)AND (@endDateTime <> null))
BEGIN
Set @[EMAIL PROTECTED]' AND DATETIME BETWEEN '+ ''''+
CONVERT(VARCHAR,@startDateTime) + '''' + ' AND ' + '''' +
CONVERT(VARCHAR,@endDateTime) +''''
Set @selectFromList [EMAIL PROTECTED] + ',DateTime'
END
SELECT @JobIDView = 'SELECT DISTINCT LTRIM(RTRIM(JOB_ID)) as RaftID FROM
MESSAGES WHERE 1=1 AND JOB_ID <>'''' ' + @queryString + ' Order By RaftID
ASC'
SELECT @NodeView = 'SELECT DISTINCT LTRIM(RTRIM(NODE)) as Node FROM MESSAGES
WHERE 1=1 AND NODE <>'''' ' + @queryString + ' Order By Node ASC'
SELECT @ServerView = 'SELECT DISTINCT LTRIM(RTRIM(SERVER)) as Server FROM
MESSAGES WHERE 1=1 AND SERVER <> '''' ' + @queryString + ' Order By Server
ASC'
SELECT @TypeView = 'SELECT DISTINCT LTRIM(RTRIM(TYPE)) as Type FROM MESSAGES
WHERE 1=1 AND TYPE <>'''' ' + @queryString + ' Order By Type ASC'
SELECT @ServiceView = 'SELECT DISTINCT LTRIM(RTRIM(SERVICE)) as Service FROM
MESSAGES WHERE 1=1 AND SERVICE <> '''' ' + @queryString + ' Order By
Service ASC'
SELECT @SubServiceView = 'SELECT DISTINCT LTRIM(RTRIM(SUBSERVICE)) as
SubService FROM MESSAGES WHERE 1=1 AND SUBSERVICE <> '''' ' + @queryString
+ ' Order By SubService ASC'
SELECT @MessIDView = 'SELECT DISTINCT LTRIM(RTRIM(MESSAGE_ID)) as MessageID
FROM MESSAGES WHERE 1=1 AND MESSAGE_ID <> '''' ' + @queryString + ' Order By
MessageID ASC'
EXEC (@JobIDView)
EXEC (@NodeView)
EXEC (@ServerView)
EXEC (@ServiceView)
EXEC (@SubServiceView)
EXEC (@TypeView)
EXEC (@MessIDView)
GO
Travis D. Falls |Consultant, Raft.Net IT | 860.547.4070 |
[EMAIL PROTECTED]
This communication, including attachments, is for the exclusive use of
addressee and may contain proprietary, confidential or privileged
information. If you are not the intended recipient, any use, copying,
disclosure, dissemination or distribution is strictly prohibited. If
you are not the intended recipient, please notify the sender
immediately by return email and delete this communication and destroy all
copies.
------------------------ Yahoo! Groups Sponsor --------------------~-->
Make a clean sweep of pop-up ads. Yahoo! Companion Toolbar.
Now with Pop-Up Blocker. Get it for free!
http://us.click.yahoo.com/L5YrjA/eSIIAA/yQLSAA/saFolB/TM
--------------------------------------------------------------------~->
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
