Board index » delphi » Garbage data in non-nullable disconnected recordset

Garbage data in non-nullable disconnected recordset

I am using RDSConnection and ADODataSet to connect to the SQL Server 7 Pubs
database's Authors table. I add a new record to the dataset thru the
DBNavigator, set the Authors.au_id to something and then click on the Post
button on the DBNavigator. Boom, garbage appears in the fields au_lname,
au_fname and au_phone.

I am including the sample file for anyone interested to take a look.

FDisconnectedRS.PAS
--------------------------------
unit FDisconnectRS;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Grids, DBGrids, Db, ADODB, ADOInt, StdCtrls, DBCtrls, Mask, ExtCtrls;

type
  TForm1 = class(TForm)
    DBNavigator1: TDBNavigator;
    edtAU_ID: TDBEdit;
    edtAU_LName: TDBEdit;
    edtAU_FName: TDBEdit;
    edtPhone: TDBEdit;
    edtAddress: TDBEdit;
    edtCity: TDBEdit;
    edtState: TDBEdit;
    edtZip: TDBEdit;
    chkContract: TDBCheckBox;
    RDSConnection1: TRDSConnection;
    ADODataSet1: TADODataSet;
    DataSource1: TDataSource;
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

const
  // Change the COnnectionString for your server
  SConnectString = 'Provider=SQLOLEDB.1;Password=;User ID=sa;Initial
Catalog=pubs;Data Source=DBSERVER';

procedure TForm1.FormCreate(Sender: TObject);
begin
  DataSource1.DataSet := ADODataSet1;
  with ADODataSet1 do
  begin
    RDSConnection := RDSConnection1;
    ConnectionString := SConnectString;
    CommandText := 'SELECT * FROM Authors WHERE 1 = 0';
    Active := True;
  end;
end;

end.

FDisconnectedRS.DFM
----------------------------------
object Form1: TForm1
  Left = 110
  Top = 19
  Width = 558
  Height = 167
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object DBNavigator1: TDBNavigator
    Left = 40
    Top = 92
    Width = 240
    Height = 25
    DataSource = DataSource1
    TabOrder = 0
  end
  object edtAU_ID: TDBEdit
    Left = 38
    Top = 28
    Width = 93
    Height = 21
    DataField = 'au_id'
    DataSource = DataSource1
    TabOrder = 1
  end
  object edtAU_LName: TDBEdit
    Left = 139
    Top = 27
    Width = 91
    Height = 21
    DataField = 'au_lname'
    DataSource = DataSource1
    TabOrder = 2
  end
  object edtAU_FName: TDBEdit
    Left = 237
    Top = 26
    Width = 90
    Height = 21
    DataField = 'au_fname'
    DataSource = DataSource1
    TabOrder = 3
  end
  object edtPhone: TDBEdit
    Left = 333
    Top = 26
    Width = 91
    Height = 21
    DataField = 'phone'
    DataSource = DataSource1
    TabOrder = 4
  end
  object edtAddress: TDBEdit
    Left = 431
    Top = 25
    Width = 93
    Height = 21
    DataField = 'address'
    DataSource = DataSource1
    TabOrder = 5
  end
  object edtCity: TDBEdit
    Left = 38
    Top = 56
    Width = 93
    Height = 21
    DataField = 'city'
    DataSource = DataSource1
    TabOrder = 6
  end
  object edtState: TDBEdit
    Left = 140
    Top = 55
    Width = 91
    Height = 21
    DataField = 'state'
    DataSource = DataSource1
    TabOrder = 7
  end
  object edtZip: TDBEdit
    Left = 238
    Top = 55
    Width = 89
    Height = 21
    DataField = 'zip'
    DataSource = DataSource1
    TabOrder = 8
  end
  object chkContract: TDBCheckBox
    Left = 336
    Top = 52
    Width = 91
    Height = 27
    Caption = 'Contract'
    DataField = 'contract'
    DataSource = DataSource1
    TabOrder = 9
    ValueChecked = 'True'
    ValueUnchecked = 'False'
  end
  object DataSource1: TDataSource
    Left = 372
    Top = 88
  end
  object ADODataSet1: TADODataSet
    Parameters = <>
    Left = 330
    Top = 88
  end
  object RDSConnection1: TRDSConnection
    Left = 290
    Top = 90
  end
end

 

Re:Garbage data in non-nullable disconnected recordset


Quote
Hardy Yau <h...@bc.sympatico.ca> wrote in message

news:7ug5hb$1n225@forums.borland.com...

Quote
> I am using RDSConnection and ADODataSet to connect to the SQL Server 7 Pubs
> database's Authors table. I add a new record to the dataset thru the
> DBNavigator, set the Authors.au_id to something and then click on the Post
> button on the DBNavigator. Boom, garbage appears in the fields au_lname,
> au_fname and au_phone.

Thanks for the report.  I can confirm your findings and I have logged this (the
defect# is  73478).

The best workaround is probably to set the required property on the fields which
are currently showing garbage (because they are required).  You can also modify
the VCL source to solve the problem.

In the TCustomADO.GetFieldData method add the line indicated below:

    Data := Recordset.Fields[Field.FieldNo-1].Value;
    if VarIsEmpty(Data) then Data := Null; <== Add this line.
    PVariantList(RecBuf+SizeOf(TRecInfo))[Field.Index] := Data;

This is the only case I have seen so far where ADO actually returns an
uninitialized variant from the field's value property.  I'm not sure if this is an
ADO bug or by design.  Regardless, it should be handled in the VCL code.  Thanks
again.

Mark

Re:Garbage data in non-nullable disconnected recordset


Thanks Mark.

At least I know that I am not nut and have a workaround. May be we can test
it under VB and see if VB exhibit the same problem.

Thanks anyway

Mark Edington (Borland) <meding...@nolunchmeat.com> wrote in message
news:7unni3$8fl1@forums.borland.com...

Quote
> Hardy Yau <h...@bc.sympatico.ca> wrote in message
> news:7ug5hb$1n225@forums.borland.com...

> > I am using RDSConnection and ADODataSet to connect to the SQL Server 7
Pubs
> > database's Authors table. I add a new record to the dataset thru the
> > DBNavigator, set the Authors.au_id to something and then click on the
Post
> > button on the DBNavigator. Boom, garbage appears in the fields au_lname,
> > au_fname and au_phone.

> Thanks for the report.  I can confirm your findings and I have logged this
(the
> defect# is  73478).

> The best workaround is probably to set the required property on the fields
which
> are currently showing garbage (because they are required).  You can also
modify
> the VCL source to solve the problem.

> In the TCustomADO.GetFieldData method add the line indicated below:

>     Data := Recordset.Fields[Field.FieldNo-1].Value;
>     if VarIsEmpty(Data) then Data := Null; <== Add this line.
>     PVariantList(RecBuf+SizeOf(TRecInfo))[Field.Index] := Data;

> This is the only case I have seen so far where ADO actually returns an
> uninitialized variant from the field's value property.  I'm not sure if
this is an
> ADO bug or by design.  Regardless, it should be handled in the VCL code.
Thanks
> again.

> Mark

Other Threads