Active Server Pages Frequently Asked Questions

ActiveX Data Objects (ADO) General Questions

Questions

Connection Properties
Time-out properties
Parameter Values with Stored Procedures
Time-outs
Return Values in Stored Procedures
Referencing DSNs
AbsolutePosition
Cursor Choices
CacheSize Default
Features of my Data Provider
Connections
Forward-Only Recordsets and Caching
Working with Cursors Tips
ADO and the OLE DB SDK
Advanced OLE DB Provider Information
Parameters
RecordCount
Using Input & Output Parameters
Spaces in Table or Query Name
Using GetChunk
File DSN's

Coding Examples
Using Stored Procedures
Using Input / Output Parameters


Connection Properties


QUESTION: How do I use ODBC options like TRACE, TRANSLATE, or any other driver or manufacturer specific options in ADO?
ANSWER: The Connection.Properties collection contains provider-specific properties. When using the ODBC provider for OLE DB, the collection does not include tracing ODBC calls or controlling character set translation. To set the default values for these, use the ODBC administrator or the system registry as directed.
Tip: For the specific properties that appear in the Connection.Properties collection when using the ODBC provider for OLE DB, check the ADO Web page at: http://www.microsoft.com/ado/

Time-out properties

QUESTION: I have tried setting both the Server.ScriptTimeout and the Connection.ConnectionTimeout properties to really high numbers (600 seconds), but the script is still timing after a minute or so. I can run the query from the SQL object manager and it finishes in about three minutes. When the user runs the report, he or she knows that the query may take several minutes and this is acceptable. I just need to get the Web page to be able to wait that long. Any ideas on how to troubleshoot?
ANSWER: You need to set the Command.CommandTimeout property here instead of the Connection.ConnectionTimeout. The difference between these properties is that with the CommandTimeout, you are indicating how long to wait for the Command (your query) to execute, while with the Connection.ConnectionTimeout you are indicating how long to wait for the Connection to open.

Parameter Values with Stored Procedures

When executing a stored procedure, to use the defaulting parameter values, you should set the Command Parameter Value to EMPTY in the Visual Basic for Applications or Visual Basic Script environment. In the Java Script environment, Null should be used as an equivalent to EMPTY. For example:

' The following lines use MyValue1
MyCommand.Parameters(0).Value = "MyValue1"
MyCommand.Execute
' The subsequent lines use the default value
MyCommand.Parameters(0).Value = EMPTY
MyCommand.Execute
' The next lines indicate to use the provided value, MyValue2
MyCommand.Parameters(0).Value = "MyValue2"
MyCommand.Execute

Time-outs

QUESTION: What's the difference between a Connection CommandTimeout and a Command CommandTimeout?

ANSWER: CommandTimeout on a Connection applies to the Connection.Execute & Recordset.Open where the Source property is not a Command object. If the Source of the Recordset.Open call is a genuine Command object, then it uses the CommandTimeout on the Command object instead of the Connection. Similarly, the Command.Execute uses the Command Object's property value.

Return Values in Stored Procedures

QUESTION: Does ADO (aka the Database Component in Active Server Pages) support Return Value parameters?
ANSWER: Yes. In the following example, there is a Stored Procedure on SQL Server, a DSN called Pubs setup on the client machine to get to the pubs database on the SQL Server. There is a stored procedure called MyRetValSP, which has a Return Value (as in: create procedure outparam(@out int out) as Exec @out=MyRetValSP).

…
Dim cmd As New Command
cmd.ActiveConnection = "dsn=pubs;uid=sa;pwd=;database=master;"

cmd.CommandText = "{ ? = call MyRetValSP}"
cmd(0).Direction = adParamReturnValue

cmd.Execute
Debug.Print cmd(0)
…

Note that this example is making an extra trip to the server, as the collection lookup (the line where cmd(0).Direction is called) causes an implicit refresh and thus forces an automatic parameter description. You could avoid this refresh by creating and appending your own parameter, with the Direction property set in the CreateParameter call.

Referencing DSNs

QUESTION: I can't seem to connect using ODBC File DSN. What do I need to do to make it work?
ANSWER: You may not be providing a fully qualified path. To work without a fully qualified path, you need to locate the file in the working ODBC directory or to locate it in "Program files\Common files\Odbc\Data sources." Please see the Microsoft Office or ODBC 3.0 documentation for further information.

AbsolutePosition

QUESTION : I'm using my own OLE DB Provider, and when I set AbsolutePosition to 1, I get the 2nd row. Why don't I get the first row?
ANSWER: For most cases, you won't run into this, but if you are using an OLE DB provider that supports IRowsetLocate, AbsolutePosition is 0 based instead of 1 based. You can check your provider using Recordset.Supports.

Cursor Choices

