Solved

"[Microsoft][ODBC SQL Server Driver]String data, right truncation" Error

Posted on 2004-09-14
11
2,416 Views
Last Modified: 2008-01-09
i'm currently getting an error updating a large text field in my sql server database the error is:

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

[Microsoft][ODBC SQL Server Driver]String data, right truncation

/adm/includes/object_data.asp, line 220

thie error line coincides with my stored procedure being executed. this worked fine when the field was a varchar datatype but now it is a text datatype (i need this size) it doesn't work. can anyone please help...
0
Comment
Question by:lildrc
  • 5
  • 5
11 Comments
 
LVL 31

Expert Comment

by:alorentz
ID: 12054818
It means that you are trying to put to long of a string in the field.
0
 

Author Comment

by:lildrc
ID: 12055012
no i can put long strings into the field with query analyser but not through my asp page. is it a limitation of the stored procedure or command object?
0
 
LVL 31

Expert Comment

by:alorentz
ID: 12055056
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 15

Accepted Solution

by:
joeposter649 earned 300 total points
ID: 12055145
Are you passing it as adLongVarChar?
0
 

Author Comment

by:lildrc
ID: 12055198
thanks but i've already read that link and it doesn't help me.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 12055296
Have you tried ntext?
0
 

Author Comment

by:lildrc
ID: 12055304
not passing it as adLongVarChar - i thought by default it would take the type defined in the SP this is the asp code:

********START ASP************

set dataConn = Server.CreateObject("ADODB.Connection")
      dataConn.Open  "[sourcename]"
      Set adocmd = Server.CreateObject("ADODB.Command")
      'on error resume next
      with adocmd
            .CommandText = sSP
            .ActiveConnection = dataConn
            .CommandType = adCmdStoredProc
                  .Parameters(1) = sTitleE
                  .Parameters(2) = sTitleC
                  .Parameters(3) = sAuthors
                  .Parameters(4) = sDescE
                  .Parameters(5) = sDescC
                  .Parameters(6) = sDate
                  .Parameters(7) = sLocE
                  .Parameters(8) = sLocC
                  .Parameters(9) = sPubE
                  .Parameters(10) = sPubC
                  .Parameters(11) = sGen1E
                  .Parameters(12) = sGen1C
                  .Parameters(13) = sGen2E
                  .Parameters(14) = sGen2C
                  .Parameters(15) = sUrlE
                  .Parameters(16) = sUrlC
                  .Parameters(17) = sNameE
                  .Parameters(18) = sNameC
                  .Parameters(19) = sEmail
                  .Parameters(20) = sOrgE
                  .Parameters(21) = sOrgC
                  .Parameters(22) = sTel
                  .Parameters(23) = sFax
                  .Parameters(24) = iRestrict
                  .Parameters(25) = sRank
                  .Parameters(26) = iLatest
                  .Parameters(27) = iVis
                  .Parameters(28) = iArchive
                  .Parameters(29) = iCat
            if sMode="Edit" then
                  .Parameters(30) = iID
            Elseif sMode="New" then
                  .Parameters(30) = null
                  sMode="Edit"
            End If
            .Parameters(31) = request.cookies("memid")
            .Parameters(32) = request.cookies("orgid")
            .Parameters(33) = admLevel
            .Execute
            iID=.Parameters(30).value
      end with

      Set adocmd = Nothing
      dataConn.close
      Set dataConn = Nothing

*********END ASP*************

here is the stored procedure:

***********START SP**************
CREATE PROCEDURE dbo.wsp_gen_objects_update
@title_e varchar(128),
@title_c varchar(128),
@authors varchar(256),
@desc_e text,
@desc_c text,
@date varchar(16),
@location_e varchar(128),
@location_c varchar(128),
@pub_e varchar(128),
@pub_c varchar(128),
@gen1_e varchar(128),
@gen2_c varchar(2048),
@gen2_e varchar(2048),
@gen1_c varchar(128),
@url_e varchar(128),
@url_c varchar(128),
@name_e varchar(128),
@name_c varchar(128),
@email varchar(128),
@org_e varchar(128),
@org_c varchar(128),
@tel varchar(50),
@fax varchar(50),
@restrict int,
@ranking datetime,
@latest int,
@visible int,
@archive int,
@cid int,
@id int,
@mid int,
@oid int,
@level int

