Navision Attain Database access via C/ODBC in ASP.NET Application

Navision Software was purchased by Microsoft and now it is supported by Microsoft Business Solutions together with Microsoft Great Plains, Axapta, Solomon, Microsoft Retail Management System and Microsoft CRM. Navision has extremely strong position on mid-size European and US markets, plus it has excellent manufacturing solution. Our goal is to help IT departments to support and tune Navision with in-house expertise and skills. The topic of this article is Navision database access from ASP.NET application via C/ODBC interface. Our goal will be ASPX page accessing Navision Customers.

Let's begin

1. In our case we will use Navision Attain 3.6 with Navision Database Server, Navision Application Server and Navision Client. These components are installed on Windows XP. You also need to install C/ODBC component form Navision Attain CD.

2. Let's create ODBC DSN for Navision data access. Select Control Panel -> Administrative Tools -> Data Sources (ODBC). Then select System DSN tab and press Add button. We'll use C/ODBC 32-bit data access driver. We'll name Data Source Name Navision, Connection leave Local. As the database (Database button) select Program FilesNavision AttainClientdatabase.fdb (demo database). Then click Company button ? we'll use CRONUS demo company. It is important for C/SIDE correct database access to setup proper options for C/ODBC connection. Press Options button and look at the options available ? we'll need Identifiers parameter ? it defines identifiers types, which will be transferred to the client application. In order to work correct with MS SQL Server 2000 with C/ODBC source we need to use these type: "a-z,A-Z,0-9,_". Now DNS is done. Let's create Linked Server.

3. Open MS SQL Server Enterprise Manager. Open server tree for the server, which you plan to use, for this server open Security folder and Lined Servers. With right click select New Linked Server in context menu. In the dialog box opened in the Provider Name select Microsoft OLE DB Provider for ODBC Drivers. Let's name our Linked Server NAVISION. In Data Source string enter ODBC DSN name - NAVISION in our case. Linked Server is ready! Let's select tables list and look at the data from Navision Attain database.

4. Next we need to create small stored procedure for sales data selection. Here is the text of the procedure: SET ANSI_NULLS ON SET ANSI_WARNINGS ON GO

CREATE PROCEDURE NavisionCustomers AS

DBCC TRACEON(8765) SELECT No_, Name, Address, City, Contact FROM OPENQUERY(NAVISION, 'SELECT * FROM Customer')

RETURN

Let's clarify some points here. TRACEON(8765) directive allows us to work with the data of variable length, returned by C/ODBC driver. Without it we can not select Navision tables fields ? we will have these errors:

OLE DB error trace [Non-interface error: Unexpected data length returned for the column: ProviderName='MSDASQL', TableName='[MSDASQL]', ColumnName='Ship_to_Filter', ExpectedLength='250', ReturnedLength='1']. Server: Msg 7347, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' returned an unexpected data length for the fixed-length column '[MSDASQL].Ship_to_Filter'. The expected data length is 250, while the returned data length is 1.

OPENQUERY command opens linked server and gives it execution request, and returns record set selected. Directives ANSI_NULLS and ANSI_WARNINGS are required ? they provide the possibility of the execution for heterogeneous requests. To test the procedure you can give its name in MS SQL Query Analyzer ? EXEC NavisionCustomers

5. Now we need to create ASP.NET application. Let's use free RAD environment ASP.NET WebMatrix. You can get infor and download it at http://asp.net/webmatrix . You need .NET SDK 1.1 installed, before WebMatrix installation.

6. Launch WebMatrix, select creation of the Data Pages -> Editable Data Grid in the wizard screen. Let's keep all the defaults suggested. On the access page created we'll change the header to Navision Attain Customers, select Verdana font and the font size desired. Next place on the page the component SqlDataSourceControl and tune it's ? Connection String : server='(local)'; database='Alba';trusted_connection=true (change server and database name to your actual names), and SelectCommand ? as EXEC NavisionCustomers

7. For our Data Grid, which will show customers, define DataSource as SqlDataSourceControl1, and DatKeyField ? as No_

8. Next switch to code edition mode and make these changes:

? In the DataGrid_Delete method let's correct the request for the customer deletion to the one we need

? Change DataGrid_Delete code, base on the fields we plan to use

9. Launch our application and test it. This is it!

Happy customizing, implementing and modifying! If you want us to do the job - give us a call 1-866-528-0577 or 1-630-961-5918! help@albaspectrum.com

