时间戳

首页 > 解决方案 > 使用存储过程从 ListBox 插入多个值

sql - 使用存储过程从 ListBox 插入多个值

问题描述

我有两个相关的表，第一个称为postaDip(IDpostaDip, CODdip, CODposta, from)，第二个称为cassPosta(IDposta, Desc)。

我正在尝试postaDip通过加载表格的列表框将多行插入表格cassPosta。例如，我想插入与我在列表框中选择的 ID 一样多的多行。我正在尝试使用此代码，如果我在列表框中仅选择一项，即相同的一项，则会重复插入 2 次。如果我选择多个元素，则只输入一个，两次！

标记：

<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"/>

后面的代码：

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

这是存储过程

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

标签： sqlasp.netsql-serverstored-procedures

解决方案

你不需要在DataAdapter这里，它只是导致程序再次执行。您也不需要关闭连接作为Using句柄

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

推荐阅读