Anything in the table?

[bob323]

I have a function that opens the database and returns the highest id, I have been using executescalar as there is only one value to return.
The problem is though if there is nothing in the database then it crashes. is there a way to determine if there is anything there before I executescalar?

here is the code
<code>

//open the database and return the most recent alertid
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
connection.Open();
string commandString = "SELECT MAX(AlertId) FROM Alert";
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
command.CommandText = commandString;
command.Connection = connection;

int alertid = (int) command.ExecuteScalar();

//Release all resources
connection.Close();
command.Dispose();

//return the id of the largest alert
return(Convert.ToInt16(alertid));

</code>

[bob323]

humm I can get it to work if I put a try, catch around it, but I am sure there must be a better way, any ideas?

[Lethal]

Could you simply do a select to get the count first??
You could even create a simple stored procedure that handled the business logic and returned the value.

CREATE PROC p_Get_Values
AS
DECLARE @iCount   int
SELECT @iCount = (SELECT COUNT(*) FROM TABLE_NAME)
IF @iCount > 0
    -- Add Your Max Logic Here
ELSE
    RETURN(0)
GO

However, when it comes down to performance, I think using the try..catch block is you safest best, not to mention you should be using it anyway..