QUESTION : What is the cost or overhead of using a keyset or dynamic cursor, as opposed to a static or dynamic cursor? Why would I not always create dynamic cursors?
ANSWER: There is no single cursor type you should always use. Your choice of cursor type would depend on the functionality you want like updatability, cursor membership, visibility and scrollability. Opening a keyset cursor may take time for building the key information if you have a lot of rows in the table whereas opening a dynamic cursor is much faster. You can however configure your data provider (such as SQL Server) to populate the key information asynchronously. Dynamic cursors don't support bookmarks. If you need bookmark support, go for a keyset or static cursor. Please refer to the documentation of ADO for further suggestions.

CacheSize Default

QUESTION: The online documentation says that the Cache size "Default is 1 for forward-only cursors, 10 for all other types.", but it defaults to 1.
ANSWER: There is an error in the online documentation. It defaults to 1 for all cursor types.


Features of my Data Provider

QUESTION: I'm using ADO with the ODBC Driver and Access95 and am trying to do simple queries using the date/time fields in my database. What is the proper syntax for comparing date/time fields using ADO?
ANSWER: ADO passes the command text you provide on to the provider, so it's up to you to make sure the provider you're calling supports the syntax. The following example, calls an Access 95 Northwind database and it assumes you have an ODBC DSN setup as "MyNorthWind".


… Dim rs As New Recordset
rs.Open "select * from employees where hiredate = #10/17/93#", "dsn=MyNorthWind;uid=sa;pwd=;"

' making sure that we got the right thing back
While Not rs.EOF
Debug.Print rs("hiredate")
rs.MoveNext
Wend
…

Connections

QUESTION: The following code creates a Command, opens a Connection and calls a Stored procedure. What else should I be aware of?


<%
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = "dsn=sqlsvr;database=pubs;uid=sa;pwd=;"
cmd.CommandText = "{call mysproc}"
cmd.Execute
%>

ANSWER: A connection is opened underneath your Command object and it is released when the Command goes out of scope at the end of the page. If you want to release the Connection you can either set the Command object to reference Nothing as in:


Set cmd = Nothing


Or, you could close the connection by closing the Active connection as in:


cmd.ActiveConnection.Close


You should know that if you are using Connection Pooling, the REAL underlying connection may have been opened before you run this page of code, and it hangs around after you leave the page. See the Active Server Pages documentation for details.
Other Tips With Connections:

• Use a Connection variable, instead of a Connection string as the ActiveConnection for Commands. By using a Connection variable, you're calling an existing connection. If you use a string you are creating a new connection for the command.
  
