ASP - Visual Foxpro DBF

What version of FoxPro?

FtB

Here is a good source:

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q165/4/92.ASP&NoWebContent=1

FtB

In short, this should be close:

strConnectionString = "Driver=Microsoft Visual Foxpro Driver; " & _
   "UID=;SourceType=DBC;SourceDB=C:\VFP\Samples\Tastrade\Data\Tastrade.dbc"
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionTimeout = 15
conn.CommandTimeout =  10
conn.Mode = 3 'adModeReadWrite
conn.open strConnectionString

VFP7.  Thanks.

I am getting a page error as if the page does not exist.  It may be because it is not supported in my current hosting config, although the Access database access and code works fine.

Okay, with VFP 6 you needed to use an ODBC connection, with 7.0, however, there is a OleJet driver.

oConn.Open "Provider=vfpoledb;" & _
           "Data Source=C:\vfp8\Samples\Data\myVFPDB.dbc;" & _
           "Mode=ReadWrite|Share Deny None;" & _
           "Collating Sequence=MACHINE;" & _
           "Password=''"


Fritz the Blank

By the way, I changed SourceType to DBF.  Would that cause a problem?

Don't forget--you will have to have either the ODBC driver or the oledb driver installed. Is this your machine or are you using a web-hosting service.

FtB

IN any event, I suggest that you create a separate test page just to test the connection. Once you get that working, you can turn your attention to integrating the new connection into your existing code.

FtB

Here is my current Build Connection section

set conn = server.CreateObject ("ADODB.Connection")
      conn.Open "Provider=vfpoledb;Password=;Data Source=" & server.MapPath ("testdat.dbf")
      set rs = server.CreateObject ("ADODB.Recordset")
      'Open all records
      rs.Open "SELECT * FROM userlist", conn, 3, 3

Is this the reason for the "Page Cannot be displayed" message?

Well, let's find out:

In IE:

Tools Menu --> Internet Options -->Advanced Tab-->Show Friendly HTTP Error Messages

The last item should be unchecked.

Try that and then re run your page.

Also, did you read all of my comments above?

FtB

I read those and looked at the ConnectStrings page.  I am getting "Provider cannot be found" since friendly is turned off.

The open line is set to

conn.Open "Provider={Microsoft Visual Foxpro Driver};SourceType=DBF;Exclusive=No;Data Source=" & server.MapPath ("testdat.dbf")

Is this your machine or are you using a web-hosting service?

FtB

Hosting service.  They are on the west coast and I am East so they are not even open yet.

Okay, that is the issue. In order for this to work, you will have to have the VFP drivers installed on the server, so this may not work for you. You might try using the oledb connection to see if you have any luck with that:

Provider=vfpoledb.1;Data Source=C:\MyDataDirectory\;Password=MyPassWord;Collating Sequence=general

FtB

That didn't work either.  I was thinking that I may have to contact the host.
Maybe that will be possible in about 45 minutes.

Okay, if none of these work, then one of two things will have to happen:

1) your host will have to install the necessary drivers
2) you will have to give up on the idea of using VFP

FtB

Okay, are we all set with this? If so, please accept the comment that helped you the most as an answer.

FtB

Nope.  Unfortunately not.  I finally got an email from them about the DSN specifics.  Now my file section looks like

'Build connection
      set conn = server.CreateObject ("ADODB.Connection")
      conn.Open "DSN=linkumup"
      set rs = server.CreateObject ("ADODB.Recordset")
      'Open all records
-->      rs.Open "SELECT * FROM userlist", conn, 3, 3

and I get

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

ODBC driver does not support the requested properties.

/data/create.asp, line 16 (indicated above)

Is this something I did or a failure on the host setup?

So it looks like they created a DSN for you? One way to test thencreate a new page with just this code on it:

<%
set conn = server.CreateObject ("ADODB.Connection")
conn.Open "DSN=linkumup"
response.write("Connection State: " & conn.state)

%>

FtB

Connection State is 1.  Does that mean the DSN is set up properly?  Is the problem on that line with OPEN or SELECT?

