CODEWITHSUNDEEP
c#asp.netsql-server
Fahmieyz
Fahmieyz

Reputation: 255

Find the highest number in SQL database, if unavailable return '0'

Currently I am working on producing a way for ASP.NET C# web app to retrieve the highest number in a column in SQL database. Right now, I manage to produce the following line of code.

commTagNo = new SqlCommand("SELECT MAX(ComponentTagEntry) FROM dbo.HullDataSheet", connHull);

connHull.Open();
int newTagNo = (int)commTagNo.ExecuteScalar();
connHull.Close();

newTagNo = newTagNo + 1;

where connHull is the SqlConnection for above line of codes.

The above code can retrieve the highest number in column ComponentTagEntry if and only if the database already have a minimum one row of data.

If the database is empty, it will return 'Specified cast is invalid' since there are no data to do .ExecuteScalar().

What I need is, when the database is empty, for the code to retrieve the highest number as '0'.

I know that I have to modify the above code with if then statement but I don't know the value that I must compare to the true/false statement.

Any help is very appreciated.

Upvotes: 2

Views: 131

Answers (4)

jonju
jonju

Reputation: 2736

Try this query. Compatible in both SQL Server & Mysql:

select 
    case 
       when MAX(ComponentTagEntry) IS NULL 
          then 0 
          else MAX(ComponentTagEntry) 
    end 
from 
    dbo.HullDataSheet

Upvotes: 0

Anton Gogolev
Anton Gogolev

Reputation: 115779

coalesce is the way to go:

select coalesce(max(ComponentTagEntry)) from ...

For example:

create table dbo.HullDataSheet (ComponentTagEntry int);

select coalesce(max(ComponentTagEntry), 0) from HullDataSheet

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

-----------
0

(1 row(s) affected)

Table 'HullDataSheet'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Upvotes: 3

user6996876
user6996876

Reputation:

I'd suggest

int newTagNo = 0;
object testMe = commTagNo.ExecuteScalar();
if (testMe != null) { newTagNo = (int)testMe; }

Upvotes: 1

Khurram Zafar
Khurram Zafar

Reputation: 11

ISNULL operator should help.

commTagNo = new SqlCommand("SELECT ISNULL(MAX(ComponentTagEntry), 0) FROM dbo.HullDataSheet", connHull);

Upvotes: 1

Related Questions