Insert multiple values from ListBox with stored procedure-CodePudding

I have two related tables, the first called postaDip (IDpostaDip, CODdip, CODposta, from) and the second called cassPosta (IDposta, Desc).

I am trying to insert multiple rows into the postaDip table through a listbox that loads the table cassPosta. For example I would like to insert multiple lines of as many lines as IDs I select in the listbox. With this code I am trying, if I select only one item in the listbox, the same one, it is inserted repeatedly 2 times. If I select multiple elements, only one is entered, twice!

Markup:

<asp:DropDownList ID="sel_dip" CssClass="chzn-select" Width="50%" 
     runat="server" DataSourceID="SqlDataSource1" 
     DataTextField="nomecogn" DataValueField="IDdipendenti" 
     ValidateRequestMode="Enabled">
    <asp:ListItem Text="Seleziona Dip" Value=""></asp:ListItem>
</asp:DropDownList>

<asp:TextBox runat="server" id="sel_data" CssClass="form-control" clientidmode="static" Width="20%" ></asp:TextBox>
<asp:ListBox ID="ListBox1" runat="server" SelectionMode="Multiple" AppendDataBoundItems="true" DataSourceID="SqlDataSource2" DataTextField="Desc" DataValueField="IDposta" ></asp:ListBox>
<asp:button ID="btnAssPc" runat="server" OnClick="btnAssPc_Click"/>

Code behind:

Protected Sub ass_postaDip()

  For Each selectedItem As Object In ListBox1.SelectedValue

            Dim cmdText As String = "Sp_ass_postaDip2"
            Dim postaid As Integer = Int32.Parse(selectedItem.ToString())
    
            Using Myconnection As New SqlConnection(SqlContConnStrinG), command As New SqlCommand(cmdText, Myconnection), da As New SqlDataAdapter(command)

                Myconnection.Open()
                command.CommandType = CommandType.StoredProcedure
                command.Parameters.Add("@CodDip", SqlDbType.Int).Value = sel_dip.Text
                command.Parameters.Add("@CodPosta", SqlDbType.Int).Value = postaid
                command.Parameters.Add("@Data", SqlDbType.Date).Value = sel_data.Text
                command.ExecuteNonQuery()
                Dim dst As New DataSet
                da.Fill(dst)

                Myconnection.Close()
            End Using
        Next   
    End Sub

This is the stored procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Sp_ass_postaDip2] 
    @CodDip int,
    @CodPosta int,
    @Data date
AS
BEGIN
    SET NOCOUNT OFF;

    INSERT INTO postaDip (CODdip, CODposta, from)
    VALUES (@CodDip, @CodPosta, @Data);
END

CodePudding user response：

You don't need a DataAdapter here, it's just causing the procedure to get executed again. You also don't need to close the connection as the Using handles that

Protected Sub ass_postaDip()

  For Each selectedItem As Object In ListBox1.SelectedValue

            Dim cmdText As String = "Sp_ass_postaDip2"
            Dim postaid As Integer = Int32.Parse(selectedItem.ToString())
    
            Using Myconnection As New SqlConnection(SqlContConnStrinG), command As New SqlCommand(cmdText, Myconnection)

                command.CommandType = CommandType.StoredProcedure
                command.Parameters.Add("@CodDip", SqlDbType.Int).Value = sel_dip.Text
                command.Parameters.Add("@CodPosta", SqlDbType.Int).Value = postaid
                command.Parameters.Add("@Data", SqlDbType.Date).Value = sel_data.Text
                Myconnection.Open()
                command.ExecuteNonQuery()
            End Using
        Next   
    End Sub

CodePudding user response：

you have a great routine posted.

Just keep in mind that you have two values in a list box. The dispay, value (text), and then the other hidden value (often the PK value).

Just STRONG TYPE the posted routine, and you get this:

        selectedItem.Text
        selectedItem.Value

So:

    For Each selectedItem As ListItem In ListBox1.Items

        If selectedItem.Selected Then

            Dim cmdText As String = "Sp_ass_postaDip2"

            Using Myconnection As New SqlConnection(SqlContConnStrinG), command As New SqlCommand(cmdText, Myconnection)
                command.CommandType = CommandType.StoredProcedure
                command.Parameters.Add("@CodDip", SqlDbType.Int).Value = sel_dip.Text
                command.Parameters.Add("@CodPosta", SqlDbType.Int).Value = selectedItem.Value
                command.Parameters.Add("@Data", SqlDbType.Date).Value = sel_data.Text
                Myconnection.Open()
                command.ExecuteNonQuery()

            End Using
        End If

    Next

So, note how you can get/grab .Value, or .Text

The original code would be fine if you don't allow multiple rows - but you do.