ODBC Connection to FileMaker Server 13

[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 Expand


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


Reference:
http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+Tutorials/OLE~TUTORIAL+ADO.txt

[PaulSamuelson]

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 Expand


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.