Home > Forum > ASP.NET > Show Error Message when its a Duplicate Record in CRUD

Show Error Message when its a Duplicate Record in CRUD

by Dave   on Sep 18, 2013   Category: ASP.NET  |  Views: 2021    |  Points: 5   |  Starter 
  Reply
Hi,
My Query is related to the article How to perform insert, update, delete and select rows in ASP.NET gridview control using stored procedure
If a user enters Duplicate value like same Customer Name or same email ID then how to show a error message to the user saying "Duplicate record",how to modify the store procedure in this case.

Bookmark and Share:
 
  User Reply  | Ask a question  |   Reply 
  Re :Show Error Message when its a Duplicate Record in CRUD   
by Thamil
on Sep 18 2013 9:50AM
Points : 10
Gold 
Hello Dev,

I have updated the stored proc and GridViewSample_RowCommand event as per your query see the below SP & GridViewSample_RowCommand event changes

CREATE PROC [dbo].[SP_Customer]        
(
@pvchCustId int =0,
@pvchCustName varchar(100)=null,
@pvchustaddress1 varchar(100)=null,
@pvchustaddress2 varchar(100)=null,
@pvchCustPhone varchar(100)=null,
@pvchCustEmail varchar(100)=null,
@pvchCreatedBy varchar(100)=null,
@pvchAction varchar(50)=null,
@pIntErrDescOut int output
)
AS
BEGIN

if(@pvchAction='select')
begin
SELECT CUSTID,CUSTNAME,CUSTNAME,CUSTADDRESS1,CUSTADDRESS2,CUSTPHONE,
CUSTEMAIL,ACTIVE FROM tblCustomer WHERE Active=1;
end
else if(@pvchAction='insert')
begin
if not exists(select * from tblCustomer where CustName=@pvchCustName or CustEmail=@pvchCustEmail)
begin
INSERT INTO tblCustomer(CustName,Custaddress1,Custaddress2,CustPhone,
CustEmail,Createdby,CreatedDt,Active)VALUES(@pvchCustName,@pvchustaddress1,@pvchustaddress2,@pvchCustPhone,
@pvchCustEmail,@pvchCreatedBy,GETDATE(),1);
end
else
begin
set @pIntErrDescOut=2
end

end

else if(@pvchAction='update')
begin
UPDATE tblCustomer SET CustName=@pvchCustName,Custaddress1=@pvchustaddress1,Custaddress2=@pvchustaddress2,
CustPhone=@pvchCustPhone,CustEmail=@pvchCustEmail,Updatedby=@pvchCreatedBy,UpdatedDt=GETDATE()
WHERE CustID=@pvchCustId;
end
else if(@pvchAction='delete')
begin
UPDATE tblCustomer SET Active=0,Updatedby=@pvchCreatedBy,UpdatedDt=GETDATE()
WHERE CustID=@pvchCustId;
end

IF (@@ERROR <> 0)
BEGIN
SET @pIntErrDescOut = 1
END
END


GridViewSample_RowCommand event changes

protected void GridViewSample_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("Insert"))
{
int retVal=0;
TextBox Name = (TextBox)GridViewSample.FooterRow.FindControl("txtAddCustName");
TextBox Address1 = (TextBox)GridViewSample.FooterRow.FindControl("txtAddCustAdres1");
TextBox Address2 = (TextBox)GridViewSample.FooterRow.FindControl("txtAddCustAdres2");
TextBox Phone = (TextBox)GridViewSample.FooterRow.FindControl("txtAddCustPhone");
TextBox Email = (TextBox)GridViewSample.FooterRow.FindControl("txtAddEmail");

using (SqlConnection Sqlcon = new SqlConnection(strCon))
{
using (SqlCommand cmd = new SqlCommand())
{
Sqlcon.Open();
cmd.Connection = Sqlcon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_Customer";

cmd.Parameters.Add(new SqlParameter("@pvchAction", SqlDbType.VarChar, 50));
cmd.Parameters.Add(new SqlParameter("@pvchCustName", SqlDbType.VarChar, 100));
cmd.Parameters.Add(new SqlParameter("@pvchustaddress1", SqlDbType.VarChar, 100));
cmd.Parameters.Add(new SqlParameter("@pvchustaddress2", SqlDbType.VarChar, 100));
cmd.Parameters.Add(new SqlParameter("@pvchCustPhone", SqlDbType.VarChar, 100));
cmd.Parameters.Add(new SqlParameter("@pvchCustEmail", SqlDbType.VarChar, 100));
cmd.Parameters.Add(new SqlParameter("@pvchCreatedBy", SqlDbType.VarChar, 100));
cmd.Parameters.Add("@pIntErrDescOut", SqlDbType.Int).Direction = ParameterDirection.Output;

cmd.Parameters["@pvchAction"].Value = "insert";
cmd.Parameters["@pvchCustName"].Value = Name.Text.Trim();
cmd.Parameters["@pvchustaddress1"].Value = Address1.Text.Trim();
cmd.Parameters["@pvchustaddress2"].Value = Address2.Text.Trim();
cmd.Parameters["@pvchCustPhone"].Value = Phone.Text.Trim();
cmd.Parameters["@pvchCustEmail"].Value = Email.Text.Trim();
cmd.Parameters["@pvchCreatedBy"].Value = "Admin";

cmd.ExecuteNonQuery();
retVal = (int)cmd.Parameters["@pIntErrDescOut"].Value;

}
}

if (retVal == 2)
{
lblMessage.Text = "Customer name or Email ID aready exist!";
}
else
{
GridViewSample.EditIndex = -1;
LoadData();
lblMessage.Text = "Record inserted successfully!";

}

}
}


i hope this will help you.

Recent Post

Latest Posts