I have modified the New Articles Part to work with our already in place database running on M$SQL 2000.

Everything seems to work so far until I try to visit the browsearticles.aspx and i get:

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

Source Error:


Line 102: protected virtual ArticleDetails GetArticleFromReader(IDataReader reader, bool readBody)
Line 103: {
Line 104: ArticleDetails article = new ArticleDetails(
Line 105: (int)reader["HID"],
Line 106: (DateTime)reader["CreatedDate"],


Source File: d:\PubDev\App_Code\DAL\ArticlesProvider.cs Line: 104

Stack Trace:


[InvalidCastException: Specified cast is not valid.]
MB.Summit.DAL.ArticlesProvider.GetArticleFro mReader(IDataReader reader, Boolean readBody) in d:\PubDev\App_Code\DAL\ArticlesProvider.cs:104
MB.Summit.DAL.ArticlesProvider.GetArticleCol lectionFromReader(IDataReader reader, Boolean readBody) in d:\PubDev\App_Code\DAL\ArticlesProvider.cs:137
MB.Summit.DAL.SqlClient.SqlArticlesProvider. GetArticles(Int32 groupID, Int32 pageIndex, Int32 pageSize) in d:\PubDev\App_Code\DAL\SqlClient\SqlArticlesProvid er.cs:152
MB.Summit.BLL.Articles.Article.GetArticles(I nt32 groupID, Int32 startRowIndex, Int32 maximumRows) in d:\PubDev\App_Code\BLL\Articles\Article.cs:353


Here is the code that it is pointing to:
protected virtual ArticleDetails GetArticleFromReader(IDataReader reader, bool readBody)
     {
         ArticleDetails article = new ArticleDetails(
            (int)reader["HID"],
            (DateTime)reader["CreatedDate"],
            (int)reader["GroupID"],
            reader["GroupTitle"].ToString(),
            reader["Subject"].ToString(),
            reader["FPIntroText"].ToString(),
            reader["Body"].ToString(),
            reader["LCategory"].ToString(),
            reader["SubCategory"].ToString(),
            reader["CreatedByFullName"].ToString(),
            (DateTime)reader["ModifiedDate"],
            reader["Filename"].ToString(),
            (int)reader["NewsType"],
            (int)reader["JobLevel"]);

         if (readBody)
            article.Body = reader["Body"].ToString();

         return article;

Any Help would be greatly appreciated!
URL of error is: http://dev.summitcomputer.net/BrowseArticles.aspx

Hi,

The '(int)reader["HID"]' type statements in this code block are each taking a value from the underlying Database (which I assume is SQL Server) and trying to convert or 'cast' it from a SQL Server DataType into a .NET DataType.
You are trying to convert one or more of your 'reader["***"]' values from a SQL Server DataType to a .NET DataType which is not compatible.


I had a similar problem which I managed to fix like this:

Firstly, I ran my application in debug mode until this error occurs and program execution stops with this problem area highlighted in VS2005.

Then you can examine each part of this bit of code. Just select a piece of code, right-click and select 'Add Watch'

For example, in your example, you should highlight the code
(int)reader["HID"]
then right-click on it and select 'Add Watch'.

The 'Watch' window should open (if it isn't already) and show you the result of this expression. If the result is an error message, you know it is this particular bit which is causing the problem. If not, try doing the same thing with the next bit of the code, e.g. '(DateTime)reader["CreatedDate"]'

Once you have established which bit is causing the problem, you have a better idea of what to look for.

In my case, it turned out to be an '(int)' conversion which caused the problem.
The standard SQL Server DataType for an Integer is 'int'. However, there are other Integer DataTypes available which take up less room, such as 'smallint' and 'tinyint'. If you know your Integer value is never going to go above 255, for example, you can use a 'tinyint' rather than an 'int'.
In my case, I was using a 'tinyint' column in one of my tables. Everything went fine, until I got the same error that you did.

It turns out that although a statement such as:
'(int)reader["HID"]'
will happily convert from SQL Server 'int' to .NET 'Int', it will not convert from SQL Server 'smallint' or 'tinyint' to .NET 'int'.

The way I worked around it was to first cast to a String, and then to .NET 'int', as follows:
int.Parse(reader["HID"].ToString())
This is a double-cast and seems needlessly expensive to me, but I haven't worked out a better way to do it, yet. I may just end up changing the DataType in SQL Server to 'int' rather than 'tinyint'.


Another possibility is your query is returning 'null' for one of these fields. You cannot cast a 'null' into an 'int'. To capture this, you'd need to test for null, perhaps like:

(reader["HID"] == DBNull.Value ? 0 : (int)reader["HID"])

Of course, your code will have to cater for the fact that 'null' is being converted to '0' in this case.

Thank you SOOOOOO much that was really starting to aggrivate me. It was a tinyint in the newstype field. I cannot change this in SQL as it is a backoffice software's database and I do not like messing with it if I do not have to. Your code to cast to string then int is what is what I ended up using. Thanks again!

Cool! Glad you got it sorted. It was nice to know it wasn't just me who came across this.

It's an annoying little issue though, isn't it? We all try to save on Database space and demands by using smaller fields, and then we find it causes us issues in ASP.NET, and there is a trade off in having to do a double-cast. There must be a better way of handling it, perhaps casting from TinyInt to Int from within the SELECT statement in SQL Server.

Dunno...

Just so that ya know, the .Net equivalent to Tinyint is Byte.

http://msdn2.microsoft.com/en-us/library/ms131092.aspx

It's probably a bit more efficient to convert directly from the native type to the int type without going thru string first:

Convert.ToInt32(reader["HID"])

Eric

Aah, yes. That's a much better idea.