InsertCommand not triggered

13 posts, 1 answers
  1. Terri-Lynn
    Terri-Lynn avatar
    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!
  2. Bozhidar
    Admin
    Bozhidar avatar
    1103 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
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Terri-Lynn
    Terri-Lynn avatar
    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>
  5. Bozhidar
    Admin
    Bozhidar avatar
    1103 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
  6. Terri-Lynn
    Terri-Lynn avatar
    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>
  7. Bozhidar
    Admin
    Bozhidar avatar
    1103 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
  8. Terri-Lynn
    Terri-Lynn avatar
    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>
  9. Bozhidar
    Admin
    Bozhidar avatar
    1103 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
  10. Terri-Lynn
    Terri-Lynn avatar
    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
  11. Terri-Lynn
    Terri-Lynn avatar
    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.
  12. Terri-Lynn
    Terri-Lynn avatar
    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
  13. Answer
    Bozhidar
    Admin
    Bozhidar avatar
    1103 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
  14. Terri-Lynn
    Terri-Lynn avatar
    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!
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017