Re: DW, FP and Access Databases

by "Eric Anderson" <blainesoft(at)hotmail.com>
Date:	Fri, 18 Feb 2000 11:24:13 EST
To:	mwilson(at)sigmapath.com, kddm(at)worldnet.att.net
Cc:	hwg-techniques(at)hwg.org
	todo: View Thread, Original
>Simple example: Access Database named "db" with one table called
>"table_1" and 5 fields...
>
>
><CFQUERY NAME= "any_name_you_want" DATASOURCE="db">
>SELECT * FROM table_1
><!--the star (*) means select all fields. You could also select just the
>fields you want by the feild name -->
></CFQUERY>
><HTML>
><HEAD>
><TITLE>connect</TITLE>
></HEAD>
><BODY BGCOLOR="#FFFFFF">
><H1>my output</H1>
><CFOUTPUT QUERY= "same_as_cfquery_name">
><P>#field_1#</P>
><P>#field_2#</P>
><P>#field_3#</P>
><P>#field_4#</P>
><P>#field_5#</P>
></CFOUTPUT>
></BODY>
></HTML>
>
>...done
>20 lines of code, including HTML tags.
>
>I am not an ASP master. Could someone be so kind as to post a similar
>example using ASP? I have seen it done and the code was much larger for
>the same process. Could it be done in less lines of code using ASP?


I don't know that fewer lines of code is necessary a good thing. I could 
write the same thing in three lines of code or 20 lines. The difference is 
that you'd have a hard time understanding the 3 lines, where the 20 lines 
might be crystal clear. Fewer lines of code are not necssarily a good thing 
for maintainability.

That said, here's how I'd do it in ASP. I'm using a DSN-less connection, 
which means you need nothing at all from the system administrator (i.e. no 
setup from them whatsoever). A DSN connection saves a couple of lines of 
code.

Normally, you'd put the database connection as an include file so that it 
would be available to every page and to make changing database locations 
easy. For clarity, I'm showing that connection here.

Also, ADO (the method in which you access the database) does not explicitly 
require opening a connection before opening a recordset.  Under many 
circumstances (including this one), you might not need to actually code the 
database connection. But, in some cases, you do need to open the connection, 
so I'm including it.

<%@ language = VBScript %>
<% 'this opens the connection to the database
set conn = server.createobject("ADODB.connection")
myconnection = "Driver={Microsoft Access Driver (*.mdb)}; 
DQ=d:\inetpub\mywebsite\mydatabase.mdb"
conn.open myconnection

SQLStmt = "Select * FROM table_1"
Set Rs = Conn.Execute(SQLstmt)

' you could but it's poor coding practice to open the recordset this way: 
Set rs = conn.execute("Select * from table_1")

rs.movefirst

<HTML>
<HEAD>
<TITLE>connect</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF">
<H1>my output</H1>
<P><%= rs("field_1") %></P>
<P><%= rs("field_2") %></P>
<P><%= rs("field_3") %></P>
<P><%= rs("field_4") %></P>
<P><%= rs("field_5") %></P>
rs.close
conn.close
</BODY>
</HTML>

I count 22 lines of code, which could be condensed to about 12-14 lines or 
so depending on what you needed to do and how the site is set up.

In general, I'd say it's a tossup -- at least at this level -- about which 
is more efficient. ColdFusion and ASP both have strengths and weaknesses. 
You can do a lot of things in ASP that cannot be done (or done easily) in 
CF. But if you don't need to do those things, those features are valueless. 
CF is clearly easier in terms of setting up database connections.


Eric Anderson


______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com
HWG hwg-techniques mailing list archives, maintained by Webmasters @ IWA