Looping code for inserting Checkboxlist values into DB

I am trying to do two things for a test app:
1) Populate a CheckboxList webcontrol by loading a list of subject names (for the DataTextField) and subject codes (for the DataValueField).
2) Respond to user selection by inserting their boolean values based on checked vs. unchecked boxes. I have a SQL Server table with a lot of columns of data type bit. All of the columns are named after different subject codes (such as HSmath, HSast, etc.)

The first step I can do fine - but the second I am have trouble with. How would I write looping code that would insert the status of each checkbox into a field with names after the checkbox's value? Meaning, how would I get HSmath's checked status to be inserted into the HSmath field in my table, and then the HSast's checked status to be inserted into the HSast field, and so on.

I have included my code thus far (removing unnecessary markup), including my looping code for printing out results on the screen - obviously this is what I'd replace with my database code. Any help is appreciated.


Dim strConn as string = ConfigurationSettings.AppSettings("ConnString")

Sub Page_Load(sender AS Object,E as EventArgs)
  If Not IsPostBack Then
    Dim conPubs As SqlConnection
    Dim cmdSelect As SqlCommand
    Dim dtrSubjects As SqlDataReader


    conPubs = New SqlConnection(strConn)
    cmdSelect = New SqlCommand( "Select * From tutors_subjectcodes order by Subject", conPubs )
    conPubs.Open()
    dtrSubjects = cmdSelect.ExecuteReader()

    check1.DataSource = dtrSubjects
    check1.DataTextField = "Subject"
    check1.DataValueField = "subjectcode"
    check1.DataBind()

    dtrSubjects.Close()
    conPubs.Close()
  End If
End Sub

Sub btnStep2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
   Dim msg As String
   Dim li As ListItem
   msg = ""
   For Each li In check1.Items
      If li.Selected = True Then
         msg = msg & "<li> " & li.Text & "&nbsp;(" & li.Value & ")</li><br>"
      End If
   Next
   lblSubjects.Text = "<br><b>Selected:<br></b>[list]" & msg & "</ul>"
End Sub


                <asp:checkboxlist id="check1" runat="server" RepeatColumns="4"></asp:checkboxlist>
        <br>
                <asp:Button id="btnStep2" onclick="btnStep2_Click" runat="server" Text="Submit"></asp:Button>
        <br>
                <asp:label id="lblSubjects" runat="server"></asp:label>

Dim ckbxlt1 As CheckBoxList = PlaceHolder1.FindControl("checkboxlistID")
Label1.Text = ckbxlt1.SelectedValue

Try that. And look at the posts at the end of this discussion.

http://p2p.wrox.com/topic.asp?TOPIC_ID=9408