AS

declare @oldstamp datetime
set @oldstamp=(select gen_object_ranking from gen_objects where gen_object_id=@id)
if @ranking='' or @ranking=null
      set @ranking = @oldstamp

update gen_objects set
gen_object_title_eng=@title_e,
gen_object_title_cym=@title_c,
gen_object_authors=@authors,
gen_object_desc_eng=@desc_e,
gen_object_desc_cym=@desc_c,
gen_object_date=@date,
gen_object_location_eng=@location_e,
gen_object_location_cym=@location_c,
gen_object_publication_eng=@pub_e,
gen_object_publication_cym=@pub_c,
gen_object_gen1_eng=@gen1_e,
gen_object_gen1_cym=@gen1_c,
gen_object_gen2_eng=@gen2_e,
gen_object_gen2_cym=@gen2_c,
gen_object_url_eng=@url_e,
gen_object_url_cym=@url_c,
gen_object_name_eng=@name_e,
gen_object_name_cym=@name_c,
gen_object_email=@email,
gen_object_org_eng=@org_e,
gen_object_org_cym=@org_c,
gen_object_tel=@tel,
gen_object_fax=@fax,
gen_object_restrict=@restrict,
gen_object_ranking=@ranking,
gen_object_latest=@latest,
gen_object_visible=@visible ,
gen_object_archive=@archive
where gen_object_id=@id

delete from assocs where gen_object_id=@id and subsection_id>0
if @cid>0
      insert into assocs (gen_object_id, subsection_id) values (@id, @cid)

if @level=2 or (@level=3 and @restrict=3)
      if not exists(select assocs_id from assocs where organisation_id=@oid and gen_object_id=@id)
            insert into assocs (organisation_id,gen_object_id) values (@oid,@id)
GO

*********END SP************
0
 
LVL 31

Assisted Solution

by:alorentz
alorentz earned 200 total points
ID: 12055325
From the KB:

QUESTION: When I try to set the parameter value to a string greater than 255, I get "String data right truncation" error. What's going on?
ANSWER: You need to set the Type to adLongVarChar to avoid this error when you execute. Here's an example that uses an SQL Server PUBS database (with a DSN setup as 'Pubs'):


Dim cmd As Command
Dim conn As New Connection
Dim param As Parameter
On Error GoTo HandleErr

conn.Open "Pubs", "sa"

Set cmd = New Command
Set cmd.ActiveConnection = conn

cmd.CommandText = "DROP PROC Bug26bSP"
cmd.CommandType = adCmdText
cmd.Execute

cmd.CommandText = "Create Procedure Bug26bSP (@ADOInParam text) AS RETURN(1)"
cmd.CommandType = adCmdText
cmd.Execute

cmd.CommandText = "Bug26bSP"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh

Debug.Print "Param 1 Type : "; cmd(1).Type
Debug.Print "Param 1 Size : "; cmd(1).Size

' Executing generates an error "String data right truncation"
' unless you set the Field.Type to adLongVarChar
'cmd(1).Type = adLongVarChar
cmd(1).Value = String(256, "A")

cmd.Execute

Exit Sub

HandleErr:
Debug.Print "Err Num : "; Err.Number
Debug.Print "Err Desc: "; Err.Description
Resume Next
0
 

Author Comment

by:lildrc
ID: 12055454
ammended my code to include this:

.Parameters(4).Type = adLongVarChar
.Parameters(4) = sDescE
.Parameters(5).Type = adLongVarChar
.Parameters(5) = sDescC

and it works!

cheers alorentz and joeposter649 - is there any way for you to share the points?
0
 
LVL 31

Expert Comment

by:alorentz
ID: 12055615
You can use the split link at the bottom, near textarea.  Or, give them to joeposter.
0
 

Author Comment

by:lildrc
ID: 12055764
300 to joeposter for the initial answer
and 200 to alorentz for being more specific

cheers both!
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Attaching a file to SMTP in Classic ASP 1 42
FileUp - Classic ASP 5 19
Call a function within the ASP code 4 19
innerHTML 7 34
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

839 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