GAMBAS BOOK 3.19.1

This project demonstrates the display of data from a database table in a GridView. When the program starts, the number of rows and columns in the grid view is not known, but depends on the SQL query. The structure of the result of data records (rows) and fields (columns), which is returned by the database server as a matrix, is of great advantage. Since there is no need to convert or temporarily store the delivered data, it can be inserted and displayed in the GridView immediately. A SQLite3 database table is used in the project:

Figure 17.7.5.1: Display of DB data in SQLite Database Browser

With this program source code you can display data from a database table in a GriDView:

[1]' Gambas class file
[2]
[3]PUBLIC rDBResult AS Result
[4]PUBLIC cDBVerbindung AS NEW Connection
[5]
[6]PUBLIC SUB Form_Open()
[7]  FMain.Center
[8]  FMain.Border = 1
[9]  GridView.Rows.Count = 0 ' Beim Programmstart wird KEIN Gitter angezeigt
[10]END ' Form_Open
[11]
[12]PUBLIC SUB btnDBDatenImGitterAnzeigen_Click()
[13]  GridView.Clear
[14]  GridView.Rows.Count = 0
[15]  TRY DBDatenAuslesen()
[16]  IF ERROR THEN
[17]    Message.Error("Es konnten keine DB-Daten ausgelesen werden!")
[18]    RETURN 
[19]  ENDIF ' ERROR ? 
[20]  
[21]  IF rDBResult.Count = 0 THEN 
[22]     Message.Info("Die Anzahl der selektierten Datensätze ist Null!")
[23]     RETURN 
[24]  ELSE 
[25]     SetGridViewProperty()
[26]     GridView.Rows.Count = rDBResult.Count ' Jetzt wird das Gitter gezeichnet!
[27]     GridView.SetFocus
[28]   ' Sprung in die letzte Datenzeile ---> GridView.MoveTo(rDBResult.Count - 1, 0)
[29]   ' Sprung in die erste Datenzeile ---> GridView.MoveTo(0, 0)  
[30]     GridView.MoveTo(0, 0)  
[31]  ENDIF ' rDBResult.Count = 0 ?
[32]END ' DBDatenImGitterAnzeigen
[33]
[34]PUBLIC SUB SetGridViewProperty()
[35]  DIM iDatensatzNummer, iSpaltenNummer AS Integer
[36]  
[37]  GridView.Header = 1
[38]  GridView.Columns.Count = rDBResult.Fields.Count
[39]  GridView.Columns[0].Width = 25
[40]  GridView.Columns[0].Resizable = FALSE ' Die Feldbreite ID ist fix  
[41]' GridView-Spalten-Bezeichner ermitteln und eintragen
[42]  FOR iSpaltenNummer = 0 TO rDBResult.Fields.Count - 1 
[43]      GridView.Columns[iSpaltenNummer].Title = rDBResult.Fields[iSpaltenNummer].Name
[44]  NEXT ' Feldname 
[45]END ' SetGridViewProperty
[46]
[47]PUBLIC SUB DBDatenAuslesen()
[48]  DIM sSQL_Anweisung AS String
[49]  
[50]  cDBVerbindung.Close()
[51]  cDBVerbindung.Type = "sqlite3" ' Der Typ muss klein geschrieben werden!
[52]  cDBVerbindung.Host = Application.Path &/ "SQL/GVT"  ' User.Home &/ "GVT"
[53]  cDBVerbindung.Name = "Liste" ' Das ist der Datenbank-Name
[54]  cDBVerbindung.User = "" ' ---> Nur bei MySQL und PostgreSQL erforderlich
[55]  cDBVerbindung.Password = "" ' ---> Nur bei MySQL und PostgreSQL erforderlich
[56]  cDBVerbindung.Port = "" ' ---> Nur bei MySQL und PostgreSQL erforderlich
[57]  
[58]' Versuch, eine DB-Verbindung herzustellen
[59]  TRY cDBVerbindung.Open()
[60]  IF ERROR THEN
[61]    Message.Error("Eine DB-Verbindung zum DB-Server konnte NICHT hergestellt werden!")
[62]  ENDIF  
[63]  
[64]  sSQL_Anweisung = "SELECT * FROM " & "liste" & " ORDER BY Nachname"
[65]  rDBResult = cDBVerbindung.Exec(sSQL_Anweisung)
[66]
[67]END ' DBDatenAuslesen
[68]
[69]PUBLIC SUB GridView_Data(Row AS Integer, Column AS Integer)    
[70]  rDBResult.MoveTo(Row)
[71]  GridView.Data.Text = rDBResult[Column]
[72]  IF row MOD 2 = 0 THEN GridView.Data.Background = Color.RGB(224, 224, 224)
[73]END ' grdWerteTabelle_Data
[74]
[75]PUBLIC SUB Form_Close()
[76]  TRY cDBVerbindung.Close()
[77]  IF ERROR THEN Message.Error("Fehler bei Trennen der DB-Verbindung!")
[78]END ' Form_Close
[79]
[80]PUBLIC SUB btnProgrammEnde_Click()
[81]  FMain.Close
[82]END ' Programm-Ende

The source code contains no surprises except for the procedure GridView_Data(..) in rows 69 to 73, which can be used to insert and display the data'en bloc' in the GridView:

Figure 17.7.5.2: Display of DB data in a GridView

The difference in the two figures results from the last part of the SQL statement, in which sorting by last name is required. Figure 17.7.5.1 shows the records as they were inserted into the database table.