lildrc
asked on
"[Microsoft][ODBC SQL Server Driver]String data, right truncation" Error
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...
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
[Microsoft][ODBC SQL Server Driver]String data, right truncation
/adm/includes/object_data.
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...
It means that you are trying to put to long of a string in the field.
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks but i've already read that link and it doesn't help me.
Have you tried ntext?
ASKER
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 .Connectio n")
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=@titl e_e,
gen_object_title_cym=@titl e_c,
gen_object_authors=@author s,
gen_object_desc_eng=@desc_ e,
gen_object_desc_cym=@desc_ c,
gen_object_date=@date,
gen_object_location_eng=@l ocation_e,
gen_object_location_cym=@l ocation_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=@restr ict,
gen_object_ranking=@rankin g,
gen_object_latest=@latest,
gen_object_visible=@visibl e ,
gen_object_archive=@archiv e
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_objec t_id) values (@oid,@id)
GO
*********END SP************
********START ASP************
set dataConn = Server.CreateObject("ADODB
dataConn.Open "[sourcename]"
Set adocmd = Server.CreateObject("ADODB
'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=@titl
gen_object_title_cym=@titl
gen_object_authors=@author
gen_object_desc_eng=@desc_
gen_object_desc_cym=@desc_
gen_object_date=@date,
gen_object_location_eng=@l
gen_object_location_cym=@l
gen_object_publication_eng
gen_object_publication_cym
gen_object_gen1_eng=@gen1_
gen_object_gen1_cym=@gen1_
gen_object_gen2_eng=@gen2_
gen_object_gen2_cym=@gen2_
gen_object_url_eng=@url_e,
gen_object_url_cym=@url_c,
gen_object_name_eng=@name_
gen_object_name_cym=@name_
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=@restr
gen_object_ranking=@rankin
gen_object_latest=@latest,
gen_object_visible=@visibl
gen_object_archive=@archiv
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_objec
GO
*********END SP************
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
.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?
You can use the split link at the bottom, near textarea. Or, give them to joeposter.
ASKER
300 to joeposter for the initial answer
and 200 to alorentz for being more specific
cheers both!
and 200 to alorentz for being more specific
cheers both!