Programming => Databases => Topic started by: Gizmo on August 14, 2020, 02:11:58 pm

Title: [SOLVED] How to insert values to specific columns of DBGrid
Post by: Gizmo on August 14, 2020, 02:11:58 pm

I have what I suspect an easy to solve issue, but I am struggling.

I have a DBGrid component that is shown after two seperate functions have been run.

The first function goes through a load of files and outputs the filenames and some other values to ColumnA and ColumnB of an SQLIte table.

The second function goes through another load of files from a different folder and outputs the filenames and some other values of those files to the same SQLite table. During each function, the values are written to the SQLIte database and then when both have finished, DBGrid is made visible. The problem is, the values from the second function are put in rows below the first insertions for obvious and logical reasons. But I want the second function to put its values in Columns C, Row 1, and Columns D, Row 1, and then onto Row 2 etc starting at Row1, instead of carrying on in Columns A and B, starting from the last used row. So I want to produce a table like that seen below :

Code: [Select]
Value A Value B Value C Value D
Filename1 xyz Filename3 ghj
Filename2 kkk Filename4 ert

However, what I am getting in DBGrid with the pseudo code below is this kind of display :

Code: [Select]
Value A Value B
Filename1 xyz
Filename2 kkk
Filename3 ghj
Filename4 ert

Code: Pascal  [Select][+][-]
  1. // FunctionA runs through a stringlist of filenames first, and inserts values about the files into SQLite DB
  2. FunctionA
  3. var
  4.   ValB : string;
  5. begin
  6.  for i := 0 to slFileListA.Count -1 do
  7.     begin
  8.         ValB := FunctionX(slFileListA.Strings[i]);
  9.         frmSQLiteDBases.WriteToDatabase(slFileListA.Strings[i], ValB);
  10. ...
  11. end;
  13. // FunctionB runs second using a second stringlist of filenames, and inserts values about the files into SQLite DB
  14. FunctionB
  15. var
  16.   ValD : string;
  17. begin
  18.  for i := 0 to slFileListB.Count -1 do
  19.     begin
  20.         ValD := FunctionX(slFileListB.Strings[i]);
  21.         frmSQLiteDBases.WriteToDatabase(slFileListB.Strings[i], ValD);
  22. ...
  23. end;
  25. // This is what inserts the values into SQLite, passing Filename from SL as Val1, and some other data as Val2
  26. procedure TfrmSQLiteDBases.WriteToDatabase(Val1, Val2 : string);
  27. begin
  28.   try
  29.     sqlCOMPARETWOFOLDERS.Close;
  30.     sqlCOMPARETWOFOLDERS.SQL.Text := 'INSERT into TBL_COMPARE_TWO_FOLDERS (DBColumn1, DBColumn2) values (:Val1,:Val2)';
  31.     SQLTransaction1.Active := True;
  32.     sqlCOMPARETWOFOLDERS.Params.ParamByName('DBColumn1').AsString := Val1;
  33.     sqlCOMPARETWOFOLDERS.Params.ParamByName('DBColumn2').AsString := Val2;
  35.   except
  36.       on E: EDatabaseError do
  37.       begin
  38.         MessageDlg('Error','A database error has occurred. Technical error message: ' + E.Message,mtError,[mbOK],0);
  39.       end;
  40.   end;
  41. end;        

So is there a way to say "Put the value in ColumnX and Y starting at Row Z"?

Many thanks
Title: Re: How to insert values to specific columns of DBGrid
Post by: Gizmo on August 14, 2020, 03:19:38 pm
Realised I have to use UPDATE instead of INSERT, as INSERT obviously 'inserts' a new row!

Using UPDATE, I was able to specify columns X and Y and by also using the primary key referenced by the loop counter, I can specify what row to update. Has taken me all night though and was thanks to the Wiki ( where I realised I needed to add the ':' to the syntax after getting "Parameter not found" for what seemed like forever! Example :

Code: Pascal  [Select][+][-]
  1. frmSQLiteDBases.WriteToDatabase(Val1, Val2 : string; Counter : integer);
  2. ....
  3. FQuery.SQL.Text:='UPDATE DB_TableName SET salary=:newsalary WHERE first_name=:firstname, last_name=:lastname WHERE Id=:Counter' ';
TinyPortal © 2005-2018