Record set filter in asp 1

[mtrasp]

Can anyone please tell me how can i filter record set.I have dropdownlist .
I am selecting releasenumber,productgroupid,osgroupid and Componentid .
but Componentid is by default 0.it is displaying builds by executing below stored procedure .
my problme is it is displaying lot of unwanted builds. i need to filter it.
the stored procedure sp_selectbuilds is getting matched data from one table.
rightnow it is getting data with componentid =0. but i want to get data with COmponentID not equal To 0
How can i filter to get data with the componentID NOT equal to 0. where can i filter in asp page or stored procedure.


I have code in ASP page like below
Driverpage.asp:

Set rs = objDriver.GetDriverList(intComponentID, intReleaseNumberID, intProductGroupID, intOSGroupID)
If Not(rs.BOF Or rs.EOF) Then
   'show all driver builds
else
    'Display Not availablle
end if

From the Database below stored procedure is executing

Create procedure sp_GetDriverList
(
 @intComponentID int,
@intReleaseNumberID int,
@intProductGroupID int,
@intOSGroupID


)

AS


EXEC  sp_selectbuilds intComponentID,intReleaseNumberID,intProductGroupID,intOSGroupID

 

[foxbox]

is it possible to post the source from "sp_selectbuilds"?

 

[mtrasp]

Thank you for considering my problem.
below is the source from "sp_selectbuilds"

CREATE PROCEDURE dbo.sp_selectbuilds 
(
    @ComponentID int,
    @ReleaseNameID int, 
    @ProductGroupID int,
    @OSGroupID int
)
AS


DECLARE @DriverRequest TABLE (
    Row int identity(1,1),
    DriverRequestID int,
    DateRequested datetime,
    RequestorIDSID varchar(8),
    Notes varchar(400),
    RequestID int,
    BuildVersion varchar(50),
    UpdateTime datetime,
    VersionNumber varchar(15),
    FailureID int,
    Failed bit,
    BuildOutputComponentID int,

)

INSERT INTO @DriverRequest
SELECT  DISTINCT
dq.DriverRequestID, 
dq.DateRequested, 
dq.RequestorIDSID, 
dq.Notes, 
NULL as RequestID, 
NULL AS BuildVersion,
NULL AS UpdateTime,
NULL as VersionNumber, 
0 AS FailureID,
NULL AS Failed,
dq.BuildOutputComponentID
FROM Release.dbo.t_DriverRequest AS dq 

WHERE dq.ComponentID = @ComponentID
    AND dq.ReleaseNameID = @ReleaseNameID
    AND dq.ProductGroupID = @ProductGroupID
    AND dq.OSGroupID = @OSGroupID



SELECT *
FROM @DriverRequest
ORDER BY BuildVersion

 

[foxbox]

1st: why create a table variable, do an insert and then a select?
2nd: Why order on BuildVersion while that is filled NULL for all records?

"the stored procedure sp_selectbuilds is getting matched data from one table.
rightnow it is getting data with componentid =0. but i want to get data with COmponentID not equal To 0 "

sounds like adding 1 additional line in the WHERE:

CREATE PROCEDURE dbo.sp_selectbuilds
(
    @ComponentID int,
    @ReleaseNameID int,
    @ProductGroupID int,
    @OSGroupID int
)
AS


SELECT  DISTINCT
dq.DriverRequestID,
dq.DateRequested,
dq.RequestorIDSID,
dq.Notes,
dq.BuildOutputComponentID

FROM Release.dbo.t_DriverRequest AS dq

WHERE dq.ComponentID = @ComponentID
    AND dq.ReleaseNameID = @ReleaseNameID
    AND dq.ProductGroupID = @ProductGroupID
    AND dq.OSGroupID = @OSGroupID
    AND dq.ComponentID <> 0

 

[mtrasp]

Hi Thank you for your reply
as you said I am applying dq.ComponentID <> 0 to the where condition But I am not getting any records.

CREATE PROCEDURE dbo.sp_selectbuilds
(
    @ComponentID int,
    @ReleaseNameID int,
    @ProductGroupID int,
    @OSGroupID int
)
AS


SELECT  DISTINCT
dq.DriverRequestID,
dq.DateRequested,
dq.RequestorIDSID,
dq.Notes,
dq.BuildOutputComponentID

FROM Release.dbo.t_DriverRequest AS dq

WHERE dq.ComponentID = @ComponentID --- 0 is coming
    AND dq.ReleaseNameID = @ReleaseNameID -- 3252 is coming
    AND dq.ProductGroupID = @ProductGroupID --56 is coming
    AND dq.OSGroupID = @OSGroupID -- 18 is scoming
    AND dq.ComponentID <> 0  -- we are filtering again with 0

otherwise can i use if condition in below code For example IF ComponentID = 0 then COmponentid not equal to 0

Create procedure sp_GetDriverList
(
 @intComponentID int,
@intReleaseNumberID int,
@intProductGroupID int,
@intOSGroupID


)

AS


EXEC  sp_selectbuilds intComponentID,intReleaseNumberID,intProductGroupID,intOSGroupID

 

[foxbox]

in case you use "0" for the parameter intComponentid then we ask the impossible:


WHERE dq.ComponentID = 0
AND dq.ComponentID <> 0

which of course results in nothing.
so you want
dq.ComponentID = @ComponentID when you provide a value not equal to 0 and
dq.ComponentID <> 0 if you do.

Try to find the correct SELECT by using the SQL tool for your DBMS (MSSQL?).


There may be a good reason for having 2 stored procedures for this, but it could perfectly work with that SELECT statement in sp_GetDriverList

 

[mtrasp]

Thank you very much for your valuable time .I am using sql server 2005
I am trying to do below way please suggest me If am wrong

CREATE PROCEDURE dbo.sp_selectbuilds
(
    @ComponentID int,
    @ReleaseNameID int,
    @ProductGroupID int,
    @OSGroupID int
)
AS

IF @Componentid = 0 
BEGIN

   SELECT  DISTINCT
   dq.DriverRequestID,
   dq.DateRequested,
   dq.RequestorIDSID,
   dq.Notes,
   dq.BuildOutputComponentID

    FROM Release.dbo.t_DriverRequest AS dq

    WHERE dq.ComponentID <> 0 
    AND dq.ReleaseNameID = @ReleaseNameID
    AND dq.ProductGroupID = @ProductGroupID
    AND dq.OSGroupID = @OSGroupID


END

ELSE
BEGIN
     SELECT  DISTINCT
     dq.DriverRequestID,
     dq.DateRequested,
     dq.RequestorIDSID,
     dq.Notes,
     dq.BuildOutputComponentID

     FROM Release.dbo.t_DriverRequest AS dq

     WHERE dq.ComponentID =@ComponentID 
    AND dq.ReleaseNameID = @ReleaseNameID
    AND dq.ProductGroupID = @ProductGroupID
    AND dq.OSGroupID = @OSGroupID
END

If possible please tell me how can i do same thing in sp_Getdriverlist

 

[foxbox]

basically you have a stored procedure A that calls stored procedure B that runs an (rather simple) SELECT.

Alternatives
a. Embed the complete SELECT in your source
b. Put the SELECT in a VIEW. (You could write SELECT * FROM myVIEW WHERE .... )
c. Put the SELECT in stored procedure A.