Solved

ASP CONNECTION TO SAGE DATABASE WITH PROPRIETARY ODBC DRIVER.

Posted on 2003-11-17
9
998 Views
Last Modified: 2012-05-04
Hello,

I have to connect ASP pages to my sage 100 databases (accounting + salaries). To do so I’ve got the proprietary ODBC Driver. (There is no Jet connection for SAGE database and I haven’t got the MSSQL version of SAGE database)

The connection with system DSN on an ACCESS database runs great.

I’ve tried to connect my Sage Database with ASP connection string with different methods:
1-      DSN Less
2-      DSN System
3-      DSN File
4-      UDL
5-      Using access linked database
But each time I’ve got the same error message on the line opening connection

“Microsoft OLE DB Provider for ODBC Drivers error '80004005' “
I’ve contact the Sage hotline. They gave me a connection string to the database, but it is not working.

Here is the connection example given by Sage :

<%@ LANGUAGE="VBSCRIPT" %>
<%
'-- Query
SQL = "Select DO_Piece, Do_Type, DO_Date From F_DOCENTETE Where DO_Type = 5
‘Création de l’objet ADO
Set cnxADO = Server.CreateObject("ADODB.Connection")
 ‘Connexion à la source de données BijouSA
ConnectionString="DSN=BijouSA;CODEPAGE=1252;"
cnxADO.Open Session("ConnectionString"), "", ""
set RSDoc = cnxADO.Execute(SQL)
While not RSDoc.eof%>
<p>---------------------------------------------------</p>
<p>Nom du document : <%= RSDoc (" DO_Piece ")%> </p>
<p>Date : <%= RSDoc (" DO_Date "%> </p>
<p>Montant HT : <%= FntCIAL. DocTotalHT (RSDoc (" DO_Type " ), RSDoc (" DO_Piece ")%>
<% RSDoc.movenext
wend
Set RSDoc =Nothing%>

I’ve the same error message on the cnxADO.open.

I think, reading faq, the error is due to lack of permission for IIS (and IUSR account) to access the ODBC system connection. But, right now, I’ve no idea to resolve this problem

Can anyone help?...
Thanks.
0
Comment
Question by:FROGON
9 Comments
 
LVL 20

Expert Comment

by:jitganguly
ID: 9764243
ConnectionString="DSN=BijouSA;CODEPAGE=1252;"
cnxADO.Open Session("ConnectionString"), "", ""

Above lines, change to

ConnectionString="DSN=BijouSA;CODEPAGE=1252;uid=youruserid;pwd=yourpassword"
cnxADO.Open ConnectionString

In uid put the correct user id and in password put password
0
 

Author Comment

by:FROGON
ID: 9764404
Hi,

I've already made this correction, but doesn't work anymore.
I've got the same error.

Thanks
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 9764519
Can u pls post me your latest code ??
0
 

Author Comment

by:FROGON
ID: 9764727
Hi

Here is the code i use.


<%@ LANGUAGE="VBScript"%>

<html>
<head>
<title> Essai de requête sur le driver ODBC</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body bgcolor="#FFFFFF" text="#000000">
<%

set cnxADO=Server.CreateObject ("ADODB.connection")

'ComptaSTX is a System DSN
ConnectionString ="DSN=ComptaSTX; CODEPAGE=1252;"
cnxADO.open Session ("ConnectionString"),UID=myid, PWD=mypwd

 
  ' the query
query = "SELECT Ecriture_Gene.EC_LETTRAGE, Ecriture_Gene.JO_NUM, Ecriture_Gene.EC_DATE, Ecriture_Gene.EC_PIECE, Ecriture_Gene.CT_NUM, Ecriture_Gene.CG_NUMCONT, Ecriture_Gene.EC_INTITULE, Ecriture_Gene.N°_COMMANDE, Ecriture_Gene.N°_SESSION, Ecriture_Gene.EC_LETTRE, IIf(Ecriture_Gene!EC_SENS=0,Ecriture_Gene!EC_MONTANT,'0') AS Montant_Debit, IIf(Ecriture_Gene!EC_SENS=1,Ecriture_Gene!EC_MONTANT,'0') AS Montant_Credit FROM F_ECRITUREC WHERE (((Ecriture_Gene.EC_DATE)>#12/31/2002#) AND ((Ecriture_Gene.CT_NUM)='401AAA00') AND ((Ecriture_Gene.EC_LETTRE)=1)) ORDER BY Ecriture_Gene.EC_LETTRAGE, Ecriture_Gene.EC_DATE, Ecriture_Gene.EC_PIECE"
 
set rs=cnxADO.Execute(query)

'listing results

            
if rs.eof then
response.write "pas de sessions"
response.end
end if

%>


<table width="98%" border="1">
  <tr>
    <td>
      <div align="center">Lettrage</div>
    </td>
    <td>  
      <div align="center">Journal</div>
    </td>
    <td>  
      <div align="center">Date</div>
    </td>
    <td>  
      <div align="center">Piece</div>
    </td>
    <td>  
      <div align="center">Tiers</div>
    </td>
    <td>  
      <div align="center">Contre-Partie</div>
    </td>
    <td>  
      <div align="center">Libelle</div>
    </td>
    <td>  
      <div align="center">N° Commande</div>
    </td>
    <td>  
      <div align="center">N° Session</div>
    </td>
    <td>  
      <div align="center">Débit</div>
    </td>
         <td>  
      <div align="center">Crédit</div>
    </td>
  </tr>
    <%
      do while not rs.eof
      %>      

  <tr>
     <td>
           <div align="center"><% response.write rs.fields ("Ecriture_Gene.EC_LETTRAGE")%></div>
     </td>
     <td>
           <div align="center"><% response.write rs.fields ("Ecriture_Gene.JO_NUM")%></div>
     </td>
     <td>
           <div align="center"><% response.write rs.fields ("Ecriture_Gene.EC_DATE")%></div>
     </td>
     <td>
           <div align="center"><% response.write rs.fields ("Ecriture_Gene.EC_PIECE")%></div>
     </td>
     <td>
           <div align="center"><% response.write rs.fields ("Ecriture_Gene.CT_NUM")%></div>
     </td>
     <td>
           <div align="center"><% response.write rs.fields ("Ecriture_Gene.CG_NUMCONT")%></div>
     </td>
     <td>
           <div align="center"><% response.write rs.fields ("Ecriture_Gene.EC_INTITULE")%></div>
     </td>
     <td>
           <div align="center"><% response.write rs.fields ("Ecriture_Gene.N°_COMMANDE")%></div>
     </td>
     <td>
           <div align="center"><% response.write rs.fields ("Ecriture_Gene.N°_SESSION")%></div>
     </td>
     <td>
           <div align="center"><% response.write rs.fields ("Montant_Debit")%></div>
     </td>
     <td>
           <div align="center"><% response.write rs.fields ("Montant_Credit")%></div>
     </td>
 </tr>
      <%
      rs.movenext
      loop
      %>
</table>
<%
  ' close connection
  rs.close
  set rs=nothing

%>


All error point to the line cnsADO.open
the error message send informations :
[Microsoft][Gestionnaire de pilotes ODBC] Source de données introuvable et nom de pilote non spécifié (as ODBC gest. panel : can't find data source and pilot name not specified)

/ComptaStx/querycompta.asp, line 20

Thanks
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.

 
LVL 20

Expert Comment

by:jitganguly
ID: 9764758
>>'ComptaSTX is a System DSN
ConnectionString ="DSN=ComptaSTX; CODEPAGE=1252;"
cnxADO.open Session ("ConnectionString"),UID=myid, PWD=mypwd

I didn't tell you that. Please see my first posting. Let me rewrite onc eagain

'ComptaSTX is a System DSN
cnxADO.open "DSN=ComptaSTX; CODEPAGE=1252;uid=myid;pwd=mypwd"

Is myid and mypwd your user id and password ? If not then repalce it accordingly

0
 

Author Comment

by:FROGON
ID: 9769475
I've rewrite my code as you tell me :

<%@ LANGUAGE="VBScript"%>

<html>
<head>
<title> Essai de requête sur le driver ODBC</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body bgcolor="#FFFFFF" text="#000000">
<%

set cnxADO=Server.CreateObject ("ADODB.connection")
'set rs=Server.CreateObject("adodb.Recordset")



cnxADO.open "DSN=ComptaSTX; CODEPAGE=1252;uid=Jean;pwd=mypwd"

 
  ' selection des écritures lettrées pour le 401AAA00 avec tri
query = "SELECT Ecriture_Gene.EC_LETTRAGE, Ecriture_Gene.JO_NUM, Ecriture_Gene.EC_DATE, Ecriture_Gene.EC_PIECE, Ecriture_Gene.CT_NUM, Ecriture_Gene.CG_NUMCONT, Ecriture_Gene.EC_INTITULE, Ecriture_Gene.N°_COMMANDE, Ecriture_Gene.N°_SESSION, Ecriture_Gene.EC_LETTRE, IIf(Ecriture_Gene!EC_SENS=0,Ecriture_Gene!EC_MONTANT,'0') AS Montant_Debit, IIf(Ecriture_Gene!EC_SENS=1,Ecriture_Gene!EC_MONTANT,'0') AS Montant_Credit FROM F_ECRITUREC WHERE (((Ecriture_Gene.EC_DATE)>#12/31/2002#) AND ((Ecriture_Gene.CT_NUM)='401AAA00') AND ((Ecriture_Gene.EC_LETTRE)=1)) ORDER BY Ecriture_Gene.EC_LETTRAGE, Ecriture_Gene.EC_DATE, Ecriture_Gene.EC_PIECE"
 
set rs=cnxADO.Execute(query)

            
if rs.eof then
response.write "pas de sessions"
response.end
end if

%>


<table width="98%" border="1">
  <tr>
    <td>
      <div align="center">Lettrage</div>
    </td>
    <td>  
      <div align="center">Journal</div>
    </td>
    <td>  
      <div align="center">Date</div>
    </td>
    <td>  
      <div align="center">Piece</div>
    </td>
    <td>  
      <div align="center">Tiers</div>
    </td>
    <td>  
      <div align="center">Contre-Partie</div>
    </td>
    <td>  
      <div align="center">Libelle</div>
    </td>
    <td>  
      <div align="center">N° Commande</div>
    </td>
    <td>  
      <div align="center">N° Session</div>
    </td>
    <td>  
      <div align="center">Débit</div>
    </td>
         <td>  
      <div align="center">Crédit</div>
    </td>
  </tr>
    <%
      do while not rs.eof
      %>      

  <tr>
     <td>
           <div align="center"><% response.write rs.fields ("Ecriture_Gene.EC_LETTRAGE")%></div>
     </td>
     <td>
           <div align="center"><% response.write rs.fields ("Ecriture_Gene.JO_NUM")%></div>
     </td>
     <td>
           <div align="center"><% response.write rs.fields ("Ecriture_Gene.EC_DATE")%></div>
     </td>
     <td>
           <div align="center"><% response.write rs.fields ("Ecriture_Gene.EC_PIECE")%></div>
     </td>
     <td>
           <div align="center"><% response.write rs.fields ("Ecriture_Gene.CT_NUM")%></div>
     </td>
     <td>
           <div align="center"><% response.write rs.fields ("Ecriture_Gene.CG_NUMCONT")%></div>
     </td>
     <td>
           <div align="center"><% response.write rs.fields ("Ecriture_Gene.EC_INTITULE")%></div>
     </td>
     <td>
           <div align="center"><% response.write rs.fields ("Ecriture_Gene.N°_COMMANDE")%></div>
     </td>
     <td>
           <div align="center"><% response.write rs.fields ("Ecriture_Gene.N°_SESSION")%></div>
     </td>
     <td>
           <div align="center"><% response.write rs.fields ("Montant_Debit")%></div>
     </td>
     <td>
           <div align="center"><% response.write rs.fields ("Montant_Credit")%></div>
     </td>
 </tr>
      <%
      rs.movenext
      loop
      %>
</table>
<%
  ' Fermer la connexion et détruire l'objet
  rs.close
  set rs=nothing

%>

the error is still on the line : cnxADO.open "DSN=ComptaSTX; CODEPAGE=1252;uid=Jean;pwd=mypwd"
but the error has change :

Erreur de compilation Microsoft VBScript error '800a0401'

Fin d'instruction attendue (instruction end waited)

/iisHelp/common/500-100.asp, line 11

Dim objASPError, blnErrorWritten, strServername, strServerIP, strRemoteIP Dim strMethod, lngPos, datNow, strQueryString, strURL
--------------------------------------------------------------------------^
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Simba][Simba ODBC Driver][CBase]Erreur inconnue ! (unknown error)

/ComptaStx/querycompta.asp, line 20


Thanks
0
 

Author Comment

by:FROGON
ID: 9769626
I've jsut made correction on the page 500-100.asp of my IIS Server.
But the error on the ODBC DRIVER is still there.
0
 
LVL 7

Expert Comment

by:vnvk
ID: 12474866
This Question is now classified abandoned as no comment has been added to this question in more than 21 days.

I will leave the following recommendation on this question to the moderators in the Cleanup topic area:
    PAQ with points refunded

Any objections should be posted here in the next 4 days. After that time, the question will be closed at the discretion of the moderator.

vnvk
EE Cleanup Volunteer
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12512164
PAQed, with points refunded (125)

modulo
Community Support Moderator
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

Title # Comments Views Activity
ASP SQL Syntax Duplicate Key 7 93
Query not working correctly? (or how i want it to... 6 49
CDO.Message not able to send attachement 5 14
IP API - need data... 4 13
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 would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

920 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

17 Experts available now in Live!

Get 1:1 Help Now