0

I would like to update the DB table by taking user input from SSRS front end.And then, when I click generate report, this should get the user input values from DB table (that were previously updated in DB) and render/display in the same report body. Ref: https://github.com/JerBoon/updating_with_SSRS

Here is how I'm trying to achieve this:

  1. Created 2 parameters for user to input the text.

  2. In DataSet4, I'm writing update query to update the DB. enter image description here

  3. No errors

  4. report preview. enter image description here

5.Here is the table structure that needs to be updated: enter image description here 

USE [AdventureWorks2017]
 GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WIP](
[WIPID] [int] IDENTITY(1,1) NOT NULL,
[Cata] [nvarchar](50) NULL,
[Cout] [int] NULL,
[Remark] [nvarchar](max) NULL,
[Ack] [nvarchar](max) NULL,
CONSTRAINT [PK_WIP] PRIMARY KEY CLUSTERED 
(
   [WIPID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = 
  OFF, 
  ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 GO

However, the problem is : I'm unable to update the table and also unable to render those inputted by user. Below image: enter image description here

The user input should have been rendered, which is not. Hope I explained this correctly. What am I doing wrong?

I would like to achieve this in a single report. That is, same report should be used for user input and display that were inputted by user. How can I achieve this in SSRS 2016/2017? Unable to find something suitable over the net. It looks like there are 3rd party tools used, However, I would like to do this using SSRS in build feature.Thanks.

AskMe
  • 2,495
  • 8
  • 49
  • 102
  • Why do you need to take the values, store them, and then run a query based on those stored values? Why not just use a parametrised query? Then there's no need to store them. – Thom A May 04 '19 at 09:53
  • I understood. However, I need to store them as it may be required in the future. – AskMe May 04 '19 at 10:30
  • What would be, knowing what parameters the user passed? This, as it stands, sounds like an XY Problem. – Thom A May 04 '19 at 10:41
  • Modified the question. I'm able to clear the error now. However, the update statement is not updating the table. – AskMe May 04 '19 at 11:17
  • *"However, the update statement is not updating the table."* This is impossible for us to trounle shoot without being able to replicate the problem. Best guess, your `UPDATE` statement is wrong. – Thom A May 04 '19 at 11:31
  • Anyways, used SP to make it work. Thanks. – AskMe May 04 '19 at 11:44
  • 2
    The original problem was that you had declared the parameters in your dataset query. If you had commented out the 2 declare statements it would work assuming the names in the UPDATE statement matched the name of the SSRS parameter exactly (case sensitive) – Alan Schofield May 04 '19 at 19:35
  • As Alan mentioned, your update statements was declaring it's own parameters and was SETting your values to NULL since you don't have the SSRS parameters mapped to the query's parameters. – Hannover Fist May 06 '19 at 21:57

0 Answers0