Solved

Attaching Access Table To Recordset DTC

Posted on 2001-09-10
30
194 Views
Last Modified: 2013-12-24
I have a web page set up and want to attach an Access table and get it set up working locally and then want to publish it and have it work for the world.  I have accomplished attaching to a database once but that was only playing and learning on my server.  What is the best route to take to accomplish this? (150 pts)
Along the same lines, I will need to use variables in a SQL statement to only display the requested data.  Do I do this using the ? and the variable tab in the recordset with an Access table? (additional 50 pts either separately or added to this)
0
Comment
Question by:slink9
  • 16
  • 10
  • 3
  • +1
30 Comments
 
LVL 15

Expert Comment

by:robbert
Comment Utility
> What is the best route to take to accomplish this?

Do not grade this -- but the way that hurts the less is still not using DTCs (but learning at www.asp101.com), and FTPing instead of InterDev-publishing. -- Use a DSN-less connection with Server.MapPath().
The same for dynamically assigning an SQL statement (WHERE clause) to a recordset.

I'm sorry but in my eyes you've been cheated; InterDev makes starting easy but not maintaining. It will become worse with .net WebForms.
0
 
LVL 23

Author Comment

by:slink9
Comment Utility
I am beginning to believe that myself.  There is so much coding to do setting up a data connection, though.  I am reading through Active Server Pages 3 and the author is using VI to create the data-connected web pages.  Isn't there an ADO Data Connection or Recordset that I could use instead of DTC?
0
 
LVL 5

Accepted Solution

by:
raizon earned 100 total points
Comment Utility
First you need to establish a connection to your database.

<%
Set con = Server.CreateObject("ADODB.CONNECTION")
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("relative path to your .mdb like /myDatabase.mdb)")
%>

Then once you have that established you can execute your various SQL statments.  To perform a query against a database you will want to create a record set.  There are various ways of doing this.  My preference is something along the line of

<%
strSQL = "SELECT * FROM Table where TableID = " & intID
Set myRst = con.Execute(strSQL)
%>

To execute a DELETE, INSERT, or UPDATE then you wouldn't need to create a recordset and could do it like

<%
strSQL = "INSERT INTO Table (field1, field2) VALUES ("
strSQL = strSQL & "'" & strField1 & "', "
strSQL = strSQL & "'" & strField2 & "'"

con.Execute(strSQL)
%>

in the sql statments I also show how you would use variables to query against.  

0
 
LVL 23

Author Comment

by:slink9
Comment Utility
The problem comes in with the relative path.  I played around with the example in the book and finally got it to work.  Now I am trying to create something usable but am running into the problem again that the data is not accessible.  I have the table on my server in the root of the web.  Is there a better place to have it?  How can I point the recordset to it?
0
 
LVL 5

Expert Comment

by:raizon
Comment Utility
you need to map a path to the database in your connection.
By useing Server.MapPath you can do this.

If your db is at the root of the web then set your connection string like so

<%
Set con = Server.CreateObject("ADODB.CONNECTION")
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("/myDatabase.mdb)")
%>


0
 
LVL 23

Author Comment

by:slink9
Comment Utility
The only problem with that is that I am doing this through the VI IDE, not in code.  I have it connected and open and can view the data.  I just can't use it on the web page.  I get 80004005 when going to the page.  I have checked quite a few things from a few MS articles but still can't get it to connect.
Is there a better thing to use other than a recordset DTC?
0
 
LVL 5

Expert Comment

by:raizon
Comment Utility
You need to do it in code.  As Robert suggested I wouldn't use DTC.  There is problems with supporting it once its created.  Use the examples that I've shown to create your ADODB Connection and Recordset.
0
 
LVL 23

Author Comment

by:slink9
Comment Utility
Okay.  I am setting this up in code.  I have the RS set up.  I have the SQL statement set up and working properly as far as I know.
How do I display that info?  I can't use a label DTC, can I?  What do I use to display it?
0
 
LVL 15

Expert Comment

by:robbert
Comment Utility
You would mix HTML and ASP:

<table>
<%
Do While Not objRecordset.EOF
    %>
    <tr>
    <%
    For i = 0 To objRecordset.Fields.Count - 1
        Response.Write "<td>" & objRecordset.Fields(i) & "</td>" & vbCrLf
    Next
    %>
    </tr>
    <%
    objRecordset.MoveNext
Loop
%>
</table>

You can also address fields by, objRecordset("database_column_name")
0
 
LVL 23

Author Comment

by:slink9
Comment Utility
I finally discovered that one myself.  I am working on it.  Thanks.
0
 
LVL 23

Author Comment

by:slink9
Comment Utility
It works well locally (finally) but doesn't work when I deploy it.  It can't find the data file again.  I have it in as /shipstat/filenm to work with it locally and have tried it with that and /filenm on the published version unsuccessfully.  Any suggestions here?
0
 
LVL 15

Expert Comment

by:robbert
Comment Utility
You need to post your code, the error number and description.

Response.Write Server.MapPath("/") to get orientation.

The anonymous user needs Read/Write permissions to the folder the database is in. Make sure that this is the case on the remote server; possibly ask the admins.
0
 
LVL 23

Author Comment

