Non-ADO?

P

PW

If I am using ASP and access an Access database, but I am not using ADO,
what is the name of the database methodology I am using ?
 
B

Bob Barrows [MVP]

PW said:
If I am using ASP and access an Access database, but I am not using
ADO, what is the name of the database methodology I am using ?

Are we supposed to read your mind? Give us an example.
 
P

PW

Bob Barrows said:
Are we supposed to read your mind? Give us an example.
--
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"


Apoogies for not posting an example before ...


<td>
myDSN="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Session("SystemDatabaseName")
set rs1=server.createobject("adodb.recordset")
rs1.CursorLocation = 3
rs1.CursorType = 3

mySQL = ""
mySQL = mySQL & "SELECT DISTINCT"
mySQL = mySQL & " ESCI, ESCN "
mySQL = mySQL & "FROM "
mySQL = mySQL & " QTags "
mySQL = mySQL & "ORDER BY "
mySQL = mySQL & " ESCI "
rs1.open mySQL,myDSN

myQueryString = Request.QueryString("lbESCI")
mySearchString = Request.QueryString("txtSearch")

<form method="GET" action="index.asp">
<input type="submit" value="Ok" style="width:
<%=Session("SystemButtonWidth")%>; height:
<%=Session("SystemButtonheight")%>;">
Q-TAGS SELECTION LIST
<br>
<SELECT name="lbESCI" size="9"
style="font-size:10;color:BLACK;font-family:ARIAL">
<%
Do While Not rs1.EOF
if mySearchString <> "" then
if rs1("ESCI") = mySearchString then
response.write "<option selected>"
else
response.write "<option>"
end if
elseif rs1("ESCI") = left(myQueryString,6) then
response.write "<option selected>"
else
response.write "<option>"
end if
myOption = rs1("ESCI") & " | " & rs1("ESCN")
response.write myOption
response.write "</option>"
rs1.MoveNext
Loop
response.write "</select>"
%>
</form>
</td>
 
M

Mike Brind

PW said:
Apoogies for not posting an example before ...


<td>
myDSN="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Session("SystemDatabaseName")
set rs1=server.createobject("adodb.recordset")

Server.CreateObject("ADOdb.RecordSet")
^^^^^^

What makes you think that isn't ADO?
 
B

Bob Barrows [MVP]

Apoogies for not posting an example before ...


<td>
myDSN="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Session("SystemDatabaseName")
set rs1=server.createobject("adodb.recordset")
rs1.CursorLocation = 3
rs1.CursorType = 3

Well, you ARE using ADO (see that "adodb.recordset" line?). What you are
doing is using the deprecated MSDASQL provider (which is loaded by default
when you don't specify a provider in your connection string) to connect to
the obsolete Access ODBC driver.

By reading this sentence, you should get an idea about why this practice is
not recommended, even if I had left out the words "deprecated" and
"obsolete": by making ADO use a provider to communicate with a separate data
access library, you are adding an extra, and unnecessary, layer of software
between your code and the database.

Simply use the native Jet OLE DB provider. The only time the MSDASQL
provider should be used is when a native provider for your database does not
exist, or does not provide the functionality you need. Neither of these is
the case with Jet.


The other thing you are doing, also highly discouraged BTW, is failing to
use an explicit connection object. By supplying a string instead of a
connection object in the rs.open statement, you are causing ADO to open an
implicit connection. This is bad because:
1. You have no direct control over the connection and thus cannot explicitly
close it without accessing the recordset's ActiveConnection property.
2. Using implicit connections can disable ADO Session Pooling, resulting in
too many connections being opened to the database.

Always create and open an explicit connection object and use it for
subsequent database activity. You are not really saving yourself any time
when you use implicit connections, and you could definitely be causing
problems for your web server.
 
P

PW

Well, you ARE using ADO (see that "adodb.recordset" line?). What you are
doing is using the deprecated MSDASQL provider (which is loaded by default
when you don't specify a provider in your connection string) to connect to
the obsolete Access ODBC driver.

By reading this sentence, you should get an idea about why this practice
is not recommended, even if I had left out the words "deprecated" and
"obsolete": by making ADO use a provider to communicate with a separate
data access library, you are adding an extra, and unnecessary, layer of
software between your code and the database.

Simply use the native Jet OLE DB provider. The only time the MSDASQL
provider should be used is when a native provider for your database does
not exist, or does not provide the functionality you need. Neither of
these is the case with Jet.


The other thing you are doing, also highly discouraged BTW, is failing to
use an explicit connection object. By supplying a string instead of a
connection object in the rs.open statement, you are causing ADO to open an
implicit connection. This is bad because:
1. You have no direct control over the connection and thus cannot
explicitly close it without accessing the recordset's ActiveConnection
property.
2. Using implicit connections can disable ADO Session Pooling, resulting
in too many connections being opened to the database.

Always create and open an explicit connection object and use it for
subsequent database activity. You are not really saving yourself any time
when you use implicit connections, and you could definitely be causing
problems for your web server.


Ok, I have replaced my connection string with ...
myDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Session("SystemDatabaseName")

I'm not quite sure what change to make to change to an explicit connection
object. Can you give me an example?

TIA,
PW
 
M

Mike Brind

PW said:
Ok, I have replaced my connection string with ...
myDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Session("SystemDatabaseName")

I'm not quite sure what change to make to change to an explicit connection
object. Can you give me an example?

TIA,
PW

Set conn = Server.CreateObject("ADODB.Connection")
myDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Session("SystemDatabaseName")
conn.Open myDSN
 
B

Bob Barrows [MVP]

PW said:
Ok, I have replaced my connection string with ...
myDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Session("SystemDatabaseName")

I'm not quite sure what change to make to change to an explicit
connection object. Can you give me an example?
dim cn
set cn=createobject("adodb.connection")
cn.open myDSN
....
rs1.open mySQL,cn,,,1

or

set rs1=cn.Execute(mySQL,,1)


And, while we're at it:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e
 
P

PW

dim cn

I think I'm already doing that in a round-about fashion ... this is what I
have in my "settings.asp"....

myDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Session("SystemDatabaseName")
set rs1=server.createobject("adodb.recordset")
rs1.CursorLocation = 3
rs1.CursorType = 3

To do it the way you suggest would be ...

dim cn
myDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Session("SystemDatabaseName")
set rs1=createobject("adodb.connection")
rs1.open myDSN
set rs1=cn.Execute(mySQL,,1)

Is that correct?

TIA,
PW

PS,
Bob, you're a fantastic help, thanks!
 
B

Bob Barrows [MVP]

PW said:
I think I'm already doing that in a round-about fashion ... this is
what I have in my "settings.asp"....

myDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Session("SystemDatabaseName")
set rs1=server.createobject("adodb.recordset")
rs1.CursorLocation = 3
rs1.CursorType = 3

To do it the way you suggest would be ...

dim cn
myDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Session("SystemDatabaseName")
set rs1=createobject("adodb.connection")
rs1.open myDSN

Ummm. You should use "cn" instead of "rs1" in the above 2 statements. "rs1"
is already a recordset object.
set rs1=cn.Execute(mySQL,,1)
Is that correct?
Almost.
If you are going to use cn.Execute to open your recordset, then there is no
need for the "set rs1=server.createobject("adodb.recordset")" line in your
include file. Also, using Execute causes your previous settings of
CursorLocation and CursorType to be ignored. If you really want to control
the cursor type and location, do not use Execute to return a recordset.
Instead, create the recordset and set its properties as you show above, then
create and open a connection, then use the recordset's Open method to open
the recordset as I showed above.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top