Hello,



I would like to be able to programmatically execute a query that

contains parameters.  The query must be a stored query, I cannot use an

SQL statement.  



For example, I have a query called 'qClients'.  Its SQL statement is:



SELECT [Client ID], ClientName, Street, Pcode

FROM qClients

WHERE (((qClients.[Client ID])=[Enter ClientID]));



'[Enter ClientID]' is the parameter that needs to be supplied.



I believe I can do so using ADO Commands?  Does anyone have an example

of this? 



My second question is (if this is possible) what happens when the query

I am trying to execute programmatically is based on a parameter query?

For example, if I have another query that is based on 'qClients' called

'qMelboureClients', (it shows all clients for Melbourne).  If I execute

this query, how do I specify the '[Enter ClientID]' parameter in its

base query?



Once again, I cannot use SQL statements and WHERE clauses.  



Any input would be appreciated,



Cheers,



Padgett



********************

Padgett Rowell 

Aspect7

Office: +61 (08) 9202 1433

Mobile: 0402 122 419

hope this helps



<%@LANGUAGE="VBSCRIPT"%>

<!--#include file="Connections/dsn_cs_reports.asp" -->

<%



Dim Command1__REQUEST_TYPE

Command1__REQUEST_TYPE = "%"

if(Session ("RT1") <> "") then Command1__REQUEST_TYPE = Session ("RT1")



Dim Command1__ANALYSIS1

Command1__ANALYSIS1 = "%"

if(Session ("AC1") <> "") then Command1__ANALYSIS1 = Session ("AC1")



Dim Command1__ANALYSIS2

Command1__ANALYSIS2 = "%"

if(Session ("AC2") <> "") then Command1__ANALYSIS2 = Session ("AC2")



Dim Command1__FROM_DATE

Command1__FROM_DATE = "%"

if(Session ("DATE1") <> "") then Command1__FROM_DATE = Session ("DATE1")



Dim Command1__TO_DATE

Command1__TO_DATE = "%"

if(Session ("DATE2") <> "") then Command1__TO_DATE = Session ("DATE2")



Dim Command1__GROUP_TYPE

Command1__GROUP_TYPE = "%"

if(Session ("GT1") <> "") then Command1__GROUP_TYPE = Session ("GT1")



Dim Command1__GROUP_NAME

Command1__GROUP_NAME = "%"

if(Session ("GN1") <> "") then Command1__GROUP_NAME = Session ("GN1")



%>

<%



set Command1 = Server.CreateObject("ADODB.Command")

Command1.ActiveConnection = MM_dsn_cs_reports_STRING

Command1.CommandText = "K_PRTY_PERF_IG_SUMMARY"

Command1.Parameters.Append Command1.CreateParameter("REQUEST_TYPE", 200, 

1,4,Command1__REQUEST_TYPE)

Command1.Parameters.Append Command1.CreateParameter("ANALYSIS1", 200, 

1,4,Command1__ANALYSIS1)

Command1.Parameters.Append Command1.CreateParameter("ANALYSIS2", 200, 

1,4,Command1__ANALYSIS2)

Command1.Parameters.Append Command1.CreateParameter("FROM_DATE",7, 

1,8,Command1__FROM_DATE)

Command1.Parameters.Append Command1.CreateParameter("TO_DATE",7, 

1,8,Command1__TO_DATE)

Command1.Parameters.Append Command1.CreateParameter("GROUP_TYPE", 200, 

1,5,Command1__GROUP_TYPE)

Command1.Parameters.Append Command1.CreateParameter("GROUP_NAME", 200, 

1,5,Command1__GROUP_NAME)

Command1.CommandType = 4

Command1.CommandTimeout = 0

Command1.Prepared = true

set rrsSUMMARY = Command1.Execute

rrsSUMMARY_numRows = 0



%>

<%

Dim Repeat1__numRows

Repeat1__numRows = 10

Dim Repeat1__index

Repeat1__index = 0

rrsSUMMARY_numRows = rrsSUMMARY_numRows + Repeat1__numRows

%>

<%

' *** Go To Record and Move To Record: create strings for maintaining URL 

and Form parameters



' create the list of parameters which should not be maintained

MM_removeList = "&index="

If (MM_paramName <> "") Then MM_removeList = MM_removeList & "&" & 

MM_paramName & "="

MM_keepURL="":MM_keepForm="":MM_keepBoth="":MM_keepNone=""



