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

[Filips Benoit]

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...........................................

[Simon Hayes]

"Filips Benoit" <be***********@pandora.be> wrote in message
news:G3*********************@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