"Bob Barrows [MVP]" wrote:

> Seb wrote:
> > Hello,
> >
> > I work in a ASP page and a Access 97 base.
> > In Access, I have a request with a parameter (VAR_DATE).
> >
> > With this code it's good :
> > <%
> > Set Req_1 = Server.CreateObject("ADODB.Command")
> > Req_1.ActiveConnection = MM_BASE_FIL_A_FIL_TER2N_STRING
> Extremely poor programming practice here. Setting ActiveConnection to a
> string forces a new connection to be created behind the scenes. This can
> kill performance by preventing connection pooling from working, and can
> also lead to orphaned connections that cause memory leaks that
> eventually require IIS to be restarted. Always use an explicit
> connection object!
>
>
> > 'Z2_Req_Sans_FIL_Supp is a request in access with a parameter
> > (VAR_DATE is the parameter).
> >
> > Req_1.CommandText = "Z2_Req_Sans_FIL_Supp"
> >
> > Set parametre = server.CreateObject("ADODB.Parameter")
> > Set parametre = Req_1.CreateParameter("VAR_DATE")
> > parametre.Type = 7
> > parametre.Direction = 1
> > parametre.Value = "14/12/2007"
>
> All date literals must be passed in either US (m/d/yyyy) or ISO
> (yyyy-mm-dd) format.
>
> Here is how I would run this query:
>
> dim cn, dat, rs
> dat=dateserial(2007,12,14)
> set cn=createobject("adodb.connection")
> cn.open MM_BASE_FIL_A_FIL_TER2N_STRING
> set rs=createobject("adodb.recordset")
> cn.Z2_Req_Sans_FIL_Supp dat, rs
>
> HTH,
> Bob Barrows
>
> --
> 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.

"Bob Barrows [MVP]" wrote:

