Multiline parameter in Delphi7 TAdoQuery

Question

With Delphi 7 and SQL Server 2005 I'm trying to pass a multiline parameter (a Stringlist.text) to a TAdoQuery insert script. The insert is successful, but when i take back data from the field, i take

Line 1  Line 2  Line 3

instead of

Line 1  
Line 2  
Line 3

The fieldtype in the table is nvarchar(MAX) and i can't change it to any other type, the table is not mine. I tried to change the parameter type from widestring to ftMemo, but nothing changes. Any idea?

var
  QRDestLicenze: TADOQuery;
  LsLic := TStringList;

begin
  LsLic := TStringList.Create;
  LsLic.Add('Line 1');
  LsLic.Add('Line 2');
  LsLic.Add('Line 3');
  QRDestLic.Parameters.FindParam('FieldName).Value := LsLic.Text;
  QRDestLic.ExecSQL;
end;

How are you getting the value back from the database row? – Rob Apr 16 '13 at 08:49 — Rob, Apr 16 '13 at 08:49

score 0 · Answer 1 · answered Apr 16 '13 at 09:23

I created a demo doing exactly the same thing, although using Delphi 6 and SQL Server 2008.

  Memo1.Lines.Clear;
  Memo1.Lines.Add('Line 1');
  Memo1.Lines.Add('Line 2');
  Memo1.Lines.Add('Line 3');

  ADOQuery1.SQL.Text := 'INSERT INTO Absences '+
    '(Employee, Date_from, Notes) '+
    'VALUES (99999, ''16/04/2013'', :sNotes)';
  ADOQuery1.Parameters.ParamValues['sNotes'] := Memo1.Lines.Text;
  ADOQuery1.ExecSQL;

  ADOQuery1.SQL.Text := 'SELECT Notes FROM Absences '+
    'WHERE Employee = 99999';
  ADOQuery1.Open;
  Memo2.Lines.Text := ADOQuery1.FieldByName('Absence_notes').AsString;

This worked as expected, showing:

Line 1
Line 2
Line 3

in both memos.

The "Notes" field is of type VARCHAR(Max).

I left the parameter type as the default (ftString), and changed no other default settings on the TADOConnection or TADOQuery.

I was using the "Microsoft OLE DB Provider for SQL Server" as the ADO data link provider.

Could there be something else we might be able to try to reproduce your issue?

Thank you for your effort: I will try using ADOQuery1.Parameters.ParamValues['fieldname'] instead of QRDestLic.Parameters.FindParam('FieldName).Value. It may be different. — Giorgio Forti, Apr 18 '13 at 15:04

Multiline parameter in Delphi7 TAdoQuery

1 Answers1