Index Out of Range Error, SQLite3 Database

[Aruna]

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]

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

[Aruna]

Hi
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]

@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]

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][+][-]

1. procedure TForm1.LoadDataFromDatabase(const FileName: string);
2. var
3.   i, j: Integer;
4.   lRowCount: integer;
5. begin
6.  
7.   // Prepare and execute the query
8.   SQLQuery1.DataBase:=SQLite3Connection1;  //THIS IS CRITICAL chatGPT does not generate this line
9.   SQLQuery1.SQL.Text := 'SELECT * FROM '+ FileName ; // Change "your_table_name" to your actual table name
10.   SQLQuery1.Open;
11.  
12.   // Clear all cells in the grid
13.   StringGrid1.Clear;
14.   {
15.   // Optionally reset the column and row counts
16.   StringGrid1.ColCount := 0; // Reset column count to 0
17.   StringGrid1.RowCount := 1; // Reset row count to 1 (for header or empty row)
18.   }
19.  
20.   writeln(SQLQuery1.FieldCount);
21.   writeln(SQLQuery1.RecordCount);
22.  
23.   lRowCount := 0; // Count total row count.
24.   SQLQuery1.First;
25.   while not SQLQuery1.EOF do begin
26.     inc(lRowCount);
27.     SQLQuery1.Next;
28.   end;
29.    writeln(lRowCount);
30.  
31.     // Set up the StringGrid to match the number of columns in the query
32.   StringGrid1.ColCount := SQLQuery1.FieldCount;
33.   StringGrid1.RowCount := lRowCount + 1; // 1 extra row for the header
34.  
35.   // Populate the column headers in the StringGrid
36.   for i := 0 to SQLQuery1.FieldCount - 1 do
37.   begin
38.     StringGrid1.Cells[i, 0] := SQLQuery1.Fields[i].FieldName; // Add field names as headers
39.   end;
40.  
41.   // Populate the grid with the database records
42.   SQLQuery1.First; // Move to the first record
43.   i := 0;          // Start at row 1 (row 0 is the header)
44.  
45.   while not SQLQuery1.EOF do
46.   begin
47.     for j := 0 to SQLQuery1.FieldCount - 1 do
48.     begin
49.       // Populate each cell with data from the current record
50.       StringGrid1.Cells[j, i+1] := SQLQuery1.Fields[j].AsString;
51.     end;
52.     Inc(i);          // Move to the next row
53.     SQLQuery1.Next;  // Move to the next record in the query result
54.   end;
55.  
56.   SQLQuery1.SQL.Clear;
57.   SQLQuery1.Close;
58.   // SQLQuery1.Free; <- DONT DO THAT, you wont be able to do a new query
59.   //                    if SQLQuery1 has been freed
60. end;

[Aruna]

You 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.