What's New?

Prevent Duplicate Record Validation

Page 1 of 2 12 LastLast
  1. #1
    Jul 31, 2014, 8:06 AM

    Prevent Duplicate Record Validation

    Hi,

    I'm having trouble finding an example of what I am trying to achieve.
    I have a database with a large list of phone numbers in them. I then have a gridpanel that connects up to this which allows me to add/edit/delete the numbers.

    What I would like to do now is check to see if a number is already in the database before it is inserted/edited.

    I've got as far as adding the following code but I'm unsure if this is correct and I'm also unsure how to call it.
    I think I need to use remote validation but I am struggling to find an example of this which I can work into my page.

        var duplicated = function(store, number, value)
    {
        var found = false;
        store.each(function(record)
        {
          if(found = (record.data[number]==value) ? true : false){return false};
        }
        )
       return found;
    }
    The whole code is below.

    <%@ Page Language="C#" %>
<%@ Import Namespace="System.Xml" %>
<%@ Register Assembly="Ext.Net" Namespace="Ext.Net" TagPrefix="ext" %>

<script runat="server">

    private bool cancel;
    private string message;
    private int? insertedValue;

    protected void Store1_BeforeRecordInserted(object sender, BeforeRecordInsertedEventArgs e)
    {
    
    }

    protected void Store1_AfterRecordInserted(object sender, AfterRecordInsertedEventArgs e)
    {
        if (insertedValue.HasValue)
        {
            e.Keys.Add("record_id", insertedValue.Value);
            insertedValue = null;
        }
    }

    protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)
    {
        if(e.AffectedRows > 0 && e.Command.Parameters["@newId"].Value != null)
        { 
            insertedValue = (int)e.Command.Parameters["@newId"].Value;
        }
        else
        {
            insertedValue = null;
        }
    }

    protected void Store1_AfterDirectEvent(object sender, AfterDirectEventArgs e)
    {
        if (e.Response.Success)
        {
            // set to .Success to false if we want to return a failure
            e.Response.Success = !cancel;
            e.Response.Message = message;           
        }
    }

    protected void Store1_Refresh(object sender, StoreReadDataEventArgs e)
    {
        Store store = this.GridPanel1.GetStore();
        store.DataBind();
    }

    protected void Store1_BeforeDirectEvent(object sender, BeforeDirectEventArgs e)
    {
        
    }

</script>
    <link href="/resources/css/examples.css" rel="stylesheet" />

<script>

    var duplicated = function(store, number, value)
    {
        var found = false;
        store.each(function(record)
        {
          if(found = (record.data[number]==value) ? true : false){return false};
        }
        )
       return found;
    }

</script>

<!DOCTYPE html>

<html>
<head id="Head1" runat="server">
    <title>TPS List</title>
