ASP Page, Access Base, PARAMETER not send !!!

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
'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"
Req_1.Parameters.Append parametre

Set ADORecordset = Req_1.Execute

do while not ADORecordset.EOF
Response.Write ADORecordset("NUM_UNIQUE_ORIGINE") & "
"
ADORecordset.MoveNext
Loop
%>

My parameter is send to access and the result is good

But I want replace Z2_Req_Sans_FIL_Supp by a SQL request :

<%
Set Req_1 = Server.CreateObject("ADODB.Command")
Req_1.ActiveConnection = MM_BASE_FIL_A_FIL_TER2N_STRING

'This request in Access is good but in my ASP Page, the parameter is not
send to Access (ERROR : 1 parameter is request)

Req_1.CommandText = "SELECT Base_Modif.* "
Req_1.CommandText = Req_1.CommandText & " FROM Z1_Req_V_Fil_MAX_et_Date LEFT
JOIN Base_Modif ON Z1_Req_V_Fil_MAX_et_Date.MaxDeNUM_UNIQUE =
Base_Modif.NUM_UNIQUE "
Req_1.CommandText = Req_1.CommandText & " WHERE Base_Modif.STATUS<>'s'"

Set parametre = server.CreateObject("ADODB.Parameter")
Set parametre = Req_1.CreateParameter("VAR_DATE")
parametre.Type = 7
parametre.Direction = 1
parametre.Value = "14/12/2007"

Req_1.Parameters.Append parametre

Set ADORecordset = Req_1.Execute

do while not ADORecordset.EOF
Response.Write ADORecordset("NUM_UNIQUE_ORIGINE") & "
"
ADORecordset.MoveNext
Loop
%>

How can I send a parameter with a SQL request ? Is it possible ?



Thanks

Sébastien

Re: ASP Page, Access Base, PARAMETER not send !!!

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.

Re: ASP Page, Access Base, PARAMETER not send !!!

"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.

Re Hello,

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 ?
dat : ok
rs : ???
I must open cn ?


For information :
Req_1.ActiveConnection = MM_BASE_FIL_A_FIL_TER2N_STRING
is in reality :
Dim MM_BASE_FIL_A_FIL_TER2N_STRING
MM_BASE_FIL_A_FIL_TER2N_STRING = "dsn=BASE_FIL_A_FIL_TER2N;"

Re: ASP Page, Access Base, PARAMETER not send !!!

"Bob Barrows [MVP]" 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).



--
Anthony Jones - MVP ASP/ASP.NET

Re: ASP Page, Access Base, PARAMETER not send !!!

Anthony Jones wrote:
> "Bob Barrows [MVP]" 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;3284 76

--
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"

Re: ASP Page, Access Base, PARAMETER not send !!!

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&sel m=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.inetser ver.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"

Re: ASP Page, Access Base, PARAMETER not send !!!

"Bob Barrows [MVP]" wrote in message
news:OpCSZbyCIHA.3916@TK2MSFTNGP02.phx.gbl...
> Anthony Jones wrote:
> > "Bob Barrows [MVP]" 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;3284 76
>

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 can't think why MS have choosen not to use the connection pool when
creating these implicit connections.

Cheers,

--
Anthony Jones - MVP ASP/ASP.NET

Re: ASP Page, Access Base, PARAMETER not send !!!

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.
--
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.

Re: ASP Page, Access Base, PARAMETER not send !!!

"Bob Barrows [MVP]" 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.

--
Anthony Jones - MVP ASP/ASP.NET

Re: ASP Page, Access Base, PARAMETER not send !!!

Anthony Jones wrote:
> "Bob Barrows [MVP]" 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.
>

This is certainly not news to me. There have been several cases where I
can remember something causing an error at one point only to fail to
duplicate the problem at a later point. Faulty memory? Upgrades to
system fixing the problems? Who knows?

--
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.

Re: ASP Page, Access Base, PARAMETER not send !!!

"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&sel m=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.inetser ver.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"

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 !

How do that ?

Thx

Sébastien

Re: ASP Page, Access Base, PARAMETER not send !!!

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.inetser ver.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"

Re: ASP Page, Access Base, PARAMETER not send !!!

"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.inetser ver.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"

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 ?

I have not read a good idea in :
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

Again thanks for your patience

Sebastien

Re: ASP Page, Access Base, PARAMETER not send !!!

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.


--
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.

Re: ASP Page, Access Base, PARAMETER not send !!!

"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.
>

OK for [VAR_DATE] in REQ2.

It's not possible to use REQ3 because I must use in ASP, a sSQL + 1
parameter (to REQ1). Why sSQL in ASP, because after WHERE it's not the same
fields at each time.

I have find a solution but it's not very pure :

In ACCESS:
a table : TABLE_VAR with field MY_DATE

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)<(SELECT MY_DATE FROM TABLE_VAR WHERE
NUM='1')))
GROUP BY Base_Modif.NUM_UNIQUE_ORIGINE;

[VAR_DATE] is replaced by )<(SELECT MY_DATE FROM TABLE_VAR WHERE NUM='1')

REQ2 =
SELECT Base_Modif.*
FROM Page_Result_Z1_Req_V_Fil_MAX_et_Date LEFT JOIN Base_Modif ON
Page_Result_Z1_Req_V_Fil_MAX_et_Date.MaxDeNUM_UNIQUE = Base_Modif.NUM_UNIQUE;

So in ASP :
I update the good date in the table TABLE_VAR, field MY_DATE

and my recordset:
"SELECT * FROM Page_Result_Z2_Req_Sans_FIL_Supp WHERE ........"

The solution is good but I must update the date before, so it's not very
very pure.

For me, my work is finish now. I come back tomorow.

Sebastien from France

Re: ASP Page, Access Base, PARAMETER not send !!!

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


--
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.

Re: ASP Page, Access Base, PARAMETER not send !!!

"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

???

Thanks

Sebastien

Re: ASP Page, Access Base, PARAMETER not send !!!

Seb wrote:
> "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
>
> ???
>
Start by changing sSQL to
sSQL = "SELECT * FROM Req2"

and using the above code to execute it.. Does it work? if so, then
either STATUS or ORGANE is not a field supplied by Req2


--
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.