Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2008-10-16
7
632 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
ID: 22732091
String variable type will work fine
dim  vSCFText as string
 
0
 

Author Comment

by:MDKIMZEY
ID: 22733459
Actually the string comes through as binary.
0
 
LVL 9

Expert Comment

by:JonMny
ID: 22733479
I tested it on my system and works fine, what is stored in your field? How large is it?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:MDKIMZEY
ID: 22733651
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
ID: 22733761
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
ID: 31506745
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
ID: 22734059
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Activity Monitor detail 2 24
SQL, How to do aggregation on extracting field and range band field? 2 30
SQL Dump exec output to table 3 21
Code enhancement 4 21
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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.

856 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