none
"Input string was not in a correct format" error RRS feed

  • Question

  • public DataTable getPaySlipDetails(Int32 intYearCode, Int32 intMonthCode, String strRouteCode, Int32 FromRange, Int32 ToRange)
            {
                
                DateTime PreviousMonth = new DateTime(intYearCode, intMonthCode, 1).AddMonths(-1);

                String LastMonth = PreviousMonth.Month.ToString();
                Int32 CurruntYear = DateTime.Now.Year;
                Int32 LastYear = CurruntYear - 1;

                DataTable dt = new DataTable("PaymentSlipTable");
                dt.Columns.Add(new DataColumn("YearCode"));//0
                dt.Columns.Add(new DataColumn("MonthCode"));//1
                dt.Columns.Add(new DataColumn("MonthName"));//2
                dt.Columns.Add(new DataColumn("RouteCode"));//3
                dt.Columns.Add(new DataColumn("RouteName"));//4
                dt.Columns.Add(new DataColumn("SupplierCode"));//5
                dt.Columns.Add(new DataColumn("SupplierName"));//6

                dt.Columns.Add(new DataColumn("BestLeaf"));//7
                dt.Columns.Add(new DataColumn("GoodLeaf"));//8
                dt.Columns.Add(new DataColumn("NormalLeaf"));//9
                dt.Columns.Add(new DataColumn("TotalLeaf"));//10

                dt.Columns.Add(new DataColumn("BestLeafValue"));//11
                dt.Columns.Add(new DataColumn("GoodLeafValue"));//12
                dt.Columns.Add(new DataColumn("NormalLeafValue"));//13
                dt.Columns.Add(new DataColumn("TotalLeafValue"));//14

                dt.Columns.Add(new DataColumn("BFCoins"));//15
                dt.Columns.Add(new DataColumn("OtherAddition"));//16
                dt.Columns.Add(new DataColumn("IncentivePAyment"));//17
                dt.Columns.Add(new DataColumn("TotalEarnings"));//18

                dt.Columns.Add(new DataColumn("BFCashBalance"));//19
                dt.Columns.Add(new DataColumn("BFFertilizerInstallment"));//20
                dt.Columns.Add(new DataColumn("AdvanceAmount"));//21
                dt.Columns.Add(new DataColumn("FertilizerIssueAmount"));//22
                dt.Columns.Add(new DataColumn("TeaIssue"));//23
                dt.Columns.Add(new DataColumn("LoanInstallment"));//24
                dt.Columns.Add(new DataColumn("TransportChrage"));//25
                dt.Columns.Add(new DataColumn("OtherDeduction"));//26
                dt.Columns.Add(new DataColumn("StampDuty"));//27

                dt.Columns.Add(new DataColumn("TotalDeduction"));//28

                dt.Columns.Add(new DataColumn("PaybleBalance"));//29            
                dt.Columns.Add(new DataColumn("PaidBalance"));//30
                dt.Columns.Add(new DataColumn("MinusBalance"));//31

                dt.Columns.Add(new DataColumn("CFCoins"));//32
                dt.Columns.Add(new DataColumn("CFFertBalace"));//33
                dt.Columns.Add(new DataColumn("CFLoanBalance"));//34
                
                dt.Columns.Add(new DataColumn("AccHolder"));//35
                dt.Columns.Add(new DataColumn("BankCode"));//36
                dt.Columns.Add(new DataColumn("BankName"));//37
                dt.Columns.Add(new DataColumn("BranchName"));//38
                dt.Columns.Add(new DataColumn("AccNo"));//39

                DataRow dtrow;
                SqlDataReader dataReader;
                dtrow = dt.NewRow();

                if (FromRange >= 0 && ToRange > 0)
                
                    dataReader = SQLHelper.ExecuteReader("SELECT "   ", CommandType.Text);
                
                else
                
                    dataReader = SQLHelper.ExecuteReader("SELECT  "    ", CommandType.Text);
                
                                
                while (dataReader.Read())
                {
                    dtrow = dt.NewRow();
                    if (!dataReader.IsDBNull(0))
                    {
                        dtrow[0] = dataReader.GetInt32(0);
                    }
                    if (!dataReader.IsDBNull(1))
                    {
                        dtrow[1] = dataReader.GetInt32(1);
                    }
                    if (!dataReader.IsDBNull(2))
                    {
                        dtrow[2] = dataReader.GetString(2).Trim();
                    }
                    if (!dataReader.IsDBNull(3))
                    {
                        dtrow[3] = dataReader.GetString(3).Trim();
                    }
                    if (!dataReader.IsDBNull(4))
                    {
                        dtrow[4] = dataReader.GetString(4).Trim();
                    }
                    if (!dataReader.IsDBNull(5))
                    {
                        dtrow[5] = dataReader.GetString(5).Trim();
                    }
                    if (!dataReader.IsDBNull(6))
                    {
                        dtrow[6] = dataReader.GetString(6).Trim();
                    }
                    if (!dataReader.IsDBNull(7))
                    {
                        dtrow[7] = dataReader.GetDecimal(7).ToString();
                    }
                    if (!dataReader.IsDBNull(8))
                    {
                        dtrow[8] = dataReader.GetDecimal(8).ToString();
                    }
                    if (!dataReader.IsDBNull(9))
                    {
                        dtrow[9] = dataReader.GetDecimal(9).ToString();
                    }
                    if (!dataReader.IsDBNull(10))
                    {
                        dtrow[10] = dataReader.GetDecimal(10).ToString();
                    }
                    if (!dataReader.IsDBNull(11))
                    {
                        dtrow[11] = dataReader.GetDecimal(11).ToString();
                    }
                    if (!dataReader.IsDBNull(12))
                    {
                        dtrow[12] = dataReader.GetDecimal(12).ToString();
                    }
                    if (!dataReader.IsDBNull(13))
                    {
                        dtrow[13] = dataReader.GetDecimal(13).ToString();
                    }
                    if (!dataReader.IsDBNull(14))
                    {
                        dtrow[14] = dataReader.GetDecimal(14).ToString();
                    }
                    if (!dataReader.IsDBNull(15))
                    {
                        dtrow[15] = dataReader.GetDecimal(15).ToString();
                    }
                    if (!dataReader.IsDBNull(16))
                    {
                        dtrow[16] = dataReader.GetDecimal(16).ToString();
                    }
                    if (!dataReader.IsDBNull(17))
                    {
                        dtrow[17] = dataReader.GetDecimal(17).ToString();
                    }
                    if (!dataReader.IsDBNull(18))
                    {
                        dtrow[18] = dataReader.GetDecimal(18).ToString();
                    }
                    if (!dataReader.IsDBNull(19))
                    {
                        dtrow[19] = dataReader.GetDecimal(19).ToString();
                    }
                    if (!dataReader.IsDBNull(20))
                    {
                        dtrow[20] = dataReader.GetDecimal(20).ToString();
                    }
                    if (!dataReader.IsDBNull(21))
                    {
                        dtrow[21] = dataReader.GetDecimal(21).ToString();
                    }
                    if (!dataReader.IsDBNull(22))
                    {
                        dtrow[22] = dataReader.GetDecimal(22).ToString();
                    }
                    if (!dataReader.IsDBNull(23))
                    {
                        dtrow[23] = dataReader.GetDecimal(23).ToString();
                    }
                    if (!dataReader.IsDBNull(24))
                    {
                        dtrow[24] = dataReader.GetDecimal(24).ToString();
                    }
                    if (!dataReader.IsDBNull(25))
                    {
                        dtrow[25] = dataReader.GetDecimal(25).ToString();
                    }
                    if (!dataReader.IsDBNull(26))
                    {
                        dtrow[26] = dataReader.GetDecimal(26).ToString();
                    }
                    if (!dataReader.IsDBNull(27))
                    {
                        dtrow[27] = dataReader.GetDecimal(27).ToString();
                    }
                    if (!dataReader.IsDBNull(28))
                    {
                        dtrow[28] = dataReader.GetDecimal(28).ToString();
                    }
                    if (!dataReader.IsDBNull(29))
                    {
                        dtrow[29] = dataReader.GetDecimal(29).ToString();
                    }
                    if (!dataReader.IsDBNull(30))
                    {
                        dtrow[30] = dataReader.GetDecimal(30).ToString();
                    }
                    if (!dataReader.IsDBNull(31))
                    {
                        dtrow[31] = dataReader.GetDecimal(31).ToString();
                    }
                    if (!dataReader.IsDBNull(32))
                    {
                        dtrow[32] = dataReader.GetDecimal(32).ToString();
                    }
                    if (!dataReader.IsDBNull(33))
                    {
                        dtrow[33] = dataReader.GetDecimal(33).ToString();
                    }
                    if (!dataReader.IsDBNull(34))
                    {
                        dtrow[34] = dataReader.GetDecimal(34).ToString();
                    }                
                    if (!dataReader.IsDBNull(35))
                    {
                        dtrow[35] = dataReader.GetString(35).Trim();
                    }
                    if (!dataReader.IsDBNull(36))
                    {
                        dtrow[36] = dataReader.GetString(36).Trim();
                    }
                    if (!dataReader.IsDBNull(37))
                    {
                        dtrow[37] = dataReader.GetString(37).Trim();
                    }
                    if (!dataReader.IsDBNull(38))
                    {
                        dtrow[38] = dataReader.GetString(38).Trim();
                    }
                    if (!dataReader.IsDBNull(39))
                    {
                        dtrow[39] = dataReader.GetString(39).Trim();
                    }
                    dt.Rows.Add(dtrow);
                }
                dataReader.Close();
                return dt;
            }
    Tuesday, February 13, 2018 6:37 PM

All replies

  • Firstly please do not post code directly in your thread. Use the Code Block button to create a code block.

    Secondly, posting code without any context or information isn't going to be helpful. Please indicate exactly what the issue is. Clarify what line it is occurring on and any data you're using when it occurs.


    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, February 13, 2018 7:03 PM
    Moderator
  • Wrap the offending line in a try/catch, place a break point in the catch then examine the data. Once the error is known you can write assertion to prevent it from throwing an exception.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, February 13, 2018 7:08 PM
    Moderator
  • This code is very bad. I would like create two arrays. First with column names. Then you can create all columns simply in for cycle. Maybe it could be created by some builder class. Second array would be contains column datatype. Data type could be save in DataColumn. 

    Then you can create foreach data type something as StringDataReader, IntDataReader ... These classes will be inherited from interfaces which has IsMatch method with parameter DataType. Next method will be read with SqlDataReader and DataRow and index parameter. 

    Now you have list of readers and you can check if concrete reader can read this value by column data type. If yes, datarow with columnindex is read by this reader. 

    Look at rules and builder design pattern. It doesn't help with your problem but code will be more readable and you can will see which reader has problem. You can setup exceptions to break when this exception is thrown, so you can see which line cause this error. 

    Tuesday, February 13, 2018 7:52 PM