This is a migrated thread and some comments may be shown as answers.

How to Check for Duplicates before Updating Rows

2 Answers 481 Views

Grid

This is a migrated thread and some comments may be shown as answers.

gc_0620 asked on 15 Sep 2009, 06:03 PM

Folks,

I have a nested GridTableView (DataSourceId: SqlDataSource2) in my RadGrid. In GridTableView I have a RadComboBox (SqlDatasourceId: SqlDataSource3). I would like to check for Duplicates if that Location is already exists for that person (i.e. Do not allow Duplicate entries for Update/Inserts). Below is my complete code as well as SqlDataSource2_Updating event (Is there any other event that can do the same also?).

Any Help will be appreciated. Thanks

GC_0620

_________________
<telerik:RadGrid ID="RadGrid1" runat="server" AllowAutomaticDeletes="True"

AllowAutomaticInserts="True" AllowAutomaticUpdates="True"

AllowFilteringByColumn="True" AllowPaging="True" AllowSorting="True"

AutoGenerateColumns="False" DataSourceID="SqlDataSource1" GridLines="None"

Skin="Office2007" PageSize="8" EnableLinqExpressions="False"

OnInsertCommand="RadGrid1_InsertCommand" OnUpdateCommand = "RadGrid1_UpdateCommand"

<telerik:GridTableView runat="server" DataSourceID="SqlDataSource2" DataKeyNames="UserLocationsPrimaryKey"

Width="100%" Skin="WebBlue" CommandItemDisplay="Top" Name = "JobPerson" AllowAutomaticDeletes="true" AllowAutomaticInserts="true" AllowAutomaticUpdates="true">

<telerik:GridRelationFields DetailKeyField="UserTableForeignKey" MasterKeyField="UserTablePrimaryKey" />

</ParentTableRelation>

</RowIndicatorColumn>

</ExpandCollapseColumn>

Custom command item template    

<asp:LinkButton ID="btnEditSelected" runat="server" CommandName="EditSelected" Visible='<%# RadGrid1.EditIndexes.Count == 0 %>'><img style="border:0px;vertical-align:middle;" alt="" src="Images/Edit.gif" /> Edit selected</asp:LinkButton>  

<asp:LinkButton ID="btnUpdateEdited" runat="server" CommandName="UpdateEdited" Visible='<%# RadGrid1.EditIndexes.Count > 0 %>'><img style="border:0px;vertical-align:middle;" alt="" src="Images/Update.gif" /> Update</asp:LinkButton>  

<asp:LinkButton ID="btnCancel" runat="server" CommandName="CancelAll" Visible='<%# RadGrid1.EditIndexes.Count > 0 || RadGrid1.MasterTableView.IsItemInserted %>'><img style="border:0px;vertical-align:middle;" alt="" src="Images/Cancel.gif" /> Cancel editing</asp:LinkButton>  

<asp:LinkButton ID="LinkButton2" runat="server" CommandName="InitInsert" Visible='<%# !RadGrid1.MasterTableView.IsItemInserted %>'><img style="border:0px;vertical-align:middle;" alt="" src="Images/AddRecord.gif" /> Add new</asp:LinkButton>  

<asp:LinkButton ID="LinkButton3" runat="server" CommandName="PerformInsert" Visible='<%# RadGrid1.MasterTableView.IsItemInserted %>'><img style="border:0px;vertical-align:middle;" alt="" src="Images/Insert.gif" /> Add this Location</asp:LinkButton>  

<asp:LinkButton ID="LinkButton1" OnClientClick="javascript:return confirm('Delete all selected Person?')"

runat="server" CommandName="DeleteSelected"><img style="border:0px;vertical-align:middle;" alt="" src="Images/Delete.gif" /> Delete selected Location</asp:LinkButton>  

<asp:LinkButton ID="LinkButton4" runat="server" CommandName="RebindGrid"><img style="border:0px;vertical-align:middle;" alt="" src="Images/Refresh.gif" /> Refresh list</asp:LinkButton>

</div>

</CommandItemTemplate>

<telerik:GridBoundColumn DataField="UserLocationsPrimaryKey"

DataType="System.Int32" HeaderText="UserLocationsPrimaryKey" ReadOnly="True"

SortExpression="UserLocationsPrimaryKey" UniqueName="UserLocationsPrimaryKey"

Visible="False">

</telerik:GridBoundColumn>

<telerik:GridTemplateColumn DataField="Location" DataType="System.Double"

HeaderText="Location" SortExpression="Location" UniqueName="Location">

<asp:Label ID="LocationLabel" runat="server" Text='<%# Eval("Location") %>'></asp:Label>

</ItemTemplate>

</telerik:GridTemplateColumn>

<telerik:GridBoundColumn DataField="Program" HeaderText="Program"

SortExpression="Program" UniqueName="Program" >

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="UserTableForeignKey"

DataType="System.Int32" HeaderText="UserTableForeignKey"

SortExpression="UserTableForeignKey" UniqueName="UserTableForeignKey"

Display="False" ReadOnly="True" Visible="False">

</telerik:GridBoundColumn>

</Columns>

<table id="Table1a" cellspacing="2" cellpadding="1" width="100%" border="0" rules="none"

style="border-collapse: collapse; background: white;">

<b>User Location</b></td>

</tr>

<tr>

</tr> <tr>

<td>

</td>

<tr>

<td>

Location</td>

<td>

<telerik:RadComboBox ID="RadComboBoxLocation" runat="server"

DataSourceID="SqlDataSource3" DataTextField="Program" DataValueField="Location"

