i am developing a small website where users first upload excel sheet with project names, the next page i have a gridview which displays results of stored procedure Report. the report shows learners extracted from the database using the project names. the problem i have is that when i upload new projects the gridview shows the old data or is empty. i tried to truncate the table first then run the procedure but then my gridview is empty. here is the code and stored procedure. would really appreciate the help
- protected void Page_Load(object sender, EventArgs e)
- {
- if (!IsPostBack)
- {
- bind();
- }
- }
- public void bind(){
- SqlConnection con = new SqlConnection(strConnString);
- con.Open();
- SqlCommand command = new SqlCommand("BenReport", con) { CommandType = System.Data.CommandType.StoredProcedure };
- SqlDataAdapter sda = new SqlDataAdapter("BenReport", con);
- command.Connection = con;
- sda.SelectCommand = command;
- DataSet ds = new DataSet();
- sda.Fill(ds);
- GRDBencount.DataSource = ds.Tables[0];
- GRDBencount.DataBind();
- con.Close();
- }
stored procedure - ALTER PROCEDURE BenReport
- AS
- INSERT INTO [Verify_Pbank_Projects_MassT]
- SELECT DISTINCT master.id,PPRS.PPR_Caption ,CAST(replace(substring(PPR_Caption,1,CHARINDEX('[',PPR_Caption)),'[','')AS DATE)AS UPLOAD_DATE,PPRS.[Upload_Date]
- From PPRS
- left join master
- on PPRS.PPR_Caption = masters.Caption
- WHERE PPR_Caption IN (SELECT DISTINCT master.Caption FROM master)
-
- SELECT G.ParentId ,(Select Caption from Master Where ID = G.ParentId) as PPRName,
- COUNT(DISTINCT G.Field_972) as BeneficiaryCnt ,
- SUM(CASE WHEN ( (G.AccountNumber IS NOT NULL AND G.AccountNumber <> 0) AND NOT
- ((ISNULL(cast(ROUND(G.FIELD_647,0) as decimal(18,0)),0)+ISNULL(cast(ROUND(G.FIELD_649,0) as decimal(18,0)),0)+ISNULL(cast(ROUND(G.FIELD_1233,0) as decimal(18,0)),0)) * ISNULL(cast(ROUND(G.FIELD_648,2) as decimal(18,2)),0)) = 0 ) THEN 1 ELSE 0 END) as Ben_Account_Included,
- SUM(CASE WHEN NOT (G.AccountNumber IS NOT NULL AND G.AccountNumber <> 0) THEN 1 ELSE 0 END) as Ben_ZeroAccount_Excluded,
- SUM(CASE WHEN
- ((ISNULL(cast(ROUND(G.FIELD_647,0) as decimal(18,0)),0)+ISNULL(cast(ROUND(G.FIELD_649,0) as decimal(18,0)),0)+ISNULL(cast(ROUND(G.FIELD_1233,0) as decimal(18,0)),0)) * ISNULL(cast(ROUND(G.FIELD_648,2) as decimal(18,2)),0)) = 0
- THEN 1 ELSE 0 END) as Ben_ZeroWages_Excluded
- FROM pb_g164 G with(nolock)
- INNER JOIN P_data_search with(nolock)
- ON P_data_search.RecordID = cast(G.FIELD_972 as int)
- INNER JOIN C_ListItems with(nolock)
- ON C_ListItems.ItemID = cast(G.FIELD_645 as int)
- INNER JOIN
- (SELECT m2.id as PPR_Recordid,m2.Caption,
- CAST(Substring(m2.Caption, 1,Charindex('[', m2.Caption)-1) as date) PPRDate
- FROM dbo.Masterm with (nolock)
- INNER JOIN dbo.Master m1 with (nolock) on m.ID=m1.ParentRecordID and m1.TypeID=77
- INNER JOIN dbo.Master m2 with (nolock) on m1.ID=m2.ParentRecordID and m2.TypeID=82 and m2.TransactionID>1
- INNER JOIN dbo.Master_Status ms on m.Status=ms.Status
- INNER JOIN dbo.Master_Status msp on m2.Status=msp.Status
- INNER JOIN dbo.[Verify_Pbank_Projects_MassT] vp with (nolock) on vp.PPRName = m2.Caption AND vp.PPRDate = CAST(Substring(m2.Caption, 1,Charindex('[', m2.Caption)-1) as DateTime)
- WHERE m.TypeID=72
- ) pb
- ON pb.PPR_Recordid = G.ParentId
- WHERE G.isActive = 1 and G.Status = 'C'
-
- group by G.ParentId