Funny problem (bug?) using MAX() on SQL Mobile

This sure looks like a bug, but I'm still new...

First I create a simple database:

SqlCeEngine eng = new SqlCeEngine(@."Data Source=\My Documents\test.sdf");
eng.CreateDatabase();
eng.Dispose();

Then I put something in it:

SqlCeConnection conn = new SqlCeConnection(@."Data Source=\My Documents\test.sdf");
SqlCeCommand cmd = new SqlCeCommand("CREATE TABLE myTable (ID int, name nchar(10))");
cmd.Connection=conn;
conn.Open();
cmd.ExecuteNonQuery();
cmd.CommandText="INSERT INTO myTable (ID, name) VALUES (1,'bill')";
cmd.ExecuteNonQuery();

Then I try to get the maximum value from the first column.

cmd.CommandText = "SELECT max(ID) FROM myTable";
int index = (int)cmd.ExecuteScalar();

At this point I get an InvalidCastException raised. Using the datareader to extract the value produces the same exception, however I can go through the Query Analyzer on the device and execute the query and it comes out fine...?

This has really got me confused. Any thoughts?

What object type you're actually getting?|||Good question--how do I find that out?|||

you should NEVER cast the result of an execute scalar before you check for DBNull (not the same as C# null or VB Nothing) being returned.

VB.NET

Dim o As Object = cmd.ExecuteScalar()
If Not IsDBNull(o) Then

' now it's safe to cast the result

End If

-Darren

|||

You can look in the debugger or you can print out type name:

object o = ...;

// Get that mysteries type here

string s = o.GetType().ToString();

// Print out string here

And yes, you do need to check for DBNull.

|||

Yes, I'm still learning this stuff...

It turns out an System.Int64 is being returned. That hadn't occured to me since I was under the impression that MAX() will return the type that it operates on (in this case an int32). Nevertheless, I am now able to successfully cast it to an int32 and use it in my program.

Thanks for your help guys. Problem solved.