by:slink9
Comment Utility
I believe the problem is that the table hasn't actually been written to the web.  I will FTP the table today when I get to work and see if that helps.
It is nice to finally be making progress on this, though.
0
 
LVL 5

Expert Comment

by:raizon
Comment Utility
if you still get an error after you ftp the db up to the server post the error number, description, and code as robert suggested and we can help you finish this.

Glad to see progress is being made.
0
 
LVL 23

Author Comment

by:slink9
Comment Utility
I ditched VI and put the code in FP98.  All is working well locally but I am having problems getting it published.  I believe that is because of a router configuration.  I will play with that later and hopefully close out this question with a point split.  Thanks for the help.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

Expert Comment

by:raizon
Comment Utility
glad to help.

Don't ditch VI6 quite yet tho.  It is a much better tool then FP98 with better publishing tools.  Just stay away from the Design Time Controls in VI.

0
 
LVL 23

Author Comment

by:slink9
Comment Utility
This is true.  I can't ditch VI because, for some reason, FP will not publish.  I didn't like the inability to publish only changed pages in VI.  This is important because the table will change daily but that will be the only change.  I can't FTP to it because it is set up to be published with FP.
I thought my FP was messed up but I uninstalled it, reinstalled it, and repatched it it to the b ver.  I still can't publish or open the remote web.  Any suggestions as to where to look for that?
0
 
LVL 5

Expert Comment

by:raizon
Comment Utility
when you publish in VI uncheck "Copy Changed Files Only" and it will publish the entire project for you then.

What version of VI are you using?

I would also consider upgrading to FP2000 or FP2002
0
 
LVL 23

Author Comment

by:slink9
Comment Utility
VI6 Professional.  I didn't see an option for changed files only.  I may have to do just that since I am having so many problems with FP.  How can I get VI to put my data table up?
0
 
LVL 5

Expert Comment

by:raizon
Comment Utility
if your database is a part of the VI project then it will publish it as well.  

When you publish it you should see a screen/window that asks you where you want to publish it to (Destination Web Server) and the name of the project (Web Project).

Underneath that you should have 4 options.

Copy Changed files only.
Add to existing web project.
Copy child webs.
Register Server Components.


Make sure that "Add to existing web project" is selected. If not then "Copy Changed files only" will not be an option.
0
 
LVL 23

Author Comment

by:slink9
Comment Utility
I hate these questions that just go on ... and on ... and on ...
I have tried to include it by right-clicking on the root and adding it but I get a message stating that the server connection has been aborted.  Am I adding this properly or is there a better procedure to follow?
0
 
LVL 23

Author Comment

by:slink9
Comment Utility
I have it appearing in the project.  I added a DATABASE directory and put it in there.  It still works locally.  When I try to Deploy (I believe is the proper method) I get UNABLE TO SEND REQUEST TO THE WEB SERVER returned and a failed deployment.
Is there another option to publish the site?  I don't see the things you mentioned.
0
 
LVL 5

Expert Comment

by:raizon
Comment Utility
do you have frontpage server extensions installed on the server?


What are the steps you are following to publish it to the server?
0
 
LVL 23

Author Comment

by:slink9
Comment Utility
Frontpage extensions are on the server as far as I know.  We are using a web host.
I use Project-Deploy-Deploy to ... and it goes.  I have removed the table from the project and it deploys fine.  I wanted to make sure I got the command in there to tell the robots not to index it.
0
 
LVL 5

Expert Comment

by:raizon
Comment Utility
In your Project Explorer You have a series of icons.  The 4th icon which looks like 2 "batch file" icons overlayed is your copy web application button.  When you click on that one you will see the options that I described earlier.

0
 
LVL 23

Author Comment

by:slink9
Comment Utility
I see, said the blind man.  I did that, got the options, and now it tells me that the FP Server extensions are not installed there.  I was talking to the support people who said it is.  I must be putting it in the wrong place on the web.
0
 
LVL 23

Author Comment

by:slink9
Comment Utility
And the problem is solved!!
Our router won't allow me to publish to the web.  I guess that is a good thing since it pretty much proves the "clamped down tightness" of our network.
I pulled my laptop out and connected via modem.  I published with FP just because it is easier and quicker and it works well.  I will ask for a 2/3, 1/3 point split.  Thanks for the help.
0
 
LVL 5

Expert Comment

by:raizon
Comment Utility
my pleasure.

Good luck.
0
 
LVL 2

Expert Comment

by:Lunchy
Comment Utility
Points reduced for split.  slink9, I think you will do the rest?
0
 
LVL 23

Author Comment

by:slink9
Comment Utility
Thanks for the help.  Robbert, look for your points in the VI topic area.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When deciding to adopt any help desk solutions many factors should be explored before taking decisions. This will change from business to another but in general there are some kind of rule of thumb. Here are some quick tips: Do we need only ticket…
Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
The purpose of this video is to demonstrate how to automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
The purpose of this video is to demonstrate how to make a WordPress Site faster and smaller in size by cleaning up the database. This will be demonstrated using a Windows 8 PC. Plugin WP Optimize will be used. Go to your WordPress login page. T…

771 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

10 Experts available now in Live!

Get 1:1 Help Now