InsertCommand not triggered

[Terri-Lynn]

Hi all:
I must be doing something wrong in the settings as I can't seem to figure out why my listbox isn't auto updating.  I've used this demo to help me on my way, and I successfully have the delete statement working great.  I need to get the "insert" to work.  Here's my code:

.aspx

<telerik:RadAjaxLoadingPanel runat="server" ID="RadAjaxLoadingPanel1" />
<telerik:RadAjaxPanel runat="server" ID="RadAjaxPanel1" LoadingPanelID="RadAjaxLoadingPanel1">
<telerik:RadListBox ID="pasLead_rlb" runat="server" Height="150px" Width="250px" AllowTransfer="true" TransferToID="pasLead_rlbDestination"
    DataSourceID="paslead_DS" DataTextField="paslead" DataValueField="contactID">
</telerik:RadListBox>
<telerik:RadListBox ID="pasLead_rlbDestination" runat="server" Height="150px" Width="250px" DataKeyField="contactID" AutoPostBackOnTransfer="true" AutoPostBack="true"
    DataSourceID="pasleadSelected_DS" DataTextField="paslead" DataValueField="contactID" AllowAutomaticUpdates="true" AutoPostBackOnDelete="true" AllowDelete="true">
</telerik:RadListBox>
</telerik:RadAjaxPanel>

sqldatasource

<asp:SqlDataSource ID="pasleadSelected_DS" runat="server"
    ConnectionString="<%$ ConnectionStrings:myconnection %>"
    SelectCommand="SELECT contacts.id AS contactID, contacts.name + ' (' + groups.short_name + ')' AS paslead
                    FROM contacts,
                        groups,
                        area_contact_map acm
                    WHERE contacts.groupID = groups.id
                        and contacts.id = acm.contactID
                        and areaID = @id
                        and (acm.blnRemoved IS NULL OR acm.blnRemoved = 0)
                    ORDER BY paslead"
    DeleteCommand="UPDATE area_contact_map SET update_date = getDate(), update_user = @u, blnRemoved = 1 WHERE areaID = @aid AND contactID = @contactID"
    InsertCommand="INSERT INTO area_contact_map (areaID, contactID, create_date, create_user, blnRemoved) VALUES (@aid, @contactID, getDate(), @u, 0)">
    <SelectParameters>
        <asp:Parameter Name="id" Type="String" />
    </SelectParameters>
    <DeleteParameters>
        <asp:Parameter Name="aid" Type="String" />
        <asp:Parameter Name="contactID" Type="String" />
        <asp:Parameter Name="u" Type="String" />
    </DeleteParameters>
    <InsertParameters>
        <asp:Parameter Name="aid" Type="String" />
        <asp:Parameter Name="contactID" Type="String" />
        <asp:Parameter Name="u" Type="String" />
    </InsertParameters>
</asp:SqlDataSource>

.vb

aid = Request.QueryString("i")
pasuser = Membership.GetUser.ProviderUserKey.ToString()

areaDS.SelectParameters("id").DefaultValue = aid
pasleadSelected_DS.SelectParameters("id").DefaultValue = aid
pasleadSelected_DS.DeleteParameters("aid").DefaultValue = aid
pasleadSelected_DS.DeleteParameters("u").DefaultValue = pasuser
pasleadSelected_DS.InsertParameters("aid").DefaultValue = aid
pasleadSelected_DS.InsertParameters("u").DefaultValue = pasuser

The action I need to track automatically, is when the user moves an item from paslead_rlb to paslead_rlbDestination.  Help!  Thanks!

[Bozhidar]

Hi Terri-Lynn,

Could you please provide the code for the second datasource, in order to reproduce your issue.

Regards,
Bozhidar
the Telerik team

[Terri-Lynn]

<asp:SqlDataSource ID="paslead_DS" runat="server"
    ConnectionString="<%$ ConnectionStrings:myconnection %>"
    SelectCommand="SELECT DISTINCT contacts.id AS contactID, contacts.name + ' (' + groups.short_name + ')' AS paslead FROM contacts INNER JOIN groups ON contacts.groupID = groups.id ORDER BY paslead">
</asp:SqlDataSource>

[Bozhidar]

Hello Terri-Lynn,

I just noticed, that you've set the AutoPostBackOnTransfer property in the second listbox, instead of the first. If you set this property in the first listbox (id="pasLead_rlb") it should solve the issue.

Regards,
Bozhidar
the Telerik team

[Terri-Lynn]

Hi Bozhidar!  Thanks again for the response.  I did as you suggested but it's still not working.  I don't get any error though!  When I select an item in the first listbox, and then click the "To Right" arrow, it does post back, but nothing happens.  The item doesn't move from the first listbox, and the second listbox is still empty.  Here's my updated code:

