locked
How to create strongly typed datasets with access parameter queries RRS feed

  • Question

  • User1574836231 posted

    Hi

    How can you create strongly typed datasets using an access database against access select statements that use parameters?

    The problem is VS.Net doesn't allow select queries with parameters to be dragged onto a form, it only allows access queries without parameters!

    I also tried the dataadapter wizard, but again it only allows me to select queries without parameters?

    Many thanks in advance

     

    Monday, January 9, 2006 3:30 PM

All replies

  • User600176218 posted
    You can create a dataset and use the WriteXmlSchema method.  It's well documented in the SDK, and I have an example at http://aspadvice.com/blogs/rjdudley/archive/2005/04/27/2585.aspx.  You'll have to adjust the SQL Server code fo rODBC, but that shouldn't be too hard.
    Tuesday, January 10, 2006 4:24 PM
  • User1574836231 posted

    Hi

    Thanks alot that has helped alot

    From your experience with crystal reports, is designing the report using a strongly typed dataset the best way to do it?

    I want to source my crystal report with stored procedures and I also like to handle the connecting and filling of the datasource in my code behind page, so the dataset source for the crystal report seems the best option?

    Also can I ask you how can you fill sub-reports from your code behind page? I want to populate datasets in my code behind page and set them in crystal report. I think I know how to do it for a master report but for sub-reports I do not know how to do it?

    Many thanks!

    Wednesday, January 11, 2006 2:56 PM
  • User600176218 posted

    In my experience, yes.  My reports don't use simple queries, and the easiest way to manage the SQL is in a stored procedure.  For any given report, I pass in a user ID and up to 7 or 8 additional parameters.  I then build the select query dynamically based on the parameter input, and join to user and security tables.  I've also had to modify the queries to include other tables, and I was able to do that right in teh SQL and not even touch the report.

    One advantage is that only the data that should appear on the report is returned.  The report only needs to group and format the results, not filter or join or antything like that.

    The downside is that in VS 2003, dataset handling is slower than directly querying the database.  But for me, the trade-off is worth it.  In VS 2005, this has apparently been improved.

    I'm going to have to find the one time I did use subreports and see how I passed the data to them.  I think I just used hierarchical dot notation and SetDataSource.

    Wednesday, January 11, 2006 4:45 PM
  • User1574836231 posted

    Thanks alot

    The typed dataset seems the best way to go then.

    It would be nice if you can set the sub-reports in the same way as the main report ie using setdatasource?

     

    Thursday, January 12, 2006 5:06 AM
  • User600176218 posted
    It's similar, using object-dot-something-dot-something-dot-...  I'll track that down, or you could probably figure it out by fiddling.
    Thursday, January 12, 2006 9:10 AM
  • User506793051 posted

    I know this is a very old topic but I came here looking for the answer and thought the original answer needed updating (or maybe I am misunderstanding the question). To make a parametized query for Microsoft Access in a Typed Dataset, simply insert the parameters you need into the SQL by placing [brackets] around the parameters like so:

    select field1 from table1 where id = [IDparam]


    When you save the changes, Visual Studio will treat IDparam as a parameter. Next, click on the ... button next to the Parameters property of the Typed Dataset query properties (in the properties pane) and add an appropriate parameter. The name of the parameter does not have to match - the data provider will use the parameter in index order - but you should make them match anyway as a good coding practice.

    After saving the entire Typed Dataset and compiling, your new Typed TableAdapter should have a method with the correct signature.

    Wednesday, August 25, 2010 12:04 PM