?
Solved

DSN / ODBC problem .asp page accessing FoxPro database

Posted on 2007-07-27
13
Medium Priority
?
430 Views
Last Modified: 2010-04-18
My website which is running on a Windows Server 2003 R2, is havin trouble loading a particular asp page. This page needs to access a FoxPro database on the local drive. I have loaded the relevant DSN / ODBC drivers (Microsoft Visual Foxpro Driver etc)

Any ideas?
0
Comment
Question by:mrbungle50
  • 4
  • 3
  • 3
  • +1
13 Comments
 
LVL 6

Accepted Solution

by:
Dragonlaird earned 150 total points
ID: 19581240
Can you paste the ASP page content and any error message(s) you get so we have a clue where/what the problem is?

My first reaction would be that the DSN may not have been declared on the server or it may have been created as a User DSN instead of a System DSN...
0
 

Author Comment

by:mrbungle50
ID: 19581345
<!-- #Include file="ADOVBS.INC" -->
<%
'On Error Resume Next

'Moms Required Variables/Data

FirstName      =      Request.Form("FirstName")
LastName      =      Request.Form("LastName")
Email            =      Request.Form("Email")
Phone            =      Request.Form("Phone")
Address       =      Request.Form("Address")
Suburb             =      Request.Form("Suburb")
State            =      Request.Form("State")
PostCode      =      Request.Form("Postcode")
Hotel            =      Request.Form("Hotel")
CruiseDay      =      Request.Form("CruiseDay")
CruiseMonth      =      Request.Form("CruiseMonth")
If CruiseMonth = "January" Then
      CruiseMonth = "1"
End If
If CruiseMonth = "January" Then
      CruiseMonth = "1"
End If
If CruiseMonth = "February" Then
      CruiseMonth = "2"
End If
If CruiseMonth = "March" Then
      CruiseMonth = "3"
End If
If CruiseMonth = "April" Then
      CruiseMonth = "4"
End If
If CruiseMonth = "May" Then
      CruiseMonth = "5"
End If
If CruiseMonth = "June" Then
      CruiseMonth = "6"
End If
If CruiseMonth = "July" Then
      CruiseMonth = "7"
End If
If CruiseMonth = "August" Then
      CruiseMonth = "8"
End If
If CruiseMonth = "September" Then
      CruiseMonth = "9"
End If
If CruiseMonth = "October" Then
      CruiseMonth = "10"
End If
If CruiseMonth = "November" Then
      CruiseMonth = "11"
End If
If CruiseMonth = "December" Then
      CruiseMonth = "12"
End If

CruiseYear      =      Request.Form("CruiseYear")
WhereFrom      =       Request.Form("WhereFrom")
Bus                  =      Request.Form("Bus")
PickUpPoint      =      Request.Form("PickUpPoint")

'Prices
AdultDirect      =      Request.Form("AdultDirect")
AdultEx            =      Request.Form("AdultEx")
StudentDirect =      Request.Form("StudentDirect")
StudentEx       =      Request.Form("StudentEx")
ChildDirect =      Request.Form("ChildDirect")
ChildEx       =      Request.Form("ChildEx")
FamilyDirect =      Request.Form("FamilyDirect")
FamilyEx       =      Request.Form("FamilyEx")


'Number of Pax
PaxAdDir      =      Request.Form("PaxAdDir")
PaxAdEx            =      Request.Form("PaxAdEx")
PaxStuDir      =      Request.Form("PaxStuDir")
PaxStuEx      =      Request.Form("PaxStuEx")
PaxKidDir      =      Request.Form("PaxKidDir")
PaxKidEx      =      Request.Form("PaxKidEx")
PaxFamDir      =      Request.Form("PaxFamDir")
PaxFamEx      =      Request.Form("PaxFamEx")

'For Testing Payment Status = Approved
eWayOption3      =      "True"

