SQL query to display data in gridview

Question

I have a grid view that displays data from a SQL Server database during page load. My gridview contains these columns:

AssetType, IssuedOn, ReturnedOn

I have used a query:

SqlCommand cmd = new SqlCommand(
    "SELECT * FROM AssetRequest WHERE ReturnedOn IS NULL OR ReturnedOn ='' ORDER BY id DESC",
    conn);

which will display data from SQL Server in the gridview when ReturnedOn column doesn't have any data.

The new query has to satisfy these conditions,

It Should not displays a record when Assetype="Laptop" or "Desktop" and IssuedOn is not empty.
But if the Assetype="Laptop" or "Desktop" and IssuedOn is empty it should display the record.
If the Assetype=anything and Returnedon is not empty it should not display that record in gridview.

I'm not sure I follow, the SQL Query you posted doesn't do this: which will display sql data in gridview when "AssetType" column has data, "IssuedOn" column has data and "ReturnedOn" column doesnt have any data.. What's the filter you need to apply exactly? — devcrp
– devcrp, Commented May 28, 2020 at 5:22
I need a query which will display data when, AssetType <> "Laptop" or "Desktop" and IssuedOn = "any data" and ReturnedOn=null. — santosh
– santosh, Commented May 28, 2020 at 5:31
Just add these criteria to your sql query: SELECT * FROM AssetRequest WHERE (ReturnedOn IS NULL OR ReturnedOn ='') AND (IssuedOn IS NOT NULL) AND (AssetType NOT IN ('Laptop', 'Desktop')) ORDER BY id DESC — TWP
– TWP, Commented May 28, 2020 at 6:20
@TWP, sorry i think i haven't made myself clear enough, the query has to satisfy these conditions, 1) It Should not displays a record when Assetype="Laptop" or "Desktop" and IssuedOn is not empty. 2) But if the Assetype="Laptop" or "Desktop" and IssuedOn is empty it should display the record. 3)If the Assetype=anything and Returnedon is not empty it should not display that record. — santosh
– santosh, Commented May 28, 2020 at 10:12

Dale K · Accepted Answer · 2020-06-03 10:42:41Z

1

I hope this query resolves your issue.

SELECT *
FROM AssetRequest
WHERE (AssetType IN ('Laptop', 'Desktop') AND (IssuedOn IS NULL OR IssuedOn = '') AND (ReturnedOn IS NULL OR ReturnedOn = '')) 
OR (AssetType NOT IN ('Laptop', 'Desktop') AND (ReturnedOn IS NULL OR ReturnedOn = ''))

edited Jun 3, 2020 at 10:42

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered May 29, 2020 at 6:40

Gaurav Patel

412 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Muhammed Nigil · Accepted Answer · 2020-05-28 12:11:43Z

0

I hope this would help

select * 
from AssetRequest 
where (ReturnedOn is null or ReturnedOn ='' )
and Assetype not in ('Laptop','Desktop')
union
select * 
from AssetRequest 
where Assetype in ('Laptop','Desktop') and IssuedOn is null or IssuedOn= ''

edited May 28, 2020 at 12:11

answered May 28, 2020 at 11:20

Muhammed Nigil

1831 silver badge10 bronze badges

8 Comments

santosh Over a year ago

Thanks. I tried the code. But there is no data displayed in gridview, it returns empty

TWP Over a year ago

Does it display the records you wish when using this query in SSMS? Just to make sure the query is returning correct results

TWP Over a year ago

Put brackets around the OR-conditions in both union parts: (ReturnedOn is null or ReturnedOn ='')

santosh Over a year ago

Now i am getting the error "The data type text cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable"

Muhammed Nigil Over a year ago

@santosh you are using TEXT datatype, instead use nvarchar(max)

|

TWP · Accepted Answer · 2020-05-28 13:03:06Z

0

According to your conditions above, this should meet your requirements:

select * 
from AssetRequest 
where 
  ((IssuedOn IS NULL OR IssuedOn = '') AND (Assetype IN ('Laptop', 'Desktop')) OR
  (ReturnedOn IS NULL OR ReturnedOn = '')

answered May 28, 2020 at 13:03

TWP

2701 gold badge6 silver badges13 bronze badges

1 Comment

santosh Over a year ago

Thanks. But it doesn't hide the record when Assettype and Issuedon column has data.

Collectives™ on Stack Overflow

SQL query to display data in gridview

3 Answers 3

Comments

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related