Updating Frequency Values from Text to Numeric in ASP.NET Applications

1. Introduction

In many ASP.NET WebForms applications, a CheckBoxList is used to allow users to select multiple options. These selections are often stored in the database as comma-separated values.

Initially, the system stored text-based frequency values like:

Daily
Weekly
Monthly

Later, the requirement changed to store numeric codes instead of text values:

Daily → 1
Weekly → 2
Monthly → 3

However, the database already contained many records with the old text values, so they needed to be converted into numeric codes without affecting existing functionality.

This article explains how to handle this situation using dummy sample data.

2. Original CheckBoxList (Old Input)

Earlier the application stored text values.

<asp:CheckBoxList ID="ckFrequency" runat="server">
    <asp:ListItem Value="Daily">Daily</asp:ListItem>
    <asp:ListItem Value="Weekly">Weekly</asp:ListItem>
    <asp:ListItem Value="Monthly">Monthly</asp:ListItem>
</asp:CheckBoxList>

Example Stored Values

Category	SubCategory	Frequency
Sales	Online	Daily
Sales	Retail	Weekly
Sales	Online	Daily,Weekly
Finance	Reports	Monthly
Finance	Reports	Weekly,Monthly

3. Updated CheckBoxList (New Input)

Later, the values were changed to numeric IDs.

<asp:CheckBoxList ID="ckFrequency" runat="server">
    <asp:ListItem Value="1">Daily</asp:ListItem>
    <asp:ListItem Value="2">Weekly</asp:ListItem>
    <asp:ListItem Value="3">Monthly</asp:ListItem>
</asp:CheckBoxList>

New Values Stored in Database

Selected Options	Stored Value
Daily	1
Weekly	2
Monthly	3
Daily + Weekly	1,2
Weekly + Monthly	2,3

4. Problem

The database already had existing records stored as text values:

Category	SubCategory	Frequency
Sales	Online	Daily
Sales	Retail	Weekly
Sales	Online	Daily,Weekly
Finance	Reports	Monthly
Finance	Reports	Weekly,Monthly

But the application now expects numeric values such as:

Category	SubCategory	Frequency
Sales	Online	1
Sales	Retail	2
Sales	Online	1,2
Finance	Reports	3
Finance	Reports	2,3

Without converting existing data, the application might produce incorrect results or filtering errors.

5. Solution: Update Existing Data Using SQL

We can convert the old text values into numeric codes using the SQL REPLACE function.

SQL Script

UPDATE DataFrequency
SET Frequency = REPLACE(Frequency,'Daily','1');

UPDATE DataFrequency
SET Frequency = REPLACE(Frequency,'Weekly','2');

UPDATE DataFrequency
SET Frequency = REPLACE(Frequency,'Monthly','3');

This query replaces the text values in all existing records.

6. Example Result

Before Update

Category	SubCategory	Frequency
Sales	Online	Daily
Sales	Retail	Weekly
Sales	Online	Daily,Weekly
Finance	Reports	Monthly
Finance	Reports	Weekly,Monthly

After Update

Category	SubCategory	Frequency
Sales	Online	1
Sales	Retail	2
Sales	Online	1,2
Finance	Reports	3
Finance	Reports	2,3

7. Handling New Selections in ASP.NET

To store selected numeric values from the CheckBoxList, the following C# code can be used:

string freq = "";

for (int i = 0; i < ckFrequency.Items.Count; i++)
{
    if (ckFrequency.Items[i].Selected)
    {
        freq += ckFrequency.Items[i].Value + ",";
    }
}

if (!string.IsNullOrEmpty(freq))
{
    freq = freq.TrimEnd(',');
}

Example

If the user selects:

✔ Daily
✔ Monthly

The stored value will be:

1,3

8. Best Practices

Store numeric IDs instead of text values in the database
Always create a backup before running update scripts
Use IDs for filtering and APIs to improve performance
Keep display names separate from stored values

9. Backup Before Running Update

Before modifying production data, create a backup table:

SELECT * INTO DataFrequency_Backup
FROM DataFrequency

10. Conclusion

Changing database values from text to numeric IDs improves:

performance
consistency
filtering
system scalability

By updating existing records using SQL and modifying the CheckBoxList values, applications can continue working smoothly without losing old data.