'**** Pricing Total Calculations ****
'Passengers
PaxTotal      =      0
PaxTotal      =      CInt(PaxAdDir) + CInt(PaxAdEx) + CInt(PaxStuDir) + CInt(PaxStuEx) + CInt(PaxKidDir) + CInt(PaxKidEx) + CInt(PaxFamDir)*4 + CInt(PaxFamEx)*4

DiscPercent=0
If PaxTotal > 19 Then
      DiscPercent = 10
End If
If PaxTotal > 49 Then
      DiscPercent = 20
End If

DiscText = FormatNumber(DiscPercent,2) & "%"

'Amount
SubTotal      =      CInt(PaxAdDir)*AdultDirect + CInt(PaxAdEx)*AdultEx + CInt(PaxStuDir)*StudentDirect + CInt(PaxStuEx)*StudentEx + CInt(PaxKidDir)*ChildDirect + CInt(PaxKidEx)*ChildEx + CInt(PaxFamDir)*FamilyDirect + CInt(PaxFamEx)*FamilyEx
Discount      = SubTotal * DiscPercent / 100
GrandTotal      = SubTotal - Discount

WebBookSQL=""

If Session("WriteMOMS") <> True Then
      Session("WriteMOMS") = True
      NextInvoice
      NextClient
      WriteMOMS
End If

'eway Required Variable/Form Data

'**** Calculate and Set eWay Variables/Post Items ****
ewayCustomerID                               =      "14153712"
ewayTotalAmount                              =      GrandTotal * 100
ewayCustomerFirstName                  =      FirstName
ewayCustomerLastName                  =      LastName
ewayCustomerEmail                        =      Email
ewayCustomerAddress                        =      Address & " " & Suburb & " " & State & " " & Postcode
ewayCustomerPostcode                  =      Postcode
ewayCustomerInvoiceDescription      =      "Whalewatching Tour"
ewayURL                                          =      "http://whalewatching.net/eWayStatus.asp"
ewaySiteTitle                              =      "Moreton Bay Whalewatching"

ReadMOMS

Sub NextInvoice

      'Get Invoice Number and increment INCVAL database by 1
      
      '**** Get Transaction Number Details
      Dim OBJdbConnection
      Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
      OBJdbConnection.CommandTimeout = 0

      DataConnectionString = ""
      DataConnectionString = "DSN=MBWW Moms;DRIVER={Microsoft Visual FoxPro-Treiber}"

      'Open Connection
      OBJdbConnection.Open DataConnectionString

      'Open Invoice Number RecordSet
      IncValQry="SELECT * FROM incval WHERE varname = 'NEXTDES'"
      Set RsIncVal = OBJdbConnection.Execute(IncValQry)

      'Set Next Invoice Value
      eWayTrxnNumber                  =      RsIncVal.Fields("nval")
      Session("TrxnNumber")      =      RsIncVal.Fields("nval")
      
      'Increment Next Invoice Number by 1
      IncValQry = "UPDATE incval SET nval = nval+1 where varname ='NEXTDES'"
      Set RsIncVal = OBJdbConnection.Execute(IncValQry)

      'Close Connection
      OBJdbConnection.Close
      Set OBJdbConnection = Nothing
      DataConnectionString = ""

End Sub

Sub NextClient

      'Get Client Number and increment INCVAL database by 1

      '**** Get Transaction Number Details
      Dim OBJdbConnection
      Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
      OBJdbConnection.CommandTimeout = 0

      DataConnectionString = ""
      DataConnectionString = "DSN=MBWW Moms;DRIVER={Microsoft Visual FoxPro-Treiber}"

      'Open Connection
      OBJdbConnection.Open DataConnectionString

      'Open Client Number RecordSet
      IncValQry="SELECT * FROM incval WHERE varname = 'NEXTID'"
      Set RsIncVal = OBJdbConnection.Execute(IncValQry)

      'Set Client ID Values
      Session("ClientID")      =      RsIncVal.Fields("nval")
      
      'Increment Next Invoice Number by 1
      IncValQry = "UPDATE incval SET nval = nval + 1 where varname ='NEXTID'"
      Set RsIncVal = OBJdbConnection.Execute(IncValQry)

      'Close Connection
      OBJdbConnection.Close
      Set OBJdbConnection = Nothing
      DataConnectionString = ""

