如何遍历CheckBoxList并为每个选中的值向数据库提交值？

I am trying to insert checkboxlist values into SQL Server using a stored procedure. I'm getting this error

我试图使用存储过程将checkboxlist值插入SQL Server。我收到了这个错误

Item In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.

项目为了评估索引属性,必须限定属性,并且必须由用户显式提供参数。

Here's my stored procedure:

这是我的存储过程:

ALTER PROCEDURE [dbo].[usp_insertquestionnarie] 
    (@interestid int)

    INSERT INTO [a_abacus].[dbo].[joininterest]
    VALUES (@interestid)

Here's my vb code:

这是我的vb代码:

 Protected Sub cmdsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdsubmit.Click

        Dim strConnString As String = WebConfigurationManager.ConnectionStrings("a_abacus").ConnectionString
        Dim con As New SqlConnection(strConnString)
        Dim cmd As New SqlCommand("usp_insertquestionnarie", con) 'references connectionstring and calls store procedure'
        cmd.CommandType = Data.CommandType.StoredProcedure 'sets default to be stored procedure'
        lblmessage.Text = String.Empty

        Dim Item As ListItem
        lblMessage.Text = ""
        For Each Item In chkboxinterest.Items
            If Item.Selected = True Then
                con.Open()
                cmd.Parameters.AddWithValue("@interestid", Item.Value)
                cmd.ExecuteNonQuery() 'for updating database'
                lblmessage.Text = "Successfull"
                cmd.Parameters.Clear()
            Else
                lblmessage.Text = "Error"
            End If
            con.Close()
        Next
    End Sub

Here's the aspx page markup:

这是aspx页面标记:

 <asp:CheckBoxList ID="chkboxinterest" runat="server" RepeatColumns="3" 
            Width="650px">
       <asp:ListItem class="chkbox" runat="server" Text="Aerobics" Value="1" />
       <asp:ListItem class="chkbox" runat="server" Text="Antiques & Collectibles" Value="2" />
       <asp:ListItem class="chkbox" runat="server" Text="Arts & Crafts" Value="3" />
       <asp:ListItem class="chkbox" runat="server" Text="Astronomy" Value="4" />
       <asp:ListItem class="chkbox" runat="server" Text="Auto Racing" Value="5" />
    </asp:CheckBoxList>

1 个解决方案

#1

First, there is no need to add parameters to your SQL Command so often.

首先,不需要经常向SQL命令添加参数。

cmd.Parameters.AddWithValue("@interestid", Item.Value)

You need to change that.

你需要改变它。

Before your loop, add the parameter (without value) to the SqlCommand. However, declare it as type SQLDbType.int.

在循环之前,将参数(没有值)添加到SqlCommand。但是,将其声明为SQLDbType.int类型。

cmd.Parameters.Add("@interestid", SQLDBType.Int)

Then, in your loop, you just need to change the value of the SQL Command.

然后,在循环中,您只需要更改SQL命令的值。

cmd.Parameters("@interestid").Value = Item.Value

Remove cmd.Parameters.Clear()

Second, Remove con.Open() and con.Close() from your loop.

其次,从循环中删除con.Open()和con.Close()。

You only need to open the connection before the loop, and close it after the loop.

您只需要在循环之前打开连接,并在循环之后关闭它。

Third, perform some input validation on Item.Value

第三,在Item.Value上执行一些输入验证

Is it really in the range that you think it is? Either use a RangeValidator in your markup, or perform some custom logic in the code-behind. At the very least, do this: CInt(Item.Value)

它真的在您认为的范围内吗?在标记中使用RangeValidator,或在代码隐藏中执行一些自定义逻辑。至少,这样做:CInt(Item.Value)

Fourth, your Alter Procedure statement is missing the required AS keyword

第四,您的Alter Procedure语句缺少必需的AS关键字

http://msdn.microsoft.com/en-us/library/ms189762.aspx

Alter MyProc (@param int) **AS** Select * from Products

#1