none
What is the ADO DataType for nVarchAR(max) RRS feed

  • Question

  • I can't seem to get nvarchar(max) to work with ADO 2.8 using sql native client.

    I am creating a stored procedure and every time i attempt to add a parameter to the command object of type nVarChar(max) I receive the error

    "Parameter object is improperly defined"

    here is the code to add the parameter

    cmd.Parameters.Append cmd.createparameter(@piComments,adLongVarWChar,adparaminput,,me.comments)

     

    adLongVarWChar is the ado data type i am using to map to the new nVarChar(max) but it does not appear to be working.

    Is this supported in ADO?  I am using the sql native client connection to connect to the database as follows.

    pubStrConnectionString = "Provider=SQLNCLI;" _
             & "Server=.\sqlExpress;" _
             & "Database=MyDBName;" _
             & "Integrated Security=SSPI;" _
             & "DataTypeCompatibility=80;" _
             & "MARS Connection=True;"

     

    thanks

    Tuesday, March 21, 2006 4:04 AM

Answers

  • You can use adLongVarWChar and still make it work. Change it as follows:

    cmd.Parameters.Append cmd.CreateParameter("@param1", adLongVarWChar, adParamInput, -1, str)

    A simple example is given below

    -----------------------------------------------------------------------------------------------------------------------

    Dim cn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset
    Dim str As String

    cn.Open "Provider=SQLNCLI;Data Source=.;Integrated Security=SSPI;DataTypeCompatibility=80;MARS Connection=True;"
    cn.Execute "create procedure testproc(@paramin nvarchar(max)) as begin select @paramin end"
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "testproc"
    str = "testing !!!! testing !!!! testing !!!! testing !!!! testing !!!! testing !!!! testing !!!! testing !!!! testing !!!! testing !!!! testing !!!!"
    cmd.Parameters.Append cmd.CreateParameter("@param1", adLongVarWChar, adParamInput, -1, str)
    Set rs = cmd.Execute
    MsgBox rs.Fields(0).Value
    cn.Execute "drop procedure testproc"
    Set cmd = Nothing
    cn.Close

    -----------------------------------------------------------------------------------------------------------------------

    Hope this helps

    Friday, March 24, 2006 2:13 AM
    Moderator

All replies

  • Hi,

    the new data types are returned as binary objects.


    HTH, Jens Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---

    Tuesday, March 21, 2006 1:26 PM
    Moderator
  • I am still not able to locate the correct ado data type enumeration to use.

    In case there is confusion on what the ado data type enumeration is here is the entire listing mapping sql server 7 and 2000 data types to the appropriate ado data type enumeration

    http://www.w3schools.com/ado/ado_datatypes.asp

    of course, varchar(max) is not in this list..sooooo heeelp!  :)

     

    What ado data type enumeration do we use with varchar(max)

     

    thanks

     

     

     

    Tuesday, March 21, 2006 4:45 PM
  • Hi,

    As viewed in the blog of bob beauchemin : http://staff.develop.com/bobb/weblog/default.aspx?date=2004-11-03

    I would check if you can use the TEXT datatype, so the adLongVarChar for your reference.

    -Jens-

    Tuesday, March 21, 2006 5:41 PM
    Moderator
  • Well, I have a really bad solution but at least it works.

    I am able to set the data type on the actual sql server table to text and then configure the ado command as follows

     

    cmd.Parameters.Append cmd.CreateParameter("@piComments", adLongVarChar, adParamInput, 300000, mstrcomments)

    *where cmd is and ADODB.command object

    definitely not pretty and I am definitely not happy with this but right now this does at least work.

    Sooooo does anyone know if we can use varChar(max) because I had to switch varchar(max) to text to get it to function.

     

    thanks for the help

    Tuesday, March 21, 2006 6:05 PM
  • okay,

     

    if you set the data type on the sql express 2005/ sql server table to VarChar(max) and you use this same code

    cmd.Parameters.Append cmd.CreateParameter("@piComments", adLongVarChar, adParamInput, 300000, mstrcomments)

    You can utilize varchar(max) from ADO 2.8

    Of course, the length (300000) is just made up.

    I don't know what the correct length is to put in there because the actual length of varchar(max) is unlimited.

     

    If you use a length of 0 the parameter creation will fail.

    Tuesday, March 21, 2006 6:14 PM
  • The folks in the SQL Data Access forum should be able to address this question so I'm moving the thread over there.

    Mike - SQL Express team

    Wednesday, March 22, 2006 5:40 PM
  • You can use adLongVarWChar and still make it work. Change it as follows:

    cmd.Parameters.Append cmd.CreateParameter("@param1", adLongVarWChar, adParamInput, -1, str)

    A simple example is given below

    -----------------------------------------------------------------------------------------------------------------------

    Dim cn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset
    Dim str As String

    cn.Open "Provider=SQLNCLI;Data Source=.;Integrated Security=SSPI;DataTypeCompatibility=80;MARS Connection=True;"
    cn.Execute "create procedure testproc(@paramin nvarchar(max)) as begin select @paramin end"
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "testproc"
    str = "testing !!!! testing !!!! testing !!!! testing !!!! testing !!!! testing !!!! testing !!!! testing !!!! testing !!!! testing !!!! testing !!!!"
    cmd.Parameters.Append cmd.CreateParameter("@param1", adLongVarWChar, adParamInput, -1, str)
    Set rs = cmd.Execute
    MsgBox rs.Fields(0).Value
    cn.Execute "drop procedure testproc"
    Set cmd = Nothing
    cn.Close

    -----------------------------------------------------------------------------------------------------------------------

    Hope this helps

    Friday, March 24, 2006 2:13 AM
    Moderator
  • Ahh, what an absolute thing of beauty, thank you, thank you, thank you!!!
    Wednesday, May 16, 2007 11:37 PM