ADO Error When Trying To Read Navision Data Via C/ODBC

I’m having a major problem reading from our navision databse via the c/odbc driver navision has provided us with. When i connect via an application such as VisData etc there are no problems, but when i attempt to connect and lookup the data in an ASP environment it errors out. Below is the code and the error, any help would be appreciated. Set dbNavConnection=Server.CreateObject(“ADODB.Connection”) dbNavConnection.Properties(“Prompt”) = 4 dbnavConnection.Open “DSN=navision;” SQLQuery = “Select [dept. code/p&l center],[gen. bus. posting group] from Job where([no.]=’” & tempjob & “’);” set RSNavision = dbnavisionConnection.Execute(sqlquery) tempjob is set to a valid job # for lookup, the error comes on the last line where it attempts to execute the query. I’ve also rewritten it to specifically create a recordset object and open it with the query and it doesn’t work either. The query is the exact same one that works in visdata. The databse connection and ways i’m running the querys works for all other databases we have, just not the navision one. i’m stumpped. Microsoft OLE DB Provider for ODBC Drivers error ‘80040e21’ The request properties can not be supported by this ODBC Driver.

Check out ‘Accessing Navision from Active Server Page using O’. It looks like a similar problem. Regards, Mark

Unfort, there is no way i’m aware of to use DAO in ASPs. Microsoft dicontinued making/supporting DAO a couple years ago and switched to RDO, then finally to ADO…

try changing the following line dbnavConnection.Open “DSN=navision;” to dbnavConnection.Open “DSN=navision;uid=sa;pwd=” If this works you have a problem with sql security. Check out SQL Manual under trusted connections. Hope this helps Best regards, Mario van Zeist

You can use ADO without problems from ASP. Just make sure you use the newest ado version (currently 2.5, but 2.1 works fine also). Create a SYSTEM DSN using C/ODBC to connect to a Navision Server, give it a name (f.i. Navision). The configuration of your DSN options is essential: you must select idenitifiers without dots (dots will kill ado). This example opens a recordset with one record for a customer with the specified customer number: Set oCon = server.CreateObject(“ADODB.Connection”) oCon.Open “DSN=Navision” strSQL = “SELECT * FROM Klant WHERE Nr_ = '” & strCust & “’” Set rsUser = Server.CreateObject(“ADODB.RecordSet”) rsUser.Open strSQL, oCon

as a followup… the reason i was experiencing problems was the navision database driver doesn’t support the Standard SQL Query notations such as [ and ] for around fieldnames that have spaces in them, and everything in it is case sensitive. I had to use exact form of fieldnames(upper and lowercase), replace ['s with "'s add a space between WHERE(, remove the “;” at the end and replace it with a null charactor… etc… It was just very anal about everything that should be interpreted just fine. IT however didn’t solve my problem with “Catastrophic Failures” with the driver after a few uses. Even after ODBC Traces and receiving no errors form my queries anymore, i was still getting it. I’m currently working with ms with the problem. They are seeing if they can make a work around for it seeing how the navision odbc driver works fine in DAO but not ADO. Edited by - davesherman on 5/19/00 9:27:53 PM