[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

ASP - Visual Foxpro DBF

Posted on 2004-08-23
38
Medium Priority
?
1,902 Views
Last Modified: 2010-08-05
I have the following code that is working well.

<%
      'Save entered username and password
      Username = Request.Form("txtUsername")
      Password = Request.Form("txtPassword")
      Fullname = Request.Form("txtFullname")
            
      'Check if username and password are entered
      if Username = "" then Response.redirect("login.asp?login=createnamefailed")
      if Password = "" then Response.Redirect("login.asp?login=createpassfailed")
      
      'Build connection
      set conn = server.CreateObject ("ADODB.Connection")
      conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath ("users.mdb")
      set rs = server.CreateObject ("ADODB.Recordset")
      'Open all records
      rs.Open "SELECT * FROM userlist", conn, 3, 3
      
      'Check if username doesn't already exist
      do while not rs.EOF
            if rs("username")=Username then
                  set rs=nothing
                  set conn=nothing
                  Response.Redirect("login.asp?login=createnamefailed")
            end if
            rs.MoveNext
      loop
            
      'Add a record
      rs.AddNew
      'Put username and password in record
      rs("username")=Username
      rs("password")=Password
      rs("fullname")=Fullname
      'Save record
      rs.Update
      
      set rs=nothing
      set conn=nothing

      Response.Redirect("login.asp?login=creatednew")
%>

It is using an Access file.  I want to change it to a VFP DBF file.  I know I need to change

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath ("users.mdb")

but I don't know exactly what to put in place of 'Microsoft.Jet.OLEDB.4.0'

Any suggestions?
0
Comment
Question by:slink9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 21
  • 17
38 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11869777
What version of FoxPro?

FtB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11869811
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Author Comment

by:slink9
ID: 11869919
VFP7.  Thanks.
0
 
LVL 23

Author Comment

by:slink9
ID: 11870028
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.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11870034
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
0
 
LVL 23

Author Comment

by:slink9
ID: 11870045
By the way, I changed SourceType to DBF.  Would that cause a problem?
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11870052
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
0
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 2000 total points
ID: 11870082
Also--there are connection string samples here for free tables, .dbc's, and etc. for different versions of VFP:

http://www.connectionstrings.com/


FtB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11870093
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
0
 
LVL 23

Author Comment

by:slink9
ID: 11870105
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?
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11870121
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
0
 
LVL 23

Author Comment

by:slink9
ID: 11870244
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")
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11870283
Is this your machine or are you using a web-hosting service?

FtB
0
 
LVL 23

Author Comment

by:slink9
ID: 11870299
Hosting service.  They are on the west coast and I am East so they are not even open yet.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11870449
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
0
 
LVL 23

Author Comment

by:slink9
ID: 11870513
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.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11870538
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
0
 
LVL 46

Expert Comment

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

FtB
0
 
LVL 23

Author Comment

by:slink9
ID: 11888340
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?
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11891435
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
0
 
LVL 23

Author Comment

by:slink9
ID: 11892779
Connection State is 1.  Does that mean the DSN is set up properly?  Is the problem on that line with OPEN or SELECT?
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11892852
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)
0
 
LVL 23

Author Comment

by:slink9
ID: 11892953
Same error, same command

rs.Open "SELECT * FROM userlist", conn, 3, 3
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11893362
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)
0
 
LVL 23

Author Comment

by:slink9
ID: 11893509
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
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11893600
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)
0
 
LVL 23

Author Comment

by:slink9
ID: 11893646
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.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11893670
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
0
 
LVL 23

Author Comment

by:slink9
ID: 11893743
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.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11893782
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
0
 
LVL 23

Author Comment

by:slink9
ID: 11893831
I am guessing that is something I would need to get set up with my host?
0
 
LVL 23

Author Comment

by:slink9
ID: 11897202
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?
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11898085
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>
0
 
LVL 23

Author Comment

by:slink9
ID: 11898175
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?
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11898238
I really liked Teach Yourself ASP 3.0 in 21 Days

Of course there is also:

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

FtB
0
 
LVL 23

Author Comment

by:slink9
ID: 11912419
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.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11912696
Good luck.

I found the SAMS book very helpful.

FtB
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

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 informatio…
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

656 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