SQL Server  

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

CategorySubCategoryFrequency
SalesOnlineDaily
SalesRetailWeekly
SalesOnlineDaily,Weekly
FinanceReportsMonthly
FinanceReportsWeekly,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 OptionsStored Value
Daily1
Weekly2
Monthly3
Daily + Weekly1,2
Weekly + Monthly2,3

4. Problem

The database already had existing records stored as text values:

CategorySubCategoryFrequency
SalesOnlineDaily
SalesRetailWeekly
SalesOnlineDaily,Weekly
FinanceReportsMonthly
FinanceReportsWeekly,Monthly

But the application now expects numeric values such as:

CategorySubCategoryFrequency
SalesOnline1
SalesRetail2
SalesOnline1,2
FinanceReports3
FinanceReports2,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

CategorySubCategoryFrequency
SalesOnlineDaily
SalesRetailWeekly
SalesOnlineDaily,Weekly
FinanceReportsMonthly
FinanceReportsWeekly,Monthly

After Update

CategorySubCategoryFrequency
SalesOnline1
SalesRetail2
SalesOnline1,2
FinanceReports3
FinanceReports2,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.

Membership not found