CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2012
Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5787

SSRS Textbox action is executed only once

I am trying to create the report that would have action link in the text box that would update the record in database. First of all I've created datasource with the "Execute in single transaction" setting on. I've created dataset that calls the stored procedure with param: enter image description here Then specified the param: enter image description here I've created the table where the last column is "Action".

In the TextBox properties, in the Action menu set to go to the same report and set param value to the one from the table: enter image description here

The thing is that it's working only once and if I click on the same field again then the procedure is not executed. I guess that param is not set, but I am not sure.

UPDATE:

After some playing I see, that variable is set so the problem is not in variable. Additionally if I click on another row then this row is updated as expected. After that I can updated the first row also, so the problem is that I can't update the same row twice in a roll.

CREATE PROCEDURE [dbo].[change_salesperson_status]
    @code AS CHAR(5)
AS
    BEGIN

       INSERT INTO dbo.test VALUES (@code);
        IF (@code = 'test') RETURN 1;

        UPDATE dbo.salespersons
           SET status_code = CASE WHEN status_code = 'I' THEN 'A' ELSE 'I' END
         WHERE code = @code;
    END;


GO

CREATE TABLE dbo.test
(
    a VARCHAR(200)
)

UPDATE2: I was able to workaround this. I've added internal datetime variable with default value =NOW() this forced SSRS to refresh report every time.

Upvotes: 0

Views: 616

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21748

This isn't really an answer, it's a guide to how I would do this but too long to put as a comment.

I've done quite a few of these reports and never had any issues. I suspect your workflow is not quite right.

What I normally do is write a dataset query that accepts parameters that update the database unless the default values are passed. I'm not sure what you are trying to do but if we take a simple example, creating a report called, say myReport which makes an employee inactive if a column is clicked then we might have a simple table 

EmpID    EmpName    MakeInactive
  1      Dave         click
  2      Bob          click

The dataset would look something like this...

SET NOCOUNT ON
IF @EmployeeID > 0
        BEGIN
            UPDATE myTable SET Active = 0 WHERE EmpID = @EmployeeID
        END

    SELECT EmpID, EmpName FROM myTable

The myReport report would have a single parameter @EmployeeID in this simple case and the default value would be -1 so when we first run the report, no updates occur.

In the last column you would do as you have already, that is, set the action to call the same report (myReport) with the @EmployeeID parameter set to the EmpID from the first column.

That's all that I would do, I don't use single transactions or anything else, I just do as above and it works.

Upvotes: 1

Related Questions