Friday, February 24, 2012

Gridview1 or gambas data grid

Gridview and the Data Handling in gambas Event

Here is another short tutorial based around database work under Gambas. I think Gambas is a marvelous data manipulation tool. I suspect it has greater facility than the so-called 4th Generation ‘application generators’ I was using not so long ago.
The tutorial highlights a feature of gridview display which saves huge amounts of time when displaying large databases. The feature is actually used in the Gambas database example so is nothing original. I do find with a lot of Gambas examples that the the example author tries too hard to show too many capabilities with the result that the example itself becomes confusing.
The following Gambas2 code:
1 Deletes a file test.sqlite in the users home directory (if it exists).
2 Creates an SQLite database test.sqlite and defines a table sampleTable with two integer fields, one a sequence (yes, I know SQLite does that internally anyway!) and the other a random number.
3 Fills the table with 10000 records.
4 Displays the results in a gridview.
On Gambas you need nothing more than a gridview (gridview1) on an otherwise empty form. The only interesting bit that may need explanation is the GridView1_Data event. This is called to fill an empty gridview cell as it is exposed (by scrolling). It saves having to load all 10000 records into the gridview with the time penalty that would imply.
Here is the code for FMain.class:

01' Gambas class file
02PRIVATE $hConn AS NEW Connection
03PRIVATE $res AS Result
04'-------------------------------------------------
05PUBLIC SUB Form_Open()
06  DIM iCount AS Integer
07  DIM hTable AS Table
08  DIM rTest AS result
09  DIM sql AS String
10   
11  'define the gridview layout
12  GridView1.header = GridView.Horizontal
13  GridView1.grid = TRUE
14  GridView1.Rows.count = 0
15  GridView1.Columns.count = 2
16  GridView1.Columns[0].text = "ID"
17  GridView1.Columns[1].text = "Value"
18  GridView1.Columns[0].width = 55
19  GridView1.Columns[1].width = 55
20   
21   
22  WITH $hConn
23    .Type = "sqlite"
24    .host = User.home
25    .name = ""
26  END WITH
27   
28  'delete an existing test.sqlite 
29  IF Exist(User.home & "/test.sqlite") THEN
30    KILL User.home & "/test.sqlite"
31  ENDIF
32   
33  'create test.sqlite
34  $hConn.Open
35    $hConn.Databases.Add("test.sqlite")
36  $hconn.Close
37   
38  'define the table sampleTable
39  $hconn.name = "test.sqlite"
40  $hConn.Open
41    hTable = $hConn.Tables.Add("sampleTable")
42    hTable.Fields.Add("s_seq", db.Integer)
43    hTable.Fields.Add("s_rndm", db.Integer)
44    hTable.PrimaryKey = ["s_seq"]
45    hTable.Update
46     
47  'fill the table with generated data
48  $hconn.Begin
49    rTest = $hConn.Create("sampleTable")
50    FOR iCount = 1 TO 10000
51      rTest!s_seq = iCount
52      rTest!s_rndm = Int(Rnd(0, 100))
53      rTest.Update
54    NEXT
55  $hConn.Commit
56   
57  'read the database
58  sql = "select s_seq as ID, s_rndm as Value from sampleTable"
59  $res = $hconn.Exec(sql)
60   
61  CATCH
62    $hConn.Rollback
63    Message.Error(DConv(Error.Text))
64     
65END
66'-------------------------------------------------
67PUBLIC SUB Form_Activate()
68  'change the rowcount of the gridview from 0 to the number of records.
69  'This triggers the data handling event
70  GridView1.Rows.Count = $res.Count 
71END
72'-------------------------------------------------
73PUBLIC SUB GridView1_Data(Row AS Integer, Column AS Integer)
74  $res.moveTo(row)
75  GridView1.Data.text = Str($res[GridView1.Columns[column].text])
76END
77'-------------------------------------------------
78PUBLIC SUB Form_Close()
79  $hconn.Close
80END
81'-------------------------------------------------
We initially set the row count of the gridview to 0. Having read the database into result $res, it will have 10000 records. In other words $res.count=10000. Setting GridView1.Rows.Count = $res.Count will make the gridview (which you have put on the form) a window to this grid of 1000 rows by 2 columns. The fact that empty cells are now displayed in this window to a much bigger grid causes the gridview object to repeatedly call Gridview1_data for each empty cell in view. The event then fills the value of the blank cell from the equivalent value in the result set. In no time at all every blank cell is filled with the corresponding data from the result set.
If you scroll down the gridview, it exposes empty cells in the grid which are automatically filled by the data handling event. And so on.
You could load all the available data into the gridview in one step but it would take an unacceptably long time. This method gives the impression of blinding speed by only loading the gridview cell values which are visible. It’s just so clever.

Saturday, February 18, 2012

Gambas datagrid

Gambas data grid code not connected to a database

Private Sub PopulateCharges()
         Dim intRow As Integer = 0
         Dim strType As String = ""

         If rsCharges.Available Then
                 rsCharges.MoveFirst
                 grdCharge.Rows.Count = rsCharges.Count
                 For Each rsCharges
                         grdCharge[intRow, 0].Text = rsCharges!unique_id
                         grdCharge[intRow, 1].Text = rsCharges!charge_source
                         grdCharge[intRow, 2].Text = rsCharges!charge_type
                         grdCharge[intRow, 3].Text = rsCharges!charge_start
                         grdCharge[intRow, 4].Text = rsCharges!charge_end
                         grdCharge[intRow, 5].Text =
