Solved

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

Posted on 2015-01-14
4
135 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
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

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 …
What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
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.
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.

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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now