Telerik Forums / UI for ASP.NET AJAX Forum / ListBox /

InsertCommand not triggered

Telerik UI for ASP.NET AJAX
Resources Buy Try

13 posts, 1 answers

Terri-Lynn

45 posts

Member since:
Dec 2010

Posted 16 Nov 2011 Link to this post

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
Admin

1192 posts

Posted 18 Nov 2011 Link to this post

Hi Terri-Lynn,
Could you please provide the code for the second datasource, in order to reproduce your issue.
Regards,
Bozhidar
the Telerik team

If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now
Terri-Lynn

45 posts

Member since:
Dec 2010

Posted 18 Nov 2011 Link to this post

<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
Admin

1192 posts

Posted 18 Nov 2011 Link to this post

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

If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now
Terri-Lynn

45 posts

Member since:
Dec 2010

Posted 18 Nov 2011 Link to this post

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
Admin

1192 posts

Posted 21 Nov 2011 Link to this post

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

If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now
Terri-Lynn

45 posts

Member since:
Dec 2010

Posted 21 Nov 2011 Link to this post

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
Admin

1192 posts

Posted 23 Nov 2011 Link to this post

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

If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now
Terri-Lynn

45 posts

Member since:
Dec 2010

Posted 24 Nov 2011 Link to this post

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

45 posts

Member since:
Dec 2010

Posted 24 Nov 2011 Link to this post

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

45 posts

Member since:
Dec 2010

Posted 24 Nov 2011 Link to this post

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
Answer
Bozhidar
Admin

1192 posts

Posted 25 Nov 2011 Link to this post

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

If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now
Terri-Lynn

45 posts

Member since:
Dec 2010

Posted 25 Nov 2011 Link to this post

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!

InsertCommand not triggered

Bozhidar

Bozhidar

Bozhidar

Bozhidar

Bozhidar