Solved

Connect sage using asp

Posted on 2004-09-28
18
446 Views
Last Modified: 2008-01-09
Hi all

I need to connect sage database in the backend with asp. Any help or any links where i can find necessary info


0
Comment
Question by:surya_madhavi
  • 10
  • 8
18 Comments
 
LVL 6

Expert Comment

by:snavebelac
ID: 12174784
It is actually quite simple as Sage provides it's own ODBC driver.  You can use the following connection string in your ASP pages

Con.Open "DSN=SageLine50v8"

Of course you will have to check the exact name of the DSN in your ODBC console.

If you need to easily find a list of tables and fields, create an access database and Click FIle - Get external data - link tables - choose ODBC Database at the bottom and select the sage DSN. Access will then show you all the tables and fields you can read.  Sage data accessed in this way is read only.

C
0
 

Author Comment

by:surya_madhavi
ID: 12177668
Do i need provide Username and password in the connection string
0
 
LVL 6

Expert Comment

by:snavebelac
ID: 12177866
I do not have a sage system I can test this on but this should work...

"DSN=SageLine50v8;Uid=username;Pwd=password;"

C
0
 

Author Comment

by:surya_madhavi
ID: 12178083
The program is as follows

Set rs=server.createobject("adodb.recordset")
Set cn = Server.CreateObject("adodb.Connection")         
cn.ConnectionString = "DSN=SageLine50v7;ID=accounts;PWD=accounts"
On error resume next
cn.Open
rs.ActiveConnection = cn
rs.CursorType = 1
rs.Open "select * from sales_ledger"

Response.Write err.Description

I am getting the following error

The connection cannot be used to perform this operation. It is either closed or invalid in this context.
0
 
LVL 6

Expert Comment

by:snavebelac
ID: 12178130
What line does the error occur on ?

It may your cursortype.  When I was doing this I used the default forward-only cursor with no problems...try this

Set rs=server.createobject("adodb.recordset")
Set cn = Server.CreateObject("adodb.Connection")        
cn.ConnectionString = "DSN=SageLine50v7;ID=accounts;PWD=accounts"
On error resume next
cn.Open
rs.Open "select * from sales_ledger", cn

This is similar the syntax I used.

Let me now if this works...

C
0
 

Author Comment

by:surya_madhavi
ID: 12178219
I tried the code i am getting same error.
0
 
LVL 6

Expert Comment

by:snavebelac
ID: 12178236
What line does the error occur on ?

C
0
 

Author Comment

by:surya_madhavi
ID: 12178267
In the 5th Line i am getting following error

Cannot find all files in data path


In the 6th line the error is the previous error
0
 
LVL 6

Expert Comment

by:snavebelac
ID: 12178350
Are you running this over a network (is the data on a different computer?) ?

I assum line 5 is "cn.Open" ?  Is this correct ?

Keep at it, I am certain we can get it sorted out.

C
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:surya_madhavi
ID: 12178370
Yes, I am working on the client machine connecting to the server with sage. When i tried linking tables using the machine i am working in  access it worked fine.
0
 
LVL 6

Accepted Solution

by:
snavebelac earned 100 total points
ID: 12178663
Are you able to try the code on the server - Where is the webpage being run from - your local version of IIS or the version on the server?  I am not exactly sure how sage links its data.  Is any data stored locally on your machine ?

C
0
 

Author Comment

by:surya_madhavi
ID: 12178678
No i am connecting to sage through network. I will try to run the program on the machine where is sage is installed
0
 
LVL 6

Expert Comment

by:snavebelac
ID: 12178996
You may want to read this  http://www.experts-exchange.com/Databases/Q_21115107.html

It mentions that sage version 7 and below cannot be connected to via ADO.  perhaps this is the problem.  I do not have access to any version of sage to test.

It wuld seem strange to me that youcould not connect to the data via ADO.  

Let me know how you get on with your test

C
0
 

Author Comment

by:surya_madhavi
ID: 12181368
I am trying to create linked server with sql server 2000 and sage and try to access the database.
0
 
LVL 6

Expert Comment

by:snavebelac
ID: 12184990
Are you trying transfer data from sage to SQL ?

Do you hav the DSN correctly set up?

Do you have your network paths properly configured ?

Please explain further what you are trying to do with sage ans SQL Server and I will try and help you.  If you could post your complete code, that would help.

C

0
 

Author Comment

by:surya_madhavi
ID: 12188068
Hi

i tried the code on the server where sage is installed. The code is as follows

Set rs=server.createobject("adodb.recordset")
Set cn = Server.CreateObject("adodb.Connection")        
cn.ConnectionString = "DSN=SageLine50v7;ID=accounts;PWD=accounts"
On error resume next
cn.Open
Response.write err.Description

I am getting the following error

User ID or Password invalid
0
 

Author Comment

by:surya_madhavi
ID: 12188425
Hi

I am working on windows 2000 server. Can you please let me know how can i use rdo object in asp.
0
 

Author Comment

by:surya_madhavi
ID: 12188525
Hi it is working the code i have written is as follows



Dim UID,PWD,DSN
UID="accounts"
PWD="accounts"
DSN="SageLine50v7"

Set rs=server.createobject("adodb.recordset")
Set cn = Server.CreateObject("adodb.Connection")  
On error resume next
Connect = "DSN=" & DSN
cn.Open Connect, UID, PWD      
Set rs=Server.CreateObject ("adodb.RecordSet")
rs.ActiveConnection = cn
rs.CursorType = 1
rs.Open "select * from sales_ledger"

do until rs.EOF
    for each x in rs.Fields
       Response.Write(x.name)
       Response.Write(" = ")
       Response.Write(x.value & "<br />")
    next
    Response.Write("<br />")
    rs.MoveNext
loop

rs.close
cn.close
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now