ODBC Connection to FileMaker Server 13

Started by PaulSamuelson, May 28, 2014, 10:35:14 AM

Previous topic - Next topic

PaulSamuelson

I am trying to connect to a FileMaker Pro Server 13 via ODBC. I am able to read data with the following code:

DBServer="192.168.1.129"
Database="dbname"
User="ODBC"
Pass="password"

cConn=ObjectCreate("ADODB.Connection")
RS1 = ObjectCreate('ADODB.Recordset')
RS1.CursorLocation=3;adUseClient

cConn.ConnectionString='DRIVER={FileMaker ODBC};SERVER=':DBServer:';DATABASE=':Database:';UID=':User:';PWD=':Pass:';'
cConn.Open()

RS1.Open("SELECT * FROM Studios WHERE Code='Test'",cConn,3,3)
StudioName=RS1.Collect("StudioName")
message("Studio",StudioName)


However, if I try to update data I get Error 1261, Multi-Stop operation generated errors...

RS1.Collect("StudioName")="12345"


I am able to use SQL to make changes, but would prefer to use ADO if possible.

RS1.Open("UPDATE Studios SET StudioName='12345' WHERE Code='Test'",cConn,3,3)

Any thoughts?


Thanks,

Paul Samuelson

stanl

Maybe

RS1.Open("UPDATE Studios SET StudioName='12345' WHERE Code='Test'",cConn,3,3,1) ;

[NOTE: I generally use cConn as my Connection String and oConn as my ADODB.Connection]


Multi-step errors the are worse to debug as they can refer to any number of issues. You might consider going directly from the connection (assuming it is opened as updateable):

cSQL="UPDATE Studios SET StudioName='12345' WHERE Code='Test';"
cConn.Execute(cSQL)




Deana

I am not familiar with the ADO Collect method. I know you can set values for Fields using something like this:

Code (winbatch) Select

objStudioName= RS.Fields("StudioName")
objStudioName.Value ="12345"


Reference:
http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+Tutorials/OLE~TUTORIAL+ADO.txt
Deana F.
Technical Support
Wilson WindowWare Inc.

PaulSamuelson

Quote from: Deana on May 29, 2014, 07:40:41 AM
I am not familiar with the ADO Collect method. I know you can set values for Fields using something like this:

You should be. It (normally) works well to read or write data in one step.

Code (winbatch) Select

objStudioName= RS.Fields("StudioName")
objStudioName.Value ="12345"


I tried that, too. The first line works, but the .value gives a 1261 error.


Thanks,

Paul

stanl

collect() is a 'hidden' ado function, not publicised that much but doesn't require the overhead of loading the fields object.

Using either connection.execute or collect(), the former requiring less code - both are ADO. You might need to look into setting the cursor location for the connection object. Also, in your SQL statement you used single quotes, while with collect() you used double. I know from experience those little things can cause multi-step (for example SQL server likes ', access likes "). Maybe setting a server cursor location rather than client for something like UPDATE. Maybe try a dynamic cursor and optimistic locking with collect().  I'm sure it is something little, perhaps peculiar to FileMakers ODBC.