Re: how to access final SQL from parameterized ADO

Here's why: I want to save the complete query so a user can name it and
then run it again at a later date...

In a web application, my users use different means (query by
example form or report builder interface) to construct a query against
a database. The query is run and the user views a report of the
results. The user then has the option to "save" this report as a named
report.

If I could access the query sent to SQL Server, I could easily store
this away in the database and allow the user to later retrieve it, then
pass it to the cmd.CommandText and have ADO run the query again. Very
easy and efficient plus I know the query was constructed correctly and
will accurately return the original results.

However, using paramaterized queries (which we all should for better
datatype checking, security, performance, etc) now complicates things
as I cannot save a simple query generated by a user. The original SQL
with my embedded question mark placeholders is useless without the
original values and datatypes. And we wonder why so many developers
continue to use concatenated strings to build their SQL statements. I
would bet this is one of them.

One final question...is it possible under ADO 2.8 to serialalize the
parameters collection so it could be stored and later retrieved? If
yes, would this retain the values added to those parameters? If yes,
any assistance in that direction would be appreciated.

TIA


Bob Barrows [MVP] wrote:
dondraper wrote:
Sorry, I was not clear enough. I need to access the SQL from ADO after
the execute has occurred. I want to be able to store the entire sql so
that it may retrieved in the future and same query ran again without
the need for the original query values.

Is there a way to do this from ADO?


No. all you can do is save the parameter values somewhere (perhaps in
Session or Application) and re-use them with the same sql statement (or
with a stored procedure).

Why would you need to be able to do this?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

.

Relevant Pages

  • Re: Using SQL statement to append a recordset to a table
    ... To insert a value into the field Priority: ... > all failed mean while each time my report was working fine. ... Used the query> wizard "Find Unmatched Query Wizard" to create a query between these two> tables, changed to an append query, appending to the ... > table (looking at the sql view along the way). ...
    (microsoft.public.access.formscoding)
  • Re: Creating Access DB
    ... database and in the SQL database that your report needs. ... click the query tab. ...
    (microsoft.public.excel.programming)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)