entering values into database from 'unlimited' dynamic controls

Question

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?

Answer 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);
    }
]

Answer 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.

Answer 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.

Answer 4

Mun, it's giving me the error of Object Reference Not Set at this line:

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

seems like it cannot find the control of the textbox.