Retrieve data from Navision 3.70 C-Side with .NET

Johan_van_Bragt

We want to retrieve data from a Navision 3.70 C/Side database using .NET. We managed to create a DSN and retrieve data from the Navision database by using Excel or MSQuery.
When i try to read data from my .NET application using the same DSN i get an error:

ERROR[IM001][Microsoft][ODBC Driver Manager]Driver does not support this function

.NET code (C#)
=============
OdbcConnection conODBC = new OdbcConnection(“DSN=Sample C/ODBC 32 bit”);
conODBC.Open();

DataTable dt=new DataTable();

OdbcDataAdapter da = new OdbcDataAdapter("SELECT * FROM Klant", conODBC);
da.Fill(dt);
============================================================================
The first two lines successfully connect to the Navision database (which is specified in the DSN).
The last two lines should retrieve data from the database but instead they give the error.

Can anyone help?

Henry@columbus

Hello Johan,

C/ODBC does not support .NET datasets. If you want to use a data set, you’ll have to create your own.

Example:

[WebMethod]
public ClientData[] GetClientData()
	{
	OdbcConnection myConnection = new OdbcConnection("dsn=navision;UID=xxx;PWD=xxx;");
	OdbcCommand myCommand = new OdbcCommand("SELECT No_, Name, City FROM Contact",myConnection);
	myConnection.Open();
	OdbcDataReader myReader = myCommand.ExecuteReader();
	ClientData [] Clients = null;
	int i = 0;
	Clients = new ClientData[600];
	while (myReader.Read()) 
	{
		Clients[i].ID = myReader.GetString(0);
		Clients[i].Name = myReader.GetString(1);
		Clients[i].City = myReader.GetString(2);
		i++;
	}
	myConnection.Close();
	return Clients;
	}

Or create a structure.

public struct ClientData
		{
			public String ID;
			public String Name;
			public String City;
		}

[WebMethod]
public ClientData[] GetClientData()
{
	OdbcConnection myConnection = new OdbcConnection("dsn=navision;UID=xxx;PWD=xxx;");
	OdbcCommand myCommand = new OdbcCommand("SELECT No_, Name, City FROM Contact",myConnection);
	myConnection.Open();
	OdbcDataReader myReader = myCommand.ExecuteReader();
	ClientData [] Clients = null;
	int i = 0;
	Clients = new ClientData[600];
	while (myReader.Read()) 
		{
		Clients[i].ID = myReader.GetString(0);
		Clients[i].Name = myReader.GetString(1);
		Clients[i].City = myReader.GetString(2);
		i++;
		}
	myConnection.Close();
	return Clients;
	}

A better solution is to run the Navision native database on a SQL server. But than you’ll have to keep in mind that the data should only be read. If you’ll write in a Navision SQL database with ODBC, the Navision data will get corrupt.

[WebMethod]
public DataSet DataSetTest()
{
	mySelectQuery = "SELECT No_, Name FROM  [CRONUS Nederland BV$Contact]"
	string connectionString = "dsn=navisionSQL;UID=xx;PWD=xx;";
	OdbcConnection conn= new OdbcConnection(connectionString);
	conn.Open();
	OdbcDataAdapter da = new OdbcDataAdapter (mySelectQuery, conn); 
	DataSet ds = new DataSet();     
	da.Fill(ds, "Customer");
	conn.Close();
	return ds;
	}

I hope it helps…
If you’ll find anything else, please let me know.

Henry

Johan_van_Bragt

Thanks, that was the solution. Now it works

oberlach

Does c/front support .NET datasets?

Henry@columbus

Hello Oberlach,

I haven’t tried it yet, but I don’t think so.

maurits

Just for everyone's information, I've had the same problem trying to populate a DataSet using the Fill method of the OdbcDataAdapter object. It turns out that it does work when you try to populate a DataTable instead of a DataSet. Took me a while to figure out so I thought I'd share, even though this thread dates back to the analog modem age.

Rgds/M

EDIT: damn, I just noticed that that was exactly what the original poster was trying to do, but it failed for him. OK, never mind, I'll shut up now...