Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > ASP Page, Access Base, PARAMETER not send !!!

Reply
Thread Tools

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

 
 
Seb
Guest
Posts: n/a
 
      10-09-2007
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


 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      10-09-2007
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.


 
Reply With Quote
 
 
 
 
Seb
Guest
Posts: n/a
 
      10-10-2007
"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;"


 
Reply With Quote
 
Anthony Jones
Guest
Posts: n/a
 
      10-10-2007

"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      10-10-2007
Anthony Jones wrote:
> "Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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...b;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"


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      10-10-2007
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&l...TNGP12.phx.gbl

http://groups.google.com/groups?hl=e...tngp13.phx.gbl

http://groups-beta.google.com/group/...d322b882a604bd



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


 
Reply With Quote
 
Anthony Jones
Guest
Posts: n/a
 
      10-10-2007
"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Anthony Jones wrote:
> > "Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> 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...b;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


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      10-10-2007
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.


 
Reply With Quote
 
Anthony Jones
Guest
Posts: n/a
 
      10-10-2007

"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      10-10-2007
Anthony Jones wrote:
> "Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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.


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing parameter to function not expecting parameter Mister B C Programming 8 08-26-2010 08:01 AM
Using declaration inside first template parameter as default valuefor second template parameter. Stuart Redmann C++ 5 12-14-2007 08:42 AM
Very annoying error: Access to the path is denied. ASP.NET is not authorized to access the requested resource. Consider granting access rights to the resource to the ASP.NET request identity Jay ASP .Net 2 08-20-2007 07:38 PM
Parameter List / Parameter Block / Anything patterns... mast2as@yahoo.com C++ 4 03-29-2007 09:37 PM
Retreiving the .Send parameter in ASP.NET =?Utf-8?B?c3RldmU=?= ASP .Net 1 04-12-2005 02:59 PM



Advertisments