' add the URL parameters to the MM_keepURL string

For Each Item In Request.QueryString

  NextItem = "&" & Item & "="

  If (InStr(1,MM_removeList,NextItem,1) = 0) Then

    MM_keepURL = MM_keepURL & NextItem & Server.URLencode

(Request.QueryString(Item))

  End If

Next



' add the Form variables to the MM_keepForm string

For Each Item In Request.Form

  NextItem = "&" & Item & "="

  If (InStr(1,MM_removeList,NextItem,1) = 0) Then

    MM_keepForm = MM_keepForm & NextItem & Server.URLencode(Request.Form

(Item))

  End If

Next



' create the Form + URL string and remove the intial '&' from each of the 

strings

MM_keepBoth = MM_keepURL & MM_keepForm

if (MM_keepBoth <> "") Then MM_keepBoth = Right(MM_keepBoth, Len

(MM_keepBoth) - 1)

if (MM_keepURL <> "")  Then MM_keepURL  = Right(MM_keepURL, Len

(MM_keepURL) - 1)

if (MM_keepForm <> "") Then MM_keepForm = Right(MM_keepForm, Len

(MM_keepForm) - 1)



' a utility function used for adding additional parameters to these strings

Function MM_joinChar(firstItem)

  If (firstItem <> "") Then

    MM_joinChar = "&"

  Else

    MM_joinChar = ""

  End If

End Function

%>

<%Session("GT1") = Request.Form ("GT")%>

<%Session("GN1") = Request.Form ("GN")%>

<%Session("RT1") = Request.Form ("RT")%>

<%Session("AC1") = Request.Form ("AC1")%>

<%Session("AC2") = Request.Form ("AC2")%>

<%Session("DATE1") = Request.Form ("DATE1")%>

<%Session("DATE2") = Request.Form ("DATE2")%>

<html>

<head>

<title>Priority Performance Summary</title>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

<link rel="stylesheet" href="textboxwidths.css" type="text/css">

</head>

<body bgcolor="#FFFFFF" text="#000000" class="textboxwidths" 

leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">

<table width="100%" border="0" cellspacing="0" cellpadding="0">

  <tr> 

    <td><img src="images/symology.png" width="192" height="60"></td>

    <td> 

      <div align="right"><img src="images/ppr.png" width="340" 

height="43"></div>

    </td>

  </tr>

  <tr> 

    <td colspan="2"><img src="images/background.gif" width="100%" 

height="20"></td>

  </tr>

</table>

<% If Not rrsSUMMARY.EOF Or Not rrsSUMMARY.BOF Then %>

<table width="760" border="0" cellspacing="0" cellpadding="0" 

align="center">

  <tr> 

    <td height="16"> </td>

  </tr>

  <tr> 

    <td><font face="Verdana, Arial, Helvetica, sans-serif" size="-

1"><b><font size="-2">Results 

      for Group Type: <font color="#FF0000"><%= Session("GT1") %></font> 

Group Name: <font color="#FF0000"><%= Session("GN1") %> 

</font></font></b></font> </td>

  </tr>

  <tr> 

    <td><font face="Verdana, Arial, Helvetica, sans-serif" size="-

1"><b><font size="-2">Request 

      Type:<font color="#FF0000"> <%= Session("RT1") %> <font 

color="#000000">Analysis Code 1:</font></font></font> 

      <font size="-2" color="#FF0000"><%= Session("AC1") %> </font><font 

face="Verdana, Arial, Helvetica, sans-serif" size="-1"><b><font size="-

2"><font color="#FF0000"><font color="#000000">Analysis 

      Code 2:</font></font></font> </b></font></b></font> <font 

face="Verdana, Arial, Helvetica, sans-serif" size="-2" 

color="#FF0000"><b><%= Session("AC2") %></b></font></td>

  </tr>

  <tr> 

    <td><font face="Verdana, Arial, Helvetica, sans-serif" size="-

1"><b><font size="-2">Between 

      <font color="#FF0000"><%= Session("DATE1") %></font> and <font 

color="#FF0000"><%= Session("DATE2") %></font></font></b></font></td>

  </tr>

  <tr> 

    <td><font color="#0000FF"></font></td>

  </tr>

  <tr> 

    <td> 

      <table width="760" border="0" cellspacing="5" cellpadding="0">

        <tr> 

          <td width="84px"> 

            <p class="scottest"><font face="Verdana, Arial, Helvetica, 

