Looping through my CheckBoxList only inserts one record

+1 vote
asked Aug 25, 2011 by jamie

The title says my problem - which is that my checkboxes will only insert one record at a time. The modal pops up and I click on 2 checkboxes, yet only one is inserted into my database and displayed on my page. I have to check boxes 1 at a time, and I have many, many checkboxes. Here is the code I have. :) Thanks in advance

<!-- Add a Feature --> <li> <asp:LinkButton ID="FeatureButton" runat="server">Feature</asp:LinkButton> <asp:Panel ID="FeaturePanel" runat="server" CssClass="modalPopup" Style="display:none"> <asp:CheckBoxList ID="cbxAddFeature" runat="server" DataSourceID="dsNewFeatures" DataTextField="FeatureTitle" DataValueField="FeatureID"></asp:CheckBoxList> <asp:Button ID="SubmitFeatures" runat="server" Text="Submit" /><asp:Button ID="CancelSubmitFeatures" runat="server" Text="Cancel" /> </asp:Panel> <asp:ModalPopupExtender ID="FeatureModal" runat="server" BackgroundCssClass="modalBackground" CancelControlID="CancelSubmitFeatures" DropShadow="True" DynamicServicePath="" Enabled="True" PopupControlID="FeaturePanel" TargetControlID="FeatureButton"></asp:ModalPopupExtender> </li>
Protected Sub SubmitFeatures_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitFeatures.Click FeatureModal.Hide() For Each feature As ListItem In cbxAddFeature.Items If feature.Selected Then 'SQL INSERT: Marketing Table Dim strSQL As String = "INSERT INTO Marketing (ProductID, MarketingTypeID, MarketingTitle, MarketingData) VALUES (@ProductID, 3, 'Feature', @MarketingData)" Using cn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString) Using cmd As New SqlCommand(strSQL, cn) cmd.Parameters.Add(New SqlParameter("@ProductID", ProductID.Value)) cmd.Parameters.Add(New SqlParameter("@MarketingData", feature.Value)) cn.Open() cmd.ExecuteNonQuery() End Using End Using
End If
Next Response.Redirect(Request.RawUrl)
End Sub

2 Answers

+2 votes
answered Aug 25, 2011 by rick-schott

Don't redirect after the first pass in your loop:

For Each feature As ListItem In cbxAddFeature.Items If feature.Selected Then Dim sqlAddFeatures As String = Nothing 'SQL INSERT: Marketing Table sqlAddFeatures = "INSERT INTO Marketing (ProductID, MarketingTypeID, MarketingTitle, MarketingData) VALUES (" & ProductID.Value & ",3, 'Feature', " & feature.Value & ")" sqlAddFeatures.Replace("'", "''") Dim SqlConnection As New SqlConnection("Server=off-db1;uid=productsDB_admin;pwd=*****;database=Products") SqlConnection.Open() Dim sqlCommand As New SqlCommand(sqlAddFeatures, SqlConnection) sqlCommand.ExecuteNonQuery() SqlConnection.Close() 'Response.Redirect(Request.RawUrl) End If Next
0 votes
answered Nov 29 by icarus

jlg, you won't be able to insert all the rows in one shot in SQL. You didn't specify what version of SQL you are using, but in SQL Server 2008 you can pass DataTable parameters to a stored procedure and then insert all records at the same time.

This requires that you create a User-Defined Data Type with the exact same structure as your DataTable. Read here.

Or if you don't want to go that route...

You can at least use transactions

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
...