Is there a way I can automate the retrieval of a recordset from a database (so I don't have to type the same code over and over)? [Print this FAQ]
Answer:
Certainly! A function like the one below encapsulates the process of getting a read-only recordset out the database. If you pass it a valid connection string or connection object in the varConnection parameter and a valid record-returning SQL query in the strSQL parameter, it will pass back a disconnected recordset. You can modify the function to return editable recordsets (recordsets that don't have a lock type of adLockReadOnly as below for example).
<% Function GetRecordset(varConnection, strSQL) Const adOpenStatic = 3 Const adLockReadOnly = 1 Const adUseClient = 3 Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset") ' Set cursor location: objRS.CursorLocation = adUseClient objRS.Open strSQL, varConnection, adOpenStatic, adLockReadOnly
' Disconnect, close and return: Set objRS.ActiveConnection = Nothing Set GetRecordset = objRS End Function %>
Examples of usage:
<% ' WITH AN EXISTING CONNECTION OBJECT: Response.Write _ GetRecordset(objConn _ , "SELECT * FROM tblScripts").GetString(2, -1) ' --- OR --- ' WITH A CONNECTION STRING: Response.Write _ GetRecordset("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\InetPub\CodeLibrary\data\CodeLibrary2000.mdb" _ , "SELECT * FROM tblScripts").GetString(2, -1) %>
FAQ posted by Richard Lowe at
11/21/2000 10:50:02 PM to the
Databases, General category.
This FAQ has been viewed 52,238 times.
Do you have a FAQ you'd like to suggest?
Suggestions? Comments? If so, send it in!
Also, if you'd like to be a FAQ Admin (creating/editing FAQs),
let me know! If you are looking for other FAQs, be
sure to check out the 4Guys
FAQ and Commonly Asked Messageboard Questions!