rsCharges!charge_display
                         Inc intRow
                 Next
                 modMain.UpdateStatus(rsCharges.Count & " Charge records
returned")
                 LoadChargeCombos(rsCharges)
                 grdCharge.Row = intRow - 1
         Endif
         Catch
             Message.Error(Error.Class.Name & ": " & Error.Where & ": "
& Error.Text)
End

I assume you have got your recordset (rsCharges) from the Db. You ned to
check how many rows are need in the grid and set this. Then populate
each row and column as above.

Gambas Sample data grid program mysql db connected



' Gambas class file
PUBLIC MyConn AS Connection 
PUBLIC MyRS AS Result 
PUBLIC vCari AS String 


PUBLIC SUB Form_Open()

  DIM iCount AS Integer
  DIM hTable AS Table
  DIM sql AS String
  DIM rsSuperMarket AS Result
  DIM intRow AS Integer = 0
  DIM strType AS String = ""
 

    
  'define the gridview layout

  GridView1.header = GridView.Horizontal
  GridView1.grid = TRUE
  GridView1.Rows.count = 2
  GridView1.Columns.count = 4
  GridView1.Columns[0].text = "Pin"
  GridView1.Columns[1].text = "Description"
  GridView1.Columns[2].text = "Quantity"
  GridView1.Columns[3].text = "Prize"
  GridView1.Columns[0].width = 100
  GridView1.Columns[1].width = 100
  GridView1.Columns[2].width = 100
  GridView1.Columns[3].width = 100


  'define the table SuperMarket for the data grid to be able to read. My 'SuperMarketInventory, database has four records(Pin, Description, Quantity and Prize.)
rsSuperMarket is the variable where records from MYSQL pass through.

  rsSuperMarket = MyConn.Exec("SELECT * FROM SuperMarket") 'executing query
   IF rsSuperMarket.Available THEN
                 rsSuperMarket.MoveNext
                 GridView1.Rows.Count = rsSuperMarket.Count
                 FOR EACH rsSuperMarket
                       GridView1[intRow, 0].Text = rsSuperMarket!Pin
                       GridView1[intRow, 1].Text = rsSuperMarket!Description
                       GridView1[intRow, 2].Text = rsSuperMarket!Quantity
                       GridView1[intRow, 3].Text = rsSuperMarket!Prize
                        
'INC intRow is a very important code that fills the table with generated data.
        INC intRow

        NEXT
          MyConn.Quote(rsSuperMarket.Count & " Charge records returned")
 
ENDIF
END

PUBLIC SUB Label1_MouseDown()

 

END

PUBLIC SUB Label2_MouseDown()

 

END



PUBLIC SUB txtPin_Leave()




END

PUBLIC SUB txtPin_KeyPress()


END

PUBLIC SUB btnClear_Click()

  txtPin.text = "" 
  txtDescription.text = "" 
  txtQuantity.text = "" 
  txtPrize.text = ""
END

PRIVATE SUB clear() 
  txtPin.text = "" 
  txtDescription.text = "" 
  txtQuantity.text = "" 
  txtPrize.text = ""
END


PUBLIC SUB _new() 
  ME.center 
  DBConnect 
 
END
 


PUBLIC SUB DBConnect() 
  MyConn = NEW Connection 
  MyConn.Close 
  MyConn.Type = "mysql" 
  MyConn.Host = "localhost" 
  MyConn.Login = "root" 
  MyConn.Password = "1111" 
  MyConn.Name = "SuperMarketInventory" 
  MyConn.Open 
CATCH 
  Message.error(Error.text) 
END


PUBLIC SUB _free() 
MyConn.Close 
Message.Info("Close. ") 
 END 
#  'End of program 

PUBLIC SUB btnClose_Click()

   FMain.Close

END


PUBLIC SUB btnAdd_Click()

     DIM MyRS AS String 
     DIM sql AS String
     MyRS = "insert into Super (Pin, Description, Quantity, Prize )values( '" & 
            txtPin.text & "','" & txtDescription.text & "','" & txtQuantity.text & "','" &
            txtPrize.text & "')" 
     MyRS = MyConn.Exec(sql) 
    
     sql = "update Super set " 
     sql = sql & "Pin = '" & 
     txtPin.text 
     sql = sql & "',Description = '" & 
     txtDescription.text 
     sql = sql & "',Quantity = '" & 
     txtQuantity.text 
     sql = sql & "',Prize = '" & txtPrize.text & "' where Pin = " & vCari 
     MyRS = MyConn.Exec(sql) 

CATCH 
Message.Error(Error.Text)

END




PUBLIC SUB btnCreateTable_Click()
  DIM hTable AS Table
  Myconn.name = "SuperMarketInventory"
  hTable = MyConn.Tables.Add("SuperMarket")
  hTable.Fields.Add("Pin", db.Integer)
  hTable.Fields.Add("Description", db.String)
  hTable.Fields.Add("Quantity", db.Integer)
  hTable.Fields.Add("Prize", db.Integer)
  hTable.Update
 

END