</head>
<body>
    <form id="Form1" runat="server">
        <ext:ResourceManager ID="ResourceManager1" runat="server" />

        <asp:SqlDataSource 
            ID="SqlDataSource1" 
            runat="server" 
            ConnectionString="<%$ ConnectionStrings:databaseConnection %>"

            DeleteCommand="DELETE FROM tps WHERE (record_id = @record_id)"

            InsertCommand="INSERT INTO tps 
                               (number) 
                            SELECT 
                                (@number);                         
                            SELECT @newId = @@Identity;"
                            
            SelectCommand="SELECT 
                                record_id, 
                                number,
                                CONVERT(VARCHAR(19),date_entered) [date_entered],
                                CONVERT(VARCHAR(19),date_removed) [date_removed],
                                active
                           FROM tps
                           ORDER BY date_entered DESC"
                           
            UpdateCommand="UPDATE tps SET 
                                number = @number,
                                last_modified_date = GETDATE(),
                                --only show a removed date if active is unticked and it was previously set to true in the database
                                date_removed = (CASE WHEN @active = 'false' AND active = 'true' THEN GETDATE() ELSE NULL END),
                                active = @active
                           WHERE (record_id = @record_id)"
                           
            OnInserted="SqlDataSource1_Inserted"

            FilterExpression="number LIKE '%{0}%'" >
            <FilterParameters>
                <asp:ControlParameter Name="number" ControlId="ProjectGridProjectIdValue" DefaultValue="" />
            </FilterParameters>
            
            <DeleteParameters>
                <asp:Parameter Name="record_id" Type="Int32" />
            </DeleteParameters>
            
            <UpdateParameters>
                <asp:Parameter Name="number" Type="String" />
                <asp:Parameter Name="record_id" Type="Int32" />
            </UpdateParameters>
            
            <InsertParameters>
                <asp:Parameter Name="number" Type="String" />
                <asp:Parameter Direction="Output" Name="newId" Type="Int32" />
            </InsertParameters>
        </asp:SqlDataSource>
        
        <ext:Store 
            ID="Store1" 
            runat="server"
            DataSourceID="SqlDataSource1" 
            ShowWarningOnFailure="false"
            OnAfterDirectEvent="Store1_AfterDirectEvent"
            OnBeforeDirectEvent="Store1_BeforeDirectEvent" 
            OnBeforeRecordInserted="Store1_BeforeRecordInserted"
            OnAfterRecordInserted="Store1_AfterRecordInserted"
            OnReadData="Store1_Refresh"
            AutoLoad="true"
            RemotePaging="false"
            PageSize="50">
            <Proxy>
                <ext:PageProxy />
            </Proxy>
            <Model>
                <ext:Model ID="Model1" runat="server" IDProperty="record_id" Name="Record">
                    <Fields>
                        <ext:ModelField Name="record_id" Type="Int" />
                        <ext:ModelField Name="number" />
                        <ext:ModelField Name="date_entered" />
                        <ext:ModelField Name="date_removed" />
                        <ext:ModelField Name="active" />
                    </Fields>
                </ext:Model>
            </Model>
            <Sorters>
                <ext:DataSorter Property="number" Direction="ASC" />
            </Sorters> 
            <Listeners>
                <Exception Handler="Ext.Msg.alert('Operation failed', operation.getError());" />                
                <Write Handler="Ext.Msg.alert('Write', 'The data successfully saved');" />
            </Listeners>
        </ext:Store>
        
        <ext:Viewport ID="Viewport1" runat="server" Layout="BorderLayout">
            <Items>
                <ext:Panel ID="Panel2" runat="server" Region="Center" Height="300" Header="false" Layout="Fit" MarginsSummary="0 5 0 5">
                    <Items>
                        <ext:GridPanel ID="GridPanel1" runat="server" Title="TPS List" StoreID="Store1" Border="false" Icon="Phone" >
                            <ColumnModel ID="ColumnModel1" runat="server">
                                <Columns>
                                    <ext:Column ID="colNumber" runat="server" DataIndex="number" Text="Number" Flex="1" >
                                        <Editor>
                                            <ext:NumberField ID="NumberField1" runat="server" AllowBlank="false" />
                                        </Editor>
                                    </ext:Column>
                                    <ext:Column ID="colDateEntered" runat="server" DataIndex="date_entered" Text="Date Added" Flex="1" />
                                    <ext:Column ID="colDateRemoved" runat="server" DataIndex="date_removed" Text="Date Removed" Flex="1" />
                                    <ext:CheckColumn ID="chkActive" runat="server" DataIndex="active" Header="Active" Width="50" Sortable="true" Editable="true" />             
                                </Columns>
                            </ColumnModel>
                            <TopBar>
                                <ext:Toolbar runat="server">
                                    <Items>
                                        <ext:Label runat="server" Text="Number" ></ext:Label>
                                        <ext:TextField ID="ProjectGridProjectIdValue" runat="server" Text="" />
                                        <ext:Button runat="server" Text="Search" StandOut="true" Icon="Magnifier" >
                                            <Listeners>
                                                <Click Handler="#{Store1}.reload({params:{EmulateError: 0}});" />
                                            </Listeners>
                                        </ext:Button>
                                    </Items>
                                </ext:Toolbar>
                            </TopBar>
                             <BottomBar>
                                <ext:PagingToolbar ID="PagingToolbar1" runat="server" />
                            </BottomBar>
                                <SelectionModel>
                                    <ext:CheckBoxSelectionModel ID="ProjectRowSelectionModel" runat="server">
                                        <Listeners>
                                            <Select Handler="#{ProjectGridProjectIdValue}.setValue(#{GridPanel1}.store.getAt(#{GridPanel1}.store.indexOf(#{GridPanel1}.getSelectionModel().getSelected())).get('number'));#{Store1}.reload();"  Buffer="250" />
                                        </Listeners>
                                    </ext:CheckBoxSelectionModel>
                                </SelectionModel>
                            <Plugins>
                                <ext:CellEditing ID="CellEditing1" runat="server" />
                            </Plugins>
                        </ext:GridPanel>
                    </Items>
                    <Buttons>
                        <ext:Button ID="btnSave" runat="server"  Text="Sync" Icon="Disk" >
                            <Listeners>
                                <Click Handler="#{Store1}.sync();" />
                            </Listeners>
                        </ext:Button>
                        <ext:Button ID="btnDelete" runat="server"  Text="Delete selected records" Icon="Delete">
                            <Listeners>
                                <Click Handler="#{GridPanel1}.deleteSelected();" />
                            </Listeners>
                        </ext:Button>
                        <ext:Button ID="btnInsert" runat="server" Text="Insert" Icon="Add">
                            <Listeners>
                                <Click Handler="#{Store1}.insert(0, new Record());#{GridPanel1}.editingPlugin.startEditByPosition({row:0, column:0});" />
                            </Listeners>
                        </ext:Button>
                        <ext:Button ID="btnRefresh" runat="server"  Text="Refresh" Icon="ArrowRefresh">
                            <Listeners>
                                <Click Handler="#{Store1}.reload({params:{EmulateError: 0}});" />
                            </Listeners>
                        </ext:Button>                     
                    </Buttons>
                </ext:Panel>
            </Items>
        </ext:Viewport>
        <ext:KeyMap ID="KeyMap1" runat="server" Target="={Ext.isGecko ? Ext.getDoc() : Ext.getBody()}">        
            <Binding>
                <ext:KeyBinding Handler="#{Store1}.reload({params:{EmulateError: 0}});">
                    <Keys>
                        <ext:Key Code="ENTER" />
                    </Keys>
                </ext:KeyBinding>    
            </Binding>        
        </ext:KeyMap>
    </form>