End Sub

Sub ReadMOMS

      Dim OBJdbConnection
      Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
      OBJdbConnection.CommandTimeout = 0

      DataConnectionString = ""
      DataConnectionString = "DSN=MBWW Moms;DRIVER={Microsoft Visual FoxPro-Treiber}"

      'Open Connection
      OBJdbConnection.Open DataConnectionString

      'Read WebBook Database
      WebBookSQL="SELECT * FROM WebBook WHERE TRA_NUM = '" & Session("TrxnNumber") & "'"
      Set RsWebBook = OBJdbConnection.Execute(WebBookSQL)
      TrxnNumber      =      RsWebBook.Fields("TRA_NUM")

      'Close Connection
      OBJdbConnection.Close
      Set OBJdbConnection = Nothing
      DataConnectionString = ""      

End Sub

Sub WriteMOMS

      '**** Get Transaction Number Details
      Dim OBJdbConnection
      Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
      OBJdbConnection.CommandTimeout = 0

      DataConnectionString = ""
      DataConnectionString = "DSN=MBWW Moms;DRIVER={Microsoft Visual FoxPro-Treiber}"

      'Open Connection
      OBJdbConnection.Open DataConnectionString

      'Write to Web Booking Database
      WebBookSQL      =      "INSERT INTO WebBook "
      WebBookSQL      =      WebBookSQL & "(TRA_NUM,TRA_DTE,TRA_TIME,FIRSTNAME,LASTNAME,EMAIL,PHONE,ADDRESS,SUBURB,STATE,POSTCODE,HOTEL,CRUISEDATE, ADCODE, ADULTDIR,CONCDIR,CHILDDIR,FAMILYDIR,ADULTEX,CONCEX,CHILDEX,FAMILYEX,TOTPAX,BUS,PICKUP,ID,TRA_TYPE,TOT_COST,AMT_PAID,PAY_METH,TRA_REM,SUBMITTED,EWAYTRXNO,APPROVED,POSTED) "
      WebBookSQL      =      WebBookSQL & "VALUES ("
      WebBookSQL      =      WebBookSQL & "'" & Session("TrxnNumber") & "'"
      WebBookSQL      =      WebBookSQL & ",'" & Day(Date) & "/" & Month(Date) & "/" & Year(Date)  & "'"
      WebBookSQL      =      WebBookSQL & ",'" & Time() & "'"      
      WebBookSQL      =      WebBookSQL & ",'" & FirstName & "'"
      WebBookSQL      =      WebBookSQL & ",'" & LastName & "'"
      WebBookSQL      =      WebBookSQL & ",'" & Email & "'"
      WebBookSQL      =      WebBookSQL & ",'" & Phone & "'"      
      WebBookSQL      =      WebBookSQL & ",'" & Address & "'"      
      WebBookSQL      =      WebBookSQL & ",'" & Suburb & "'"      
      WebBookSQL      =      WebBookSQL & ",'" & State & "'"      
      WebBookSQL      =      WebBookSQL & ",'" & Postcode & "'"
      WebBookSQL      =      WebBookSQL & ",'" & Hotel & "'"
      WebBookSQL      =      WebBookSQL & ",'" & CruiseDay & "/" & CruiseMonth & "/" & CruiseYear  & "'"
      WebBookSQL      =      WebBookSQL & ",'" & WhereFrom & "'"

      WebBookSQL      =      WebBookSQL & "," & CStr(PaxAdDir)
      WebBookSQL      =      WebBookSQL & "," & CStr(PaxStuDir)
      WebBookSQL      =      WebBookSQL & "," & CStr(PaxKidDir)
      WebBookSQL      =      WebBookSQL & "," & CStr(PaxFamDir)
      
      WebBookSQL      =      WebBookSQL & "," & CStr(PaxStuEx)
      WebBookSQL      =      WebBookSQL & "," & CStr(PaxAdEx)
      WebBookSQL      =      WebBookSQL & "," & CStr(PaxKidEx)
      WebBookSQL      =      WebBookSQL & "," & CStr(PaxFamEx)
            
      WebBookSQL      =      WebBookSQL & "," & CStr(PaxTotal)
      
      WebBookSQL      =      WebBookSQL & ",'" & Bus & "'"      
      WebBookSQL      =      WebBookSQL & ",'" & PickupPoint & "'"
      WebBookSQL      =      WebBookSQL & ",'" & Session("ClientID") & "'"
      WebBookSQL      =      WebBookSQL & ",'W'"
      WebBookSQL      =      WebBookSQL & "," & CStr(GrandTotal)
      WebBookSQL      =      WebBookSQL & ",0.00"                                    'Set As not Paid at this tage
      WebBookSQL      =      WebBookSQL & ",'Y'"
      WebBookSQL      =      WebBookSQL & ",'Website Booking'"
      WebBookSQL      =      WebBookSQL & ",0"                                           'Set Submitted As False
      WebBookSQL      =      WebBookSQL & ",'NONE YET'"                              'eWay Transaction Number
      WebBookSQL      =      WebBookSQL & ",0"                                           'Set Approved As False
      WebBookSQL      =      WebBookSQL & ",0"                                           'Set Posted As False

      WebBookSQL      =      WebBookSQL & ")"
      Set RsWebBook= OBJdbConnection.Execute(WebBookSQL)
      
      'Close Connection
      OBJdbConnection.Close
      Set OBJdbConnection = Nothing
      DataConnectionString = ""      
