Solved

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

Posted on 2015-01-14
4
134 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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Oh, yes.  Thank you for that suggestion.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Developer tools in browsers have been around for a while, yet they are still heavily underused by developers. Developers still fix html or CSS then refresh page to see effect, or they put alert or debugger in JavaScript and then try again and again …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now