sans-serif" size="-2"><b>Priority</b></font></p>

          </td>

          <td width="84px"><b><font face="Verdana, Arial, Helvetica, sans-

serif" size="-2">Count 

            of CRNO's</font></b></td>

          <td width="84px"><b><font face="Verdana, Arial, Helvetica, sans-

serif" size="-2">Not 

            Cleared</font></b></td>

          <td width="84px"><b><font face="Verdana, Arial, Helvetica, sans-

serif" size="-2">0 

            Days</font></b></td>

          <td width="84px"><b><font face="Verdana, Arial, Helvetica, sans-

serif" size="-2">1-7 

            Days</font></b></td>

          <td width="84px"><b><font face="Verdana, Arial, Helvetica, sans-

serif" size="-2">8-14 

            Days</font></b></td>

          <td width="84px"><b><font face="Verdana, Arial, Helvetica, sans-

serif" size="-2">15-21 

            Days</font></b></td>

          <td width="84px"><b><font face="Verdana, Arial, Helvetica, sans-

serif" size="-2">22-28 

            Days</font></b></td>

          <td width="84px"><b><font face="Verdana, Arial, Helvetica, sans-

serif" size="-2">28 

            Days</font></b></td>

        </tr>

        <% 

While ((Repeat1__numRows <> 0) AND (NOT rrsSUMMARY.EOF)) 

%>

        <tr> 

          <td width="84px"><font face="Verdana, Arial, Helvetica, sans-

serif" size="-2"><A HREF="ppr3.asp?&CB=&<%= MM_keepNone & MM_joinChar

(MM_keepNone) & "PR=" & rrsSUMMARY.Fields.Item("PRTY").Value %>" 

onFocus="if(this.blur)this.blur()"><%= rrsSUMMARY.Fields.Item

("PRTY").Value %></A></font></td>

          <td width="84px"><font face="Verdana, Arial, Helvetica, sans-

serif" size="-2"><A HREF="ppr3.asp?&CB=&<%= MM_keepNone & MM_joinChar

(MM_keepNone) & "PR=" & rrsSUMMARY.Fields.Item("PRTY").Value %>" 

onFocus="if(this.blur)this.blur()"><%= rrsSUMMARY.Fields.Item

("CountOfCRNO").Value %></A></font></td>

          <td width="84px"><font face="Verdana, Arial, Helvetica, sans-

serif" size="-2"><A HREF="ppr3.asp?CB=1&<%= MM_keepNone & MM_joinChar

(MM_keepNone) & "PR=" & rrsSUMMARY.Fields.Item("PRTY").Value %>" 

onFocus="if(this.blur)this.blur()"><%= rrsSUMMARY.Fields.Item

("SumOfNOT_CLEARED").Value %></A></font></td>

          <td width="84px"><font face="Verdana, Arial, Helvetica, sans-

serif" size="-2"><A HREF="ppr3.asp?CB=2&<%= MM_keepNone & MM_joinChar

(MM_keepNone) & "PR=" & rrsSUMMARY.Fields.Item("PRTY").Value %>" 

onFocus="if(this.blur)this.blur()"><%= rrsSUMMARY.Fields.Item

("SumOfDAYS_ZERO").Value %></A></font></td>

          <td width="84px"><font face="Verdana, Arial, Helvetica, sans-

serif" size="-2"><A HREF="ppr3.asp?CB=3&<%= MM_keepNone & MM_joinChar

(MM_keepNone) & "PR=" & rrsSUMMARY.Fields.Item("PRTY").Value %>" 

onFocus="if(this.blur)this.blur()"><%= rrsSUMMARY.Fields.Item

("SumOfDAYS_1TO7").Value %></A></font></td>

          <td width="84px"><font face="Verdana, Arial, Helvetica, sans-

serif" size="-2"><A HREF="ppr3.asp?CB=4&<%= MM_keepNone & MM_joinChar

(MM_keepNone) & "PR=" & rrsSUMMARY.Fields.Item("PRTY").Value %>" 

onFocus="if(this.blur)this.blur()"><%= rrsSUMMARY.Fields.Item

("SumOfDAYS_8TO14").Value %></A></font></td>

          <td width="84px"><font face="Verdana, Arial, Helvetica, sans-

serif" size="-2"><A HREF="ppr3.asp?CB=5&<%= MM_keepNone & MM_joinChar

