Re: Access Query w/VBScript

Bob wrote:
> I am a VBA programmer learning ASP.
>
> The code below is my attempt to access a database
> (C:\Bob\Personnel.mdb) and run a query that's stored there (qrySel")
> using an IIS server. I am using 'Beginning ASP Databases' and
> starting with chapter 8, this method is used to run queries.
>
> <SCRIPT LANGUAGE="VBScript">
> <HEAD>
> <TITLE>Use Access Query as Source</TITLE>
> </HEAD>
> <BODY>
>
> <%
> Dim objRS, adOpenForwardOnly
> adOpenForwardOnly = 0
>
> Set objRS = Server.CreateObject("ADODB.Recordset")
> objRS.Open "qrySel", "DSN=PersonnelTest", adOpenForwardOnly


Always use an explicit Connection object. Using a connection string in your
recordset's Open statement disables connection pooling - this can kill
performance. Also, avoid using the obsolete ODBC provider. Use the native
Jet OLE DB provider instead. Here is how I would call your query (I assume
it is a saved query - I am also assuming the query returns only the lastName
field - you wouldn't be wastefully retrieving unneeded data across the wire
would you?):

Set cn = createobject("adodb.connection")
cn.open "Provider="microsoft.jet.olecb.4.0;" & _
"Data Source=c:=path\to\database.mdb"
Set objRS = CreateObject("ADODB.Recordset")
cn.qrySel objRS
if not objRS.EOF then Response.Write objRS(,,"","<P>")
objRS.Close
Set objRS=nothing 'don't forget the Set keyword ...

Here is some more information about calling saved queries:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

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

HTH,
Bob Barrows

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


.

Relevant Pages

  • Re: ACCESS memo fields in ASP
    ... Hi Bob - thanks for your help. ... >> messages which have been saved in a memo field in an access data base ... > You should explicitly name the fields to be returned by your query. ... > performance due to the need for ADO to make an extra trip to the database ...
    (microsoft.public.inetserver.asp.general)
  • RE: SQL injection from within a table - is it possible?
    ... I would assume that all parsers would parse the /entire/ sql query ... Suppose your username was "bob", ... Going back to your initial question about a "stored" SQL Injection ... Is it possible to store an SQL injection string into a MSSQL database ...
    (Pen-Test)
  • Re: Update Application value on multiple servers?
    ... I have to agree with Aaron and Bob on this one even though I originally ... >> Why are you using poor database design as an excuse for poor application ... >> need to synchronize between all the servers in the farm. ... More often than not that particular query is the bottleneck in our ...
    (microsoft.public.inetserver.asp.general)
  • RE: SQL injection from within a table - is it possible?
    ... "bob" will be the value returned. ... Is it possible to store an SQL injection string into a MSSQL database table, so when the database performs an action like through a stored proc, the SQL injection attack takes place? ... Of course using the SQL query analyzer on the database table, ...
    (Pen-Test)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)