locked
DB tables records/data update using SSRS reports? RRS feed

  • Question

  • Hi All,

    I was asked into an interview, "Can we update/delete DB tables records using SSRS reports, if yes how?


    I'll appreciated for replies


    Thanks


    regards,
    Kumar
    Friday, June 19, 2009 7:30 PM

Answers

  • Hi Kumar,

    I tested it by creating a new table in my database as:
    Create table Test
    (
    	id int identity not null primary key,
    	testdata varchar(100)
    )
    Then I created a new report and added a dataset with the following query:
    Insert into Test Values ( CONVERT(varchar, GETDATE(), 113) )
    Select * from Test
    I then displayed the dataset using a Table on my report. Now, upon every run, I see a new row added to my table. Is this what you were looking for?

    Regards,

    Syed Mehroz Alam
    My Blog | My Articles
    • Marked as answer by gk1393 Sunday, June 21, 2009 8:35 PM
    Sunday, June 21, 2009 1:58 PM

All replies

  • Hi Kumar,

    Of course you can. Since SSRS executes the query written for a dataset regardless of knowing what is the consequence, so you can do inserts/updates/deletes in that query.

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    Saturday, June 20, 2009 4:45 AM
  • Hi Syed,

    Can you show me an example about the same, this is because of I have created one report using Report Server Wizard:-

    1. Added Shared Data Source
    2. Under Reports folder Added New item

    I have added 2 New items, first is using simple query "Select * from Table3" (Table3 is table_name) which runs perfectly into report designer as well as into report manager after deploying.

    But the second report I created using query "Insert into table3 (Id, Data, ExtraText) values (5, 'D', 'd')" after writting this query no available fields come into the next wizard and hence I'm unable to Display fields depending on "Details/Group" and finally after creating it gives me error as:-


    One or more errors encountered while loading the designer. The errors are listed below.

    Deserialization failed: The element 'TableCells' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition' has incomplete content. List of possible elements expected: 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:TableCell'. Line 38, position 16.

     

    Edit code

     



    So can you tell me how to do, I mean how to use DML statements (insert/update/delete) queries from where we can update DB table records using SSRS reports


    Thanks


    regards,
    Kumar
    Saturday, June 20, 2009 4:16 PM
  • Hi Kumar,

    I tested it by creating a new table in my database as:
    Create table Test
    (
    	id int identity not null primary key,
    	testdata varchar(100)
    )
    Then I created a new report and added a dataset with the following query:
    Insert into Test Values ( CONVERT(varchar, GETDATE(), 113) )
    Select * from Test
    I then displayed the dataset using a Table on my report. Now, upon every run, I see a new row added to my table. Is this what you were looking for?

    Regards,

    Syed Mehroz Alam
    My Blog | My Articles
    • Marked as answer by gk1393 Sunday, June 21, 2009 8:35 PM
    Sunday, June 21, 2009 1:58 PM
  • Awesome.... Thanks Syed!!!

    I was looking the same, I was thinking only we can use "Select" statement while creating reports but sorry I was wrong I appoligize for the same.

    Also I think I was getting error because I didn't use Select query with the Insert query, also everytime I referesh report it not even inserted data into Report Data Source but also into DB tables wow that is really great, thanks once again for your time towards me.


    regards,
    Kumar
    Sunday, June 21, 2009 8:38 PM