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