End Sub
%>
<html>
<head>
<title>:: Whalewatching Secure payment ::</title>
<style>.Rollover    { behavior: url("buttons/RollOver.htc") }</style>
<SCRIPT language="VBScript">
Sub MakePayment
      eWayForm.Submit()
End Sub
</SCRIPT>
</head>
<body language="VBScript" onLoad = "MakePayment" bgcolor="#000000" text="#FFFFFF" link="#008080" vlink="#008080" alink="#008080" topmargin="10" marginheight="10" leftmargin="0" marginwidth="0">
<center>
<table width="620" border="0" cellpadding="0" cellspacing="0">
      <tr>
      <td colspan="3"><img src="images/header.jpg" width="620" height="43" border="0" name="top"></td>
      </tr>
      <tr>
      <td valign="top" width="120" background="image/left_back.jpg" rowspan="2"><!-- #Include file="navigation.asp" --><img src="Buttons/logo.jpg" width="114" height="91" border="0"></td>
      <td valign="top"><img src="images/header_2.jpg" width="473" height="11" border="0"></td>
      <td valign="top" width="33" background="image/right_back.jpg" rowspan="2"><img src="../images/right.jpg" border="0"></td>
      </tr>
      <tr>
      <td bgcolor="#FFFFFF"></font><font face="Arial" size="2" color="#FF0000">
      <p align="center"><b>TAKING YOU TO eWAY </b> <br>
    <img border="0" src="images/eway_white192.gif"><br>&nbsp;</font></p>
      <FORM id="eWayForm" action="https://www.eway.com.au/gateway/payment.asp" method="POST">
      <input type="hidden" name="ewayCustomerID" value="<% = ewayCustomerID %>" size="20">
      <input type="hidden" name="ewayTotalAmount" value="<% = ewayTotalAmount %>" size="20">
      <input type="hidden" name="ewayTrxnNumber" value="<% = Session("TrxnNumber") %>" size="20">
      <input type="hidden" name="ewayCustomerFirstName" value="<% = ewayCustomerFirstName %>" size="20">
      <input type="hidden" name="ewayCustomerLastName" value="<% = ewayCustomerLastName %>" size="20">
      <input type="hidden" name="ewayCustomerEmail" value="<% = ewayCustomerEmail %>" size="20">
      <input type="hidden" name="ewayCustomerAddress" value="<% = ewayCustomerAddress %>" size="20">
      <input type="hidden" name="ewayCustomerPostCode" value="<% = ewayCustomerPostCode %>" size="20">
      <input type="hidden" name="ewayCustomerInvoiceDescription" value="<% = ewayCustomerInvoiceDescription %>" size="20">
      <input type="hidden" name="ewayCustomerInvoiceRef" value="<% = Session("TrxnNumber") %>" size="20">
      <input type="hidden" name="ewayURL" value="<% = ewayURL %>" size="20">
      <input type="hidden" name="ewaySiteTitle" value="<% = ewaySiteTitle %>" size="20">
      <input type="hidden" name="FirstName" value="<% = FirstName %>" size="20">
      <input type="hidden" name="LastName" value="<% = LastName %>" size="20">
      <input type="hidden" name="Email" value="<% = Email %>" size="20">
      <input type="hidden" name="Phone" value="<% = Phone %>" size="20">
      <input type="hidden" name="Address" value="<% = Address %>" size="20">
      <input type="hidden" name="Suburb" value="<% = Suburb %>" size="20">
      <input type="hidden" name="State" value="<% = State %>" size="20">
      <input type="hidden" name="PostCode" value="<% = Postcode %>" size="20">
      <input type="hidden" name="Hotel" value="<% = Hotel %>" size="20">
      <input type="hidden" name="CruiseDay" value="<% = CruiseDay %>" size="20">
      <input type="hidden" name="CruiseMonth" value="<% = CruiseMonth %>" size="20">
      <input type="hidden" name="CruiseYear" value="<% = CruiseYear %>" size="20">
      <input type="hidden" name="WhereFrom" value="<% = WhereFrom %>" size="20">
      <input type="hidden" name="PickUpPoint" value="<% = PickUpPoint %>" size="20">
      <input type="hidden" name="AdultDirect" value="<% = AdultDirect %>" size="20">
      <input type="hidden" name="AdultEx" value="<% = AdultEx %>" size="20">
      <input type="hidden" name="StudentDirect" value="<% = StudentDirect %>" size="20">
      <input type="hidden" name="StudentEx" value="<% = StudentEx %>" size="20">
      <input type="hidden" name="ChildDirect" value="<% = ChildDirect %>" size="20">
      <input type="hidden" name="ChildEx" value="<% = ChildEx %>" size="20">
      <input type="hidden" name="FamilyDirect" value="<% = FamilyDirect %>" size="20">
      <input type="hidden" name="FamilyEx" value="<% = FamilyEx %>" size="20">
      <input type="hidden" name="PaxAdDir" value="<% = PaxAdDir %>" size="20">      
      <input type="hidden" name="PaxAdEx" value="<% = PaxAdEx %>" size="20">
      <input type="hidden" name="PaxStuDir" value="<% = PaxStuDir %>" size="20">
      <input type="hidden" name="PaxStuEx" value="<% = PaxStuEx %>" size="20">
      <input type="hidden" name="PaxKidDir" value="<% = PaxKidDir %>" size="20">
      <input type="hidden" name="PaxKidEx" value="<% = PaxKidEx %>" size="20">
      <input type="hidden" name="PaxFamDir" value="<% = PaxFamDir %>" size="20">
      <input type="hidden" name="PaxFamEx" value="<% = PaxFamEx %>" size="20">
      <input type="hidden" name="eWayOption2" value="<% = Session("ClientID") %>" size="20">
      <input type="hidden" name="eWayOption3" value="<% = eWayOption3 %>" size="20">
    </tr>
      </FORM>
