Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
A publishing tool, a Version Control System, or a Collaboration Platform! These can be some of the defining words for the two very famous web-hosting Git repositories: Bitbucket and Github. Git is widely used amongst the programmers and developers f…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…
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.

829 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