Click here to Skip to main content
15,359,445 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
Database
dbItem:
ID     Description     Price
1       Apple           4
2       Banana          6
3       Papaya          7


Query:
SELECT * FROM dbItem WHERE price >= 10

any possible that sql query can return an empty row for us if no record found? (like example below)

Example:
ID         Description      Price
0          No record          0

The data above is going to inside into the ASP gridview.
yes, gridview have a control(EmptyDataTemplate) to display if no data inside the gridview. but i still want the grid to fill in with empty row.

i tried to create an DataTable, but once i set the datasource=dataTable, error show: "Both DataSource and DataSourceID are defined on..."
GridView1.DataSourceID = null 

^ doesn't help
Posted
Updated 20-Dec-21 2:23am
v2

The empty row situation should be taken care of at the code behind not at the database, not only is it separation of concern, but more importantly it is generic, e.g. what if the condition changes say >= any number, you can't change the sql query every time, can you?
Suggest to check for return row from sql query, if zero, create a datatable dynamically with the columns identical to the expected return columns of the sql query ,i.e. id, description, price, then add a row with the data that you decide, i.e. 0, 'no record', 0. Then bind it to the gridview.
See example:
1. show-header-and-footer-rows-in-empty-row[^]
2. how+to+add+row+to+data+table[^]
This solution will take care of any zero row situation and is reusable.
   
v3
Comments
melvintcs 24-May-14 11:35am
   
hi there, i did tried the datatable's method. As stated at the question above, im facing an error. i already assign the sqldatasource to the gridview at HTML, then code behind i assign the DataTable to the gridview again. any solution for this?
Peter Leow 24-May-14 11:42am
   
Have you read the links that I provided?
try SQL like below
SQL
IF EXISTS ( SELECT * FROM dbItem WHERE price >= 10)
BEGIN
    SELECT * FROM dbItem WHERE price >= 10
END
ELSE
  SELECT 0 as id, 'No record' as Description, 0 as price

DEMO[^]

MYSQL:
SQL
IF (SELECT * FROM dbItem WHERE price >= 10) THEN
BEGIN
    SELECT * FROM dbItem WHERE price >= 10;
END;
ELSE
BEGIN
    SELECT 0 as id, 'No record' as Description, 0 as price
END;
END IF;
   
v2
Comments
melvintcs 24-May-14 11:17am
   
im using mysql querybrowser and mysql workbench, the query cannot start with "IF"
melvintcs 24-May-14 11:24am
   
i rewrite into mySQl, still no luck
IF(SELECT * FROM dbItem WHERE price >= 10) then
BEGIN
SELECT * FROM dbItem WHERE price >= 10
END;
ELSE
BEGIN
SELECT 0 as id, 'No record' as Description, 0 as price
END
END IF
DamithSL 24-May-14 11:31am
   
any exception? you get correct result in when you run on workbench?
melvintcs 24-May-14 11:39am
   
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF(SELECT * FROM dbItem WHERE price >= 10) then BEGIN SELECT * FROM dbItem' at line 1
DamithSL 24-May-14 11:44am
   
check my updated sql statement
I found a easy solution ..
SELECT * FROM dbItem WHERE price >= 10
UNION
SELECT 0 as id, 'No record' as Description, 0 as price

but the bad thing is the 'empty record'is still show if record found
   
v2
select t.* from (select 1 as adummy) a left join (SELECT * FROM dbItem WHERE price >= 10)) t on 1=1
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900