Well, I guess it all depends on whether you need Books or Movies, I guess ;)
Just kidding. I think there is a third variation that, IMO, is even better. When you can use that solution depends on your needs. Before I get into the third solution, I'll command on these 2 first.
I find method 1 hard to read. What is a CursorType of 0 or a CursorLocation of 2? So, explicitly defining your constants is better because it makes your code easier to read. For more details on a clean way to include these variables, look
here.
The first method allows you to define your SQL statement yourself. That's good, as it will also allow you to limit the number of columns and records retrieved from the database. So, for example, you could use SELECT ID, Description FROM MyTable WHERE ID = 1, instead of SELECT * FROM MyTable. (Take a look
here to see why SELECT * is bad)
The second method opens the entire table. adCmdTable means you want to open the table instead of opening a selected (no pun intended) subset of records. If you do need all records, it will work. If you only need a subset of the records (and/or columns), you're out of luck.
So it all depends on your requirements. Need the entire table? Method 2 will work (although the overhead may be large, because you can't limit the number of columns). Need to filter records based on, say, the LastName of a user? You have to use method 1.
So, then, what is behind door number 3? Glad you asked. It's a modification of solution 1 which allows for fast, forward-only (think fire hose) data. It doesn't allow updating, ordering, filtering and all the other stuff you'll often see in desktop applications but that is usually useless in ASP pages. It does allow for dumping data on the page as quickly as possible. Here's how it looks:
Code:
Dim MyConnection
Dim MyRecordset
Dim MySQL
MySQL = "SELECT ID, Title FROM Events"
Set MyConnection = Server.CreateObject("ADODB.Connection")
MyConnection.ConnectionString = YourConnectionString
MyConnection.Open
Set MyRecordset = MyConnection.Execute(MySQL)
If Not MyRecordset.EOF Then
Do While Not MyRecordset.EOF
Response.Write("Record with ID " & _
MyRecordset.Fields("ID").Value & _
" has the title """ & _
MyRecordset.Fields("Title").Value & """<br />")
MyRecordset.MoveNext()
Loop
Else
Response.Write("No Data Found")
End If
MyConnection.Close
Set MyRecordset = Nothing
Set MyConnection = Nothing
After I have created and opened the Connection, I use its
Execute method to get a forward-only, read-only recordset. This recordset allows you to dump its contents as quickly as possible on the page, something we all want in our Web app, no?
HtH,
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Which DB connection method is better?
