I can not make ADO Recordset return the records of a MySQL table. I am using VB.NET in Visual Studio. I can establish communication with the database, I make the connection, create tables, all via connection object (as below) but at the time of returning the information via Recordset, it gives error.
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim fld As ADODB.Field
Dim aux As String
aux = "Provider = MSDASQL;Driver={MySQL ODBC 5.2 ANSI Driver}; Server=localhost; Database = db-teste; User=root; Password=123456; Option=3;"
conn.Open(aux)
'create table
conn.Execute("DROP TABLE IF EXISTS my_ado")
conn.Execute("CREATE TABLE my_ado(id int Not null primary key, name varchar(20)," &
"txt text, dt date, tm time, ts timestamp)")
'insert
conn.Execute("INSERT INTO my_ado(id,name,txt) values(1,100,'venu')")
conn.Execute("INSERT INTO my_ado(id,name,txt) values(2,200,'MySQL')")
'============================================================================
' ATÉ AQUI VINHA FUNCIONANDO OK!
'============================================================================
' Isso não retorna nada
rs.Open("SELECT * FROM my_ado", conn)
rs.MoveFirst()
' RecordCount retorna sempre "-1" <<<<<<====================
Debug.Print(Str(rs.RecordCount()))
' rs.Fields já vem com erro
For Each fld In rs.Fields
Debug.Print(fld.Name)
Next
What's wrong?