Inserting multiple items from a listbox into a database

Discussion in 'ASP.NET / ASP.NET Core' started by quazter, Sep 6, 2011.

Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.
  1. quazter

    quazter

    I have the following code for a multiselect list box as a template within a detailsview. The form works fine, but only the first item selected gets inserted into the database. How can I insert all the items selected in a comma separated format in vb. Also, I don't know how to access the codebehind for the insert link on the detailsview.

    <asp:ListBox ID="InstrumentCGBand" runat="server" AppendDataBoundItems="True" SelectionMode="Multiple"
    SelectedValue='<%# Bind("InstrumentColorGuardBand") %>' Height="200px"
    CausesValidation="true" DataTextField="InstrumentColorGuardBand"
    DataValueField="InstrumentColorGuardBand"
    onselectedindexchanged="InstrumentCGBand_SelectedIndexChanged">
    <asp:ListItem>Select All That Apply</asp:ListItem>
    <asp:ListItem>Marching Band-Flute</asp:ListItem>
    <asp:ListItem>Marching Band-Clarinet</asp:ListItem>
    <asp:ListItem>Marching Band-Alto Sax</asp:ListItem>
    <asp:ListItem>Marching Band-Tenor Sax</asp:ListItem>
    </asp:ListBox>

    There are several other fields that get inserted as well using the SQL below:

    <asp:AccessDataSource ID="MarchingBand" runat="server"
    DataFile="~/App_Data/CHSMarchingBand.accdb"
    DeleteCommand="DELETE FROM [MarchingBandStudents] WHERE [ID] = ?"
    InsertCommand="INSERT INTO [MarchingBandStudents] ([LastName], [FirstName], [Grade], [InstrumentColorGuardBand], [StudentEMail], [ParentEMail], [PhoneNumber], [TShirtSize], [StreetAddress1], [City], [State], [Zip], [MiddleSchool]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
    SelectCommand="SELECT [ID], [LastName], [FirstName], [Grade], [InstrumentColorGuardBand], [StudentEMail], [ParentEMail], [PhoneNumber], [TShirtSize], [StreetAddress1], [City], [State], [Zip], [MiddleSchool] FROM [MarchingBandStudents]"

    UpdateCommand="UPDATE [MarchingBandStudents] SET [LastName] = ?, [FirstName] = ?, [Grade] = ?, [InstrumentColorGuardBand] = ?, [StudentEMail] = ?, [ParentEMail] = ?, [PhoneNumber] = ?, [TShirtSize] = ?, [StreetAddress1] = ?, [City] = ?, [State] = ?, [Zip] = ?, [MiddleSchool] = ? WHERE [ID] = ?">
    <DeleteParameters>
    <asp:parameter Name="ID" Type="Int32" />
    </DeleteParameters>
    <InsertParameters>
    <asp:parameter Name="LastName" Type="String" />
    <asp:parameter Name="FirstName" Type="String" />
    <asp:parameter Name="Grade" Type="String" />
    <asp:parameter Name="InstrumentColorGuardBand" Type="String" />
    <asp:parameter Name="StudentEMail" Type="String" />
    <asp:parameter Name="ParentEMail" Type="String" />
    <asp:parameter Name="PhoneNumber" Type="String" />
    <asp:parameter Name="TShirtSize" Type="String" />
    <asp:parameter Name="StreetAddress1" Type="String" />
    <asp:parameter Name="City" Type="String" />
    <asp:parameter Name="State" Type="String" />
    <asp:parameter Name="Zip" Type="String" />
    <asp:parameter Name="MiddleSchool" Type="String" />
    </InsertParameters>
    <UpdateParameters>
    <asp:parameter Name="LastName" Type="String" />
    <asp:parameter Name="FirstName" Type="String" />
    <asp:parameter Name="Grade" Type="String" />
    <asp:parameter Name="InstrumentColorGuardBand" Type="String" />
    <asp:parameter Name="StudentEMail" Type="String" />
    <asp:parameter Name="ParentEMail" Type="String" />
    <asp:parameter Name="PhoneNumber" Type="String" />
    <asp:parameter Name="TShirtSize" Type="String" />
    <asp:parameter Name="StreetAddress1" Type="String" />
    <asp:parameter Name="City" Type="String" />
    <asp:parameter Name="State" Type="String" />
    <asp:parameter Name="Zip" Type="String" />
    <asp:parameter Name="MiddleSchool" Type="String" />
    <asp:parameter Name="ID" Type="Int32" />
    </UpdateParameters>
    </asp:AccessDataSource>
     
    quazter, Sep 6, 2011
    #1
  2. wisemx

    wisemx

    wisemx, Sep 6, 2011
    #2
  3. quazter

    quazter

    The example shows how to insert to multiple rows. How do I insert into the same row in comma separated format?
     
    quazter, Sep 6, 2011
    #3
  4. quazter

    quazter

    I have no idea how to put the example code in code behind so it works with my form. Please help. I am trying to use the following code behind to see if I can get a value to insert into the db for the multi select listbox but it keeps telling me that the variable InstrumentCGBand is not declared, even though I am using it as a template control in my detailsview:

    codebehind:

    Protected Sub DetailsView1_ItemInserting(sender As Object, e As EventArgs) Handles DetailsView1.ItemInserting
    Dim tempArray(10) As Integer
    Dim instrumentCGBandstr As String
    tempArray = InstrumentCGBand.GetSelectedIndices()
    For Each i As Integer In tempArray
    instrumentCGBandstr = (Convert.ToString(InstrumentCGBand.Items(i)))
    Next
    End Sub
     
    quazter, Sep 6, 2011
    #4
  5. RayH

    RayH DiscountASP.NET Lackey DiscountASP.NET Staff

    You are trying to access a control in your template field, and the only work around that I know of is to create a copy of the control. Here's some sample code:

    Code:
        Protected Sub DetailsView1_ItemInserting(sender As Object, e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles DetailsView1.ItemInserting

        Dim tempArray(10) As Integer
        Dim instrumentCGBandstr As String

        Dim tempControl As ListBox = CType(DetailsView1.FindControl("InstrumentBandCG"), ListBox)

        tempArray = tempControl.GetSelectedIndices()

        For Each i As Integer In tempArray
            instrumentCGBandstr = (Convert.ToString(tempControl.Items(i)))
        Next

    End Sub
     
    RayH, Sep 8, 2011
    #5
  6. quazter

    quazter

    got it, that works! thanks
     
    quazter, Sep 8, 2011
    #6
  7. wisemx

    wisemx

    Great teamwork ;-)
     
    wisemx, Sep 9, 2011
    #7
Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.

Share This Page