Solved

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

Posted on 2008-10-16
7
624 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:MDKIMZEY
  • 4
  • 3
7 Comments
 
LVL 9

Expert Comment

by:JonMny
Comment Utility
String variable type will work fine
dim  vSCFText as string
 
0
 

Author Comment

by:MDKIMZEY
Comment Utility
Actually the string comes through as binary.
0
 
LVL 9

Expert Comment

by:JonMny
Comment Utility
I tested it on my system and works fine, what is stored in your field? How large is it?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:MDKIMZEY
Comment Utility
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?
0
 
LVL 9

Accepted Solution

by:
JonMny earned 125 total points
Comment Utility
I have sql server 2008 not sure what version of mdac, I used excel 2007 to test it.
Sub sdefla()

Set oADOConn = CreateObject("ADODB.Connection")

oADOConn.ConnectionString = "server=.\dev;Integrated Security=SSPI;database=test;provider=sqloledb"
 
 
 

oADOConn.Open

 

Set oADOCommand = CreateObject("ADODB.Command")

oADOCommand.ActiveConnection = oADOConn

oADOCommand.CommandType = 4

oADOCommand.NamedParameters = False

oADOCommand.CommandText = "gettest"

Set oADORecSet = oADOCommand.Execute

   

vSCFText = oADORecSet.fields("test").Value

        

    oADOConn.Close                                                        ' Close the ADO Connection

    

    Set oADOConn = Nothing                                                ' Release the ADO Connection

    Set oADOCommand = Nothing                                             ' Release

End Sub

Open in new window

0
 

Author Closing Comment

by:MDKIMZEY
Comment Utility
I appreciate your looking at this - I changed the column definition from varchar(max) to text - this did the trick...
0
 

Author Comment

by:MDKIMZEY
Comment Utility
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.


0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now