Boris Makushkin is Lead Software Developer in Alba Spectrum Technologies ? USA nationwide Microsoft CRM, Microsoft Great Plains customization company, serving Chicago, Boston, San Francisco, San Diego, Los Angeles, Houston, Dallas, Atlanta, Miami, Montreal, Toronto, Vancouver, Moscow, Europe and internationally ( http://www.albaspectrum.com ), he is Microsoft CRM SDK, Navision, C#, VB.Net, SQL, Oracle, Unix developer.

---

Microsoft Great Plains Partner Selection: Overview

Microsoft Great Plains, Navision, Solomon and Axapta are Microsoft Business... Read More

Constructionalist Parsing - Deciphering Natural Language

The research in the field of Natural Language Processing usually... Read More

Cisco Certification: Five Things To Do DURING Your CCNA Exam

There are plenty of articles out there about how to... Read More

Reporting Tools for Microsoft Great Plains ? Overview For Developers

Looks like Microsoft Great Plains becomes more and more popular,... Read More

Razzle Dazzle Them

Once upon a time not so long ago, there was... Read More

Microsoft Great Plains Customization Tools Evolution ? Overview for Consultant

When Great Plains Software introduced the first graphical accounting application... Read More

SSH (SCP) Send Files from Windows to Your Linux Box

Not all of us have the luxury of working both... Read More

Microsoft Great Plains: Data Conversion & Migration Scenarios ? Overview for Consultant

Microsoft Business Solutions Great Plains serves multiple industries in the... Read More

Save Your Resources - Combine Your IM Clients

There are so many different programs that clutter up your... Read More

eStore Advantage ? Extending Microsoft eConnect for MBS Great Plains

eStore Advantage allows front-office applications to communicate with back-office business... Read More

A Simple Computer Software Definition

What is Software?Software is a set of instruction written to... Read More

Groove Network. Are you in it?

If you are in a business that passes documents around... Read More

Rapid Application Development - Is it Really a Need of Today?

Rapid Application Development (RAD) is a software development methodology. In... Read More

Groupware and Version History: Collaboration Series #1

This article is the first of a series of articles... Read More

Industry Experts Answer the One Million-dollar Question - Why Genie Backup Manager?

Many reasons made GBM a unanimous choice for experts, one... Read More

Microsoft CRM USA Nationwide Remote Support

Remember old good days when your company probably had Great... Read More

Microsoft Great Plains Nationwide Remote Support

ERP Consulting industry is on the way to serve clients... Read More

Computer Based Language Development and Spell-checking

Language development computer: Computer-based method for aiding language development seems... Read More

Free Microsoft Word Online Training Tutorial Resources

Microsoft Word is one of the most popular office applications... Read More

10 Ways to Learn a Software

Following tips help you to learn a software in lesser... Read More

Most Dangerous Types of Spyware are on the Rise: How to Choose the Weapon

Bad News - the Threat is Bigger than it SeemedHow... Read More

Professional XP Style Icons in Software and Website Development

Using professional icons in your application or website can bring... Read More

Great Plains DOS Support ? Notes for Consultant

Great Plains Accounting, accounting package for mid-size and small companies... Read More

Ukraine: a Prospective Player on the World Software Outsourcing Market

The destruction of the Soviet Union about 15 years ago,... Read More

Microsoft Great Plains SOP: Sales Order Processing

Microsoft Business Solutions Great Plains is marketed for mid-size companies... Read More

Great Plains Customization Upgrade? Overview For CIO/IT Director

Around the same time Microsoft made its move with .Net... Read More

Great Plains Dexterity Customization Options ? Overview For Developers

Looks like Microsoft Great Plains becomes more and more popular,... Read More

20 Extra Hours Per Week: What Would You Do?

While I was in college, if you would have asked... Read More

Microsoft CRM Integration with Microsoft Retail Management System (RMS) ? Overview

Microsoft Client Relation Management system (Microsoft CRM) and Microsoft RMS... Read More

Device Driver Basics

Most people understand that the "hardware" part of their computer... Read More

Microsoft Business Solutions VAR/Partner Selection ? Overview for IT Director/Manager/Controller

Microsoft Great Plains and Microsoft CRM become more and more... Read More

Popular Fleet Maintenance Programs

Some companies that are in need of fleet management may... Read More

Free Program Removes Spyware not Detected by Premium Security Scan

What is Spyware?Spyware monitors your surfing habits and sends the... Read More