Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1039
  • Last Modified:

ASP CONNECTION TO SAGE DATABASE WITH PROPRIETARY ODBC DRIVER.

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
FROGON
Asked:
FROGON
1 Solution
 
jitgangulyCommented:
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
 
FROGONAuthor Commented:
Hi,

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

Thanks
0
 
jitgangulyCommented:
Can u pls post me your latest code ??
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
FROGONAuthor Commented:
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
 
jitgangulyCommented:
>>'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
 
FROGONAuthor Commented:
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
 
FROGONAuthor Commented:
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
 
vnvkCommented:
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
 
moduloCommented:
PAQed, with points refunded (125)

modulo
Community Support Moderator
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now