FiveTech Support Forums

Hi,

I have an unexpected result when I run a Query in SQL with ADO.

When I run oQry = oSQL:execute("SELECT * FROM klanten")
Then I can do
FOR i = 0 to len(oqry:Fields())
?oQry:Fields(i):value
NEXT i

but when I run oQry = oSQL:execute("SELECT NAAM FROM klanten")
Then I get an error because Fields(1) doesn't exist
I thought that Fields(1) now was holding the info of the first field in the query. It seems that that is the first field in tha table.
I can get the information with oQry:Fields("NAAM"):value

I have create a subroutine that give me the result of a query in a database, but it doesn'n work when not all the fields are requested.
How can I know the fieldnames retured by a query? That's why I was using oQry:Fields(i):value instead of the name.
With Qry:Fields(i):name I know the name.

You have to use Recordset object. Please look at the samples.

EMG

Enrico Maria Giordano wrote:You have to use Recordset object. Please look at the samples.

EMG

Yes, but please look at Recordset object too. It's more common to use it instead of Execute() method for row-returning queries.

EMG

Enrico Maria Giordano wrote:Yes, but please look at Recordset object too. It's more common to use it instead of Execute() method for row-returning queries.

EMG

oRs :=TOleAuto():new("ADODB.RecordSet")
oRs:cursortype :=1
oRs:cursorlocation :=3
oRs:locktype :=3
oRs:open('SELECT * FROM planning','Provider=MySQLProv;Data Source=127.0.0.1;Initial Catalog=pla;User Id=myuser;Password=mypassword')

cConnectSring:="Driver={MySQL ODBC 5.1 Driver};Server=127.0.0.1;Port=3306;Database=pla;User=myuser;Password=mypassword;Option=3;"
oSQL:=CreateObject("ADODB.Connection")
oSQL:ConnectionString:=cConnectSring
oSQL:Open()
oQry = oSQL:Execute('SELECT * FROM planning')

Marc Vanzegbroeck wrote:

Code (fw): Select all Collapse

oRs :=TOleAuto():new("ADODB.RecordSet")

oRs = CREATEOBJECT( "ADODB.Recordset" )

And try using the same connection string. Why are you using a different one?

EMG

Enrico Maria Giordano wrote:

Marc Vanzegbroeck wrote:

Code (fw): Select all Collapse

oRs :=TOleAuto():new("ADODB.RecordSet")

Try

Code (fw): Select all Collapse

oRs = CREATEOBJECT( "ADODB.Recordset" )

EMG

Application
===========
   Path and name: c:\FWH\MySQL\ADO\testado.exe (32 bits)
   Size: 1,302,528 bytes
   Time from start: 0 hours 0 mins 1 secs 
   Error occurred at: 10/09/12, 11:52:00
   Error description: Error ADODB.RecordSet/16389  E_FAIL: OPEN
   Args:
     [   1] = C   SELECT * FROM planning
     [   2] = C   Provider=MySQLProv;Data Source=127.0.0.1;Initial Catalog=pla;User Id=myuser;Password=mypassword

Enrico Maria Giordano wrote:And try using the same connection string. Why are you using a different one?

EMG

Rick Lipkin wrote:Marc

Have a look at these connection strings for My Sql .. here is the connection for My Sql OLEDB:

http://connectionstrings.com/mysql#mysq ... -mysqlprov

MySQL OLEDB
Type OLE DB Provider
Usage Provider=MySQLProv
Manufacturer MySQL

Set example values
Standard

Provider=MySQLProv;Data Source=mydb;User Id=myUsername;Password=myPassword;

Code (fw): Select all Collapse

xConnect := "Provider=MySQLProv;Data Source=mydb;User Id=myUsername;Password=myPassword"

cSql := "SELECT * FROM planning"

oRsPlan := TOleAuto():New( "ADODB.Recordset" )
oRsPlan:CursorType     := 1        // opendkeyset
oRsPlan:CursorLocation := 3        // local cache
oRsPlan:LockType       := 3        // lockoportunistic

TRY
   oRsPlan:Open( cSQL,xCONNECT )
CATCH oErr
   MsgInfo( "Error in Opening PLANNING table" )
   RETURN(.F.)
END TRY

xBrowse( oRsPlan )

Also .. make sure you have the correct MySql Oledb provider .. check out this website and scroll down to the bottom for the different type of providers. Also look at the Vb examples .. they have some different connection strings to try as well.

http://cherrycitysoftware.com/ccs/provi ... MySQL.aspx

Rick Lipkin