MySQL Essentials
eBook

$8.99

eBookFrenzy.com

The following RDO (Remote Data Objects) example creates a table my_rdo and demonstrates the use of rs.addNew and rs.update.

Dim rs As rdoResultset
  Dim cn As New rdoConnection
  Dim cl As rdoColumn
  Dim SQL As String

  'cn.Connect = "DSN=test;"
  cn.Connect = "DRIVER={MySQL ODBC 3.51 Driver};"_
                      & "SERVER=localhost;"_
                      & " DATABASE=test;"_
                      & "UID=venu;PWD=venu; OPTION=3"

  cn.CursorDriver = rdUseOdbc
  cn.EstablishConnection rdDriverPrompt


  'drop table my_rdo
  SQL = "drop table if exists my_rdo"
  cn.Execute SQL, rdExecDirect

  'create table my_rdo
  SQL = "create table my_rdo(id int, name varchar(20))"
  cn.Execute SQL, rdExecDirect

  'insert - direct
  SQL = "insert into my_rdo values (100,'venu')"
  cn.Execute SQL, rdExecDirect

  SQL = "insert into my_rdo values (200,'MySQL')"
  cn.Execute SQL, rdExecDirect

  'rs insert
  SQL = "select * from my_rdo"
  Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)
  rs.AddNew
  rs!id = 300
  rs!Name = "Insert1"
  rs.Update
  rs.Close

  'rs insert
  SQL = "select * from my_rdo"
  Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)
  rs.AddNew
  rs!id = 400
  rs!Name = "Insert 2"
  rs.Update
  rs.Close

  'rs update
  SQL = "select * from my_rdo"
  Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)
  rs.Edit
  rs!id = 999
  rs!Name = "updated"
  rs.Update
  rs.Close

  'fetch back...
  SQL = "select * from my_rdo"
  Set rs = cn.OpenResultset(SQL, rdOpenStatic, rdConcurRowVer, rdExecDirect)
  Do Until rs.EOF
  For Each cl In rs.rdoColumns
              Debug.Print cl.Value,
    Next
    rs.MoveNext
    Debug.Print
             Loop
  Debug.Print "Row count="; rs.RowCount

  'close
  rs.Close
  cn.Close

End Sub