title: Adding TEXT parameters to ADODB.Command without using stored procedures
body: <p>I need to perform <code>INSERT</code>s on a SQL Server 2008 table with some <code>TEXT</code> columns, and I can't have the <code>ADODB.Command</code> parameter for the <code>TEXT</code> column working.</p>
  
  <p>The table is defined as:</p>
  
  <pre><code>CREATE TABLE dbo.Test (
      TestId INT NOT NULL IDENTITY(1, 1)
      , CreationDate DATETIME NOT NULL
      , Value TEXT NOT NULL
      , CONSTRAINT pkTest PRIMARY KEY (TestId)
  )
  </code></pre>
  
  <p>The test page is like this:</p>
  
  <pre><code>&lt;!-- METADATA TYPE="typelib" NAME="Microsoft ActiveX Data Objects 2.8 Library" UUID="{2A75196C-D9EB-4129-B803-931327F72D5C}" VERSION="2.8" --&gt;
  &lt;%@ CODEPAGE = 65001 LCID = 1040 %&gt;
  &lt;%
      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 &amp; "a "
          Else
              value = value &amp; "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
  %&gt;
  </code></pre>
  
  <p>When I execute it, I get this error:</p>
  
  <pre><code>ADODB.Command error '800a0d5d'
  Application uses a value of the wrong type for the current operation.
  /test/default.asp, line 32
  </code></pre>
  
  <p>Line 32 is</p>
  
  <pre><code>command.Parameters.Append command.CreateParameter("Value", adVarChar, adParamInput, 0, value)
  </code></pre>
  
  <p>I tried changing the Size parameter, to no avail.</p>
  
  <p>Looking around the net I found the Microsoft Knowledge Base Article called <a href="http://support.microsoft.com/kb/164485/" rel="noreferrer">How to call SQL Server stored procedures from ASP</a>, its "Method 1" example is interesting, as it does not declares the parameters but reads them form the server:</p>
  
  <pre><code>cmd.CommandText = "sp_test"
  cmd.CommandType = adCmdStoredProc
  cmd.Parameters.Refresh
  cmd.Parameters(1) = 11
  </code></pre>
  
  <p>so I created a stored procedure to perform the <code>INSERT</code>:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>and modified the <code>ADODB.Command</code> bit in the page like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>and it worked.</p>
  
  <p>Is it possible to specify a <code>TEXT</code> parameter for <code>ADODB.Command</code>s in classic ASP without resorting to stored procedures?</p>