Solved

ASP - Visual Foxpro DBF

Posted on 2004-08-23
38
1,864 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
  • 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: 11869793
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
 
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 500 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

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 …
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

744 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

15 Experts available now in Live!

Get 1:1 Help Now