• Clean up after yourself. Close Active Connections when you are done with them. You should also explicitly Close a connection when you are done with the connection and it is no longer needed. A connection is closed when it goes out of scope (when it's apparent you no longer need it). You can proactively call Close when you know that you no longer need a connection to free up resources before it goes out of scope.
  
• (SQL Server specific) With a forward-only, read only-cursors (aka Firehose cursors) against SQL Server, you cannot start a transaction on that connection. This is because the connection is dealing with the cursor and needs to complete what it's doing (get to the end of the cursor) before continuing.
  
• (SQL Server specific) It is a good idea to separate your Firehose cursor code from your Command Execution code. For example, you could get in situation where the same SQL pages are used, such as a query "Select * from TableX" and an update to TableX. Without setting your CommandTimeout you could hang.

Forward-Only Recordsets and Caching

QUESTION: The Recordset.Move documentation describes using the Cache as a way to retrieve records in a Forward-Only cursor in a backwards fashion by using the Cache. Is there anything I should look out for in doing so?
ANSWER: You should use the appropriate cursor for your situation, meaning, if you need to move backwards don't use a Forward-Only cursor type. But, if you must use this capability to handle limited backwards mobility with a Forward-Only cursor, you should avoid moving to the EOF as that releases your cached group. Instead, you should use Move to get to the specific record you want.

Working with Cursors Tips

The default for opening a recordset is forward-only, read-only. Some providers have a "firehose" mode, meaning they efficiently retrieve data by keeping a connection open to retrieve data. When working with such providers, the connection could be blocked from being used in a transaction. For example, the following examples demonstrate scenarios that result in errors.

Example 1: The problem is that the Command object's ActiveConnection is being set to a Connection that is forward-only and in "firehose" mode. This is the same Connection involved in the batch mode. The error from the provider appears only in the Err object, and it returns as unspecified. For example, with the msdasql provider, you get "Unspecified error".

MyConnection.Open "MyDSN", "MyLogin", "MyPwd"
MyConnection.BeginTrans
MyRecordset.Open MySelectStatement, MyConnection
Set MyCommand.ActiveConnection = MyConnection

Example 2: The problem is that the Connection is forward-only and in "firehose" mode, so you cannot put it into transaction mode. The error returned in Errors from the provider indicates that it is dealing with the firehose mode and cannot work in transaction mode. For example, with the msdasql provider against Microsoft SQL Server, you get the error "Can not start transaction while in firehose mode."

MyConnection.Open "MyDSN", "MyLogin", "MyPwd"
MyRecordset.Open MySelectStatement, MyConnection
MyConnection.BeginTrans

Example 3: The problem is that the Connection is in a forward-only and "firehose" mode, so it cannot be involved in a batch mode. The error returned in Errors from the provider indicates that the transaction could not be started. For example, with the msdasql provider against Microsoft SQL Server, you get the error "Cannot start transaction because more than one hdbc is in use."

MyConnection.Open "MyDSN", "MyLogin", "MyPwd"
MyRecordset.Open MySelectStatement, MyConnection
Set MyCommand.ActiveConnection = MyConnection
MyConnection.BeginTrans

ADO and the OLE DB SDK

QUESTION: I installed ADO previously when I installed the OLE DB SDK 1.0 last August. The ADO that is delivered in Active Server Pages is different and my previous ADO code does not run. Why?
ANSWER: ADO was shipped as a preview technology in the OLE DB SDK. There will be a 1.1 upgrade to the OLE DB SDK in the December/January timeframe. This upgrade will include the final release version of ADO 1.0 (the same DLLs that ship with Active Server Pages), an enhanced ODBC Provider (the same DLLs that ship with Active Server Pages), and several other components. The ADO preview samples and documentation will also be updated to correspond with the release version of ADO. In addition, the 1.1 SDK will include a list of distinctions since the ADO Preview, and some new samples.

Advanced OLE DB Provider Information

The default OLE DB provider for Active Server Pages is the msdasql provider that allows you to get to ODBC data sources.

There is a Supports property on the Recordset object that can be tested to determine what features/capabilities are supported by the OLE DB provider.

Msdasql does not provide support for a feature known as IRowsetScroll, and thus allows for the AbsolutePosition property to be written and read. Reading this property gives -1, -2, or -3 but never an actual position in the rowset. Writing to the property works but may not be extremely efficient as it asks the provider to read data after skipping the AbsolutePosition rows from the start of the start bookmark.

Parameters

QUESTION: When I try to set the parameter value to a string greater than 255, I get "String data right truncation" error. What's going on?
ANSWER: You need to set the Type to adLongVarChar to avoid this error when you execute. Here's an example that uses an SQL Server PUBS database (with a DSN setup as 'Pubs'):


…
Dim cmd As Command
Dim conn As New Connection
Dim param As Parameter

On Error GoTo HandleErr

conn.Open "Pubs", "sa"

Set cmd = New Command
Set cmd.ActiveConnection = conn

cmd.CommandText = "DROP PROC Bug26bSP"
cmd.CommandType = adCmdText
cmd.Execute

cmd.CommandText = "Create Procedure Bug26bSP (@ADOInParam text) AS RETURN(1)"
cmd.CommandType = adCmdText
cmd.Execute

cmd.CommandText = "Bug26bSP"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh

Debug.Print "Param 1 Type : "; cmd(1).Type
Debug.Print "Param 1 Size : "; cmd(1).Size

' Executing generates an error "String data right truncation"
' unless you set the Field.Type to adLongVarChar
'cmd(1).Type = adLongVarChar
cmd(1).Value = String(256, "A")

cmd.Execute

Exit Sub

HandleErr:
Debug.Print "Err Num : "; Err.Number
Debug.Print "Err Desc: "; Err.Description
Resume Next
…

RecordCount

QUESTION: After I delete a record, the Record count still includes the deleted record. Why?
ANSWER: If you were to refresh or requery the recordset would not include the deleted record and the RecordCount would be accurate. If you set your CacheSize to 1 (so that you are only caching one record at a time) the RecordCount accurately excludes deleted rows. You should avoid increasing the CacheSize with a keyset cursor in circumstances when you are expecting to be able to delete and use RecordCount.

Using Input & Output Parameters

QUESTION: I get a "bad parameter" error when I execute a Command that calls a Stored Procedure that uses an output parameter. What do I need to do to get this to work?
ANSWER: ADO creates an Output parameter if you set the CommandText appropriately and call Refresh on the Parameters collection. If you run into a "bad Parameter" error it is possible that there is a problem with the allocation of size. Since a size was not provided, the provider tells ADO the potential size of the parameter. ADO then tries to accommodate the potential size, which for Image and Text data type on SQL Server can be around 2 gigabytes, which triggers the "bad parameter" admonishment. To avoid this problem, provide the Size before executing.

Spaces in Table or Query name

QUESTION: I get an error saying it couldn't find the input table or query when I try to reference my table "My Table" or my query "My Query". Why doesn't this work?

…
Set DBConn = Server.CreateObject("ADODB.Connection")
DBConn.ConnectionTimeout = Session("DBConn_ConnectionTimeout")
DBConn.CommandTimeout = Session("DBConn_CommandTimeout")
DBConn.Open Session("DBConn_ConnectionString"), Session("DBConn_RuntimeUserName"), Session("DBConn_RuntimePassword")
Set cmdTemp = Server.CreateObject("ADODB.Command")
Set DataCommand1 = Server.CreateObject("ADODB.Recordset")
cmdTemp.CommandText = "My Query2"
cmdTemp.CommandType = adCmdTable
Set cmdTemp.ActiveConnection = DBConn
DataCommand1.Open cmdTemp, , 0, 1
…

ANSWER: You are setting the CommandType to adCmdTable for a query name that contains a space. You would run into the same problem if your table name also had a space in it.

To work around this problem, you could build your own SQL statement ("select * from [My Query2]"), or you might try not specifying the CommandType.

Using GetChunk

QUESTION: What is the best way to use GetChunk to retrieve large text fields?
Here is the code I am working with currently. (I have verified that the record is retrieved):

Set ADODBConnection = Server.CreateObject("ADODB.Connection")
ADODBConnection.Open "Data Source=xxx", "xxx", "xxx"
sSQLQuery = "SELECT MyStuff, MyStuff_txt FROM MyStuff_webview WHERE MyStuff = " & Request.QueryString("MyStuff")
Set RSSummary = ADODBConnection.Execute(sSQLQuery)
txtMyStuff = RSSummary.Fields("MyStuff_txt").GetChunk(1000)

ANSWER: You should try to avoid using GetChunk on a Forward-Only cursor (which is what happens when you call Execute and pass only your SQL query statement). If you must use chunks, then open the recordset as a Keyset or Static cursor. If you need to get the "complete field" then you should use Field.Value instead of GetChunk.

File DSNs

QUESTION: How do I use file DSNs with SQL Server? The following gives me an error:

Set oConnection = CreateObject("ADODB.Connection")
oConnection.Open "file name=xyz.dsn"

ANSWER: You should use the "filedsn" tag instead of "file name" and try including a path.


CODING EXAMPLES

In the following examples, it is assumed that you have an SQL Pubs database setup with a DSN named "pubs".

• Cursors
• Using Stored Procedures
• Using input/output parameters

Cursors


Sub SimpleCursor()

Set con = CreateObject("adodb.connection")
con.Open "pubs", "sa", ""
' creates a forward-only, read-only cursor
Set rs = con.Execute("select * from authors")

End Sub


Sub MoreFunctionalCursor()

Set con = CreateObject("adodb.connection")
con.Open "pubs", "sa", ""
Set rs = CreateObject("adodb.recordset")
' asks for a keyset cursor (with bookmark support) and optimistic concurrency
rs.Open "select * from authors", con, adOpenKeyset, adLockOptimistic

End Sub

Using Stored Procedures
ADO supports accessing a stored proc with text parameters. Here's an example:

Sub textparam()

Dim con As New Connection, cmd As New Command, rs As Recordset

con.Open "pubs", "sa", ""

On Error Resume Next
con.Execute "drop table texttable"
con.Execute "drop procedure textproc"
On Error GoTo 0
con.Execute "create table texttable(text text)"
con.Execute "create procedure textproc @text text as insert texttable values(@text)"

Set cmd.ActiveConnection = con
cmd.CommandText = "textproc"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("foo", adLongVarChar, adParamInput, 5000)

cmd(0) = String(1000, "A")
cmd.Execute

cmd(0) = String(2000, "B")
cmd.Execute

Set rs = con.Execute("select text from texttable")
While Not rs.EOF
v = rs(0)
Debug.Print Len(v), v
rs.MoveNext
Wend

End Sub

Using Input/Output Parameters
The following example demonstrates using input and output parameters & assumes that you have a DSN named MyPubs that gets you to an SQL Server Pubs database.

…
Dim cmd As New Command, con As New Connection
con.Open "MyPubs", "sa", ""

On Error Resume Next
con.Execute "drop procedure MySPOut"
On Error GoTo 0
con.Execute "create procedure MySPOut( @in char(200), @out char(200) out ) as select @out = @in"

Set cmd.ActiveConnection = con
cmd.CommandText = "{call MySPOut(?, ?)}"

' specify parameter info
cmd.Parameters.Append cmd.CreateParameter("in", adChar, , 200, "foo")
cmd.Parameters.Append cmd.CreateParameter("out", adChar, adParamOutput, 200)

cmd.Execute
…