Tim Tim - 1 year ago 54
SQL Question

How do I cycle through a CheckBoxList and submit values to the database for each checked value?

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

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:

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
cmd.Parameters.AddWithValue("@interestid", Item.Value)
cmd.ExecuteNonQuery() 'for updating database'
lblmessage.Text = "Successfull"
lblmessage.Text = "Error"
End If
End Sub

Here's the aspx page markup:

<asp:CheckBoxList ID="chkboxinterest" runat="server" RepeatColumns="3"
<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" />

Answer Source

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

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.

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

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

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

Remove cmd.Parameters.Clear()

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

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

Third, perform some input validation on 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)

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


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