ASP Web Applications accessing MAS 90
Sage MAS 90 ERP and Sage MAS 200 ERP have an e-Business application for users who wish to have an internet store for customers (B2B) and consumers (B2C). These are great, out of the box, applications if you want to keep your site simple and to the point. Most everything is configured through the accounting interface. HTML can be added in the descriptions to give the site a custom look without having to have a programmer develop your shopping cart. During the check out process it will charge the credit card with Sage payment services or PcCharge and all the transactions flow through the accounting system No import or export. It is really a great application but like I stated before it is a very basic, out of the box, solution. It lacks in Search Engine Optimization (SEO), Suggestive Selling, Social Media Links, Tell a Friend/E-Mail info, PDF of page, Human Readable URLs, and many other features and functions that most of today’s commerce websites have.
Due to this limitation of the basic out of the box solution, customers have:
- Modified the built in e-business templates with HTML code to create some of these functions.
- Export to SQL or Access and develop a website accessing the data from those applications and importing into sales orders.
- Customize the ProvideX code that the application is written in.
- Wrap e-business within another site.
- Create a site using ASP to read ODBC direct from the application. Then importing or filling in the shopping cart for the user to check out using built in function.
There might be combinations of the above list or others that I have not listed. MBA was working with a client trying to create the ASP read from ODBC and we realized that there were a few people out there trying to figure out how to do this. This blog will document a few key elements needed to read from the ODBC driver using ASP. This is for version 4.x or greater. Hopefully this will help others trying to do the same thing. This is not meant to be the only way to do it nor the correct way to do it. It is just one way that we did it and it worked.
One of the first questions that is asked is what is the string used to connect to the DSN. To be able to connect using ODBC, client workstation needs to be setup on the server. This will install the driver needed for the ODBC. It will also make it easier to troubleshoot connecting to the data. Once it is installed connect to MAS 90 and log in. Confirm that you can see the company data that you wish to connect to for the ASP site. While that is open go to ODBC Data Source Administrator. It would be best to create a new System DSN since the default creates a User DSN.
Click on Add
Select the MAS 90 4.0 ODBC Driver (if you see the MAS 90 32-Bit ODBC Driver that is for older versions of MAS 90 and is not what we want to use).
Click on Finish.
This will bring up the driver setup. The settings should be set just like the SOTAMAS90 settings under the user DSN. Enter the Data Source Name that you want to use and the description. Use the Database Directory and options information from the other DSN. This new DSN will have some extra info that the sotamas90 will not have.
It will have under logon
- Company code from MAS 90.
- User.
- Password.
Leave Session ID blank. If you are running Sage MAS 200 and the server is not local to the application server, you might use the server tab to enter the IP and port for the ODBC c/s server. This must first be setup on the application server so don’t set this tab unless you know you are using it and that it works.
Once all the information is filled out go to the debug tab. Click on the test connection button and see if the connection succeeded. If you put in the correct logon information you should not be prompted to log in it a company. If you are then check your logon info. You should get a connection success response with how many tables that the data source includes. At this point you can click on the connection string button to get the string that will be used in your ASP page. In my example I called the DSN sotamas90 but you would probably want to call is something different so you don’t get it confused with the user DSN. My connection string looks like this:
DSN=SOTAMAS90; UID=mba; PWD=""; Directory=S:\Sage\Mas90w.430\MAS90;
Prefix=S:\Sage\Mas90w.430\MAS90\SY\, S:\Sage\Mas90w.430\MAS90\==\;
ViewDLL=S:\Sage\Mas90w.430\MAS90\HOME; Company=ABC;
LogFile=\PVXODBC.LOG; CacheSize=4; DirtyReads=1; BurstMode=1;
StripTrailingSpaces=1; SERVER=NotTheServer
The next hard part is to get it to run with the correct permissions so you don’t get the Table not accessible error. One way to get around permissions issue is to add impersonation in the web.config file. In the web.config file you will want to put:
<identity impersonate="true" userName="[domain\username]" password="[password]">
This should be the domain that MAS 90 is installed on and the username who would have access to that data and the password is their password. This should get you past most of the errors that I have seen on the web about connecting to MAS 90 using ASP. You might want to make sure that MDAC is current. Older versions might have some issues. In our case we updated to the current MDAC 2.8 SP1.
If you don’t know the file layouts you can access the documentation by going into MAS 90 and click on resources under the tasks menu tab. Then click on File Layouts and Program Information. Click on File layouts under the contents tab; choose the Module that you would like to see the info for. All the tables will be listed with all the fields under that table.
