[SOLVED] How to insert values to specific columns of DBGrid
lazarus / 16-Databases

----------------------------------------------------------------------
Gizmo · 2020-08-14 12:11

Hi

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i //
FunctionA runs through a stringlist of filenames first, and inserts
values about the files into SQLite DBFunctionAvar  ValB :
string;begin for i := 0 to slFileListA.Count -1 do    begin       
ValB := FunctionX(slFileListA.Strings[i]);       
frmSQLiteDBases.WriteToDatabase(slFileListA.Strings[i],
ValB);...end; // FunctionB runs second using a second stringlist of
filenames, and inserts values about the files into SQLite
DBFunctionBvar  ValD : string;begin for i := 0 to slFileListB.Count -1
do    begin        ValD := FunctionX(slFileListB.Strings[i]);       
frmSQLiteDBases.WriteToDatabase(slFileListB.Strings[i],
ValD);...end; // This is what inserts the values into SQLite, passing
Filename from SL as Val1, and some other data as Val2procedure
TfrmSQLiteDBases.WriteToDatabase(Val1, Val2 : string);begin  try   
sqlCOMPARETWOFOLDERS.Close;    sqlCOMPARETWOFOLDERS.SQL.Text :=
'INSERT into TBL_COMPARE_TWO_FOLDERS (DBColumn1, DBColumn2) values
(:Val1,:Val2)';    SQLTransaction1.Active := True;   
sqlCOMPARETWOFOLDERS.Params.ParamByName('DBColumn1').AsString :=
Val1;    sqlCOMPARETWOFOLDERS.Params.ParamByName('DBColumn2').AsString
:= Val2;    sqlCOMPARETWOFOLDERS.ExecSQL;  except      on E:
EDatabaseError do      begin        MessageDlg('Error','A database
error has occurred. Technical error message: ' +
E.Message,mtError,[mbOK],0);      end;  end;end;                     
                      
So is there a way to say "Put the value in ColumnX and Y starting at
Row Z"?

Many thanks

----------------------------------------------------------------------
Gizmo · 2020-08-14 13:19

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
(https://wiki.freepascal.org/SQLdb_Tutorial3) 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
frmSQLiteDBases.WriteToDatabase(Val1, Val2 : string; Counter :
integer);....FQuery.SQL.Text:='UPDATE DB_TableName SET
salary=:newsalary WHERE first_name=:firstname, last_name=:lastname
WHERE Id=:Counter' ';