Index Out of Range Error, SQLite3 Database
lazarus / 16-Databases

----------------------------------------------------------------------
Aruna · 2024-09-27 13:05

Hi,

I'm currently teaching myself how to connect to a SQLite database, and
I’ve made some progress. I'm using the chinook.db file for testing.
When I access the employee's table, everything works as expected.
However, when I attempt to open any other table, I encounter "Index
Out of Range" errors. I've attached screenshots for reference. The zip
file includes the source code and chinook.db.

Thank you!

----------------------------------------------------------------------
cdbc · 2024-09-27 13:18

Hi
Do you have a 'Fixed' column?!? ...That would be index 0
In the Object inspector set 'FixedCols' to 0
Regards Benny

----------------------------------------------------------------------
Aruna · 2024-09-27 13:25

Quote from: cdbc on September 27, 2024, 03:18:09 pmHi
Do you have a 'Fixed' column?!? ...That would be index 0
In the Object inspector set 'FixedCols' to 0
Regards Benny
 Hi Benny, Thank you and I am not sure. I just plugged in what chatGPT
generated and found it does not initialize the SQL Query or
Transaction so after fiddling with those and getting it to work I was
happy thinking oh-kay we are good to go. Then I tried to open
different tables and that Index out fo range error happens.

I am puzzled because the employee table works fine and dandy any other
table chokes. Exact same code only works for the employee table, so am
a bit confused. Going to try what yu said now, thank you.

----------------------------------------------------------------------
Aruna · 2024-09-27 14:03

@cdbc your not going to believe this. Guess what I just found out? It
is gdb messing with my effin code. I usually press F9 to compile+build
and run. Just for kicks, I decided to press Ctrl+Shift+F9 and run
without the debugger and now it goes through and shows me the table
data BUT goes and chokes with the same error :-)

At least we made some progress. Screenshot attached.

----------------------------------------------------------------------
BrunoK · 2024-09-27 14:20

Problem no 1 : SQLQuery1.PacketRecords is = 10, that is what is
reported as RecordCount.
Problem no 2 : After having fed the StringGrid with data, you free
SQLQuery1, which doesn't exist anymore for a new query.

Attached code counts (iterates the Dataset) the real number of
returned records before setting the grid's rowcount.

You would be better of using a TDBGrid which is designed for exploring
tables.

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 procedure
TForm1.LoadDataFromDatabase(const FileName: string);var  i, j:
Integer;  lRowCount: integer;begin   // Prepare and execute the query 
SQLQuery1.DataBase:=SQLite3Connection1;  //THIS IS CRITICAL chatGPT
does not generate this line  SQLQuery1.SQL.Text := 'SELECT * FROM '+
FileName ; // Change "your_table_name" to your actual table name 
SQLQuery1.Open;   // Clear all cells in the grid  StringGrid1.Clear; 
{  // Optionally reset the column and row counts  StringGrid1.ColCount
:= 0; // Reset column count to 0  StringGrid1.RowCount := 1; // Reset
row count to 1 (for header or empty row)  }  
writeln(SQLQuery1.FieldCount);  writeln(SQLQuery1.RecordCount);  
lRowCount := 0; // Count total row count.  SQLQuery1.First;  while not
SQLQuery1.EOF do begin    inc(lRowCount);    SQLQuery1.Next;  end; 
 writeln(lRowCount);     // Set up the StringGrid to match the number
of columns in the query  StringGrid1.ColCount :=
SQLQuery1.FieldCount;  StringGrid1.RowCount := lRowCount + 1; // 1
extra row for the header   // Populate the column headers in the
StringGrid  for i := 0 to SQLQuery1.FieldCount - 1 do  begin   
StringGrid1.Cells[i, 0] := SQLQuery1.Fields[i].FieldName; // Add field
names as headers  end;   // Populate the grid with the database
records  SQLQuery1.First; // Move to the first record  i := 0;       
 // Start at row 1 (row 0 is the header)   while not SQLQuery1.EOF do 
begin    for j := 0 to SQLQuery1.FieldCount - 1 do    begin      //
Populate each cell with data from the current record     
StringGrid1.Cells[j, i+1] := SQLQuery1.Fields[j].AsString;    end;   
Inc(i);          // Move to the next row    SQLQuery1.Next;  // Move
to the next record in the query result  end;   SQLQuery1.SQL.Clear; 
SQLQuery1.Close;  // SQLQuery1.Free; <- DONT DO THAT, you wont be able
to do a new query  //                    if SQLQuery1 has been
freedend;

----------------------------------------------------------------------
Aruna · 2024-09-27 15:04

Quote from: BrunoK on September 27, 2024, 04:20:04 pmYou would be
better of using a TDBGrid which is designed for exploring tables.
 Hello @BrunoK Thank you so much for fixing my code it works
beautifully now. The plan was to start with a StringGrid and once
working move on to a TDBGrid.