(MM_keepNone) & "PR=" & rrsSUMMARY.Fields.Item("PRTY").Value %>" 

onFocus="if(this.blur)this.blur()"><%= rrsSUMMARY.Fields.Item

("SumOfDAYS_15TO21").Value %></A></font></td>

          <td width="84px"><font face="Verdana, Arial, Helvetica, sans-

serif" size="-2"><A HREF="ppr3.asp?CB=6&<%= MM_keepNone & MM_joinChar

(MM_keepNone) & "PR=" & rrsSUMMARY.Fields.Item("PRTY").Value %>" 

onFocus="if(this.blur)this.blur()"><%= rrsSUMMARY.Fields.Item

("SumOfDAYS_22TO28").Value %></A></font></td>

          <td width="84px"> 

            <p><font face="Verdana, Arial, Helvetica, sans-serif" size="-

2"><A HREF="ppr3.asp?CB=7&<%= MM_keepNone & MM_joinChar(MM_keepNone) 

& "PR=" & rrsSUMMARY.Fields.Item("PRTY").Value %>" onFocus="if(this.blur)

this.blur()"><%= rrsSUMMARY.Fields.Item("SumOfDAYS_GT28").Value %

></A></font></p>

          </td>

        </tr>

        <% 

  Repeat1__index=Repeat1__index+1

  Repeat1__numRows=Repeat1__numRows-1

  rrsSUMMARY.MoveNext()

Wend

%>

      </table>

    </td>

  </tr>

  <tr> 

    <td height="19"> </td>

  </tr>

  <tr> 

    <td> <font face="Verdana, Arial, Helvetica, sans-serif"> <font size="-

2"> 

      <font face="Verdana, Arial, Helvetica, sans-serif"><font size="-

2"><font size="-1"> • <a href="index.asp" onFocus="if

(this.blur)this.blur()">Return 

      to the main menu</a></font></font></font><font size="-1"> 

</font></font></font> 

    </td>

  </tr>

</table>

<% End If ' end Not rrsSUMMARY.EOF Or NOT rrsSUMMARY.BOF %>

<p><font face="Verdana, Arial, Helvetica, sans-serif"><font size="-

2"><font size="-1"> 

  <% If rrsSUMMARY.EOF And rrsSUMMARY.BOF Then %>

  Sorry, no records match the selection criteria specified <br>

  <br>

   • <a href="ppr_search.asp" onFocus="if(this.blur)

this.blur()">Re-enter 

  Selection Criteria</a><br>

  <br>

   • <a href="index.asp" onFocus="if(this.blur)this.blur

()">Return 

  to the main menu</a><br>

  <br>

  <% End If ' end rrsSUMMARY.EOF And rrsSUMMARY.BOF %>

  <%= Session("GT1") %>  <%= Session("GN1") %> <%= Session

("RT1") %> <%= Session("AC1") %> <%= Session("AC2") %

> </font><font face="Verdana, Arial, Helvetica, sans-serif"><font 

size="-2"><font size="-1"><%= Session("DATE1") %

> </font></font></font><font size="-1"><%= Session("DATE2") %

></font></font></font></p>

</body>

</html>



















> Hello,

> 

> I would like to be able to programmatically execute a query that

> contains parameters.  The query must be a stored query, I cannot use an

> SQL statement.  

> 

> For example, I have a query called 'qClients'.  Its SQL statement is:

> 

> SELECT [Client ID], ClientName, Street, Pcode

> FROM qClients

> WHERE (((qClients.[Client ID])=[Enter ClientID]));

> 

> '[Enter ClientID]' is the parameter that needs to be supplied.

> 

> I believe I can do so using ADO Commands?  Does anyone have an example

> of this? 

> 

> My second question is (if this is possible) what happens when the query

> I am trying to execute programmatically is based on a parameter query?

> For example, if I have another query that is based on 'qClients' called

> 'qMelboureClients', (it shows all clients for Melbourne).  If I execute

> this query, how do I specify the '[Enter ClientID]' parameter in its

> base query?

> 

> Once again, I cannot use SQL statements and WHERE clauses.  

> 

> Any input would be appreciated,

> 

> Cheers,

> 

> Padgett

> 

> ********************

> Padgett Rowell 

> Aspect7

> Office: +61 (08) 9202 1433

> Mobile: 0402 122 419

> 

>