Leading zeros are not adding to DataTable columns C# Asp.Net with PadLeft or String.Format

Question

Leading zeros are not adding to DataTable columns with PadLeft or String.Format.

Initially I copied user uploaded excel data to Datatable. I'm trying to add zeros in front of datatable column values if the length is less than 8 digits and after that I have to compare with another table for matching records. If I don't have leading zeros I'm missing those records while matching with other datatable columns. But I want them to be with leading zeros so they can be matched to get correct results.

Ex: I have a column "CODE" in datatable with values 30500, 501080, 5020900, 19010300 etc and Would like to have my results like 00030500, 00501080, 05020900, 19010300

Note: I would like the change the data in the Datatable not in the sql query which retrieves the data. I don't want code for converting int to string leading zeros. Even I tried in that way didn't fix my issue.

I tried couple of ways but it didn't solve. What's wrong with my code. It's not working. I used below from How to add leading zeros in DataTable columns but still not changed anything. Don't consider this post as duplicate, As I tried all ways but still the problem exist, Hence posting here.

Approach 1:

 foreach (DataRow row in dataExcelInputTable.Rows)
                {
                    row["CODE"] = row["CODE"].ToString().PadLeft(8, '0');
                }
                dataExcelInputTable.AcceptChanges();

Approach 2:

foreach (DataRow drin dataExcelInputTable.Rows)
                {
                   dr["CODE"] = String.Format("{0:00000000}", int.Parse(dr["CODE"].ToString()));
                }
                dataExcelInputTable.AcceptChanges();

Approach 3:

               int countSize = 0;
                int val = 0;
                foreach (DataRow row in dataExcelInputTable.Rows)
                {
                    countSize = row["CODE"].ToString().Length;
                    val = int.Parse(row["CODE"].ToString());
                    if (countSize < 8)
                    {

                        row["CODE"] = val.ToString("D8");

                        //Response.Write("<br/>" + val.ToString("D8"));
                    }

                }
                dataExcelInputTable.AcceptChanges();

Update:

foreach (DataRow row in dataExcelInputTable.Rows)
                {
                    if (row["CODE"].ToString().Length < 8)
                    {
                        row["CODE"] = row["CODE"].ToString().PadLeft(8, '0');
                    }
                    Response.Write("<br/>" + row["CODE"]);
                }
                dataExcelInputTable.AcceptChanges();

Right now its printing below, its not padding zero front.

9040100 (<8) , 9070100 (<8) , 9090200 (<8) , 9090300 (<8)

10020300 (=8) , 10030300 (=8) , 11010100 (=8)

Pls refer my latest update. Output it printing both <8 and =8 length CODE values. So assuming datatable column can able to store the 8 digits length. — himak
– himak, Commented Jun 2, 2016 at 0:16
Hi Sami, I found the datatype is Double for "CODE" column. Do you this is causing the issue and if I change it to string will it work? — himak
– himak, Commented Jun 3, 2016 at 14:57

Sami · Accepted Answer · 2016-06-02 00:29:38Z

2

I tried at my end and getting expected output ... below is a test code

        System.Data.DataTable dt = new System.Data.DataTable();
        dt.Columns.Add("Code");
        System.Data.DataRow r = dt.NewRow();
        r["Code"] = "30500";
        dt.Rows.Add(r);

        foreach (System.Data.DataRow row in dt.Rows)
        {
            row["CODE"] = row["CODE"].ToString().PadLeft(8, '0');
        }
        dt.AcceptChanges();

//dt.Rows[0][0] value is 00030500

answered Jun 2, 2016 at 0:29

Sami

3,8204 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

himak Over a year ago

Yes its working for fixed values. Actually I'm filling datatable from user uploaded .ibfs file (type of excel), In my case same code is not working.

Sami Over a year ago

Nemeric/Decimcal data types can't hold prefix zeros , make sure this is not the case.

himak · Accepted Answer · 2016-06-03 15:20:59Z

Finally got one solution, Thank you Sami for your idea about Datatype . I found that Datatype is double but I need string to make padding left zeros using PadLeft method. As I can't change Datatype of a Datatable after filling data from excel sheet. I cloned to a new Datatable and then changed it's datatype to string. Below is the sample code.

                dtCloned = dataExcelInputTable.Clone();
                dtCloned.Columns[1].DataType = typeof(System.String); // clone the datatble and make a column datatype to string
                foreach (DataRow row in dataExcelInputTable.Rows)
                {
                    dtCloned.ImportRow(row);
                }

                foreach (System.Data.DataRow row in dtCloned.Rows)
                {
                    row["CODE"] = row["CODE"].ToString().PadLeft(8, '0');
                    Response.Write("<br/>" + row["CODE"]);
                }
                dtCloned.AcceptChanges();

This is working as expected. But I was looking for any direct simple way other than clone? I tried below from https://msdn.microsoft.com/en-us/library/dd260048%28v=vs.110%29.aspx but it's not working. Giving me an error "No overload for method 'ToString' takes 1 arguments".

            string fmt = "00000000.##";
            foreach (System.Data.DataRow row in dataExcelInputTable.Rows)
            {
                row["CODE"] = row["CODE"].ToString(fmt);
                Response.Write("<br/>" + row["CODE"]);
            }

Glad to help you Priya. Double can't store values like '000300' but converted to "300" . Accept my answer if this is the case. Thanks

Collectives™ on Stack Overflow

Leading zeros are not adding to DataTable columns C# Asp.Net with PadLeft or String.Format

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related