<telerik:RadAjaxLoadingPanel runat="server" ID="RadAjaxLoadingPanel1" />
<telerik:RadAjaxPanel runat="server" ID="RadAjaxPanel1" LoadingPanelID="RadAjaxLoadingPanel1">
<telerik:RadListBox
    ID="pasLead_rlb" runat="server"
    DataSourceID="paslead_DS"
    DataKeyField="contactID" DataTextField="paslead"
    Height="100px" Width="250px"
    AllowTransfer="true" TransferToID="pasLead_rlbDestination"
    DataValueField="contactID" AutoPostBackOnTransfer="true">
</telerik:RadListBox>
<telerik:RadListBox
    ID="pasLead_rlbDestination" runat="server"
    DataSourceID="pasleadSelected_DS"
    DataKeyField="contactID" DataTextField="paslead"
    Height="100px" Width="250px"
    AllowAutomaticUpdates="true" AutoPostBackOnDelete="true" AllowDelete="true">
</telerik:RadListBox>
</telerik:RadAjaxPanel>

[Bozhidar]

Hi Terri-Lynn,

Currently it's only possible to move items from one listbox to another (not Copy them) in Automatic Updates. Therefore you should also include an AllowAutomaticUpdates="true" property in the first listbox, as well as a DeleteCommand in it's corresponding SQL DataSource.

Also, make sure that all the insert parameters of the second datasource exist in the Select command of the first one and have matching names. For instance, the select command from your first datasource:

SelectCommand="SELECT DISTINCT contacts.id AS contactID, contacts.name + ' (' + groups.short_name + ')' AS paslead FROM contacts INNER JOIN groups ON contacts.groupID = groups.id ORDER BY paslead"

lets you use only two insert parameters: contactID and paslead. If you want to use any additional insert parameter in the second datasource, you should include it in the select command of the first one and give it the same name.

All the best,
Bozhidar
the Telerik team

[Terri-Lynn]

Hi again Bozhidar:

I'm only looking to move items from one listbox to another.  However, I'm simply not getting this.  I'm not understanding why I need a deletecommand in my first listbox??  Listbox 1 is pulling from a reference table, so why would I delete from that?  Perhaps I'm not getting this control at all, but I'm frustrated at this point as I'm already behind in my project plan due to this.  Here's my code again.  Please let me know what I'm doing wrong. 

<telerik:RadAjaxLoadingPanel runat="server" ID="RadAjaxLoadingPanel1" />
<telerik:RadAjaxPanel runat="server" ID="RadAjaxPanel1" LoadingPanelID="RadAjaxLoadingPanel1">
<telerik:RadListBox
    ID="pasLead_rlb" runat="server"
    DataSourceID="paslead_DS"
    DataKeyField="contactID" DataTextField="paslead"
    Height="100px" Width="250px"
    AllowTransfer="true" TransferToID="pasLead_rlbDestination" AllowAutomaticUpdates="true"
    DataValueField="contactID" AutoPostBackOnTransfer="true">
</telerik:RadListBox>
<telerik:RadListBox
    ID="pasLead_rlbDestination" runat="server"
    DataSourceID="pasleadSelected_DS"
    DataKeyField="contactID" DataTextField="paslead"
    Height="100px" Width="250px"
    AllowAutomaticUpdates="true" AutoPostBackOnDelete="true" AllowDelete="true">
</telerik:RadListBox>
</telerik:RadAjaxPanel>

<asp:SqlDataSource ID="paslead_DS" runat="server"
    ConnectionString="<%$ ConnectionStrings:myconnection %>"
    SelectCommand="SELECT DISTINCT up.id AS contactID, up.FirstName + ' ' + up.LastName + ' (' + groups.short_name + ')' AS paslead, '' AS areaID FROM User_Profile up INNER JOIN groups ON up.groupID = groups.id
                    WHERE (up.blnRemoved IS NULL OR up.blnRemoved = 0)
                        AND up.paslead_flag = 1
                    ORDER BY paslead">
</asp:SqlDataSource>
<asp:SqlDataSource ID="pasleadSelected_DS" runat="server"
    ConnectionString="<%$ ConnectionStrings:myconnection %>"
    SelectCommand="SELECT up.id AS contactID, up.FirstName + ' ' + up.LastName + ' (' + groups.short_name + ')' AS paslead, acm.areaID
                    FROM User_Profile up,
                        groups,
                        area_contact_map acm
                    WHERE up.groupID = groups.id
                        and up.id = acm.userID
                        and acm.areaID = @id
                        and (acm.blnRemoved IS NULL OR acm.blnRemoved = 0)
                        and (up.blnRemoved IS NULL OR up.blnRemoved = 0)
                    ORDER BY paslead"
    DeleteCommand="UPDATE area_contact_map SET update_date = getDate(), update_user = @u, blnRemoved = 1 WHERE areaID = @aid AND userID = @contactID"
    InsertCommand="INSERT INTO area_contact_map (areaID, userID, create_date, create_user, blnRemoved) VALUES (@aid, @contactID, getDate(), @u, 0)">
    <SelectParameters>
        <asp:Parameter Name="id" Type="String" />
    </SelectParameters>
    <DeleteParameters>
        <asp:Parameter Name="aid" Type="String" />
        <asp:Parameter Name="userID" />
        <asp:Parameter Name="u" Type="String" />
    </DeleteParameters>
    <InsertParameters>
        <asp:Parameter Name="aid" Type="String" />
        <asp:Parameter Name="contactID" />
        <asp:Parameter Name="u" Type="String" />
    </InsertParameters>
