CODEWITHSUNDEEP
c#asp.net
Tannya
Tannya

Reputation: 171

how to insert multiple values into a table cell in sql server

I have a listbox item in my website with selectionmode as "multilple". I want to insert all the selected items to a column in the table in my sql server when I click the submit button.

This is my listbox items.

<asp:TableCell>
    <asp:ListBox ID="lbLanguagesKnown" runat="server" Height="217px" 
        SelectionMode="Multiple">
        <asp:ListItem Selected="True" Value="-1">-Select Languages-</asp:ListItem>
        <asp:ListItem Value="1">Arabic</asp:ListItem>
        <asp:ListItem Value="2">Bengali</asp:ListItem>
        <asp:ListItem Value="3">English</asp:ListItem>
        <asp:ListItem Value="4">German</asp:ListItem>
        <asp:ListItem Value="5">Hindi</asp:ListItem>
        <asp:ListItem Value="6">Japanese</asp:ListItem>
        <asp:ListItem Value="7">Javanese</asp:ListItem>
        <asp:ListItem Value="8">Mandarin</asp:ListItem>
        <asp:ListItem Value="9">Others</asp:ListItem>
        <asp:ListItem Value="10">Portuguese</asp:ListItem>
        <asp:ListItem Value="11">Russian</asp:ListItem>
        <asp:ListItem Value="12">Spanish</asp:ListItem>
    </asp:ListBox>
</asp:TableCell>

How is that possible? Please help.

Upvotes: 0

Views: 2094

Answers (3)

Ramesh Sivaraman
Ramesh Sivaraman

Reputation: 1305

Combining all the values and putting them into single column is NOT A GOOD IDEA AT ALL... As above answer it does not even meet the 1NF condition for data normalization, result of it would be inefficient disk space usage, slow queries (really messy to get results out from such a table from my understanding). But if you still want to do it you can probably try concatenating the values with a delimeter (, or /) and insert them so that it can be split out if required at a later stage..

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460340

You could use a simple loop:

string sql = "INSERT INTO dbo.Language VALUES(@languageID, @languageName);";
using (var con = new SqlConnection(yourConnectionString))
using(var cmd = new SqlCommand(sql, con))
{
    con.Open();
    foreach (ListItem item in lbLanguagesKnown.Items)
    {
        cmd.Parameters.Clear();
        if (item.Selected)
        {
            cmd.Parameters.AddWithValue("@languageID", int.Parse(item.Value));
            cmd.Parameters.AddWithValue("@languageName", item.Text);
            int insertedCount = cmd.ExecuteNonQuery();
        }
    }
}

I have used sql-parameters to prevent sql-injection. The using-statement is used to ensure that the connection gets closed as soon as possible (even in case of an exception).

However, the requirement makes little sense. Why do you want to insert masterdata when the user selects part of it. Sounds as if you actually want to insert in a different table which links to the language table.

For example:

Table1: UserLanguages with UserID + LanguageID

Table2: Languages with above model

Upvotes: 2

devdigital
devdigital

Reputation: 34369

You likely don't want to do this, as you'll be breaking even first normal form. Instead, it may make more sense to model the relationship as a many-to-many.

In this case you would have a Languages table, an (e.g.) User table, and then a junction table (e.g. LanguagesKnown) which links many languages to many users using two foreign keys for each row in the junction table.

Upvotes: 5

Related Questions