Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

FREE 12 month online training for ASP.NET & MS Expression Studio and a Free copy of MS Expression Web with Windows Server Purchase
How to insert multiple selected items of ListBox into SQL Server database in ASP.NET

Author: Nikki Smith
Download Source Code : 1383_InsertMultipleRecordsFromListBox.zip

In this article, you will learn how to insert multiple selected ListBox items into SQL Server database in ASP.NET.

 

ListBox control SelectionMode Property can be set to “Multiple” to select multiple items. You may need to store multiple selected items of ListBox control to SQL Server database. For this task, you have to loop through the ListBox items to get selected item. There are different ways to loop ListBox items and there are also different ways to store these selected items to SQL Server database. In this article, I will explain a simple way to loop through ListBox control, get selected items and insert these items to SQL Server database.

 

  1. Create a new Empty Web Site in Visual Studio 2010 either in Visual Basic or Visual C#.
  2. Add a Web Form in the Web Site and add TextBox, ListBox, Button and Label controls in the Web Form

    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
    <br />
    <asp:ListBox ID="ListBox1" runat="server" SelectionMode="Multiple"
        Height="100px" Width="90px">
        <asp:ListItem>USA</asp:ListItem>
        <asp:ListItem>UK</asp:ListItem>
        <asp:ListItem>Germany</asp:ListItem>
        <asp:ListItem>France</asp:ListItem>
        <asp:ListItem>Canada</asp:ListItem>
    </asp:ListBox>
    <br />
    <asp:Button ID="Button1" runat="server" Text="Insert" onclick="Button1_Click" />
    <br />
    <asp:Label ID="Label1" runat="server"></asp:Label>
     
  3. Namespaces used in the code

    Visual Basic
     
    Imports System.Data.SqlClient
    Imports System.Data
     
    Visual C#
     
    using System.Data.SqlClient;
    using System.Data;
     
  4. Write below code in Button Click event to get selected ListBox items and insert into SQL Server database.

    In below code, I have written a query to insert data into CountryVisited table of Sample database. I have established my connection to SQL Server, created a SqlCommand object and added parameters to command object. Value of CountryVisited parameter is the text we will enter in TextBox. I have used a “For Each” loop to loop through the ListBox and get the selected item. The selected item will be the value of CountryVisited parameter. I have called the ExecuteNonQuery() method for each selected value.
     
    Visual Basic
     
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        Dim insertText As String = "INSERT INTO CountryVisited(PersonName, CountryVisited) Values(@PersonName, @CountryVisited)"
        Dim connString As String = "Data Source=YourServer;Initial Catalog=Sample;Integrated Security=True"
        Dim conn As New SqlConnection(connString)
        Dim cmd As New SqlCommand(insertText, conn)
        conn.Open()
     
        cmd.Parameters.Add("@PersonName", SqlDbType.NChar, 20)
        cmd.Parameters("@PersonName").Value = TextBox1.Text
        cmd.Parameters.Add("@CountryVisited", SqlDbType.NChar, 10)
     
        For Each item As ListItem In ListBox1.Items
            If item.Selected Then
               Try
                    cmd.Parameters("@CountryVisited").Value = item.Text
                    cmd.ExecuteNonQuery()
                    Label1.Text = "Data Inserted"
                Catch ex As Exception
                    Label1.Text = ex.Message
                End Try
            End If
        Next
        conn.Close()
    End Sub
     
    Visual C#
     
    protected void Button1_Click(object sender, EventArgs e)
    {
        string insertText = "INSERT INTO CountryVisited(PersonName, CountryVisited) Values(@PersonName, @CountryVisited)";
        string connString = "Data Source=YourServer;Initial Catalog=Sample;Integrated Security=True";
        SqlConnection conn = new SqlConnection(connString);
        SqlCommand cmd = new SqlCommand(insertText, conn);
        conn.Open();
     
        cmd.Parameters.Add("@PersonName", SqlDbType.NChar, 20);
        cmd.Parameters["@PersonName"].Value = TextBox1.Text;
        cmd.Parameters.Add("@CountryVisited", SqlDbType.NChar, 10);
     
        foreach(ListItem item in ListBox1.Items)
        {
            if(item.Selected)
            {
                try
                {
                    cmd.Parameters["@CountryVisited"].Value = item.Text;
                    cmd.ExecuteNonQuery();
                    Label1.Text = "Data Inserted";
                }
                catch (Exception ex)
                {
                    Label1.Text = ex.Message;
                }
            }
        }
        conn.Close();
    }
     
  5. Now you can see website in your browser
   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to convert an image to icon in ASP.NET

Disclaimer - Privacy
© 2002-2014 DevASP.net