Solved: Passing a SQL ntext field into VB

Create Account

Visual Basic Classic

Questions

Followers

Top Experts

forloop

Passing a SQL ntext field into VB

Here's the stored proc:
(Body is the ntext field)

CREATE Procedure usp_GetMessage
(
      @MessageID int,

      @Subject varchar(255) output,
      @Body ntext output,
      @Deliver char(12) output,
      @Marketing varchar(50) output,
      @Name varchar(50) output,
      @Email varchar(100) output,
      @OrgID int output
)
As
      Select @Subject = Subject, @Body = Body, @Deliver = convert(char(12), Deliver), @Marketing = Marketing, @Name = Name, @Email = Email, @OrgID = OrgID
      From Messages
      Where ID = @MessageID

SQL freaks out saying the assignment operator can't take ntext as an argument.

This SP gets called from a VB COM object (here's some of the call):

With cmd
.ActiveConnection = dsn
.CommandType = ADODB.adCmdStoredProc
.CommandText = "usp_GetMessage"
.Parameters.Append .CreateParameter("@MessageID", adInteger, adParamInput, 4, MsgID)
.Parameters.Append .CreateParameter("@Subject", adVarChar, adParamOutput, 255)
.Parameters.Append .CreateParameter("@Body", adVarChar, adParamOutput, 4000)

As you can see from that I orginally had the field as nvarchar but I'm trying to change it to ntext so it can hold larger pieces of text.

So, I need to know how to get an ntext field (body) to be delivered to VB through the above described code.

Zero AI Policy

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

ASKER CERTIFIED SOLUTION

agarwal_rahul

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

MTroutwine🇺🇸

Here is a way of calling a stored procedure using ADO that does not require you to use any Parameter or Command objects and should retrieve the nText value with little code. Instead use a Recordset object and code it like this:

With rstRecordset
.ActiveConnection = dsn
.Source = "EXECUTE usp_GetMessage " & MessageValue
.Open
' Assign values to local variables:
strSubject = !Subject
strBody = !Body
strDeliver = !Deliver
strMarketing = !Marketing
strName = !Name
strEmail = !Email
intOrgID = !OrgID
end with

Obviously if you are itterating through records you will need other code but this should get you going!

forloop

ASKER

MTroutwine:
You don't mention how to get the stored proc to give an ntext parameter. I'm still hung up on getting that to work.

BTW, this ntext parameter is then passed into VBScript(ASP).

jgrieger

Have any of you bothered to try this:

With oCmd
.ActiveConnection = oConn
.CommandText = "sp_someproc"
.Parameters.Refresh
.Parameters(1).Size = 2147483647
.Parameters(1) = myBigLongNTEXTString

.Execute
End With

It actually works. I use it all the time. Though, I'm using SQL Server 2000 as the DB.

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

Visual Basic Classic

Questions

Followers

Top Experts

Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.