Duplicate records in sql

Question

1.00/5 (2 votes)

See more:

i have a form which is supervisor ,manager and director aspx forms . when any user upload documents then this document first goes to supervsior then manager and then director for approve/reject

user "kat" uplaod document and his/her document goes for approval /reject to supervsior,manager,director and it seems like this in table

SQL

Seqno DocID ApproveID ApproveBy DesigID ApproveDate

1        16        1              abc            1           10/11/2013

2        16        1               def           2           11/12/2013

3       16          1            xyzz         3             14/12/2013

when user "kat" see document wheather document approve/reject it seems like this

SQL

docname fileuploaded uploadedate deptype status

finad   fina.docx   04/11/2013  finance approve

when another user upload document and when this document goes for approval to supervsior,manager,director

then in supervsior form it seems like this

SQL

docid    docname     filename department      email             uploadedby uploadedate

  16     finad      fina.docx    fiance   sadas@gmail.com        kat        04/11/2013 (this is old document)

   17    hrrr         hrr.docs  finance    abc@gmail.com         john   15/11/2013  (this is new document

)

so when supervsior approve document (docid 17) then old document which is already approve/reject also save in database and after this in table seems like this

SQL

Seqno DocID     ApproveID         ApproveBy   DesigID    ApproveDate

1        16        2              abc            1           10/11/2013

2        16        1               def           2           11/12/2013

3       16          1              xyzz          3             14/12/2013

5       17          3              dfsdf         1             15/11/2013

here 1 is approve and 3 is pending

"i use dropdown and in dropdown i fill all values in dropdown "approve,reject ,pending"

so when "kat" agian to see his /her document it sees me like this
dropdown[^]

C#

docname fileuploaded uploadedate deptype status

finad   fina.docx   04/11/2013  finance reject

i use sp like this

SQL

ALTER procedure [dbo].[approveddd]
@DocID int,
@ApproveID int,     
@ApproveBy nvarchar(50),
@DesigID int
as

IF EXISTS(Select DocID from Approval where DocID=@DocID and ApproveBy=@ApproveBy)
Update Approval set ApproveID=@ApproveID where DocID=@DocID and ApproveBy=@ApproveBy
ELSE
insert Approval (DocID,ApproveID,ApproveBy,DesigID,ApproveDate)
values(@DocID,@ApproveID,@ApproveBy,@DesigID,GETDATE())

Posted 3-Feb-14 0:59am

Diya Ayesa

Updated 4-Feb-14 18:08pm

v3

Add a Solution

Comments

ZurdoDev 3-Feb-14 10:35am

Sorry, what's the question?

Diya Ayesa 3-Feb-14 12:04pm

is there any solution to stop save records in table which is already exists in table.. like in above once supervsior approve/reject 16 document then when any supervsior approve/reject documents then 16 document record not save in table beacuse this is aleardy approve/reject

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andrius Leonavicius · Answer 1 · 2014-02-03T08:09:00

Solution 1

Hi,

Your stored procedure should look something like this (added code in bold):

SQL

ALTER procedure [dbo].[approveddd]
@DocID int,
@ApproveID int,     
@ApproveBy nvarchar(50),
@DesigID int
as

IF NOT EXISTS(SELECT 1
		FROM Approval
		WHERE DocID = @DocID
			AND (ApproveID = 1
				OR ApproveID = 2))
   BEGIN
	IF EXISTS(Select DocID from Approval where DocID=@DocID and ApproveBy=@ApproveBy)
	Update Approval set ApproveID=@ApproveID where DocID=@DocID and ApproveBy=@ApproveBy
	ELSE
	insert Approval (DocID,ApproveID,ApproveBy,DesigID,ApproveDate)
	values(@DocID,@ApproveID,@ApproveBy,@DesigID,GETDATE())
   END

Here, I am assuming that ApproveID = 1 - document is approved, ApproveID = 2 - document is rejected (you may want to parametrize this).

Posted 3-Feb-14 8:09am

Andrius Leonavicius

Comments

Diya Ayesa 4-Feb-14 0:06am

