I have a checkbox list which is used in a many to many table. The checkbox list in this case displays all of the sites that are associated with a certain contract, and is contained within a separate detailsview allowing editing of the other details about the contract. Upon clicking insert I want to update the many to many table with any new sites selected, or remove any sites that were unselected. In order to do this I have created 4 stored procedures (SP)

<asp:ObjectDataSource ID="SiteContractDataSource" runat="server"
    SelectMethod="GetContractSites"
    InsertMethod="InsertContractSite"
    DeleteMethod="DeleteContractSite"
    TypeName="SiteDB">
        <SelectParameters>
            <asp:QueryStringParameter Name="contractRef" QueryStringField="contractRef" Type="String" />
            <asp:Parameter Name="sortExpression" Type="String" />
        </SelectParameters>
        <InsertParameters>
            <asp:QueryStringParameter Name="contractRef" QueryStringField="contractRef" Type="String" />
            <asp:Parameter Name="SiteID" Type="String" />
        </InsertParameters>
        <DeleteParameters>
            <asp:QueryStringParameter Name="contractRef" QueryStringField="contractRef" Type="String" />
            <asp:Parameter Name="SiteID" Type="String" />
        </DeleteParameters>
    </asp:ObjectDataSource>

The main select simply populates the checkbox list and is working fine, but I have yet to integrate the insert and delete SP. There is another select SP which I creaeted to check whether the site and contract already exist within the table - I'm not sure if this is something that could be done with the existing select query.

Here is my code behind so far:

1    protected void ContractDetails_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)
2        {
3            CheckBoxList selectSites = new CheckBoxList();
4    
5            foreach (ListItem item in selectSites.Items)
6            {
7                if (item.Selected)
8                {
9                    //item selected therefore check not already added, if not add
10               }
11               else
12               {
13                   //item not selected, therefore check and if present in DB delete it, else do nothing
14               }
15           }
16       }

So at the moment all it does is catch the update on the detailsview and then iterates through the ListItems in the checkBox. My question are these: How do I call the stored procedures I've created passing them the current ListItem (if it's selected)? How do I use the select SP (either of them) to make sure it doesn't already exist in the DB before performing the correct operation, and is it possible to prevent the detailsView update from continuing if this doesn't work? I think that pretty much covers all problems I might encounter, I think ;-)

Could anyone lend a hand with this? Please shout if i'm not providing enough clarity. Cheers! Daf 

Hi,

Based on my understanding, you want to know how to use the DataSource control in the code-behind. If I have misunderstood you, please feel free to let me know.

We can update, insert, select, delete the data in the code-behind with ObjectDataSource. To do this, we need to set the parameters and call the Update, Insert, Select, Delete  method. For example, we want to update data  when the item of CheckBoxList is Selected:

        ObjectDataSource1.UpdateParameters.Add(new Parameter());
        int iResult = ObjectDataSource1.Update();
        if (iResult > 0)
        {
            //Update successfully
        }
        else
        {
            //Update unsuccessfully
        }

We can add another ObjectDataSource to the page and use this control to call the SP that you created to check whether the site and contract already exist within the table. We need to set the parameters for this SP in the code-behind and then call the Select method:

        ObjectDataSource1.SelectParameters.Add(new Parameter());
        DataView dv = (DataView)ObjectDataSource1.Select();
        if (dv != null && dv.Count > 0)
        {
            //The data already exists.  
        }
        else
        {
            //The data doesn't exist.
        }

For more information about  parameterized DataSource, see http://www.asp.net/learn/data-access/tutorial-48-cs.aspx.

I hope this helps.