0

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.

4
  • 2
    FYI, Tables have columns, not cells. Cells are found in spreadsheets and prisons. Commented Jul 9, 2013 at 11:26
  • sorry. I corrected it. Commented Jul 9, 2013 at 11:28
  • Actually i have many other controls like textboxes,calendar controls,fileupload and so on. I have a submit button. On click of that i want to insert all these values to the user table. Since the listbox items are many in number, i am stuck up other. Commented Jul 9, 2013 at 11:30
  • Should i create another table and link these two tables using foreign key relations. Is that the only way? Commented Jul 9, 2013 at 11:33

3 Answers 3

5

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.

Sign up to request clarification or add additional context in comments.

1 Comment

+1, but I don't think the OP is close to being able to design a database at this point.
2

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

1 Comment

I want to insert only the user selected values to the table. Also there are many other controls also like textboxes which accept username,password,DOB, file upload and download controls. I have a submit button in the page. When i click on that, i want to insert all these values to the user table.
0

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.