Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 3.5 > BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3
This is the forum to discuss the Wrox book Beginning ASP.NET 3.5: In C# and VB by Imar Spaanjaars; ISBN: 9780470187593
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 2nd, 2009, 07:06 PM
Authorized User
 
Join Date: Sep 2003
Location: Orange County, CA, USA.
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to julianfraser Send a message via MSN to julianfraser Send a message via Yahoo to julianfraser
Default Inserting CheckBoxList selected items to database

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.
Reply With Quote
  #2 (permalink)  
Old November 3rd, 2009, 12:45 PM
Imar's Avatar
Wrox Author
Points: 66,836, Level: 100
Points: 66,836, Level: 100 Points: 66,836, Level: 100 Points: 66,836, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 16,161
Thanks: 65
Thanked 1,394 Times in 1,374 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
Reply With Quote
  #3 (permalink)  
Old November 3rd, 2009, 12:50 PM
Authorized User
 
Join Date: Sep 2003
Location: Orange County, CA, USA.
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to julianfraser Send a message via MSN to julianfraser Send a message via Yahoo to julianfraser
Default

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
Reply With Quote
  #4 (permalink)  
Old November 3rd, 2009, 12:54 PM
Imar's Avatar
Wrox Author
Points: 66,836, Level: 100
Points: 66,836, Level: 100 Points: 66,836, Level: 100 Points: 66,836, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 16,161
Thanks: 65
Thanked 1,394 Times in 1,374 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
Reply With Quote
  #5 (permalink)  
Old November 4th, 2009, 03:18 PM
Authorized User
 
Join Date: Sep 2003
Location: Orange County, CA, USA.
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to julianfraser Send a message via MSN to julianfraser Send a message via Yahoo to julianfraser
Default

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
Reply With Quote
  #6 (permalink)  
Old November 4th, 2009, 03:43 PM
Imar's Avatar
Wrox Author
Points: 66,836, Level: 100
Points: 66,836, Level: 100 Points: 66,836, Level: 100 Points: 66,836, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 16,161
Thanks: 65
Thanked 1,394 Times in 1,374 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
check n uncheck items in checkboxlist in datalist moorejkrkumar ASP.NET 1.0 and 1.1 Basics 1 April 17th, 2007 01:39 PM
Check all Checkboxlist items on client-side avilren ASP.NET 1.0 and 1.1 Basics 2 July 9th, 2006 10:40 AM
Cannot have multiple items selected in a DropDownL Ron Howerton ASP.NET 2.0 Basics 2 May 18th, 2006 04:59 AM



All times are GMT -4. The time now is 10:12 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.