</asp:SqlDataSource>

[Bozhidar]

Hello Terri-Lynn,

In order for the insert to work, you have to supply a delete command in the first datasource. That is how the control is designed. That doesn't mean however, that you have to delete from the database. In your case, you should add a dummy command, and the listbox should work the way you want it to. Try adding the following delete command in the first datasource.

DeleteCommand="DELETE FROM [User_Profile] WHERE [id] = -1"

This is assuming all your id fields are positive.
The idea is to supply a command that doesn't affect the database. You could even do this:

DeleteCommand="SELECT * FROM [User_Profile]"

Best wishes,
Bozhidar
the Telerik team

[Terri-Lynn]

Hi again Bozhidar!  Thanks again for your response.  I've added the deletecommand, but still no luck.  I get the same result as before where I get no error, but no item moves from the first listbox, and the selected item still does not move over to the second listbox.  At this point I'm guessing it's my parameters?  In my code behind I'm adding two parameters as seen below.  The rest of my code is the same as previously shown (minus the addition of the deletecommand in my first listbox).

aid = Request.QueryString("i")
currentuser = Membership.GetUser.ProviderUserKey.ToString()
pasleadSelected_DS.SelectParameters("id").DefaultValue = aid
pasleadSelected_DS.DeleteParameters("aid").DefaultValue = aid
pasleadSelected_DS.DeleteParameters("u").DefaultValue = currentuser
pasleadSelected_DS.InsertParameters("aid").DefaultValue = aid
pasleadSelected_DS.InsertParameters("u").DefaultValue = currentuser

Still stuck.  I NEED to get this working ASAP.

Thanks again,
Terri-Lynn

[Terri-Lynn]

Also, my delete now does NOT work.  I'm beyond frustrated.

[Terri-Lynn]

Update:

I've gotten my delete to work again (DataValueField or DataKeyField set), and the INSERT is now kind of working.  I realized that the "fake" deletecommand I put in was actually causing an error in the browswer, but never noticed the little warning symbol in the bottom left hand corner.  I used your WHERE Id = -1, but my ID field was a uniqueidentifier field.  So, right now it almost works.  What's not working?  My CONTACTID isn't saving, which is the most important piece of the mapping.  I'm still working on it, but please reply with any insight!

Thanks,
Terri-Lynn

[Bozhidar]

Hi Terri-Lynn,

 I think I was finally able to reproduce your problem. I noticed that you're using the contactID field as a DataKeyField in the first ListBox, and also it in the INSERT command of the second one. That, however is not permitted in the listbox. This is because usually the transfer is made between similar tables, and the ID of the tables should be unique and cannot be inserted externally. In your case though, you can do the following workaround: select the up.id field twice in your first datasource and give it different names. Like so:

Copy Code

<asp:SqlDataSource ID="paslead_DS" runat="server"

    ConnectionString="<%$ ConnectionStrings:myconnection %>"

    SelectCommand="SELECT
      DISTINCT up.id AS contactDataKeyID,
      up.id AS contactID,
      up.FirstName + ' ' + up.LastName + ' (' + groups.short_name + ')' AS paslead, '' AS areaID FROM User_Profile up INNER JOIN groups ON up.groupID = groups.id

                    WHERE (up.blnRemoved IS NULL OR up.blnRemoved = 0)

                        AND up.paslead_flag = 1

                    ORDER BY paslead">

</asp:SqlDataSource>

Now assign contactDataKeyID to the DataKeyField of the listbox, and contactID to the insert command. This should solve the issue. 
Regards,
Bozhidar
the Telerik team

[Terri-Lynn]

Thanks again Bozhidar.  That does work.  Late last night I kept pushing through and got it to work another way by using the Transferring server-side events like this:

Protected Sub pasLead_rlb_Transferring(ByVal sender As Object, ByVal e As RadListBoxTransferringEventArgs) Handles pasLead_rlb.Transferring
    e.Cancel = True
    aid = Request.QueryString("i")
    pasuser = Membership.GetUser.ProviderUserKey.ToString()
    Using conn As New SqlConnection(connectionString)
        'obtain the id to insert
        Dim id As String = e.Items(0).Value
        'manually insert into the table
        Dim cmdText As String = "INSERT INTO area_contact_map (areaID, userID, create_date, create_user, blnRemoved) VALUES (@aid, @origcontactID, getDate(), @u, 0)"
        Dim cmd As New SqlCommand(cmdText, conn)
        cmd.Parameters.AddWithValue("@origcontactID", id)
        cmd.Parameters.AddWithValue("@aid", aid)
        cmd.Parameters.AddWithValue("@u", pasuser)
        Try
            conn.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("Error: " & ex.ToString)
        End Try
    End Using
    e.DestinationListBox.DataBind()
End Sub

It's working!  Thanks again!