Last post Mar 12, 2008 09:00 AM by Mikesdotnetting
Mar 12, 2008 06:52 AM|curlydog|LINK
I'm currently trying to convert an Access database for use via .aspx web pages. Previously forms and queries were used to access the data, now I have to create the sql which will retreive the data I want.
One particular report in the database used SQL to select data from two queries which are stored in the database ( the queries selected specific data and then the sql would select the required data from the queries).
I understand that a single sql query would be the best option so that all the data I need is obtained in a single go. Unfortunately the database was written by someone else and it is difficult interpreting their sql to find out wht everything is doing.
What I'm trying to establish is if there is a way to run the queries in Access and the have my page query the queries(??)
or, can I use something like multiple datasets. Can I fill two datasets with the data the queries would retrieve and then query these datasets to get the data I need.
Mar 12, 2008 07:27 AM|Mikesdotnetting|LINK
You can create a query in Access, then save it. Once you have done that, you can query that query: "Select field1, field2, field5 From MySavedQuery"
Mar 12, 2008 07:52 AM|curlydog|LINK
Thanks for the swift response Mike.
Unfortunately it's not that simple (should have explained myself better).
The queries in the database use values selected on forms to form part of their SQL. Is there a way I can pass thse values to them?
for example one of the queries contains the line
WHERE (((qTagNo_ITRs.sTagNoId) Like [Forms]![frmdlgITRStatus]![stagnoid]))
I need to be able to pass the query the values for [Forms]![frmdlgITRStatus]![stagnoid#
Mar 12, 2008 09:00 AM|Mikesdotnetting|LINK
Yes, you can pass the values from the web form to the SQL as parameters. Clearly you can't use the Access forms in a web application, but this should help you a bit more:
In the case above, you would probably replicate the parts of the Access form you want to work with as a web form, and the SQL will be
..... WHERE qTagNo_ITRs.sTagNoId LIKE '%' + ? + '%'
The ? is the parameter marker. You would add the parameter to the command object, which tells ASP.NET where to get the value for the parameter from. In the case of a like-for-like web form. the source may well be
a TextBox called stagnoid, in which case the parameter source will be
% are wildcard characters which must be used with the
LIKE predicate. If you are not looking for wildcard matching, then the SQL should be
WHERE field = ?