Solved

Connect sage using asp

Posted on 2004-09-28
18
448 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

863 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

22 Experts available now in Live!

Get 1:1 Help Now