After copying a record with an SP all textfields has max lenght

Dear All,

After copying a record using an Stored procedure all textfields (nvarchar)
has max lenght !

See VBA-code and SP below

VBA-code tos execute SP

Dim objcommand As ADODB.Command
Dim intReturnParam As Long
Set objcommand = New ADODB.Command
With objcommand
.CommandType = adCmdStoredProc
.CommandText = "FB_CopyOrder"
.Parameters.Append .CreateParameter("return_value", adInteger,
adParamReturnValue)
.Parameters.Append .CreateParameter("ORD_ID", adInteger,
adParamInput, , Me.ORD_ID)
.Parameters.Append .CreateParameter("ORD_P_ID", adInteger,
adParamInput, , Me.ORD_P_ID)
.Parameters.Append .CreateParameter("ORD_PHTI_ID", adInteger,
adParamInput, , Me.ORD_PHTI_ID)
.Parameters.Append .CreateParameter("ORD_NAME", adWChar,
adParamInput, 50, Me.ORD_NAME)
.Parameters.Append .CreateParameter("ORD_CLIENT_CODE", adWChar,
adParamInput, 50, Me.ORD_CLIENT_CODE)
.Parameters.Append .CreateParameter("ORD_INTERNAL_NOTE", adWChar,
adParamInput, 1024, Me.ORD_INTERNAL_NOTE)
.Parameters.Append .CreateParameter("ORD_REQUESTED_DELIVERY_DATE",
adDate, adParamInput, , Me.ORD_REQUESTED_DELIVERY_DATE)
.Parameters.Append .CreateParameter("ORD_REQUESTED_QUANTITY",
adInteger, adParamInput, , Me.ORD_REQUESTED_QUANTITY)
.Parameters.Append .CreateParameter("ORD_AVAILABLE_QUANTITY",
adInteger, adParamInput, , Me.ORD_AVAILABLE_QUANTITY)
.ActiveConnection = CurrentProject.Connection
.Execute
intReturnParam = .Parameters(0).Value
End With

Stored procedure

Alter Procedure FB_CopyOrder

--List of parameters to be added to the parametercollection of the
ADO-commandobject before executing the command

@.SourceOrderID int,

@.ORD_P_ID int,

@.ORD_PHTI_ID int,

@.ORD_NAME nvarchar(50),

@.ORD_CLIENT_CODE nvarchar(50),

@.ORD_INTERNAL_NOTE nvarchar(1024),

@.ORD_REQUESTED_DELIVERY_DATE datetime,

@.ORD_REQUESTED_QUANTITY int,

@.ORD_AVAILABLE_QUANTITY int

as

declare @.err int

declare @.NewOrderid int

begin tran

-- add new order values = command-parameters

insert into [ORDER] (ORD_P_ID, ORD_PHTI_ID, ORD_NAME, ORD_CLIENT_CODE,
ORD_CREATION_DATE, ORD_INTERNAL_NOTE, ORD_REQUESTED_DELIVERY_DATE,
ORD_REQUESTED_QUANTITY, ORD_AVAILABLE_QUANTITY)

values (@.ORD_P_ID, @.ORD_PHTI_ID, @.ORD_NAME, @.ORD_CLIENT_CODE,
convert(varchar,getdate(),101), @.ORD_INTERNAL_NOTE,
convert(varchar,@.ORD_REQUESTED_DELIVERY_DATE,101), @.ORD_REQUESTED_QUANTITY,
@.ORD_AVAILABLE_QUANTITY)

set @.err = @.@.Error

select @.NewOrderID =SCOPE_IDENTITY()

etc................................."Filips Benoit" <benoit.filips@.pandora.be> wrote in message
news:G3bkc.90727$o73.5680193@.phobos.telenet-ops.be...
> Dear All,
> After copying a record using an Stored procedure all textfields (nvarchar)
> has max lenght !
>
> See VBA-code and SP below
> VBA-code tos execute SP
> Dim objcommand As ADODB.Command
> Dim intReturnParam As Long
> Set objcommand = New ADODB.Command
> With objcommand
> .CommandType = adCmdStoredProc
> .CommandText = "FB_CopyOrder"
> .Parameters.Append .CreateParameter("return_value", adInteger,
> adParamReturnValue)
> .Parameters.Append .CreateParameter("ORD_ID", adInteger,
> adParamInput, , Me.ORD_ID)
> .Parameters.Append .CreateParameter("ORD_P_ID", adInteger,
> adParamInput, , Me.ORD_P_ID)
> .Parameters.Append .CreateParameter("ORD_PHTI_ID", adInteger,
> adParamInput, , Me.ORD_PHTI_ID)
> .Parameters.Append .CreateParameter("ORD_NAME", adWChar,
> adParamInput, 50, Me.ORD_NAME)
> .Parameters.Append .CreateParameter("ORD_CLIENT_CODE", adWChar,
> adParamInput, 50, Me.ORD_CLIENT_CODE)
> .Parameters.Append .CreateParameter("ORD_INTERNAL_NOTE", adWChar,
> adParamInput, 1024, Me.ORD_INTERNAL_NOTE)
> .Parameters.Append
..CreateParameter("ORD_REQUESTED_DELIVERY_DATE",
> adDate, adParamInput, , Me.ORD_REQUESTED_DELIVERY_DATE)
> .Parameters.Append .CreateParameter("ORD_REQUESTED_QUANTITY",
> adInteger, adParamInput, , Me.ORD_REQUESTED_QUANTITY)
> .Parameters.Append .CreateParameter("ORD_AVAILABLE_QUANTITY",
> adInteger, adParamInput, , Me.ORD_AVAILABLE_QUANTITY)
> .ActiveConnection = CurrentProject.Connection
> .Execute
> intReturnParam = .Parameters(0).Value
> End With
> Stored procedure
> Alter Procedure FB_CopyOrder
> --List of parameters to be added to the parametercollection of the
> ADO-commandobject before executing the command
> @.SourceOrderID int,
> @.ORD_P_ID int,
> @.ORD_PHTI_ID int,
> @.ORD_NAME nvarchar(50),
> @.ORD_CLIENT_CODE nvarchar(50),
> @.ORD_INTERNAL_NOTE nvarchar(1024),
> @.ORD_REQUESTED_DELIVERY_DATE datetime,
> @.ORD_REQUESTED_QUANTITY int,
> @.ORD_AVAILABLE_QUANTITY int
> as
> declare @.err int
> declare @.NewOrderid int
> begin tran
> -- add new order values = command-parameters
> insert into [ORDER] (ORD_P_ID, ORD_PHTI_ID, ORD_NAME, ORD_CLIENT_CODE,
> ORD_CREATION_DATE, ORD_INTERNAL_NOTE, ORD_REQUESTED_DELIVERY_DATE,
> ORD_REQUESTED_QUANTITY, ORD_AVAILABLE_QUANTITY)
> values (@.ORD_P_ID, @.ORD_PHTI_ID, @.ORD_NAME, @.ORD_CLIENT_CODE,
> convert(varchar,getdate(),101), @.ORD_INTERNAL_NOTE,
> convert(varchar,@.ORD_REQUESTED_DELIVERY_DATE,101),
@.ORD_REQUESTED_QUANTITY,
> @.ORD_AVAILABLE_QUANTITY)
> set @.err = @.@.Error
> select @.NewOrderID =SCOPE_IDENTITY()
> etc.................................

It looks like you should be using adVarWChar, not adWChar - the data is
being treated as nchar, not nvarchar, so it's being 'padded out' with
spaces.

Simon