Inserting CheckBoxList selected items to database

julianfraser · November 2nd, 2009, 08:06 PM

I'm trying to accomplish something similar to the Try it out on page 570. Storing Genre Profiles in the Profile.

I need to store these checkbox values in a SQL Server Express DB. I'm working with the DetailsView Inserting/Updating event to fire a sub.

Code:

Protected Sub DetailsView1_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdateEventArgs) Handles DetailsView1.ItemUpdating
        UpdateCategories(ID VALUE)
End Sub

Private Sub UpdateCategories(ByVal thisCompany As Int32)
    Dim thisCBXList As CheckBoxList = CType(FindControl("CheckBoxList1"), CheckBoxList)
    Dim thisSDS As SqlDataSource = CType(FindControl("SqlDataSource4"), SqlDataSource)

    'DELETE ALL EXISTING ITEMS LINKED TO AN ID

     For Each thisCat As ListItem In thisCBXList.Items
        'INSERT FIELD FOR EACH CHECKED ITEM
     Next
End Sub

Searched high and low for an answer on Google and this forum. I must be using bad search terms, because this must be a common issue.

Thanks.

Imar · November 3rd, 2009, 01:45 PM

Hi there,

You can't do this with the SqlDataSource as it's designed to work with a single record at the time. You could loop through your items, modify parameters on the data source and execute the underlying SQL statement. However, if you go that route, you might as well do it the "right" way: with straight ADO.NET code.

With ADO.NET you create Connection and Command objects that target the database directly. For more info:

http://quickstarts.asp.net/Quickstar...wcontents.aspx

http://www.aspfree.com/c/a/ASP.NET/U...ET-20-Pages/3/

I think the Pro ASP.NET 3.5 book has some material on this as well. In addition, Wrox has a book dedicated to ADO.NET.

Hope this helps,

Imar

julianfraser · November 3rd, 2009, 01:50 PM

Thanks Imar,

That would explain why I had so much trouble finding a solution that utilized the SqlDataSource.

I worked a lot with ADO in Classic ASP, so I just need to read up on the differences between Classic and .Net

Imar · November 3rd, 2009, 01:54 PM

In that case, you'll find yourself right at home. A lot of the concepts from ADO are the same in ADO.NET with a better and cleaner API....

Imar

julianfraser · November 4th, 2009, 04:18 PM

Thanks, I've figured everything out now, here is the result. Any advise on better or more efficient ways to do anything, i'd like to hear.

Code:

Private Sub DefaultRedirect()
        Response.Redirect("~/Admin/Page.aspx")
    End Sub

Private Sub UpdateCategories(ByVal thisCompany As Int32)
        Dim myCbl As New CheckBoxList
        myCbl = CType(DetailsView1.FindControl("CheckBoxList1"), CheckBoxList)

        Dim myConnection As SqlConnection
        Dim mySqlCleanup As SqlCommand
        Dim mySqlCommand As SqlCommand

        myConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString())
        mySqlCleanup = New SqlCommand("DELETE FROM CompanyCatagory WHERE CompanyID = @CompanyID", myConnection)
        mySqlCommand = New SqlCommand("INSERT INTO CompanyCatagory (CompanyID, CatagoryID) VALUES (@CompanyID, @CatagoryID)", myConnection)

        myConnection.Open()

        mySqlCleanup.Parameters.Add(New SqlParameter("CompanyID", thisCompany))
        mySqlCleanup.ExecuteNonQuery()

        mySqlCommand.Parameters.AddWithValue("CompanyID", thisCompany)
        mySqlCommand.Parameters.AddWithValue("CatagoryID", 0)

        For Each myItem As ListItem In myCbl.Items
            If myItem.Selected = True Then
                mySqlCommand.Parameters("CatagoryID").Value = myItem.Value
                mySqlCommand.ExecuteNonQuery()
            End If
        Next

        myConnection.Close()
    End Sub


Protected Sub SqlDataSource2_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource2.Inserted
        Dim thisCompany As Int32 = e.Command.Parameters("@NewID").Value

        UpdateCategories(thisCompany)

        DefaultRedirect()
    End Sub

    Protected Sub SqlDataSource2_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource2.Updated
        Dim thisCompany As Int32 = Request.QueryString("id")

        UpdateCategories(thisCompany)

        DefaultRedirect()
    End Sub

Protected Sub CheckBoxList1_DataBound(ByVal sender As Object, ByVal e As System.EventArgs)
        If DetailsView1.DefaultMode = DetailsViewMode.Edit Then
            Dim thisCompany As Int32 = Request.QueryString("id")

            Dim myCbl As New CheckBoxList
            myCbl = CType(DetailsView1.FindControl("CheckBoxList1"), CheckBoxList)

            Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString())
            Dim mySqlCommand As New SqlCommand("SELECT [CatagoryID] FROM CompanyCatagory WHERE [CompanyID] = @CompanyID", myConnection)

            myConnection.Open()

            mySqlCommand.Parameters.Add(New SqlParameter("CompanyID", thisCompany))

            Dim myDataReader As SqlDataReader = mySqlCommand.ExecuteReader

            Dim myItem As New ListItem

            While myDataReader.Read
                myItem = myCbl.Items.FindByValue(myDataReader("CatagoryID").ToString())

                If myItem.Value IsNot Nothing Then
                    myItem.Selected = True
                End If
            End While

            myConnection.Close()
        End If
    End Sub

Imar · November 4th, 2009, 04:43 PM

Hi there,

Looks fine to me except for a few things you can optimize:

Code:

 
Dim myCbl As New CheckBoxList
myCbl = CType(DetailsView1.FindControl("CheckBoxList1"), CheckBoxList)

No need to New up an object if you're going to overwrite it any way. You can replace this code with this:

Code:

 
Dim myCbl As CheckBoxList
myCbl = CType(DetailsView1.FindControl("CheckBoxList1"), CheckBoxList)

or even:

Code:

 
Dim myCbl As CheckBoxList = CType(DetailsView1.FindControl("CheckBoxList1"), CheckBoxList)

You should wrap your database objects in a Using statement. This ensures the objects are properly disposed off when they are out of scope, even whan an error occurs.:

Code:

 
Using myConnection As New SqlConnection( _
           ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString())
  Using mySqlCommand As New SqlCommand( _
         "SELECT [CatagoryID] FROM CompanyCatagory WHERE [CompanyID] = @CompanyID", myConnection)
    myConnection.Open()
    ...
  End Using
  myConnection.Close()
End Using

Hope this helps,

Imar