</body>
</html>
    Thanks in advance,
    Hixxey

    EDIT:
    I have found this but I am unsure how to connect this up to my store.
    Last edited by Hixxey; Jul 31, 2014 at 8:49 AM.
  2. #2
    Aug 01, 2014, 4:22 AM
    Hi @Hixxey,

    You could do all the validation at once. Say, a user has added and edited everything. Then he initiates a sync request, all the new data goes to the server and you can validate everything.

    If you need validation on each operation, then you should do it time. For example, if you insert a new record, send a request (probably, a DirectMethod) to the server with the data being inserted. If the data is not valid, do not insert them.

    As for editing, a CellEditing has the Edit and ValidateEdit events which you might use to send a request to server.
    http://docs.sencha.com/extjs/4.2.1/#...ing-event-edit
    http://docs.sencha.com/extjs/4.2.1/#...t-validateedit
  3. #3
    Aug 01, 2014, 7:34 AM
    Hi Daniil,

    Thank you for the reply again.

    Validating everything at once when the user clicks sync seems like the best approach to me.

    I have a few questions regarding this.
    Do you know of an example I could follow which uses validation against a SqlDataSource?
    How would I output a message to the user which tells them which records were not entered due to them being duplicated?
    Would all the validation be done in the SQL insert statement?

    Thanks,
    Hixxey
  4. #4
    Aug 01, 2014, 9:14 AM
    Please look at this example:
    https://examples2.ext.net/#/GridPane...SqlDataSource/

    Try to Insert a record (-s) and Sync. There will be an error.
  5. #5
    Aug 01, 2014, 9:53 AM
    Excellent, thanks Daniil.

    What about when a record is edited rather than inserted. Is there an OnBeforeRecordEdited?
  6. #6
    Aug 01, 2014, 10:05 AM
    I think you are looking for a Store's OnBeforeRecordUpdated event.
  7. #7
    Aug 01, 2014, 2:28 PM
    Thank you for all the help so far.
    I have a couple more questions and then I think I'm set.

    Firstly, how would I compare the new value to all existing values in the table?
    Secondly, I noticed I can't update the active bit field due to the number not matching. How can I change this so that if I have edited the active field and not the number then the BeforeRecordUpdated isn't executed?

        protected void Store1_BeforeRecordInserted(object sender, BeforeRecordInsertedEventArgs e)
    {
        object number = e.NewValues["number"];

        if (number.ToString() != "115989")
        {
            e.Cancel = true;
            this.cancel = true;
            this.message = number.ToString() + " is already in the TPS list";
        }
    }

    protected void Store1_BeforeRecordUpdated(object sender, BeforeRecordUpdatedEventArgs e)
    {
        object number = e.NewValues["number"];

        if (number.ToString() != "115989")
        {
            e.Cancel = true;
            this.cancel = true;
            this.message = number.ToString() + " is already in the TPS list";
        }
    }
  8. #8
    Aug 01, 2014, 9:09 PM
    I've managed to get this working by looping through all the existing data in the table and looking for a match.

    The update is then done by first checking if the record ID is a match. If the record ID doesn't match but the number does then it must be a duplicate and therefore the data cannot be inserted.

    One issue I have found is that when I edit a number the active checkbox seems to reset itself back to checked and then unchecked again which seems to be clearing my removed_date column. Any ideas why this would be? It seems to be a bug but I'm sure it's probably something I've done wrong.

        protected void Store1_BeforeRecordInserted(object sender, BeforeRecordInsertedEventArgs e)
    {
        object number = e.NewValues["number"];

        using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["databaseConnection"].ConnectionString))
        {
            using (var command = connection.CreateCommand())
            {
                command.CommandText = "SELECT number FROM tps";

                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    var indexOfNumber = reader.GetOrdinal("number");

                    while (reader.Read())
                    {
                        var numberValue = reader.GetValue(indexOfNumber);

                        //check to see if the number matches any others in the db
                        if (number.ToString() == numberValue.ToString())
                        {
                            e.Cancel = true;
                            this.cancel = true;
                            this.message = number.ToString() + " is already in the TPS list";
                        }
                    }
                }
                connection.Close();
            }
        }
    }

    protected void Store1_BeforeRecordUpdated(object sender, BeforeRecordUpdatedEventArgs e)
    {
        object number = e.NewValues["number"];
        object record = e.NewValues["record_id"];

        using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["databaseConnection"].ConnectionString))
        {
            using (var command = connection.CreateCommand())
            {
                command.CommandText = "SELECT record_id, number FROM tps";

                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    var indexOfNumber = reader.GetOrdinal("number");
                    var indexOfRecord = reader.GetOrdinal("record_id");

                    while (reader.Read())
                    {
                        var numberValue = reader.GetValue(indexOfNumber);
                        var recordValue = reader.GetValue(indexOfRecord);

                        //if we are on a different record id and the number matches then its a duplicate
                        if (record.ToString() != recordValue.ToString() && number.ToString() == numberValue.ToString())
                        {
                            e.Cancel = true;
                            this.cancel = true;
                            this.message = number.ToString() + " is already in the TPS list";
                        }
                    }
                }
                connection.Close();
            }
        }
    }
  9. #9
    Aug 05, 2014, 1:27 PM
    I have no idea. If you provide a standalone and simplified test case to reproduce it, I could look at.
  10. #10
    Aug 06, 2014, 12:15 PM
    Hi Daniil,

    The image below explains the problem I am facing.
    When I update the number of a record that has its active flag set to 0 and click Sync it will show the active flag as 1.
    When I then click OK and Refresh the flag is back to 0 (as it should be) but the removed_date has been cleared.



    The SQL for the update is below.

                UpdateCommand="UPDATE tps SET 
                                number = CAST(@number AS BIGINT), --cast as big int to remove the leading 0
                                last_modified_date = GETDATE(),
                                --only show a removed date if active is unticked and it was previously set to true in the database
                                date_removed = (CASE WHEN @active = 'false' AND active = 'true' THEN GETDATE() ELSE NULL END),
                                active = @active
                           WHERE (record_id = @record_id)"
Page 1 of 2 12 LastLast

Similar Threads

  1. [CLOSED] Validate duplicate record with Auto sync on store.
    By iansriley in forum 2.x Legacy Premium Help
    Replies: 1
    Last Post: Jun 02, 2014, 3:51 PM
  2. [CLOSED] I want to play index like auto play ,Next record ,Previous Record ,First record ,Last Record in gridpanel
    By UT007 in forum 2.x Legacy Premium Help
    Replies: 11
    Last Post: Oct 29, 2013, 5:32 PM
  3. [CLOSED] DirectEvents + MVC + client side validation: prevent direct event on invalid form
    By jlosi in forum 1.x Legacy Premium Help
    Replies: 4
    Last Post: Apr 30, 2012, 4:49 PM
  4. [CLOSED] Need help in PagingToolbar record Navigate Validation
    By ISI in forum 1.x Legacy Premium Help
    Replies: 2
    Last Post: Sep 07, 2011, 9:57 AM
  5. [CLOSED] server validation, datagrid, prevent row change?
    By seanwo in forum 1.x Legacy Premium Help
    Replies: 3
    Last Post: Jun 29, 2010, 1:07 PM

Tags for this Thread

Posting Permissions