DropDownWidth="369px" Height="150px" HighlightTemplatedItems="true"

MarkFirstMatch="true"

SelectedValue='<%#Bind("Location") %>' Skin="Office2007" Width="320px"

<tr>

Program</td>

Location</td>

</tr>

</table>

</HeaderTemplate>

<tr>

<%# DataBinder.Eval(Container.DataItem, "Program") %>

</td>

<%# DataBinder.Eval(Container.DataItem, "Location") %>

</td>

</tr>

</table>

</ItemTemplate>

</telerik:RadComboBox>

<asp:RequiredFieldValidator ID="RadComboBoxLocationRequiredFieldValidator"

runat="server" ControlToValidate="RadComboBoxLocation"

ErrorMessage="! Required">

</asp:RequiredFieldValidator>

</td>

<td>

Program</td>

<td>

<asp:TextBox ID="TxtProgram" runat="server" TabIndex="2" ReadOnly="true"

Text='<%# Eval( "Program") %>' Width="250px">

</asp:TextBox>

</td>

</tr>

<tr>

<td>

</td>

<td>

</td>

<td>

</td>

<asp:Button ID="btnUpdate" runat="server"

CommandName='<%# (Container is GridEditFormInsertItem) ? "PerformInsert" : "Update" %>'

Text='<%# (Container is GridEditFormInsertItem) ? "Insert" : "Update" %>'

<asp:Button ID="btnCancel" runat="server" CausesValidation="False"

CommandName="Cancel" Text="Cancel" />

</td>

</tr>

</table>

</FormTemplate>

</EditFormSettings>

</telerik:GridTableView>

</DetailTables>

</Columns>

</FormTemplate>

</EditFormSettings>

</MasterTableView>

</ClientSettings>

</telerik:RadGrid>

<asp:Label ID="Msglabel" runat="server" Text="Label4"></asp:Label>

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

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

DeleteCommand="DELETE FROM [UserLocations] WHERE [UserLocationsPrimaryKey] = @UserLocationsPrimaryKey"

InsertCommand="INSERT INTO [UserLocations] ([Location], [Program], [UserTableForeignKey]) VALUES (@Location, @Program, @UserTableForeignKey)"

SelectCommand="SELECT [UserLocationsPrimaryKey], [Location], [Program], [UserTableForeignKey] FROM [UserLocations] WHERE ([UserTableForeignKey] = @UserTableForeignKey) ORDER BY [Program]"

UpdateCommand="UPDATE [UserLocations] SET [Location] = @Location, [Program] = @Program, [UserTableForeignKey] = @UserTableForeignKey WHERE [UserLocationsPrimaryKey] = @UserLocationsPrimaryKey" onupdating="SqlDataSource2_Updating"

<asp:Parameter Name="UserTableForeignKey" Type="Int32" />

</SelectParameters>

<asp:Parameter Name="UserLocationsPrimaryKey" Type="Int32" />

</DeleteParameters>

<asp:Parameter Name="Location" Type="Double" />

<asp:Parameter Name="Program" Type="String" />

<asp:Parameter Name="UserTableForeignKey" Type="Int32" />

<asp:Parameter Name="UserLocationsPrimaryKey" Type="Int32" />

</UpdateParameters>

<asp:Parameter Name="Location" Type="Double" />

<asp:Parameter Name="Program" Type="String" />

<asp:Parameter Name="UserTableForeignKey" Type="Int32" />

</InsertParameters>

</asp:SqlDataSource>

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

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

SelectCommand="SELECT * FROM [CCNS_All_Location_Codes] WHERE ([Location] IS NOT NULL) ORDER BY [Program]">

</asp:SqlDataSource>

________________________________________________________　

So Far I came up with this for Duplicate Checking but is not working. Any help will be appreciated..

protected void SqlDataSource2_Updating(object sender, SqlDataSourceCommandEventArgs e)

{

RadComboBox combo = (RadComboBox)RadGrid1.MasterTableView.FindControl("RadComboBoxLocation");

//RadComboBox combo1 = (RadComboBox)RadGrid1.MasterTableView.Items[0].FindControl("RadComboBoxLocation");

//string strSelectedItem = combo1.SelectedItem.Text.ToString();

SqlConnection conLocation = new SqlConnection(ConfigurationManager.

ConnectionStrings["HR_Payroll_AuthorizationConnectionString"].ToString());

SqlCommand cmd = new SqlCommand("SELECT Count(*) FROM UserLocations " +

"WHERE ([Location] = '" + combo.SelectedValue.ToString() + "'", conLocation);

conLocation.Open();

Object result = cmd.ExecuteScalar();

if (result != null)

{

Msglabel.Visible = true;

Msglabel.Text = "Duplicate Location for this Person - Can't proceed.."

e.Cancel = true;

}

conLocation.Close();

}

2 Answers, 1 is accepted

answered on 15 Sep 2009, 11:10 PM

Folks,

I am using RadControls for ASP.NET AJAX Q2 2009; VS 2008 (Service Pack1). In my original post I forgot to mention that.

1) My Intention is not to allow Child DetailTable Update or Insert if that location is already exists. I have no problem to pull data into RadComboBox ID="RadComboBoxLocation" (i.e. Bind to Location) in Child DetailTable->FormTemplate. It works. But just do not know how to prevent duplicates for Item Updating or Inserting in Child Table.

2) How do you extract the Selected value of a Radcombobox (i.e. Populate into a string variable) in Update/Insert mode of RadGrid->MasterTable->Related Child Detailtable->FormTemplate? Thats what I am struggling.

Thanks.for any help.

gc_0620