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
What is the ADO DataType for nVarchAR(max)
Ankith
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
grogmaster
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
Markmar1
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
salafa
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
Anna Lidman
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.
Jason Dear
Hi,
the new data types are returned as binary objects.
HTH, Jens Suessmeyer.
---
http://www.sqlserver2005.de
---
AlexFreitas
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-
Jamador