Question Updating a checkboxlist on web page from sql database

chenschel

New member
Joined
Apr 10, 2013
Messages
1
Programming Experience
Beginner
Hi
Hope I'm posting this is right place - have not used this forum before.

I need to update a checkboxlist on a web page from a sql database.
I have 3 tables:

Dogs Table:
DogID - primary Key
DogName,
DogSex, etc.

Breeds Table:
DogBreedID - primary Key
Breed (Irish Terrier, Great Dane, etc)

Then because a dog can be more than one breed (mixed), one more table between the Dogs and Breeds tables that contains DogID from Dogs table and DogBreedID from Breeds table, along with primary key Dog_BreedID.

Dog_Breed Table:
Dog_BreedID - primary Key
FKDogID - links to Dogs table
FKDogBreedID - links to Breeds table

When the user updates a particular Dogs information, I need to have the checkboxlist populated with all the dog breeds from the Breeds table which it does, but also I need the breeds that belong to that particular dog - checked, in case user wishes to change the breeds of the dog.
No problem with listing all the breeds.
Problem comes in when I try to compare the DogBreedID in the checkboxlist to the Dogs breeds that I select in the Reader.
Can not get value of the DogBreedID in the checkboxlist.

Here is code trying to use for this:
The checkboxlist on the page is selecting all the dog breeds and this works fine:
VB.NET:
<asp:CheckBoxList ID="CheckBoxList1" runat="server" 
           DataSourceID="SqlDataSource1" DataTextField="Breed" DataValueField="DogBreedID" 
           RepeatColumns="4">
  </asp:CheckBoxList>
   <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
       ConnectionString="<%$ ConnectionStrings:myConnectionString5 %>" 
         SelectCommand="SELECT [DogBreedID], [Breed] FROM [DogBreeds] ORDER BY [DogBreedID]">
     </asp:SqlDataSource>

vb code for page to check the particular dogs breeds in checkboxlist:
VB.NET:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not IsPostBack Then
               Dim vDogID = 44 ' Dog where 2 breeds should be checked in checkboxlist
               PopulateBreeds(vDogID)
        End If
   End Sub
 
   Private Sub PopulateBreeds(ByVal vDogID As Integer)
       Dim conn As SqlConnection = Dogs.MyConnection
       Dim cmd As New SqlCommand()

  ' Getting the DogBreedIDs for the particular Dog.
       cmd.CommandText = "select DogBreeds.DogBreedID, Breed FROM Dog_DogBreeds INNER JOIN DogBreeds ON Dog_DogBreeds.FKDogBreedID = DogBreeds.DogBreedID INNER JOIN  Dogs ON Dog_DogBreeds.FKDogID = Dogs.DogID WHERE dogs.DogID = " & vDogID & ""
       cmd.Connection = conn
       conn.Open()
       Dim reader As SqlDataReader = cmd.ExecuteReader()
 
         ' The next line is not working - how do I get the DogBreedID from the checkboxlist1 so
         ' I can compare it to the readers DogBreedID from the above select statement?
       Dim chk1 As CheckBoxList = CType(FindControl("CheckBoxList1"), CheckBoxList)
 
       Dim num As Integer = (chk1.Items.Count) - 1
       While reader.Read
 
             ' I know the connection and reader are working cause I can see the DogBreedID in the 
             ' vDogBreedID variable in next line. 
           Dim vDogBreedID = reader.GetValue(0)
           Dim i As Integer
           For i = 0 To num
                 ' Comparing the DogBreedID in reader to the DogBreedID in the CheckBoxList1 and if equal
                 ' mark as checked.
               If reader(0) = chk1.Items(i).Value Then
                   chk1.Items(i).Selected = True
               End If
           Next
       End While
   End Sub

Thanks in advance for any help
Cindy
 
Last edited:
Back
Top