store procedure with parameters

11 posts, 0 answers
  1. Leszek
    Leszek avatar
    8 posts
    Member since:
    Jan 2009

    Posted 01 Apr 2009 Link to this post

    Hello,
    How I can use store procedires with parameters? I would like to fill DataAdapter depends on parameters.
    Could you heplp me?
    Regards.
    Leszek
  2. Steve
    Admin
    Steve avatar
    10941 posts

    Posted 01 Apr 2009 Link to this post

    Hi Leszek,

    Please take a look at the following KB article that explains how to work with stored procedures and let us know if further help is needed.

    Regards,
    Steve
    the Telerik team

    Check out Telerik Trainer , the state of the art learning tool for Telerik products.
  3. DevCraft banner
  4. mattc
    mattc avatar
    21 posts
    Member since:
    Aug 2007

    Posted 02 Apr 2009 Link to this post

    hi

    I want to pass a GUID to a query as a parameter so going through the steps in the above article. However, in the report parameters collection in the GUI there isn't a type for uniqueidentifier, so I get a type clash when I run the report...

    Matt
  5. Steve
    Admin
    Steve avatar
    10941 posts

    Posted 02 Apr 2009 Link to this post

    Hi mattc,

    You need to set the type of the report parameter as string  and filter on the GUID column without problems e.g.:

    this.Filters.Add(new Telerik.Reporting.Data.Filter("=Fields.rowguid", Telerik.Reporting.Data.FilterOperator.Equal, "= Parameters.paramGuid"));

    and here is the parameter :

    this.ReportParameters["paramGuid"].Value = "cfbda25c-df71-47a7-b81b-64ee161aa37c";

    Sincerely yours,
    Steve
    the Telerik team

    Check out Telerik Trainer , the state of the art learning tool for Telerik products.
  6. mattc
    mattc avatar
    21 posts
    Member since:
    Aug 2007

    Posted 02 Apr 2009 Link to this post

    Hi Steve

    thanks for the reply.
    I'm only just starting using the reporting product so correct me if I'm wrong.
    Wouldn't this solution mean I was filtering in the report and would have retrieved all the records from the DB? Really don't want to do this as the table is very large and seems wasteful?

    Regards
    Matt
  7. Leszek
    Leszek avatar
    8 posts
    Member since:
    Jan 2009

    Posted 02 Apr 2009 Link to this post

    Hi, I have the same problem with guid.
    I have to send parameter (Guid) into store procedure. I do't want select all of records from databases and after to filter it. It could be 100 000 records.
    Regards.
    Leszek
  8. Steve
    Admin
    Steve avatar
    10941 posts

    Posted 02 Apr 2009 Link to this post

    Hi guys,

    There is no problem to do that as well. Make sure the report parameter's type is string and the Guid class contains an overload which accepts string as argument, so make sure you handle it accordingly.

    Sincerely yours,
    Steve
    the Telerik team

    Check out Telerik Trainer , the state of the art learning tool for Telerik products.
  9. Matthew Hile
    Matthew Hile avatar
    23 posts
    Member since:
    Aug 2008

    Posted 07 Apr 2009 Link to this post

    Below is a code snipit from T-SQL proc that takes a passed string, converts it to a GUID (uniqueidentifier) and uses in the select statement. On the SQL Server side this took the passed string parameter from the report and returned the record of interest.

    CREATE PROCEDURE procRptPrf
        @PId varchar(50)
        
    AS
    BEGIN

        DECLARE @ParticipantId as uniqueidentifier
        SELECT @ParticipantId = cast(@Pid as uniqueidentifier)


        SELECT  [ProgramName]
          ,[SiteName]
          ...
          ,[StageDrug]
      FROM [Sbirt].[dbo].[vwRptPfr]
      WHERE @ParticipantId = ParticipantId
    END

  10. Leszek
    Leszek avatar
    8 posts
    Member since:
    Jan 2009

    Posted 09 Apr 2009 Link to this post

    Hi,
    It is very easy when you add DataSource programatycly

    //constructor

     

     

    public void MyReport()

     

    {

     

    InitializeComponents();
    SqlConnection
    sqlConnection1 = new SqlConnection();

     

     

    SqlCommand sqlSelectCommand1 = new SqlCommand();

     

     

    SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter();

     

     

    // define a connection string

     

     

     

     

    sqlConnection1.ConnectionString = Properties.

    Settings.Default.RRLevel1DBCS;

     

     

    // define the command object properties

     

     

     

     

    sqlSelectCommand1.CommandType =

    CommandType.StoredProcedure;

     

    sqlSelectCommand1.CommandText =

    "SelectNewExceptionsForAssetRep";

     

    sqlSelectCommand1.Parameters.Add(

    new SqlParameter("@AssetGuid", gu));

     

    sqlSelectCommand1.Connection = sqlConnection1;

    sqlDataAdapter1.SelectCommand = sqlSelectCommand1;

     

    this.DataSource = sqlDataAdapter1;

     

    }

  11. oscarcuestameza
    oscarcuestameza avatar
    2 posts
    Member since:
    Dec 2006

    Posted 21 Apr 2009 Link to this post

    Hi,
    How can I do that, but for a Report that is "called" in a subreport item ?
    I'm trying to pass parameters for a report within subreport, but I can't get work.  The data for report (the report called within subreport item as report source) is too from a stored procedure.
    Thanks in advance.
    PD. I'm using the trial version of Reporting (v3.0), VisualStudio 2008 and C#
  12. clayton
    clayton avatar
    1 posts
    Member since:
    Feb 2013

    Posted 13 Nov 2013 Link to this post

    Matthew Hile,

    Thanks for posting the script. This saved me a lot of time!
Back to Top
DevCraft banner