Entering values into database from ‘unlimited’ dynamic controls

There’s a dropdown displaying 1-10 from where the user selects the number of textboxes he wants – if he chooses unlimited, it shows him a textbox wherein he enters the exact number of textboxes he’d like. if he enters 40, it shows him 40 textboxes which are created at runtime.

My question is, how do I enter the data from 40 – or ‘whatever number he enters’ textboxes into the MS SQL database. There’s no way I can create a column name dynamically, and that’d be too tedious and messy. Is there any way I can do this?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

When you create the textboxes, you can give them sequential ID’s, and then iterate through these on the postback, writing the values to your database.

For example:

/// <Summary>
///  This would be fired when the user enters the number of textboxes
///  the want and click the 'Create Textboxes' button.
/// </Summary>
protected void CreateTextBoxes_Click(object sender, EventArgs e)
{
    // Check how many textboxes the user wants
    int count = Convert.ToInt32(CountTextBox.Text);

    // Generate the number of textboxes requested
    // and add them to the page
    for (int i=0; i < count; i++)
    {
        // Create the textbox
        TextBox textbox = new Textbox();

        // Set the ID so we can access it later
        textbox.ID = "TextBox_" + i;

        // Add the textbox to the panel
        TextBoxPanel.Controls.Add(textbox);
    }
}

/// <Summary>
///  This would be fired when the user has entered values in the textboxes
///  and clicked the Save button to save the values to the database.
/// </Summary>
protected void SaveButton_Click(object sender, EventArgs e)
{
    // Check how many textboxes the user created
    int count = Convert.ToInt32(CountTextBox.Text);

    // Loop through them
    for (int i=0; i < count; i++)
    {
         // Get the TextBox
         TextBox textbox = (TextBox) FindControl("TextBox_" + i);

         // Get the value
         string val = textbox.Text;

         // Insert into DB
         SaveValueToDatabase(val);
    }
]

Method 2

What you have is a 1-to-many relationship between whatever it is that is on that page and the comments or descriptions or whatever. You shouldn’t have this modeled in your database as text_box_1, text_box_2, etc. Instead, it should be:

CREATE TABLE Some_Entity
(
    some_entity_id  INT NOT NULL,
    CONSTRAINT PK_Some_Entity PRIMARY KEY CLUSTERED (some_entity_id)
)
GO

CREATE TABLE Some_Entity_Comments
(
    some_entity_id  INT     NOT NULL,
    comment_number  INT     NOT NULL,
    comments        VARCHAR(1000)   NOT NULL,
    CONSTRAINT PK_Some_Entity_Comments
         PRIMARY KEY CLUSTERED (some_entity_id, comment_number),
    CONSTRAINT FK_Some_Entity_Comments_Some_Entity
         FOREIGN KEY (some_entity_id) REFERENCES Some_Entity (some_entity_id)
)
GO

Once that’s done, you can use code similar to what Mun wrote to handle things on the front end. You’ll just pass the text box index as the comment_number.

Method 3

Have you considered storing the data as an XML field in the database?

If you have 40+ text boxes you could just have a simple schema to store them like:

<YourData>
    <TextBox ID="TextBox1">Value1</TextBox>
    <TextBox ID="TextBox2">Value2</TextBox>
    <TextBox ID="TextBox3">Value3</TextBox>
    ... etc ...
</YourData>

No need for dynamic fields in your DB. You should also store the schema somewhere as well. This is a good way to store dynamic data that can change from record to record.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0