Adding TEXT parameters to ADODB.Command without using stored procedures

Question

I need to perform INSERTs on a SQL Server 2008 table with some TEXT columns, and I can't have the ADODB.Command parameter for the TEXT column working.

The table is defined as:

CREATE TABLE dbo.Test (
    TestId INT NOT NULL IDENTITY(1, 1)
    , CreationDate DATETIME NOT NULL
    , Value TEXT NOT NULL
    , CONSTRAINT pkTest PRIMARY KEY (TestId)
)

The test page is like this:

<!-- METADATA TYPE="typelib" NAME="Microsoft ActiveX Data Objects 2.8 Library" UUID="{2A75196C-D9EB-4129-B803-931327F72D5C}" VERSION="2.8" -->
<%@ CODEPAGE = 65001 LCID = 1040 %>
<%
    Option Explicit
    Response.Clear
    Response.CharSet = "UTF-8"
    Response.CodePage = 65001
    Response.ContentType = "text/plain"
    Dim i : i = 0
    Dim value : value = ""
    For i = 1 To 10000
        If i Mod 3 = 0 Then
            value = value & "a "
        Else
            value = value & "a"
        End If
    Next
    Dim connection : Set connection = Server.CreateObject("ADODB.Connection")
    connection.CommandTimeout = 5
    connection.ConnectionString = "Server=XXX; Database=XXX; Uid=XXX; Pwd=XXX;"
    connection.ConnectionTimeout = 5
    connection.IsolationLevel = adXactReadUncommitted
    connection.Mode = adModeRead
    connection.Provider = "SQLOLEDB"
    connection.Open
    Dim command : Set command = Server.CreateObject("ADODB.Command")
    command.ActiveConnection = connection
    command.CommandText = "insert into dbo.Test (CreationDate, Value) values (getdate(), ?)"
    command.CommandTimeout = 5
    command.CommandType = adCmdText
    command.Parameters.Append command.CreateParameter("Value", adVarChar, adParamInput, 0, value)
    command.Prepared = True
    command.Execute
    Set command = Nothing
    connection.Close
    Set connection = Nothing
    Response.End
%>

When I execute it, I get this error:

ADODB.Command error '800a0d5d'
Application uses a value of the wrong type for the current operation.
/test/default.asp, line 32

Line 32 is

command.Parameters.Append command.CreateParameter("Value", adVarChar, adParamInput, 0, value)

I tried changing the Size parameter, to no avail.

Looking around the net I found the Microsoft Knowledge Base Article called How to call SQL Server stored procedures from ASP, its "Method 1" example is interesting, as it does not declares the parameters but reads them form the server:

cmd.CommandText = "sp_test"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters(1) = 11

so I created a stored procedure to perform the INSERT:

CREATE PROCEDURE dbo.TestInsertion (@CreationDate DATETIME, @Value TEXT) AS BEGIN
    SET NOCOUNT ON
    INSERT INTO dbo.Test (CreationDate, Value) VALUES (@CreationDate, @Value)
    SELECT TestId = SCOPE_IDENTITY()
END

and modified the ADODB.Command bit in the page like this:

Dim command : Set command = Server.CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandText = "dbo.TestInsertion"
command.CommandTimeout = 5
command.CommandType = adCmdStoredProc
command.Parameters.Refresh
command.Parameters(1).Value = Date
command.Parameters(2).Value = value
command.Prepared = True
command.Execute
Set command = Nothing

and it worked.

Is it possible to specify a TEXT parameter for ADODB.Commands in classic ASP without resorting to stored procedures?

Answer 1

Try to pass adLongVarChar constant for the text data type.

With adLongVarChar you also need to specify the size of value, otherwise you will get the Parameter object is improperly defined. Inconsistent or incomplete information was provided. error.

command.Parameters.Append command.CreateParameter("Value", adLongVarChar, adParamInput, Len(value), value)

See some ADO Data Type Mappings.