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

Discussion in 'ASP General' started by Seb, Oct 9, 2007.

  1. Seb

    Seb Guest

    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") & "<br>"
    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") & "<br>"
    ADORecordset.MoveNext
    Loop
    %>

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



    Thanks

    Sébastien
     
    Seb, Oct 9, 2007
    #1
    1. Advertising

  2. 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], Oct 9, 2007
    #2
    1. Advertising

  3. Seb

    Seb Guest

    "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;"
     
    Seb, Oct 10, 2007
    #3
  4. "Bob Barrows [MVP]" <> wrote in message
    news:...
    > 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
     
    Anthony Jones, Oct 10, 2007
    #4
  5. Anthony Jones wrote:
    > "Bob Barrows [MVP]" <> wrote in message
    > news:...
    >> 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

    --
    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], Oct 10, 2007
    #5
  6. 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=

    http://groups.google.com/groups?hl=...=1&selm=

    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], Oct 10, 2007
    #6
  7. "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Anthony Jones wrote:
    > > "Bob Barrows [MVP]" <> wrote in message
    > > news:...
    > >> 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,

    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
     
    Anthony Jones, Oct 10, 2007
    #7
  8. 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.
     
    Bob Barrows [MVP], Oct 10, 2007
    #8
  9. "Bob Barrows [MVP]" <> wrote in message
    news:...
    > 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
     
    Anthony Jones, Oct 10, 2007
    #9
  10. Anthony Jones wrote:
    > "Bob Barrows [MVP]" <> wrote in message
    > news:...
    >> 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.
     
    Bob Barrows [MVP], Oct 10, 2007
    #10
  11. Seb

    Seb Guest

    "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=
    >
    > http://groups.google.com/groups?hl=...=1&selm=
    >
    > 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"


    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
     
    Seb, Oct 11, 2007
    #11
  12. 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], Oct 11, 2007
    #12
  13. Seb

    Seb Guest

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


    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.inetserver.asp.db/msg/72e36562fee7804e

    Again thanks for your patience

    Sebastien
     
    Seb, Oct 11, 2007
    #13
  14. 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.
     
    Bob Barrows [MVP], Oct 11, 2007
    #14
  15. Seb

    Seb Guest

    "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
     
    Seb, Oct 11, 2007
    #15
  16. 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.
     
    Bob Barrows [MVP], Oct 11, 2007
    #16
  17. Seb

    Seb Guest

    "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
     
    Seb, Oct 12, 2007
    #17
  18. 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.
     
    Bob Barrows [MVP], Oct 12, 2007
    #18
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. msnews
    Replies:
    7
    Views:
    563
  2. Quick Function

    jsp send parameter to next page

    Quick Function, Jul 15, 2004, in forum: Java
    Replies:
    1
    Views:
    4,071
    Tor Iver Wilhelmsen
    Jul 15, 2004
  3. Petar Popara
    Replies:
    5
    Views:
    22,844
    Petar Popara
    Feb 16, 2006
  4. Alf P. Steinbach
    Replies:
    6
    Views:
    557
    John Carson
    Sep 3, 2005
  5. =?Utf-8?B?SmF5IFBvbmR5?=
    Replies:
    6
    Views:
    497
    =?Utf-8?B?SmF5IFBvbmR5?=
    Nov 14, 2007
Loading...

Share This Page