determine which database row is causing an "Input string was not in correct format"

Question

I have a project using NHibernate linked with a MySQL database. All of a sudden, the homepage started throwing the below error:

Input string was not in a correct format.

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.FormatException: Input string was not in a correct format.

Source Error: 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace: 


[FormatException: Input string was not in a correct format.]
   System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +14281985
   System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +305
   System.SByte.Parse(String s, NumberStyles style, NumberFormatInfo info) +41
   MySql.Data.Types.MySqlByte.MySql.Data.Types.IMySqlValue.ReadValue(MySqlPacket packet, Int64 length, Boolean nullVal) +182
   MySql.Data.MySqlClient.NativeDriver.ReadColumnValue(Int32 index, MySqlField field, IMySqlValue valObject) +376
   MySql.Data.MySqlClient.ResultSet.ReadColumnData(Boolean outputParms) +105
   MySql.Data.MySqlClient.ResultSet.NextRow(CommandBehavior behavior) +263
   MySql.Data.MySqlClient.MySqlDataReader.Read() +98
   NHibernate.Driver.NHybridDataReader.Read() +28
   NHibernate.Impl.MultiCriteriaImpl.GetResultsFromDatabase(IList results) +1363

    [HibernateException: Error executing multi criteria : [SELECT count(distinct this_.ID) as y0_ FROM `PropertyItem` this_ WHERE (this_._Temporary_Flag = ?p0 or this_._Temporary_Flag is null) and (this_._Deleted = ?p1 or this_._Deleted is null) and this_.Featured = ?p2 and this_.Activated = ?p3 and this_.ListingStatus = ?p4 and this_.HideListingFromWeb = ?p5 and this_.HideListingFromPublic = ?p6;
SELECT this_.ID as ID53_1_, this_.FullPropertyDescription as FullProp2_53_1_, this_.HeaderTagText as HeaderTa3_53_1_, this_.PropertyCategory as Property4_53_1_, this_.PropertyStatus as Property5_53_1_, this_.ShortDescription as ShortDes6_53_1_, this_.PropertyPrice as Property7_53_1_, this_.RefCode as RefCode53_1_, this_.Featured as Featured53_1_, this_.Title as Title53_1_, this_.StarRating as StarRating53_1_, this_.AmountOfBedrooms as AmountO12_53_1_, this_.Activated as Activated53_1_, this_.PropertyPlotSize as Propert14_53_1_, this_.PropertyFloorArea as Propert15_53_1_, this_.AmountOfBathrooms as AmountO16_53_1_, this_.AmountOfGarages as AmountO17_53_1_, this_.VirtualTourURL as Virtual18_53_1_, this_.RentInterval as RentInt19_53_1_, this_.HolidayPriceDescription as Holiday20_53_1_, this_.Exclusive as Exclusive53_1_, this_.FacebookImageFilename as Faceboo22_53_1_, this_.MainImageFilename as MainIma23_53_1_, this_.IsFromXMLFile as IsFromX24_53_1_, this_.PriceDaily as PriceDaily53_1_, this_.FeatureValuesForSearch as Feature26_53_1_, this_._LastEditedOn as column27_53_1_, this_.IsExclusive as IsExclu28_53_1_, this_.HidePriceFromPublic as HidePri29_53_1_, this_.ListingStatus as Listing30_53_1_, this_.HideListingFromWeb as HideLis31_53_1_, this_.LastXmlImportInfo as LastXml32_53_1_, this_.HideListingFromPublic as HideLis33_53_1_, this_._Temporary_LastUpdOn as column34_53_1_, this_._Temporary_Flag as column35_53_1_, this_._Deleted as column36_53_1_, this_._DeletedOn as column37_53_1_, this_.Priority as Priority53_1_, this_.LocalityID as LocalityID53_1_, this_.PropertyTypeID as Propert40_53_1_, this_._LastEditedByID as column41_53_1_, this_.OfficeNameID as OfficeN42_53_1_, this_.SalesAgentID as SalesAg43_53_1_, location2_.ID as ID36_0_, location2_.ReferenceID as Referenc2_36_0_, location2_.Title as Title36_0_, location2_.LocationType as Location4_36_0_, location2_._Temporary_LastUpdOn as column5_36_0_, location2_._Temporary_Flag as column6_36_0_, location2_._Deleted as column7_36_0_, location2_._DeletedOn as column8_36_0_, location2_.Priority as Priority36_0_, location2_.ParentLocationID as ParentL10_36_0_ FROM `PropertyItem` this_ left outer join `Location` location2_ on this_.LocalityID=location2_.ID WHERE (this_._Temporary_Flag = ?p7 or this_._Temporary_Flag is null) and (this_._Deleted = ?p8 or this_._Deleted is null) and this_.Featured = ?p9 and this_.Activated = ?p10 and this_.ListingStatus = ?p11 and this_.HideListingFromWeb = ?p12 and this_.HideListingFromPublic = ?p13 and this_.ID in (?p14, ?p15, ?p16, ?p17, ?p18, ?p19, ?p20, ?p21);]]
       Classes.Search.DatabaseSearcher.GetSearchInDatabase() 
       System.Web.UI.Control.LoadRecursive() +71
       System.Web.UI.Control.LoadRecursive() +190
       System.Web.UI.BasePartialCachingControl.LoadRecursive() +143
       System.Web.UI.Control.LoadRecursive() +190
       System.Web.UI.Control.LoadRecursive() +190
       System.Web.UI.Control.LoadRecursive() +190
       System.Web.UI.Control.LoadRecursive() +190
       System.Web.UI.Control.LoadRecursive() +190
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3178

Is it be possible to determine which rows are causing the issue? The resulting dataset is quite large and difficult to go through it one by one.

Just debug your code and watch the value inside the dataset? — Soner Gönül, Jan 18 '16 at 13:43
This error is happening from NHibernate/MySQL code. Basically, when the query is executed, the data cannot be converted back to the C# types and that error apparently is being generated. — Karl Cassar, Jan 18 '16 at 13:48
Can you show the full query? It looks like it was truncated. `SELECT count(distinct this_.ID) as y0_ FROM PropertyItem this_ WHERE ....`. There may be a second `SELECT` included in this batch. Next, it will be helpful if you show your NHibernate mappings, and also the `CREATE TABLE` statements for the tables in question. — Daniel Schilling, Jan 18 '16 at 16:08

score 0 · Answer 1 · edited May 23 '17 at 10:27

A few observations from your error messages:

NHibernate is trying to convert a string to a number, and failing. So you should focus on varchar (or similar) columns that have been mapped to int properties. Take a look at your NHibernate mappings and CREATE TABLE statements to figure out which columns might be the problem.
It is not possible for SELECT count(...) to return a string, so the problem must be elsewhere.
It looks like the query has been truncated (....), and you are using MultiCriteria, so I suspect that there is a second or third SELECT query in this batch that is actually causing the problem. Focus on the columns listed in the SELECT clauses of these queries.

Once you've determined which columns are potentially guilty, you can identify which rows contain non-integer text in those columns with a simple SELECT query: How do I check to see if a value is an integer in MySQL?

I've updated the full WHERE clause in the SQL query in the original question. Yes regarding your points - those would help in solving the issue. However, my issue is how I can know which is the offending row so I can fix it. The issue is probably that a particular db column is not matched exactly to the C# type. I'll need to go through the codebase but I would like to do a quick fix and update the data, but it's too tedious to go through each row and column one-by-one, especially as each row has loads of columns. — Karl Cassar, Jan 18 '16 at 16:42

determine which database row is causing an "Input string was not in correct format"

1 Answers1