This demonstration will populate a ASP.NET checkbox control, check the matching records saved in a database and then save any changes made by the user
back to the database
'This first populates a checkboxlist control with a list of records
'then loops back through the control and checks the checkboxes which have been saved to the database
Public Sub BindAlertsList()
'This creates the list of checkboxes for the alert events types
Dim strSQLConn As SqlConnection
Dim cmd As SqlCommand
Dim objReader As SqlDataReader
Dim list_id As Integer = CInt(lbllist_id.Text)
strSQLConn = New SqlConnection(ConfigurationManager.ConnectionStrings("Tearsheet_ConString").ConnectionString)
cmd = New SqlCommand("SELECT ' ' + [AlertEvent] AS AlertEvent, [alert_id] FROM SavedCompanyListAlertValues ORDER BY AlertEvent", strSQLConn)
strSQLConn.Open()
cblAlertTypes.DataSource = cmd.ExecuteReader(CommandBehavior.CloseConnection)
cblAlertTypes.DataTextField = "AlertEvent"
cblAlertTypes.DataValueField = "alert_id"
cblAlertTypes.DataBind()
'Dim cmd As SqlCommmand
cmd = New SqlCommand("SELECT * FROM SavedCompanyListAlertTypes WHERE list_id=" & list_id, strSQLConn)
strSQLConn.Open()
objReader = cmd.ExecuteReader()
While objReader.Read()
Dim currentCheckBox As ListItem = cblAlertTypes.Items.FindByValue(objReader("alert_id").ToString())
If Not (currentCheckBox Is Nothing) Then
currentCheckBox.Selected = True
End If
End While
strSQLConn.Close()
End Sub
'This saves the checked records in the database
Sub SaveAlerts_Click(ByVal sender As Object, ByVal e As System.EventArgs)
'The first step in the save alerts process is to delete all of the exising ones for the selected company list
DeleteAlertTypes()
End Sub
Sub DeleteAlertTypes()
'The first step in the save alerts process is to delete all of the exising ones for the selected company list
Dim strSQLConn As SqlConnection
Dim cmd As SqlCommand
Dim SqlText As String
Dim intlist_id As Integer = CInt(lbllist_id.Text)
strSQLConn = New SqlConnection(ConfigurationManager.ConnectionStrings("Tearsheet_ConString").ConnectionString)
SqlText = "p_SavedCompanyList_Delete_AlertTypes"
cmd = New SqlCommand(SqlText)
cmd.Connection = strSQLConn
cmd.CommandType = CommandType.StoredProcedure
'input parameters for the sproc
cmd.Parameters.Add("@list_id", SqlDbType.Int).Value = intlist_id
Try
strSQLConn.Open()
cmd.Connection = strSQLConn
cmd.ExecuteNonQuery()
Finally
strSQLConn.Close()
End Try
'Now that the existing alert types are deleted, we can add the new ones
SaveAlertTypes()
End Sub
Sub SaveAlertTypes()
Dim strSQLConn As SqlConnection
Dim cmd As SqlCommand
Dim SqlText As String
Dim intlist_id As Integer = CInt(lbllist_id.Text)
'Make the lblError label invisible in case the user is resubmitting
lblError.Visible = False
strSQLConn = New SqlConnection(ConfigurationManager.ConnectionStrings("Tearsheet_ConString").ConnectionString)
SqlText = "p_SavedCompanyList_Save_AlertTypes"
Try
strSQLConn.Open()
For Each Item As ListItem In cblAlertTypes.Items
If (Item.Selected) Then
cmd = New SqlCommand(SqlText)
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = strSQLConn
'input parameters for the sproc
cmd.Parameters.Add("@list_id", SqlDbType.Int).Value = intlist_id
cmd.Parameters.Add("@alertevents", SqlDbType.Int).Value = Item.Value
cmd.ExecuteNonQuery()
End If
Next
Finally
strSQLConn.Close()
lblSaveAlerts.Visible = True
End Try
End Sub
<asp:CheckboxList ID="cblAlertTypes" runat="server"
CssClass="smalltextNoUnderline" BorderStyle="Solid" BorderWidth="2px">
</asp:CheckboxList>
CREATE PROCEDURE [dbo].[p_SavedCompanyList_Delete_AlertTypes]
@list_id int
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM
SavedCompanyListAlertTypes
WHERE
list_id = @list_id
END
CREATE PROCEDURE p_SavedCompanyList_Save_AlertTypes
@list_id int,
@alertevents int
AS
BEGIN
INSERT INTO SavedCompanyListAlertTypes
(list_id, alertevents)
VALUES
(@list_id, @alertevents)
END
GO