Fahmieyz Fahmieyz - 4 months ago 10
ASP.NET (C#) Question

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.

Answer

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.
Comments