?
Solved

How to initiate an UPDATE to a table for SSRS and an ASP.Net web application

Posted on 2015-01-14
4
Medium Priority
?
145 Views
Last Modified: 2015-01-20
I'm sure this is a simple problem.  The explaining it will be the hardest part.  ;-)  

#1.  On our SQL Server is a database (Gazelle) used by a 3rd party software that we use for customer tracking.  We can not make any changes to the structure of that database.  We can't add stored procedures or triggers or new tables or fields to existing tables.  We can't touch Gazelle.

#2.  We have a Youth Employment Program that consists of a set of records from the Gazelle database.  We created a SQL View to pull in that set of records.

#3.  We need to enter dates to track milestone completions.  For that, I've created a Table in the same database as the View that's pulling data from the Gazelle database.  Those Table and View are in our own in-house database so we can modify it as needed.  I've added new fields for those milestones and fields for completion dates.  Staff will enter completion dates as necessary in a ASP.Net Web Application (VB.Net).

Okay.  The View, of course, stays up to date with changes to the Gazelle database.  But, I've got the Table for entering the milestone dates which needs to be "refreshed" or updated periodically from the View so it has the same set of records.

I think for the web application, I should be able to just put this MERGE query into the Page_Load or a "Refresh" button or both.  
MERGE [TempTables].[dbo].[WIAYouthITADates] da
USING [TempTables].[dbo].[vw_WIAYouthITA] vw
ON da.PID = vw.ParticipantId

WHEN MATCHED AND
  da.ActualEndDate IS NOT NULL THEN
  DELETE
  
WHEN MATCHED THEN
  UPDATE
  SET 
	da.Client = vw.client,
	da.SSN = vw.last4ssn,
	da.ProgramStart = vw.ProgramStart,
	da.AnticipatedEndDate = vw.AnticipatedEndDate,
	da.ActualEndDate = vw.ActualEndDate

WHEN NOT MATCHED BY TARGET THEN
  INSERT (PID, Client, SSN, ProgramStart, AnticipatedEndDate, ActualEndDate)
  VALUES (vw.ParticipantId, vw.client, vw.last4ssn, vw.ProgramStart, vw.AnticipatedEndDate, vw.ActualEndDate);
   
 
SELECT * 
--DELETE
FROM [TempTables].[dbo].[WIAYouthITADates]

Open in new window


Sorry for the long background information...
Here's my Question...  

I also have an SSRS Report to show the status of the milestone dates that have been entered into my in-house table.  

How can I update the table for the SSRS user(s) if the ASP.Net application hasn't been accessed in a while to update the table?

Thanks.

TLDR:  How do you update a Table from a View in SSRS?  SSRS displays the Table.  The table needs to be "refreshed" or updated from an up to date View.
0
Comment
Question by:megnin
  • 2
  • 2
4 Comments
 
LVL 37

Accepted Solution

by:
ValentinoV earned 2000 total points
ID: 40550798
I'm not sure I get what you're asking.  If the view returns the up-to-date information and the SSRS dataset uses the view then the report should also display the up-to-date info.

Are you asking how you can have SSRS execute an update (or merge) statement so that the data gets updated before it's returned to the report user?  In that case I believe it should be possible by adding the update statement to your dataset that's returning the data for the report, basically goes like this:

<UPDATE/MERGE statement>

<SELECT statement>

The SSRS engine will execute the update before the select and should thus return updated information.
0
 
LVL 1

Author Comment

by:megnin
ID: 40551413
Your second assumption is correct.  SSRS is using my custom Table, not the View as the data source, so I need to do the UPDATE/MERGE statement.

I wasn't sure you could include an UPDATE in the SSRS dataset.  That will make it much easier.

Thank you!
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 40553340
Another option is to move all the T-SQL code to a stored procedure and then call the stored proc from the dataset, could be more interesting in terms of maintenance :)
0
 
LVL 1

Author Comment

by:megnin
ID: 40559771
Oh, yes.  Thank you for that suggestion.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Without even knowing it, most of us are using web applications on a daily basis.  In fact, Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We generally confuse these web applications to…
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to selectively show certain fields based on user input using rules to gather relevant information and data from your forms. The rules feature provides you with an opportunity…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question