ok i try this when i upload new document and then supervsior wants to approve this document then record not goes to table
here is code
protected void Button1_Click(object sender, EventArgs e)
{ try
{
string connStr = ConfigurationManager.ConnectionStrings["mydms"].ConnectionString;
SqlConnection mySQLconnection = new SqlConnection(connStr);
string empId = string.Empty;
DataTable dt = new DataTable();

if (mySQLconnection.State == ConnectionState.Closed)
{
mySQLconnection.Open();

for (int i = 0; i < Repeater2.Items.Count; i++)
{
DropDownList DropDownListcontrol = ((DropDownList)Repeater2.Items[i].FindControl("DropDownList4"));
HiddenField DocId = ((HiddenField)Repeater2.Items[i].FindControl("DocId"));
SqlCommand cmd = new SqlCommand("approveddd", mySQLconnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@DocID", SqlDbType.Int).Value = Convert.ToInt32((DocId.Value));
cmd.Parameters.Add("@ApproveID", SqlDbType.Int).Value = Convert.ToInt32(DropDownListcontrol.SelectedValue);
cmd.Parameters.Add("@ApproveBy", SqlDbType.VarChar, 50).Value = (Session["Login2"]);
cmd.Parameters.Add("@DesigID", SqlDbType.Int).Value = (Session["UserDesignationID"]);
string DocName = ((Label)Repeater2.Items[i].FindControl("DocName")).Text;
string emailId = ((Label)Repeater2.Items[i].FindControl("YourEamil")).Text;
DropDownList dropdownvalue = ((DropDownList)Repeater2.Items[i].FindControl("DropDownList4"));
string approver = Session["Login2"] != null ? Session["Login2"].ToString() : string.Empty;
string Designationn = Session["UserDesignationID"] != null ? Session["UserDesignationID"].ToString() : string.Empty;
string docname = String.Empty;
string emailID = String.Empty;
string dropdownvalues = String.Empty;
if (Session["Login2"] != null)
{
approver = Session["Login2"].ToString();
}
if (Session["UserDesignationID"] != null)
{
Designationn = Session["UserDesignationID"].ToString();
}

if (DocName.ToString() != "")
{
docname = DocName.ToString();
}
else
{
docname = "Unavailable";
}
if (emailId.ToString() != "")
{
emailID = emailId.ToString();
}
else
{
emailID = "Unavailable";
}

if (dropdownvalue.SelectedItem.ToString() != "")
{
dropdownvalues = dropdownvalue.SelectedItem.ToString();
}
else
{
dropdownvalues = "Unavailable";
}
SendEmailUsingGmail(DocName, emailId, dropdownvalues, approver, Designationn);
cmd.ExecuteNonQuery();
Successz.Text = "Documents Approve and Email Successfully Send To Users"; Convert.ToInt32(DropDownListcontrol.SelectedValue)); }
}
else

Andrius Leonavicius 4-Feb-14 19:56pm

Hi,

How exactly you have applied my solution? If you just "copy-pasted", then it shouldn't work as you expect. I was unsure about ApproveID values, so I made some assumptions in my solution. Could you tell me more about it?

Or maybe I'm not understanding what you are trying to achieve. Could you please tell me how exactly it should work?

Diya Ayesa 5-Feb-14 0:17am

1=approve 2=reject 3=pending

when any user upload new document then this document goes for approval to supervisor then suppose supervisor approve this document then this document approveID goes to 1(approve) in table then when any user upload new document then this document also goes for approval to supervisor suppose supervisor approve this document then this document approveID goes to table that is 1 ... now here the problem is when supervisor already approve first document and the data also save in table with approveID 1(approve) and then supervisor approve 2nd document then data also save in table but with 2nd document data 1st document data again update in table and approve id changes with 1(approve) to 3(pending)

HOPE U GET MY POINT

Andrius Leonavicius 6-Feb-14 9:55am

Hi,

I think I get it now. Please try this one:

ALTER PROCEDURE [dbo].[approveddd] @DocID INT,
@ApproveID INT,
@ApproveBy NVARCHAR(50),
@DesigID INT
AS
IF EXISTS(SELECT 1
FROM Approval
WHERE DocID = @DocID
AND ApproveBy = @ApproveBy
AND ApproveID = 3)
UPDATE Approval
SET ApproveID = @ApproveID
WHERE DocID = @DocID
AND ApproveBy = @ApproveBy
AND ApproveID = 3
ELSE IF NOT EXISTS (SELECT 1
FROM Approval
WHERE DocID = @DocID
AND ApproveBy = @ApproveBy)
INSERT Approval
(DocID,
ApproveID,
ApproveBy,
DesigID,
ApproveDate)
VALUES(@DocID,
@ApproveID,
@ApproveBy,
@DesigID,
GETDATE())

Let me know it it works for you.