Click here to Skip to main content
15,916,280 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
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
Updated 4-Feb-14 18:08pm
v3
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

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).
 
Share this answer
 
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900