Edit and Update values in dbgrid in delphi

Question

I am new to delphi and I am creating a database for customers and I am able to populate their details in a dbgrid by Viewcustomers button. However I am trying to update their details by: selecting a row and change a value in a cell and updating the same by click of edit button.

I am able to do the same by selecting the row and fething the data in a seperate form. But i would like to do change the values in dbgrid itself and update. Could someone help? Updated with TDB navigator and dgEnabled property.

a)MySQL b)TDatasource c) In a form, to update a customer i did like:

procedure TForm5.editCustomersButtonClick(Sender: TObject);
var i:integer;
begin
 for i:=0 to customersDataGrid.Columns.Count-1 do begin
  if customersDataGrid.Fields[i].FieldName='customerId' then 
        if customersDataGrid.Fields[i].FieldName='customerName' then customerNameValue.Text:=customersDataGrid.Fields[i].AsString;
        if customersDataGrid.Fields[i].FieldName='product' then productValue.Text:=customersDataGrid.Fields[i].AsString;
        if customersDataGrid.Fields[i].FieldName='city' then cityValue.Text:=customersDataGrid.Fields[i].AsString;
        if customersDataGrid.Fields[i].FieldName='country' then countryValue.Text:=customersDataGrid.Fields[i].AsString;
          begin
            try
              editCustomerQuery.Close;
              editCustomerQuery.Sql.Clear;
              editCustomerQuery.SQL.Add('UPDATE `employees_details`.`customers` SET `customerId`='''+customerIDValue1.Text+''', `customerName`='''+customerNameValue.Text+''', `product`='''+productValue.Text+''', `city`='''+cityValue.Text+''', `country`='''+countryValue.Text+''' WHERE `customerId`='+FloatToStr(customersDataGrid.Fields[i].AsFloat)+'');
              editCustomerQuery.Open;
              viewCustomerQuery.ApplyUpdates;
              viewCustomerQuery.Refresh;
              except on E : Exception do
            end;
          end;
        end;
        customerIDValue1.Text:='';
        customerNameValue.Text:='';
        productValue.Text:='';
        cityValue.Text:='';
        countryValue.Text:='';
        ShowMessage('Customer Changes have been updated in database');
        customersDataGrid.DataSource.DataSet.Refresh;
    end;

d) Just checking working of TDBedit in database demos it works, but i have created a new project with out any backend database still it doesn't allow me to edit.

Answer 1

Yes, this is possible.

It's best to add a TDBNavigator to your form and set its DataSource to the same one as the grid. The point of doing this is that it gives you an easy way to save or cancel edits because of its buttons for those operations.

You should find that if you click twice in a cell in the grid, the first click focuses it and the second one puts it in edit mode. Then, you can edit the cell value in-place. If you want to avoid clicking the cell twice, set dgAlwaysShowEditor to True.

Btw, to enable in-place editing in the grid, the value dgEditing (under the grid's Options property in the Object Inspector) needs to be True and dgRowSelect needs to be False, as does the grid's ReadOnly property.

If you find that you can't edit a column value that you think ought to be editable, try the following:

• If you've got persistent TFields set up for the dataset, check the TField for that column in the Object Inspector to make sure that the field isn't marked read-only.

• Check that the dtataset doesn't have a read-only property that's set to True.

• Put a TDBedit on the form and connect it to one of the dataset's fileds. Will that accept an edit.

• If none of those things work, you'll just have to do some debugging. Inspect the grid's, fields' and datasets read-only properties at run-time using the debugger to make sure that none of them is True. OR start a new project consisting of only one form, a dataset, dataset, dbgrid and a dbnavigator with properties set as I've described: Now, can you in-place edit in that?

As an alternative to using the DBNavigator's Save and Cancel buttons, you can simply call DataSet.Post and DataSet.Cancel.

Answer 2

I thought I'd post this as a new answer as it's become apparent that I'm not answering the same question as I thought I was. Below are extracts from two D7 projects which provide in-place editing of a MySql table which has an ID column and two 80-character-wide string columns. Both are tested and work fine.

The extracts are actually from the DFM files of the projects and show which components you need, how to connect them up, and which properties you need to set in the Object Inspector (the defaults the IDE gives to components dropped on the form from the Component Palette are ok for the rest.

There is no code that needs adding to either of these projects: All you need to do is to set the Active property of the dataset which is connected to DataSource1 to True, i.e. CDS1 for the DBExpress one and ZQuery1 for the Zeos one.

Obviously, you would need to adapt the database name, field names, user name and password to your database.

DFMs

Using DBExpress components (which come with D7)

      object SQLConnection1: TSQLConnection
        ConnectionName = 'OpenMySQL50Connection'
        DriverName = 'OpenMySQL50'
        GetDriverFunc = 'getSQLDriverMYSQL50'
        LibraryName = 'dbxopenmysql50.dll'
        LoginPrompt = False
        Params.Strings = (
          'DriverName=OpenMySQL50'
          'HostName=LocalHost'
          'Database=MATestDB'
          'User_Name=sa'
          'Password='
          'BlobSize=-1')
        VendorLib = 'libmysql.dll'
      end
      object SQLQuery1: TSQLQuery
        GetMetadata = True
        MaxBlobSize = -1
        Params = <>
        SQL.Strings = (
          'select * from MATable1')
        SQLConnection = SQLConnection1
        object SQLQuery1Table1ID: TIntegerField
          FieldName = 'Table1ID'
          Required = True
        end
        object SQLQuery1AName: TStringField
          FieldName = 'AName'
          Size = 80
        end
        object SQLQuery1AValue: TStringField
          FieldName = 'AValue'
          Size = 80
        end
      end
      object DataSetProvider1: TDataSetProvider
        DataSet = SQLQuery1
      end
      object CDS1: TClientDataSet
        Aggregates = <>
        Params = <>
        ProviderName = 'DataSetProvider1'
      end
      object DataSource1: TDataSource
        DataSet = CDS1
      end
      object DBGrid1: TDBGrid
        DataSource = DataSource1
      end
      object DBNavigator1: TDBNavigator
        DataSource = DataSource1
        Align = alBottom
        TabOrder = 1
      end

Using ZEOS components (available from SourecForge.Net)

      object ZConnection1: TZConnection
        ControlsCodePage = cGET_ACP
        AutoEncodeStrings = False
        Connected = True
        DesignConnection = True
        SQLHourGlass = True
        HostName = 'localhost'
        Port = 0
        Database = 'MATestDB'
        User = 'sa'
        Protocol = 'mysql'
        LibraryLocation = 'D:\aaad7\MySql\libmysql.dll'
      end
      object ZQuery1: TZQuery
        Connection = ZConnection1
        Active = True
        SQL.Strings = (
          'select * from matable1')
        Params = <>
      end
      object DataSource1: TDataSource
        DataSet = ZQuery1
      end
      object DBGrid1: TDBGrid
        DataSource = DataSource1
      end
      object DBNavigator1: TDBNavigator
        DataSource = DataSource1
      end

Answer 3

Also make sure dgRowSelect is false. Editing and RowSelect are mutually exclusive (can't have both true at the same time).