I'm having three problems specifically when the DB is MySQL.  Please note:  When I do the exact same operations/coding on the SQL Server 2005 Express (localhost), it works just fine.  Also, this is all ASP.Net.  I am running the following:

MySQL v5.1
MySQL Connector 5.0.7
ES 730
C#
XP SP2
VS2005 SP1

1.  "Specified cast is not valid."

When I try to cast the count value using (int) I get the following error:

Server Error in '/NAPSWS_MySQL' Application.
Specified cast is not valid.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: Specified cast is not valid.

Source Error:

Line 41:         if (eOption.Query.Load())
Line 42:         {
Line 43:             dsOptions.TotalRowCount = (int)eOption.GetColumn("Count");
Line 44:         }
Line 45:         gvOptions.Sort(OptionsMetadata.PropertyNames.Description, SortDirection.Ascending);


Source File: e:\Nationwide\NAPSWS_MySQL\Default.aspx.cs    Line: 43

Stack Trace:

[InvalidCastException: Specified cast is not valid.]
   _Default.count_options() in e:\Nationwide\NAPSWS_MySQL\Default.aspx.cs:43
   _Default.Page_Load(Object sender, EventArgs e) in e:\Nationwide\NAPSWS_MySQL\Default.aspx.cs:20
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34
   System.Web.UI.Control.OnLoad(EventArgs e) +99
   System.Web.UI.Control.LoadRecursive() +47
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061


Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832

This doesn't occur when I use the SQL Server version.  When I use Convert.ToInt32(xxx) it works just fine.

2.  When I set the AutoSorting and AutoPaging to true the next time I go into design mode I get the following error:

"Error Creating Control dsOptions -
'true' could not be set on property AutoPaging (or AutoSorting, just seems to pick one)

This appears to just effect the design mode since the program still runs just fine.  It also allows sorting (see next problem) but I have to remove the AutoSort and AutoPage options from the source and then make changes and then put them back.  I don't have enough rows to check the AutoPaging.

3.  AutoSorting:

Yes, it does allow clicking on the column header and sorting.  However, it's delayed.  If I change columns I have to click once before it seems to take effect.  The second time it sorts.  After that it sorts each time I click the same column.  Move to another column and the same thing happens.

Final:

Again, everything is the same except for the DB.  SQL Server Express 2005 works as expected.  MySQL 5.1 doesn't.  Is it something I'm doing?  I could easily zip up the data structures and the code and send it to you if that would help.

Thank you again for a wonderful product and your tremendous support.

Phil Mickelson

Further:

I'm not quite right above.  Error #2, the "true could not be set...." problem, also occurs for SQL Server Express.

Sorry for the screw up.

Phil Mickelson
 

Hello:

ES 819 solved problems 2 and 3 (the worst of the above).  Thank you very much!

Phil Mickelson 

The data type returned by an SQL Server Aggregate Count is an Int32. The data type returned by MySQL is an Int64. When casting, implicit conversion will get that error. For MySQL, you would need to use a long.

long TotalRowCount = (long)emp.GetColumn("Count");

But, if you use explicit conversion, then the same line will work with both databases and both data types.

int TotalRowCount = Convert.ToInt32(emp.GetColumn("Count"));

Understood.  Thank you very much!

Phil Mickelson