After Setting DataSource I receive the error message the "Input string was not"

After Setting DataSource I receive the error message the "Input string was not"

Posted by: miguel.marques on 1 November 2017, 12:30 pm EST

• 

  miguel.marques

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 1 November 2017, 12:30 pm EST

  I have a Spread that if I SetText before associate the DataSource it works, but after setting the DataSource I receive the message:

  Input string was not in a correct format.

• 

  miguel.marques

  • Post Options:
  • Link
    Copy

  Posted 1 November 2017, 1:12 pm EST

  I Verified that the problema has to be with DataSource with the columns of int type that are returned from database.

  For sample if I bind “SELECT Month FROM

  ” the column Month is formated by the spread as Integer, but if If “SELECT CAST(Month AS NVARCHAR(2)) FROM ” It works.

  I Already Set ActiveSheet.DataAutoCellTypes = false; and formatted the Column as Edit, but still ot works.

• 

  deepak.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 14 November 2017, 3:21 am EST

  Hello,

  You need to set the DataAutoCellTypes to False after setting the DataSource for Spread. For example:

  
  DataSet ds = new DataSet();
  DataTable dt = default(DataTable);
  dt = new DataTable();
  dt.Columns.Add(new DataColumn("Number1", typeof(string)));
  dt.Columns.Add(new DataColumn("Number2", typeof(string)));
  dt.Columns.Add(new DataColumn("Number3", typeof(string)));
  dt.Columns.Add(new DataColumn("Text1", typeof(string)));
  dt.Columns.Add(new DataColumn("Text2", typeof(string)));
  dt.Columns.Add(new DataColumn("Text3", typeof(string)));
  dt.Columns.Add(new DataColumn("Text4", typeof(decimal)));
  dt.Rows.Add(new object[] { "101", "1", "0", "Num1", "Num2", "Num3", 21000 });
  dt.Rows.Add(new object[] { "201", "2", "0", "a", "b", "c", 9400 });
  dt.Rows.Add(new object[] { "203", "2", "0", "Jan", "Feb", "March", 4550 });
  dt.Rows.Add(new object[] { "301", "3", "1", "First", "Second", "Third", 32000 });
  dt.Rows.Add(new object[] { "302", "3", "1", "Test", "Test1", "Test2", 13280 });
  dt.Rows.Add(new object[] { "204", "2", "0", "Mon", "Tues", "Wed", 1700 });
  dt.Rows.Add(new object[] { "303", "3", "1", "Week1", "Week2", "Week3", 36080 });
  dt.Rows.Add(new object[] { "402", "4", "1", "d", "e", "f", 31500 });
  dt.Rows.Add(new object[] { "405", "4", "1", "July", "August", "September", 4958 });
  dt.Rows.Add(new object[] { "102", "1", "0", "next", "aaa", "max", 1539 });
  ds.Tables.Add(dt);
  
  FarPoint.Win.Spread.Data.SpreadDataBindingAdapter Sdba = new FarPoint.Win.Spread.Data.SpreadDataBindingAdapter();
  Sdba.DataSource = ds.Tables[0];
  Sdba.Spread = fpSpread1;
  Sdba.SheetName = fpSpread1.ActiveSheet.SheetName;
  Sdba.AutoGenerateRow = true;
  Sdba.DataAutoCellTypes = false;  
  Sdba.MapperInfo = new FarPoint.Win.Spread.Data.MapperInfo(2, 0, 10, 7);   
  Sdba.FillSpreadDataByDataSource();
  
  

  If you are setting the datasource for the sheet directly you need to use the code as follows:

  
  string conStr = "Provider=Microsoft.JET.OLEDB.4.0;data source= " + Application.StartupPath + "\\Patients2000.mdb";
  string sqlStr = "SELECT * FROM Patients"; 
  System.Data.OleDb.OleDbConnection conn  = new System.Data.OleDb.OleDbConnection(conStr); 
  DataSet ds = new DataSet(); 
  System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sqlStr, conn);
  fpSpread1.ActiveSheet.DataAutoCellTypes = false; 
  fpSpread1.ActiveSheet.DataMember = "Patients"; 
  da.Fill(ds); 
  fpSpread1.ActiveSheet.DataSource = ds;
  
  

  Please test above mentioned approach and let me know if you still face the same issue.

  Thanks,
  
  Deepak Sharma

• 

  miguel.marques

  • Post Options:
  • Link
    Copy

  Posted 25 January 2018, 10:33 am EST

  Hello,

  the cell stays correctly formated, what happens in my situation is than when teh cell has “0,0000” and you enter “0,5690” when you leave the cell the value is transformed in “1.0000”.

  When I remove the bound the problem does not happen, so the problem has to be with bound and the cell type that is automatically done by the spread.

  The same problema happens with you sample just put “SELECT 0 AS Total, Address, City, CompanyName, ContactName, Conta, ctTitle, Country, CustomerID, Fax, Phone, PostalCode, Region FROM Customers”

  and format the cell as Number with 4 decimal places, after setting the databound the problem ocurrs.

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 29 January 2018, 8:09 am EST

  Hello,

  I am able to replicate the problem at our end. I have escalated this to the development team(tracking id 253730) and will revert you back once have any information from them.

  Thanks
  
  Mohit

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 30 January 2018, 6:07 am EST

  Hello,

  When binding datasource, inputted cell value will be determined by DataType of DataColumn, not by celltype. Celltype only has effect to format text to display.

  In this case, querry “SELECT 0 AS Total, Address, City, CompanyName, ContactName, ContactTitle, Country, CustomerID, Fax, Phone, PostalCode, Region FROM Customers”

  will create temporary DataColumn Total with type = Int32.

  When input cell value 0,5690, it’ll be converted to int32 using the following method

  Convert.ChangeType(value, fieldType); → 1 is return

  Hence, this is designed behaviour of spread.

  You could create a temporary column to avoid above issue with type = decimal using following query string

  “SELECT 0.1 AS Total, Address, City, CompanyName, ContactName, ContactTitle, Country, CustomerID, Fax, Phone, PostalCode, Region FROM Customers”

  Hope it helps.

  Thanks,
  
  Mohit