Stone Coast Web Design Blog

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