How to cast the value in C# resulted from max() command in SQL Server Developer

Could anyone help of how to cast the value in C# resulted from max() command. To complicate the matter, from the query result I see in the Microsoft SQL Server Management Studio, if there are records the value is number. But if there are no records, the value is NULL. How do I handle these two possible different conditions?

I have tried:

- stringtest = (string)reader["MaxOrderID"];

- inttest = Convert.ToInt32((string)reader["MaxOrderID"]);

- stringtest = Convert.ToInt32(reader["MaxOrderID"]).ToString();

all failed. And what do I do if the value is NULL. And also how do I do to cope with these two different possible conditions?

For example, I have the following code:

command.CommandText ="Select max(OrderID) as 'MaxOrderID' from [Order]";command.CommandType =CommandType.Text;

command.Connection = conn;

command.Connection.Open();

reader = command.ExecuteReader();

reader.Read();

? orderID = ?reader["MaxOrderID"];

Select MAX(ISNULL(OrderID,0) as MaxOrderID from [Order] try this statment and retest your three statments again...

- stringtest = (string)reader["MaxOrderID"];

- inttest = Convert.ToInt32((string)reader["MaxOrderID"]);

- stringtest = Convert.ToInt32(reader["MaxOrderID"]).ToString();

|||

I executedSelect MAX(ISNULL(OrderID,0)) as MaxOrderID from [Order] , but the value is still NULL, when the table has no records.

I can see that it should be 0.

|||

hi dedyandy,

dedyandy:

I executedSelect MAX(ISNULL(OrderID,0)) as MaxOrderID from [Order] , but the value is still NULL, when the table has no records.

what you are getting is correct, max will return value if there's any record in table else it wont return anything i.e. its a null. you can either use 1 as default value in case there are no records or in case you've procedure then you can check something like

if @.@.Rowcount = 0 Select 1 as 'MaxOrderID'

thanks,

satish.

|||

Thanks Satish. Yes, I will use Count function first. If there are records, I will use AVG function, else return 1.

Andy.

|||

cheers

thanks,

Satish.