Bug #4593 BYFX is unable to convert BIGINT UNSIGNED to UInt64
Submitted: 17 Jul 2004 20:46 Modified: 6 Nov 2007 14:40
Reporter: Timothy Graupmann Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S4 (Feature request)
Version: OS:Microsoft Windows (Win2k)
Assigned to: CPU Architecture:Any

[17 Jul 2004 20:46] Timothy Graupmann
Description:
I'm getting a "System.InvalidCastException:
Specified cast is not valid." when I have a BIGINT UNSIGNED field and try to
convert it to UInt64.

Here is the table structure:
create table user
(
	UserId           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
	ScreenName       VARCHAR(50) NOT NULL,
	AccessKey        VARCHAR(50) NOT NULL,
	LastModified     DATETIME NOT NULL
);

The count line is throwing the exception:

System.InvalidCastException: Specified cast is not valid.
   at TagML.DbLayer.DbClient.CountSimilarUsers(String screenName) in c:\inetpub\wwwroot\tagmlws\dblayer\connection.cs:line 76
   at TagML.WebServices.ClientManager.AnnounceUser(String screenName) in c:\inetpub\wwwroot\tagmlws\clientmanager.asmx.cs:line 78

How to repeat:
static public UInt64 CountSimilarUsers(String screenName) 
{
	IDbConnection myConnection = DBFactory.CreateConnection();
	myConnection.Open();
	IDbCommand myCommand = myConnection.CreateCommand();
	myCommand.CommandText = "SELECT COUNT(*) FROM tagml_user WHERE ScreenName like
@SN";
	IDbDataParameter sn = myCommand.CreateParameter();
	sn.ParameterName = "@sn";
	sn.Value = screenName + "%";
	myCommand.Parameters.Add(sn);
	UInt64 count = (UInt64)myCommand.ExecuteScalar();
	myConnection.Close();
	return count;
}

Suggested fix:
The bug isn't specific to counts. The error occurs in returned fields as well.

A fix in the client code can do this:
UInt64 count = (UInt64)(Int32)myCommand.ExecuteScalar();

However, it would be best if this type could be supported. UInt32 holds the largest int that the current system will allow.
[17 Jul 2004 22:11] Timothy Graupmann
I suppose the priority could be higher.

The UInt64 value type represents unsigned integers with values ranging from 0 to 184,467,440,737,095,551,615.
[4 Sep 2007 2:59] Keefe Johnson
I think you meant 18,446,744,073,709,551,615
[6 Nov 2007 14:40] Reggie Burnett
Has not been a problem for a very long time.