Tim Tim - 2 months ago 8
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
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:

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

Answer

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

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

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