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

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.

housekeepers near Park Ridge ..

In The News:

Costco has expanded into EV infrastructure with new ultra-fast charging stations that can charge most electric vehicles to 80% in 20-60 minutes while shoppers browse the warehouse.

The new Gemini Robotics On-Device AI allows robots to perform complex tasks without internet, offering enhanced privacy, reliability and adaptability for real-world use.

Protect yourself from Amazon phishing scams by spotting red flags like suspicious sender addresses and spelling errors while using Amazon's Message Center to verify communications.

GAC Group's new Govy AirCab flying car combines lightweight design with advanced safety systems and smart cabin technology to revolutionize city travel with an 18.6-mile range.

The Berkeley Humanoid Light (BHL) is a lightweight, open source humanoid robot that anyone can build using 3D-printed parts and off-the-shelf components.

Meta’s new AI chatbot is getting personal, and it might be sharing more than you realize.

A new threat targets both Android and iPhone users: SparkKitty, a powerful mobile malware strain that scans private photos to steal cryptocurrency recovery phrases and other sensitive data.

Thanks to a team at the University of California, Davis, there's a new brain-computer interface (BCI) system that's opening up real-time, natural conversation for people who can't speak.

Privacy risks are hiding in plain sight, as your personal data is likely being collected, tracked, and sold without your knowledge.

VenHub, a fully autonomous, AI-powered smart store just opened at the LAX/Metro Transit Center in Los Angeles.

A woman's Facebook account takeover reveals dangerous social engineering tactics and provides lessons on recovery, avoiding scams and enacting stronger security measures.

Shanghai engineers are using 432 walking robots to relocate a complex, preserving Shikumen architecture while creating space for a modern underground hub and cultural center.

Major healthcare data analytics firm Episource had a cybersecurity incident exposing 5 million patients' medical records and personal information in recent breach.

A new sophisticated PayPal scam sends legitimate-looking emails from official PayPal addresses, using phone numbers instead of links to convince victims to download remote access tools.

Fox News' AI Newsletter brings you the latest on this rapidly evolving technology.

The innovative Kara Pod device extracts moisture from air to create mineral-rich water and brew coffee, featuring UV sterilization and compatibility with Nespresso pods.

Gov. Hochul unveiled New York's nuclear power plant project to power a million homes, which faces permit challenges while promising jobs and a cleaner energy future.

SAFE introduces Aerie, an underground luxury bunker combining high-end living with advanced security, wellness amenities and interactive walls simulating panoramic views.

A Facebook scammer posing as Elon Musk tricked a victim with promises of a Tesla and $250,000, requesting gift cards that become untraceable once the codes are shared.

Experience hands-free golfing with the Robera Neo smart caddie that uses AI to follow you, navigate obstacles and carry your clubs with GPS course mapping.

Cybersecurity alert: 16 billion passwords exposed in massive credential database affecting major platforms. Protect your accounts with password managers

The innovative BeBot robot uses electric power to remove pollution from shorelines, covering 20-30 times more area than manual collection while preserving wildlife.

A new ClickFix campaign targets macOS users with fake Spectrum support sites, tricking victims with CAPTCHA failures to paste commands that install information-stealing malware.

AI-powered autonomous trucks from Plus Automation are being tested across the U.S., Europe and Asia to solve logistics challenges while facing regulatory hurdles and labor concerns.

Your health information might feel private and secure with your doctor, but the reality is far more complicated. Data brokers collect a wide range of sensitive health data, from diagnoses and prescription details to personal identifiers, and sell this data to marketers, insurers, and other third parties.