</table>
      </tr>
      <tr>
      <td width="3" bgcolor="#000000"><img src="image/spacer.gif" width="3" height="3" border="0"></td>
      </tr>
      <tr>
      <td colspan="3"><!-- #Include file="footer.asp" --></td>
      </tr>
</table>
</center>
</body>
</html>
0
 

Author Comment

by:mrbungle50
ID: 19581368
Thank you for your comments Dragonlaird.

Just checked - declared as a system DSN on the server, free table with correct path
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 6

Expert Comment

by:Dragonlaird
ID: 19581841
OK - Firstly, is the DSN name declared exactly as entered? "MBWW Moms"

I would also recommend never using a space character in a DSN name (if you need to break it up to make it readable, try replacing spaces with underscores) as some drivers see a space as an 'end' character.

Does your Foxpro database require/expect a username/password combination?

What error do you get when you try to view the page in your browser?
0
 
LVL 11

Expert Comment

by:L00M
ID: 19581890
I think your connection string may be incorrect as well.
Head over to:
http://www.connectionstrings.com/
And try some different connection strings.
0
 
LVL 11

Expert Comment

by:L00M
ID: 19581896
You may want to try a DSNless connection string as well. I try to avoid DSN's when possible. Doing so removes an extra middle man, and speeds up transactions a bit.
0
 