This is very good indeed. That means that you have a valid connection.

Next step--again on a separate test page:

set conn=Server.CreateObject("ADODB.Connection")
conn.ConnectionTimeout = 15
conn.CommandTimeout =  10
conn.Mode = 3 'adModeReadWrite
if conn.state = 0 then
      conn.Open "DSN=linkumup"
end if

set rs = server.CreateObject ("ADODB.Recordset")
rs.Open "SELECT * FROM userlist", conn, 3, 3

response.write("Connection State: " & conn.state &"<BR>")
response.write("RecordSet State: " & rs.state)

Same error, same command

rs.Open "SELECT * FROM userlist", conn, 3, 3

Okay, what about this:

set conn=Server.CreateObject("ADODB.Connection")
conn.ConnectionTimeout = 15
conn.CommandTimeout =  10
conn.Mode = 3 'adModeReadWrite
if conn.state = 0 then
     conn.Open "DSN=linkumup"
end if

set rs = server.CreateObject ("ADODB.Recordset")
rs.Open "SELECT * FROM userlist", conn

response.write("Connection State: " & conn.state &"<BR>")
response.write("RecordSet State: " & rs.state)

I just noticed something.  The name of the file being used is TESTDAT.DBF.  Does that mean that TESTDAT should be in place of USERLIST in the SELECT command?

I tried that one change and get the same message

Did you try the last bit I posted? Try also the different naming conventions in the sql select.

set conn=Server.CreateObject("ADODB.Connection")
conn.ConnectionTimeout = 15
conn.CommandTimeout =  10
conn.Mode = 3 'adModeReadWrite
if conn.state = 0 then
     conn.Open "DSN=linkumup"
end if

set rs = server.CreateObject ("ADODB.Recordset")
rs.Open "SELECT * FROM TESTDAT ", conn

response.write("Connection State: " & conn.state &"<BR>")
response.write("RecordSet State: " & rs.state)

I believe I have finally figured it out.  The DSN was set up to pull the file from my root web directory instead of the DATA directory where I was using it.  I will play with it some more and hopefully be able to close this soon and begin my journey with learning and using ASP.

As an aside, I have not found the results to be particularly good with VFP through some of the old drivers. However, the new driver is supposed to be pretty good.

If you are just doing this for learning purposes, Access might be a good bet.

FtB

I have actually written a program in VFP and have been asked to make the data web accessible.  I have previously converted the VFP table (in another app) to Access and then used it on the web.  This time I had rather not go through that conversion if it is avoidable.  I hope to jump on this later today but am juggling a couple of things right now.  Single focus results in a better learning experience.

Thanks.

Well, if you already have the data in VFP, then we should pursue getting this working.

For your own info, it is much better to use the new VFP Jet driver--it is signficantly faster.

FtB

I am guessing that is something I would need to get set up with my host?

Now on to something that I hope is the final problem.  By the way, I will open a new question for this one if you prefer.

I have this code in the VERIFY routine.

<%
      'Save the entered username and password
      Username = Request.Form("txtUsername")      
      Password = Request.Form("txtPassword")
      
      'Build connection with database
      set conn = server.CreateObject ("ADODB.Connection")            
      conn.Open "DSN=linkumup"
      set rs = server.CreateObject ("ADODB.Recordset")            
      'Open record with entered username
      rs.Open "SELECT * FROM testdat where username='"& Username &"'", conn, 1
      
      'If there is no record with the entered username, close connection
      'and go back to login with QueryString
      If rs.recordcount = 0 then
            rs.close
            conn.close
            set rs=nothing
            set conn=nothing
            Response.Redirect("login.asp?login=namefailed")
      end if
      
      'If entered password is right, close connection and open mainpage
      if rs("password") = Password then
            Session("name") = rs("fullname")
            rs.Close
            conn.Close
            set rs=nothing
            set conn=nothing
            Response.Redirect("default.asp")
      'If entered password is wrong, close connection
      'and return to login with QueryString
      else
            response.write(rs("username")&"---<BR>")
            response.write(username&"+++<BR>")
            response.write(rs("password")&"---<BR>")
            response.write(password&"+++")
            rs.Close
            conn.Close
            set rs=nothing
            set conn=nothing
            'Response.Redirect("login.asp?login=passfailed")
      end if      

