.NET Data Accessing of Attain database

I am attempting to provide a lookup of data from a Navision Attain database from within a Visual Studio .NET (C#) environment test application. So far I have not been successful. I can set up a Connection (OdbcConnection or OleDbConnection), setting its ConnectionString to make use of an ODBC connection through to the database - this data Connection opens successfully when fed with the correct information (database + logon information). However when I come to attempt to run a query to feed either a DataTable or DataReader I get an error : “Microsoft.Data.Odbc.OdbcException: ERROR [IM001][Microsoft][ODBC Driver] Driver does not support this function at Microsoft.Data.Odbc.OdbcConnection.HandleError(intPtr hHandle, SQL_HANDLE hType, RETCODE retcode), … etc.” The syntax that generates this error is as follows (Note : the connection is open - the ConnectionState against it tells me this much) : string myQuery = “SELECT Code FROM Bin”; OdbcDataAdapter adapter = new OdbcDataAdapter(myQuery, odbcConnection1); dt = new DataTable(); dt.Clear(); adapter.Fill(dt); dataGrid1.DataSource = dt; Am I doing something wrong ? Has anyone else attempted this strategy ? I am able to make use of the ODBC connection through Borland Delphi (and Borland Database Engine) to read and write data to / from the Navision Attain database - so I know that it can be done. I was hoping that we might be able to achieve the same through .NET C# code (as this is the way things are headed). Any help or advice would be appreciated. Thanks in advance Steve (steven.keclik@s3t.co.uk)

I dont think you are doing anything wrong. The problem is with the support required of the ODBC driver, by the OLEDB provider (weather from the ADO.NET API’s or from raw OLEDB - doesnt matter). Since C/ODBC is only an ODBC 2.5 compliant driver, many of the functions and attributes (particularly connection attributes of SQLSetConnectAttr()) are not impemented, but they are needed by OLEDB. It can always be done by raw ODBC calls, which for e.g. Access us using and I’m pretty sure Delphi is using. But once OLEDB is in the loop, I dont think it will go.

So does this mean that with the tools at my disposal I remain unable to review data within the Attain database structure ? Can I obtain anything to enable me to achieve something like this ? Thanks in advance. Steve (steven.keclik@s3t.co.uk)

I’ve be able to use the ADO in Delphi 6 to connect the ODBC layer and then to C/ODBC. Check your C/ODBC settings. I think Bin might be a reserved word (short for Binary). My understanding is that C/ODBC was barely level 1 compliant so here are the two settings my application needed to be set at (which reads and writes to Navision): Identifiers: a-z,A-Z,0-9,_ Option field type: Text With out the identifiers setting like that I had all kinds of problems. Here’s my connection string: Provider=MSDASQL;Persist Security Info=False;Data Source=Navision1 Navision1 = my ODBC System DSN pointing to Navision Good luck! Django

Maybe you want to try to use the C/FRONT OCX. The OCX accesses directly the db and you do not have to worry about ODBC compliancy. Cristi

I have no problem in making a connection, retrieving + updating data from Delphi (in this case Delphi 5) using standard components. My issue is with connecting and retrieving data through C#.NET. 1). If I attempt to use the ConnectionString as you mention above (Django) I get the error “System.ArgumentException: The .Net Data OLE DB Provider(System.Data.OleDb) does not support the MSDASQL Provider, Microsoft OLE DB Provider for ODBC Drivers” - this is making use of OleDbConnection, OleDbDataAdapter and OleDbCommand. Am I missing something here ? I’m now trying to run the command “SELECT Name, Address FROM Contact” to avoid potential clashes with keywords (like Bin). 2).Cristian : How do I make use of the C/FRONT OCX ? This is not something I am familiar with - is there an easy way I can crash in on it ? :slight_smile: Steve (steven.keclik@s3t.co.uk)

Please, we would like to know more also, We have been trying to use ASP/IIS to connect, and are struggling with C/ODBC to get it working also… Same problem with ADO vs C/ODBC

I’ve just started using the OCX within my Delphi application and find it to be much faster and more reliable. As for learning I’ve been looking at the Excel spreadsheet example and the help documentation. I don’t know ASP well but you should be able to access the OCX easily within the ASP coding once the OCX is registered and available to the ASP processor (if such a thing exists).

People keep indicating that they have achieved the above (I’ve done as much as this through the old ODBC / BDE route). Has anyone made any headway with accessing the database tables from C# .NET ? (Win Forms) Any further pointers would be appreciated. Thanks so far. Steve

I have accessed database tables from C#, by using the ADODB interop, in stead of using ADO.net. private string GetXml(string strCommand, string strCompanyname) { string xmlSet = “”; ADODB.Connection oConnection = new ADODB.ConnectionClass(); string strConnectionString = GetConnectionstring(strCompanyname); oConnection.Open(strConnectionString, “”, “”, 0); object o = 0; ADODB._Recordset oRecordset; oRecordset = oConnection.Execute(strCommand, out o, 8); ADODB._Stream oStream = new ADODB.StreamClass(); oRecordset.Save(oStream, ADODB.PersistFormatEnum.adPersistXML); xmlSet = oStream.ReadText(-1); //ADODB.StreamReadEnum.adReadAll == -1 xmlSet = CheckXML(xmlSet); oConnection.Close(); return xmlSet; }

I have the same problems with ADO.NET but I think I am one step further. The trick is to use the ODBCDataAdapter instead of the OLEDB version. The C/ODBC driver does not support all OLEDB functionality, therefore it does not work with default ADO.NET dataadapters(sql, oledb). (ODBCDataAdapter can be downloaded at microsoft.com for free!). In the connectionstring you need to replace “data source= navision1” to “DSN=navision1”. I have a connection with the C/ODBC driver but it tells me every time ‘Table does not exist’ when I run “SELECT FirstName FROM Employee”. What is wrong with this SQL. I think this is the last piece of my puzzle, help… Ronald