Working With GridView in Gambas using postgreSQL Database

In Gambas design a Form as Below, I am adding a TextBox,A command Button and a gridview.

Create a database table as follows

 

CREATE TABLE public.xproducts
(
    id integer NOT NULL DEFAULT nextval('"Products_id_seq"'::regclass),
    name character varying(50) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "Products_pkey" PRIMARY KEY (id)
)

inside code behind I wrote a function for initialization of database connection

Public Procedure Connect()

$Con.Close() ' Close the connection

$Con.Type = "postgresql" ' Type of connection

$Con.Host = "localhost" ' Name of the server

$Con.Login = "xdba" ' User's name for the connection

$Con.Port = "5432" ' Port to use in the connection, usually 3306

$Con.Name = "sangram" ' Name of the database we want to use

$Con.Password = "sangram" ' User's password

$Con.Open() ' Open the connection

End

Now we will write a subroutine for populating Gridview

Public Sub LoadAll()

Dim $Result As Result

Dim $Field As ResultField

Dim $rowCount As Integer = 0

Dim $Query As String

$Query = "SELECT * FROM xproducts"

Connect()

$Result = FMain.$Con.Exec($Query)

'define layout

'define the gridview layout

GridView1.header = GridView.Horizontal

GridView1.grid = True

GridView1.Rows.count = $Result.Count + 1

GridView1.Columns.count = 2

'column headers

GridView1.Columns[0].text = "Id"

GridView1.Columns[1].text = "Name"

'column width

GridView1.Columns[0].width = 100

GridView1.Columns[1].width = 100

For Each $Result

GridView1[$rowCount, 0].Text = $Result!id

GridView1[$rowCount, 1].Text = $Result!name

'Message.Info($Result!id & " " & $Result!name)

$rowCount = $rowCount + 1

Next

End

On Click of Button wrote code to Insert record into database as follows

Public Sub BtnSave_Click()

Dim $Result As Result

Dim $name As String

Dim $Product As String

$name = TxtName.Text

Dim $Query As String

$Query = "INSERT INTO XProducts(name)VALUES('" & $name & "')"

Connect()

$Result = FMain.$Con.Exec($Query)

LoadAll

End

Here $Con is connection defined globally outside of all subroutines as follows

 

 

 

Public $Con As New Connection

In Button Click event handler we are inserting record & then populate gridview with all records.

To load gridview at page load I am doing following

 

Public Sub Form_Open()

    LoadAll()

End

When we run our project Form looks like below

 

Code sample for this project can be viewed at

 https://github.com/gitsangramdesai/gambas-pg-gridview.