%>

It finds the name because it goes through that okay.  It does not match the password although the output of the write statements shows that the name and password match with the exception of an extra space at the end of the RS() data.  Any ideas why this is not working?

Ideally, this should be a separate question as it is a completely different topic. However, I'll post something here for you to look at:

              <%
                        If request.form("btnSubmit") = "Submit" then
                              dim strErrorMessage, strUserName, strPassWord
                              strErrorMessage = ""
                              'make sure username and password entered
                              strUserName = Trim(Request.Form("strUserName"))
                              strPassWord = Trim(Request.Form("strPassWord"))

                              if strUserName = "" then
                                    strErrorMessage = "You must enter a User Name!"
                              elseif strPassWord = "" then
                                    strErrorMessage = "You must enter a Pass Word"
                              end if
                        end if
                        'if username and password entered, continue
                        if strErrorMessage ="" then
                              dim strDataPath, objConnection, strConnectString
                              strDataPath = Server.MapPath("hyannisportcapital.mdb")
                              set objConnection=Server.CreateObject("ADODB.Connection")
                              strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;"_
                                       & " Data Source= " & strDataPath & ";"_
                                       & " Mode=Share Deny None;User Id=admin;PASSWORD=;"

                              objConnection.ConnectionTimeout = 15
                              objConnection.CommandTimeout =  10
                              objConnection.Mode = 3 'adModeReadWrite
                              if objConnection.state = 0 then
                                objConnection.Open strConnectString
                              end if
                              
                              dim strSQL, objRS
                              strSQL = "SELECT * FROM tblUsers Where Trim(UCase(strUserName)) = '" & UCase(strUserName) & "'"
                              set objRS=Server.CreateObject("ADODB.RecordSet")
                              objRS.Open strSQL,objConnection,3,3
                              if objRS.EOF then
                                    strErrorMessage = "You have entered an invalid Username"
                              elseif UCase(objRS("strPassword")) <> UCase(strPassword) then
                                    strErrorMessage = "You have entered an invalid Password"
                              end if
                              objRS.Close()
                              set objRS = Nothing
                              objConnection.Close()
                              Set objConnection = Nothing
                        end if
                        If strErrorMessage <>"" then
                              'errors, don't give access
                              strErrorMessage = "<tr><td colspan=2 align=center><font color=red>" & strErrorMessage & "</font></td></tr>"
                        else
                              'everything is fine
                              dim bolAuthenticated
                              Session("bolAuthenticated") = true
                              response.redirect("investorsonly.asp")
                        end if
             %>
            <td width="100%" class="body">
                        <form name="frmAuthenticate" method="POST">
                              <TABLE>
                                    <%=strErrorMessage%>
                                    <TR>
                                          <TD align="right">UserName:</TD>
                                          <TD><input type="text" name="strUserName" value="<%=Request.Form("strUserName")%>"></TD>
                                    </TR>
                                    <TR>
                                          <TD align="right">Pass Word:</TD>
                                          <TD><input type="password" name="strPassWord" value="<%=Request.Form("strPassWord")%>"></TD>
                                    </TR>
                                    <TR>
                                          <TD colspan=2 align=center><input type="Submit" value="Submit" name="btnSubmit"></TD>
                                    </TR>
                              </TABLE>
                        </form>

Excellent.  I added TRIM and it is happy.  I knew it needed to be there (from a VFP standpoint, at least) but didn't know which command to use.  Would you happen to know of a few good primers on ASP?

I really liked Teach Yourself ASP 3.0 in 21 Days

Of course there is also:

http://www.w3schools.com/asp/default.asp

FtB

The link is a little too disorganized to lend itself to a "beginner" learning experience.  I ordered the book but had to do it online.  I thought it would be available at the local BAMM stores but no luck on that one.  I guess I have to work with web resources until it comes.  Thanks.

Good luck.

I found the SAMS book very helpful.

FtB