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.

---

What is Preventive Maintenance Software?

Preventive Maintenance (PM) is defined as scheduled work done on... Read More

Software Engineering: An Introduction

Software Engineering is the Systematic Approach for analysis design implementation... Read More

Do You Know These Facts About Spyware ?

Imagine something that follows you home and sets itself up... Read More

Making The Decision To Automate Your Software Testing

Not every software testing project can or should be automated.... Read More

Fundraising Software ? How Can That Help Me?

Fundraising software lets you connect with donors in a way... Read More

The XP Firewall Isnt Enough

You might think you don't need a firewall... Read More

Reduce Pop-ups and Annoying Ads

There is many things more frustrating than surfing a website... Read More

Software Piracy - Global Increase

Pirated software is on the increase and now accounts for... Read More

Microsoft Great Plains in Metal Distribution: Implementation & Customization ? Consultant Overview

Microsoft Business Solutions Great Plains serves to the wide spectrum... Read More

Downloading Spyware Removers: Think Before, not After

Just imagine: you are walking, say, towards your car, and... Read More

Algebra Help Software

Need help making sense of algebra? Have algebra lectures in... Read More

Fleet Maintenance Software Reviews

Innovative Maintenance Systems (IMS) is one company that offers solutions... Read More

MySQL for Beginners ? How to Create a MySQL Database

Whether you are an experienced web programmer or a complete... Read More

Microsoft eCommerce Web-development: Great Plains eConnect .Net ? Highlights for Programmer

In our small article we'll consider Microsoft Business Solutions Great... Read More

Microsoft CRM Implementation & Remote Support

We would like to give you pluses and minuses of... Read More

Microsoft Great Plains: Large Scale Implementation

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

Groove Network. Are you in it?

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

OEComplete ? A Personal Information Manager

OEComplete is a utility for managing the personal information of... Read More

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

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

netstat - Linux Command to Display Networking Information

In linux, one of great commands for finding out information... Read More

Microsoft Great Plains Integrations - Tips for Developer

In this short FAQ style article we would like to... Read More

Microsoft Great Plains implementation: Restaurants Supply Chain Management Example

Microsoft Great Plains serves majority of US based horizontal and... Read More

Microsoft CRM for Corporate Business ? Working Offline

If your company has regional and worldwide operations, you might... Read More

Importance of Coding Standards

Programming Help for BeginnersWe write programs to instruct computers. When... Read More

ERP System of the Future: Database, Business Logic and Interface

We will base our prognosis on our Microsoft Business Solutions... Read More

Programming Environments And The Software Production Process

Introduction: The creating of a computer program involves a number... Read More

Outlook... Not Just for Email! Using Your Outlook Calendar

Microsoft Outlook is one of the most widely used software... Read More

Microsoft Great Plains Customization: Project Organization ? International Business Example

Microsoft Business Solutions Great Plains fits to majority of horizontal... Read More

Linux Vs. Windows

This article will not attempt to advocate the use of... Read More

Microsoft CRM: Implementation, Customization, Support ? Consultant Overview

Microsoft Business Solutions CRM is present several years on the... Read More

Benefits of Shareware

Shareware has been fighting the stigma of being misunderstood for... Read More

.Net Charts and Graphs Interact with Businesses and Customers

Bar charts, bar graphs, and any other chart or graph... Read More

Screenplay and Script Writing Software

When it comes to screenplay software each screenwriter needs to... Read More