> Seb wrote:
> > Sorry but I do not understand exactly :
> >> dim cn, dat, rs
> >> dat=dateserial(2007,12,14)
> >> set cn=createobject("adodb.connection")
> >> cn.open MM_BASE_FIL_A_FIL_TER2N_STRING
> >> set rs=createobject("adodb.recordset")
> >> cn.Z2_Req_Sans_FIL_Supp dat, rs
> >
> > Please can you finish your exemple ?
>
> Well ... it IS finished. At this point you have an open recordset (I called
> it "rs" - I tend to use short variable names especially when the purpose of
> the variable should be obvious from the context) through which you can loop
> just as you looped through the recordset referenced by the variable you
> called "ADORecordset").
>
> > dat : ok
> > rs : ???
>
> It's a recordset ... I don't understand the confusion?
>
> > I must open cn ?
>
> Yes. Again, I don't understand the confusion. An explicit connection object
> (in this case I've called it "cn") must be opened before it can be used. The
> open method accepts an argument: the connection string to be used to open
> the connection.
>
> A little more information about this technique (called
> "procedure-as-connection-method") can be found in these posts:
> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
>
> http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd
>
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"

"Bob Barrows [MVP]" wrote:

> Seb wrote:
> > OK sorry, it's good, I have understand and your recordset is good,
> > but I
> > want replace Z2_Req_Sans_FIL_Supp by a SQL request in my asp page
> > with a
> > parameter:
> >
> > MyRecorset in asp (not in access) for exemple :
> > NewRS = "SELECT * FROM Z1_Req WHERE TOTO='s'"
> > but with a parameter for Z1_Req !
> >
> This is my recommended technique:
>
> Dim sSQL, cmd, rs, arParms
> arParms = Array("s")
> sSQL = "SELECT * FROM Z1_Req WHERE TOTO=?"
> set cmd=createobject("adodb.command")
> with cmd
>     .CommandText=sSQL
>     .CommandType=adCmdText
>     Set .ActiveConnection=cn
>     Set rs = .Execute(,arParms)
> end with
>
>
> See this to see how to use this technique to execute a sql statement that
> does not return records:
> http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"

> "Bob Barrows [MVP]" wrote:
>
> I am very sorry but it's not exactly the solution.
> So the exact example :
>
> In ACCESS :
> REQ1 =
> SELECT Base_Modif.NUM_UNIQUE_ORIGINE, Max(Base_Modif.NUM_VERSION_FIL)
> AS MaxDeNUM_VERSION_FIL, Max(Base_Modif.NUM_UNIQUE) AS MaxDeNUM_UNIQUE
> FROM Base_Modif
> WHERE (((Base_Modif.DATE_DE_L_OM)<[VAR_DATE]))
> GROUP BY Base_Modif.NUM_UNIQUE_ORIGINE;
>
> [VAR_DATE] is a parameter in REQ1
>
> Again In ACCESS :
> REQ2 =
> PARAMETERS VAR_DATE DateTime;
> SELECT Base_Modif.*
> FROM Req1 LEFT JOIN Base_Modif ON Req1.MaxDeNUM_UNIQUE =
> Base_Modif.NUM_UNIQUE;
>
> So in ASP I would like :
>
> sSQL="SELECT * FROM Req2 WHERE STATUS<>'s' AND ORGANE='BAR2'"
> (after WHERE I can write more fields)
>
> But I must send the parameter to ACCESS !
> How can I send this parameter ?
>

"Bob Barrows [MVP]" wrote:

> Seb wrote:
> > "Bob Barrows [MVP]" wrote:
> >
> > I am very sorry but it's not exactly the solution.
> > So the exact example :
> >
> > In ACCESS :
> > REQ1 =
> > SELECT Base_Modif.NUM_UNIQUE_ORIGINE, Max(Base_Modif.NUM_VERSION_FIL)
> > AS MaxDeNUM_VERSION_FIL, Max(Base_Modif.NUM_UNIQUE) AS MaxDeNUM_UNIQUE
> > FROM Base_Modif
> > WHERE (((Base_Modif.DATE_DE_L_OM)<[VAR_DATE]))
> > GROUP BY Base_Modif.NUM_UNIQUE_ORIGINE;
> >
> > [VAR_DATE] is a parameter in REQ1
> >
> > Again In ACCESS :
> > REQ2 =
> > PARAMETERS VAR_DATE DateTime;
> > SELECT Base_Modif.*
> > FROM Req1 LEFT JOIN Base_Modif ON Req1.MaxDeNUM_UNIQUE =
> > Base_Modif.NUM_UNIQUE;
> >
> > So in ASP I would like :
> >
> > sSQL="SELECT * FROM Req2 WHERE STATUS<>'s' AND ORGANE='BAR2'"
> > (after WHERE I can write more fields)
> >
> > But I must send the parameter to ACCESS !
> > How can I send this parameter ?
> >
> OK, so REQ1 and REQ2 are saved queries. Why did you declare [VAR_DATE]
> in REQ2?  It seems to me it should be declared in REQ1, given that it is
> not used in REQ2.
>
> I have never attempted this situation from ASP. I, personally, would
> write a third saved query, REQ3, and use
>
> cn.REQ3 dat,rs
>
> to execute it.
>
> But maybe you can use a Command object for this. I'm not sure to tell
> you the truth. I would have to play with it to see, and unfortunately, I
> am at work now so it will be several hours before I can give it the
> attention it needs. Maybe someone else can contribute.
>

> "Bob Barrows [MVP]" wrote:
>
>> Seb wrote:
>>> "Bob Barrows [MVP]" wrote:
>>>
>>> I am very sorry but it's not exactly the solution.
>>> So the exact example :
>>>
>>> In ACCESS :
>>> REQ1 =
>>> SELECT Base_Modif.NUM_UNIQUE_ORIGINE,
>>> Max(Base_Modif.NUM_VERSION_FIL) AS MaxDeNUM_VERSION_FIL,
>>> Max(Base_Modif.NUM_UNIQUE) AS MaxDeNUM_UNIQUE FROM Base_Modif
>>> WHERE (((Base_Modif.DATE_DE_L_OM)<[VAR_DATE]))
>>> GROUP BY Base_Modif.NUM_UNIQUE_ORIGINE;
>>>
>>> [VAR_DATE] is a parameter in REQ1
>>>
>>> Again In ACCESS :
>>> REQ2 =
>>> PARAMETERS VAR_DATE DateTime;
>>> SELECT Base_Modif.*
>>> FROM Req1 LEFT JOIN Base_Modif ON Req1.MaxDeNUM_UNIQUE =
>>> Base_Modif.NUM_UNIQUE;
>>>
>>> So in ASP I would like :
>>>
>>> sSQL="SELECT * FROM Req2 WHERE STATUS<>'s' AND ORGANE='BAR2'"
>>> (after WHERE I can write more fields)
>>>
>>> But I must send the parameter to ACCESS !
>>> How can I send this parameter ?
>>>
>> OK, so REQ1 and REQ2 are saved queries. Why did you declare
>> [VAR_DATE] in REQ2?  It seems to me it should be declared in REQ1,
>> given that it is not used in REQ2.
>>

"Bob Barrows [MVP]" wrote:

> Seb wrote:
> > "Bob Barrows [MVP]" wrote:
> >
> >> Seb wrote:
> >>> "Bob Barrows [MVP]" wrote:
> >>>
> >>> I am very sorry but it's not exactly the solution.
> >>> So the exact example :
> >>>
> >>> In ACCESS :
> >>> REQ1 =
> >>> SELECT Base_Modif.NUM_UNIQUE_ORIGINE,
> >>> Max(Base_Modif.NUM_VERSION_FIL) AS MaxDeNUM_VERSION_FIL,
> >>> Max(Base_Modif.NUM_UNIQUE) AS MaxDeNUM_UNIQUE FROM Base_Modif
> >>> WHERE (((Base_Modif.DATE_DE_L_OM)<[VAR_DATE]))
> >>> GROUP BY Base_Modif.NUM_UNIQUE_ORIGINE;
> >>>
> >>> [VAR_DATE] is a parameter in REQ1
> >>>
> >>> Again In ACCESS :
> >>> REQ2 =
> >>> PARAMETERS VAR_DATE DateTime;
> >>> SELECT Base_Modif.*
> >>> FROM Req1 LEFT JOIN Base_Modif ON Req1.MaxDeNUM_UNIQUE =
> >>> Base_Modif.NUM_UNIQUE;
> >>>
> >>> So in ASP I would like :
> >>>
> >>> sSQL="SELECT * FROM Req2 WHERE STATUS<>'s' AND ORGANE='BAR2'"
> >>> (after WHERE I can write more fields)
> >>>
> >>> But I must send the parameter to ACCESS !
> >>> How can I send this parameter ?
> >>>
> >> OK, so REQ1 and REQ2 are saved queries. Why did you declare
> >> [VAR_DATE] in REQ2?  It seems to me it should be declared in REQ1,
> >> given that it is not used in REQ2.
> >>
>
> I've just tested it and this works fine for me:
>
> Dim sSQL, cmd, rs, arParms, dat
> dat=dateserial(2007,12,14)
> arParms = Array(dat)
> sSQL = "SELECT * FROM Req2 WHERE STATUS<>'s' AND ORGANE='BAR2'"
> set cmd=createobject("adodb.command")
> with cmd
>     .CommandText=sSQL
>     .CommandType=adCmdText
>     Set .ActiveConnection=cn
>     Set rs = .Execute(,arParms)
> end with

> "Bob Barrows [MVP]" wrote:
>
>> Seb wrote:
>>> "Bob Barrows [MVP]" wrote:
>>>
>>>> Seb wrote:
>>>>> "Bob Barrows [MVP]" wrote:
>>>>>
>>>>> I am very sorry but it's not exactly the solution.
>>>>> So the exact example :
>>>>>
>>>>> In ACCESS :
>>>>> REQ1 =
>>>>> SELECT Base_Modif.NUM_UNIQUE_ORIGINE,
>>>>> Max(Base_Modif.NUM_VERSION_FIL) AS MaxDeNUM_VERSION_FIL,
>>>>> Max(Base_Modif.NUM_UNIQUE) AS MaxDeNUM_UNIQUE FROM Base_Modif
>>>>> WHERE (((Base_Modif.DATE_DE_L_OM)<[VAR_DATE]))
>>>>> GROUP BY Base_Modif.NUM_UNIQUE_ORIGINE;
>>>>>
>>>>> [VAR_DATE] is a parameter in REQ1
>>>>>
>>>>> Again In ACCESS :
>>>>> REQ2 =
>>>>> PARAMETERS VAR_DATE DateTime;
>>>>> SELECT Base_Modif.*
>>>>> FROM Req1 LEFT JOIN Base_Modif ON Req1.MaxDeNUM_UNIQUE =
>>>>> Base_Modif.NUM_UNIQUE;
>>>>>
>>>>> So in ASP I would like :
>>>>>
>>>>> sSQL="SELECT * FROM Req2 WHERE STATUS<>'s' AND ORGANE='BAR2'"
>>>>> (after WHERE I can write more fields)
>>>>>
>>>>> But I must send the parameter to ACCESS !
>>>>> How can I send this parameter ?
>>>>>
>>>> OK, so REQ1 and REQ2 are saved queries. Why did you declare
>>>> [VAR_DATE] in REQ2?  It seems to me it should be declared in REQ1,
>>>> given that it is not used in REQ2.
>>>>
>>
>> I've just tested it and this works fine for me:
>>
>> Dim sSQL, cmd, rs, arParms, dat
>> dat=dateserial(2007,12,14)
>> arParms = Array(dat)
>> sSQL = "SELECT * FROM Req2 WHERE STATUS<>'s' AND ORGANE='BAR2'"
>> set cmd=createobject("adodb.command")
>> with cmd
>>     .CommandText=sSQL
>>     .CommandType=adCmdText
>>     Set .ActiveConnection=cn
>>     Set rs = .Execute(,arParms)
>> end with
>
> Hello,
>
> This solution does not work at home. I don't know why !
>
> The error message in the asp page is : 1 parameter is requested
>
> ???
>

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:uzlBRfrCIHA.4308@TK2MSFTNGP06.phx.gbl...
> Seb wrote:
> > Hello,
> >
> > I work in a ASP page and a Access 97 base.
> > In Access, I have a request with a parameter (VAR_DATE).
> >
> > With this code it's good :
> > <%
> > Set Req_1 = Server.CreateObject("ADODB.Command")
> > Req_1.ActiveConnection = MM_BASE_FIL_A_FIL_TER2N_STRING
>
> Extremely poor programming practice here. Setting ActiveConnection to a
> string forces a new connection to be created behind the scenes. This can
> kill performance by preventing connection pooling from working, and can
> also lead to orphaned connections that cause memory leaks that
> eventually require IIS to be restarted. Always use an explicit
> connection object!
>

> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:uzlBRfrCIHA.4308@TK2MSFTNGP06.phx.gbl...
>> Seb wrote:
>>> Hello,
>>>
>>> I work in a ASP page and a Access 97 base.
>>> In Access, I have a request with a parameter (VAR_DATE).
>>>
>>> With this code it's good :
>>> <%
>>> Set Req_1 = Server.CreateObject("ADODB.Command")
>>> Req_1.ActiveConnection = MM_BASE_FIL_A_FIL_TER2N_STRING
>>
>> Extremely poor programming practice here. Setting ActiveConnection
>> to a string forces a new connection to be created behind the scenes.
>> This can kill performance by preventing connection pooling from
>> working, and can also lead to orphaned connections that cause memory
>> leaks that
>> eventually require IIS to be restarted. Always use an explicit
>> connection object!
>>
>
> That's interesting.  I've never come across that one.  Where can I
> find out more?
>
> (I've always used a connection object anyway but for academic reasons
> I'd like to understand this issue better).

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OpCSZbyCIHA.3916@TK2MSFTNGP02.phx.gbl...
> Anthony Jones wrote:
> > "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> > news:uzlBRfrCIHA.4308@TK2MSFTNGP06.phx.gbl...
> >> Seb wrote:
> >>> Hello,
> >>>
> >>> I work in a ASP page and a Access 97 base.
> >>> In Access, I have a request with a parameter (VAR_DATE).
> >>>
> >>> With this code it's good :
> >>> <%
> >>> Set Req_1 = Server.CreateObject("ADODB.Command")
> >>> Req_1.ActiveConnection = MM_BASE_FIL_A_FIL_TER2N_STRING
> >>
> >> Extremely poor programming practice here. Setting ActiveConnection
> >> to a string forces a new connection to be created behind the scenes.
> >> This can kill performance by preventing connection pooling from
> >> working, and can also lead to orphaned connections that cause memory
> >> leaks that
> >> eventually require IIS to be restarted. Always use an explicit
> >> connection object!
> >>
> >
> > That's interesting.  I've never come across that one.  Where can I
> > find out more?
> >
> > (I've always used a connection object anyway but for academic reasons
> > I'd like to understand this issue better).
>
> Mainly this:
> http://support.microsoft.com/?kbid=271128
> This article talks about a different situation in which implicit
connections
> are created, but the idea is the same: using a connection string instead
of
> an explicit connnection object causes a new connection to be opened. If
this
> is done multiple times on the page, you can wind up with multiple
> connections being spawned. And since implicit connections are never
> explicitly closed by the programmer, situations can crop up (pending
> results, etc.) that prevent the garbage handler from dererencing those
> connections.
> Mark McGinty helped verify much of this a while back if you want to google
> for the thread.
>
> This article talks about other ramifications of disabling pooling:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;328476
>

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:ufrAKizCIHA.5160@TK2MSFTNGP05.phx.gbl...
> Anthony Jones wrote:
> > Bob,
> >
> > Thanks for that.  For some reason I had thought that any attempt to
> > open another forward only result set on a connection whilst another
> > such result set had not yet been fully consumed would result in an
> > error.  I'm trying to think why I believed that because playing
> > around with the code sample from 271128 shows that no such error
> > occurs.
> >
> I once came across a dynamic property in the SQLOLEDB provider that
> controls whether an error is raised in this situation. It's listed in
> the ADO documentation. If you can't find it let me know and I'll try and
> find it again.

> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:ufrAKizCIHA.5160@TK2MSFTNGP05.phx.gbl...
>> Anthony Jones wrote:
>>> Bob,
>>>
>>> Thanks for that.  For some reason I had thought that any attempt to
>>> open another forward only result set on a connection whilst another
>>> such result set had not yet been fully consumed would result in an
>>> error.  I'm trying to think why I believed that because playing
>>> around with the code sample from 271128 shows that no such error
>>> occurs.
>>>
>> I once came across a dynamic property in the SQLOLEDB provider that
>> controls whether an error is raised in this situation. It's listed in
>> the ADO documentation. If you can't find it let me know and I'll try
>> and find it again.
>
>
>
> Are you refering to:-
>
> conn.Properties("Multiple connections") = False
>
> The default is true.
>
> I'd never come across this before and had thought it just errors.  In
> fact I'm sure that I've diagnosed problems that have turned out to be
> an attempt to open a new recordset on a connection whilst one is
> already open and in complete.  It would seem I'm mistaken because I
> can't reproduce the problem unless I include the line above.
>