asked on

What is the best way to get a varchar(max) from ADO

I have a SQL Express column of type varchar(max). What is the best way to extract the column value using ADO in VBA? Do I need to declare and use the Windows API Function: WideCharToMultiByte to use the value as text?

A value of correct length is returned but not as a recognizable or usable string.

Set oADOConn = CreateObject("ADODB.Connection") 
oADOConn.ConnectionString = oConnString
oADOConn.Open
 
Set oADOCommand = CreateObject("ADODB.Command")
oADOCommand.ActiveConnection = oADOConn
oADOCommand.CommandType = adCmdStoredProc
oADOCommand.NamedParameters = False
oADOCommand.CommandText = "GetSCFText"    
Set oADORecSet = oADOCommand.Execute
   
vSCFText = oADORecSet.fields("SCFText").value
        
    oADOConn.Close                                                        ' Close the ADO Connection
    
    Set oADOConn = Nothing                                                ' Release the ADO Connection
    Set oADOCommand = Nothing                                             ' Release the ADO Command

Open in new window

JonMny

String variable type will work fine
dim vSCFText as string

MDKIMZEY

ASKER

Actually the string comes through as binary.

JonMny

I tested it on my system and works fine, what is stored in your field? How large is it?

MDKIMZEY

ASKER

JonMny, Thanks much for looking at this and trying it on your system - the column SCFText is declared as SCFTEXT varchar(max) in SQL Express 2005.

What version of SQL and MDAC are you using?

ASKER CERTIFIED SOLUTION

JonMny

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MDKIMZEY

ASKER

I appreciate your looking at this - I changed the column definition from varchar(max) to text - this did the trick...

MDKIMZEY

ASKER

This may be an issue with SQL Express 2005 and ADO 2.8 - the solution I used was to change the varchar(max) column to a text column and add a SET TEXTSIZE statement to the stored procedure.

I did try using a call to:

Private Declare Function WideCharToMultiByte Lib "kernel32" _
(ByVal codepage As Long, ByVal dwFlags As Long, _
lpWideCharStr As Any, ByVal cchWideChar As Long, _
lpMultiByteStr As Any, ByVal cchMultiByte As Long, _
ByVal lpDefaultChar As String, _
ByVal lpUsedDefaultChar As Long) As Long

on the binary data in the Value property of the ADO Field. This had no affect.