LVL 6

Expert Comment

by:Dragonlaird
ID: 19581934
In addition to L00M's comment above, I would recommend NOT setting the CommandTimeout=0, this will cause the page to wait (almost) indefinitely for a response. Better to set it to a fixed value, say 15 seconds.

OBJdbConnection.CommandTimeout = 15
0
 
LVL 33

Assisted Solution

by:CarlWarner
CarlWarner earned 150 total points
ID: 19582445
Two things off the top of my head:

1) Make sure you really have the latest VFP ODBC driver installed.  The "junk" that may come with the Windows OS by default is not the latest.

Visual FoxPro ODBC Driver
http://msdn2.microsoft.com/en-us/vfoxpro/Bb190233.aspx

2) I'm not positive if my gut is correct on this one or not.  But, you have
DataConnectionString = "DSN=MBWW Moms;DRIVER={Microsoft Visual FoxPro-Treiber}"
as your connection string.  I think the "Treiber" part is not the right "flavor" of the VFP ODBC driver unless you are wanting the non-English "flavor", like maybe German.  Look for the Visual FoxPro ODBC driver without that suffix description tacked on to it.
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 19582470
As I clicked my above message, one more thought came to mind--

What version of Visual FoxPro is that data on thatr local PC created in and/or running in?

The VFP ODBC driver only truly supports VFP versions through VFP6.  If that PC data is in VFP7, VFP8, or the latest VFP9, there were data enhancements in those versions that may be used and resident in the headers of those local PC tables that a VFP6 ODBC connectivity driver won't recognize.  If that is the case, the only driver that can be used to get to that data wiould be the OLE DB Provider for VFP.

Microsoft OLE DB Provider for Visual FoxPro (through) 9.0 [Backwards compatible]
http://www.microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 19584749
I meant to check sooner, but...

The BabelFish translation, using German to English, of "Trieber" is "Driver".  Unless you need the German version of the VFP ODBC driver, don't use it.
0
 

Author Comment

by:mrbungle50
ID: 19586349
Hi everyone who attended to the solution.
As it turned out I didn't have an internet guest account set up in my security previleges for that particular folder where all the files resided. The asp pages now access the database.
Thank you so much for your assistance.
Tony Lopez
0
 
LVL 11

Assisted Solution

by:L00M
L00M earned 75 total points
ID: 19586397
Request a PAQ - Refund in Community Support/Cleanup
0
 

Author Comment

by:mrbungle50
ID: 19589919
Hi LOOM what is a PAQ?
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

840 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