update database based on gridview value

Question

I have a grid view which uses SQL to calculate information that I need! I want to put this calculated total into another table in database.

SELECT StaffDetails.StaffID, SUM(HolidayRequests.RequestTotalDays) AS Expr1 
FROM HolidayRequests INNER JOIN StaffDetails ON HolidayRequests.Username = StaffDetails.UserName 
WHERE (StaffDetails.StaffID = @staffID) 
GROUP BY StaffDetails.StaffID, HolidayRequests.ApprovalStatus 
HAVING (HolidayRequests.ApprovalStatus = N'approved')

It basically calculates the total number of approved holiday requests for a staff member which is correct. I want this number to then update the HolidayTaken field in another table each time a holiday is approved.

I have tried an update however you cannot use an aggregate in an update as I have found...Any idea how else I can do this..

NicoTek · Accepted Answer · 2012-03-13 12:56:39Z

2

you could load the executed Query into a "DataReader" or "DataSet" and from there load the information to the new Table.

you could do:

 SqlConnection conn = "You connection";
 SqlCommand cmd = new SqlCommand(conn);
 cmd.CommandText = "YOUR QUERY";
 SqlDataReader rdr = cmd.ExecuteReader();
 String UpdateQuery;

 while (rdr.Read())
  {
   UpdateQuery = "UPDATE table set col1=@Expr1 WHERE condition"
   cmd.Parameters.AddWithValue("@Expr1", rdr["Expr1"].ToString());
    //run update query
    cmd.CommandText = UpdateQuery;
    cmd.ExecuteNonQuery();
    return;
  }
    cmd.Connection.Close();

answered Mar 13, 2012 at 12:56

NicoTek

1,1671 gold badge14 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Woelig · Accepted Answer · 2012-03-13 12:48:40Z

1

Try this

UPDATE StaffDetails a
SET a.HolidayTaken = (SELECT SUM(HolidayRequests.RequestTotalDays)
FROM HolidayRequests INNER JOIN StaffDetails b ON HolidayRequests.Username = b.UserName 
WHERE (b.StaffID = @staffID) 
GROUP BY b.StaffID, HolidayRequests.ApprovalStatus 
HAVING (HolidayRequests.ApprovalStatus = N'approved'))
WHERE a.StaffID = @staffID

answered Mar 13, 2012 at 12:48

Woelig

626 bronze badges

Collectives™ on Stack Overflow

update database based on gridview value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related