Solved

Website linked to Access Database

Posted on 2004-04-13
10
180 Views
Last Modified: 2013-12-24
Hi all,

I am trying to create a website which is linked to a MS Access Database and uses that to display "live" data on an HTML page, allowing users to view and input data.

How would I do this???

Thanks for help...

seb
0
Comment
Question by:sjc2309
  • 3
  • 3
  • 2
10 Comments
 
LVL 12

Accepted Solution

by:
rcmb earned 250 total points
ID: 10813157
You must be running this site on a webserver with front page server extensions installed for the below to work.

You need to import your database into FrontPage and create an ASP page to display the results.

Click on file, import and navigate to your database and import the file. FrontPage will prompt you to put the file in the fpdb directory.

Enter a name for your database connection (default is Database1) but you can name it anything you desire.

Open a new page and click on Insert, Database, Results - this will open the Database Connection Wizard

In step 1 select the connection you just created
In step 2 select the table you desire to display
In step 3 select Edit List to remove fields you do not desire to display
In step 4 select the format you desire to have the information display as (default is a table)
In step 5 select "display all records together" and clcik on finish

Save the page a something.asp

Click on preview in browser - You will see the results of the database display (if there is data in the database, if not "No records returned" will display)

To input data you need to create a form with text, drop-down, text-area, etc. boxes to gather the data. I recommend naming each form field the same as the database field you will be writing to (right click on the form field and edit the Name).

You have the form submit directly to the database by right-clicking inside the form (dotted lines) and select form properties
Select the option send to database, click on options to select the database connection you created earlier and select the table you desire to send the information to.

Click on the saved fields tab and match your form boxes to the fields you desire them to be written to.

I recommend clicking on the additional fields tab and removing anything in there.

Click ok once complete.

There are much more efficient ways to do this but if you are not familiar with writing ASP then this is probably the easiest way to get you to where you want to go. If you want more information let me know.

RCMB
0
 

Expert Comment

by:arekmalang
ID: 10912603
Hi,

I was trying to do that but I can only retrieve data from Access but unable to insert them.
Anything else I need to do?

Thanks,
Supri
0
 
LVL 12

Expert Comment

by:rcmb
ID: 10912712
Please provide a little more specifics. I included a section in my previos comment on how to insert into a database. Did this not work? Did you need more guidance on that section?

RCMB
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Expert Comment

by:arekmalang
ID: 10913038
Yes, I followed your instruction. Below is the asp code for you to review. When I hit the submit button, I got this confirmation page:

Form Confirmation
Thank you for submitting the following information:

judul: test
komentar: test
Submit: Submit


Return to the form.


However, when I looked up the table in MS Access, I don't see this new record.
Any idea why?

Thanks for the help,

Supri

Note:
Database name is Opiniku.
Table name is Komentar. There are 2 fields in there: judul(type:text) and komentar(type:memo).


ASP Code:

<%
' FP_ASP ASP Automatically generated by a Frontpage Component. Do not Edit.

On Error Resume Next
Session("FP_OldCodePage") = Session.CodePage
Session("FP_OldLCID") = Session.LCID
Session.CodePage = 1252
Session.LCID = 1033
Err.Clear

strErrorUrl = ""

If Request.ServerVariables("REQUEST_METHOD") = "POST" Then
If Request.Form("VTI-GROUP") = "0" Then
      Err.Clear

      Set fp_conn =  Server.CreateObject("ADODB.Connection")
      FP_DumpError strErrorUrl, "Cannot create connection"

      Set fp_rs = Server.CreateObject("ADODB.Recordset")
      FP_DumpError strErrorUrl, "Cannot create record set"

      fp_conn.Open Application("Opiniku_ConnectionString")
      FP_DumpError strErrorUrl, "Cannot open database"

      fp_rs.Open "Komentar", fp_conn, 1, 3, 2 ' adOpenKeySet, adLockOptimistic, adCmdTable
      FP_DumpError strErrorUrl, "Cannot open record set"

      fp_rs.AddNew
      FP_DumpError strErrorUrl, "Cannot add new record set to the database"
      Dim arFormFields0(2)
      Dim arFormDBFields0(2)
      Dim arFormValues0(2)

      arFormFields0(0) = "komentar"
      arFormDBFields0(0) = "Komentar"
      arFormValues0(0) = Request("komentar")
      arFormFields0(1) = "judul"
      arFormDBFields0(1) = "Judul"
      arFormValues0(1) = Request("judul")

      FP_SaveFormFields fp_rs, arFormFields0, arFormDBFields0


      fp_rs.Update
      FP_DumpError strErrorUrl, "Cannot update the database"

      fp_rs.Close
      fp_conn.Close

      FP_FormConfirmation "text/html; charset=windows-1252",_
                                    "Form Confirmation",_
                                    "Thank you for submitting the following information:",_
                                    "kirimKomentar.asp",_
                                    "Return to the form."

End If
End If

Session.CodePage = Session("FP_OldCodePage")
Session.LCID = Session("FP_OldLCID")

%>
<html>

<head>
<meta http-equiv="Content-Language" content="en-us">
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Judul</title>
</head>

<body>

<form method="POST" action="--WEBBOT-SELF--">
  <!--webbot bot="SaveDatabase" suggestedext="asp" u-asp-include-url="_fpclass/fpdbform.inc" s-dataconnection="Opiniku" s-recordsource="Komentar" u-database-url="fpdb/opiniku.mdb" s-form-fields="komentar judul" s-form-dbfields="Komentar Judul" startspan --><input TYPE="hidden" NAME="VTI-GROUP" VALUE="0"><!--#include file="_fpclass/fpdbform.inc"--><!--webbot bot="SaveDatabase" endspan i-checksum="40548" --><p>
  Judul:</p>
  <p><input type="text" name="judul" size="20"></p>
  <p>Komentar:</p>
  <p><textarea rows="2" name="komentar" cols="20"></textarea></p>
  <p><input type="submit" value="Submit" name="Submit"><input type="reset" value="Reset" name="Reset"></p>
</form>

</body>

</html>
0
 
LVL 12

Expert Comment

by:rcmb
ID: 10913170
I uploaded your code and created the database per your example. I made no modifications to your code and the form inserted data into the database with no errors.

Did you save the file as .asp vice .htm?

Make sure you link the fields to the form -- right click on the form and click on form properties

Click on Options, Saved Fields and make sure your form fields are linked to the database fields.

RCMB
0
 

Expert Comment

by:arekmalang
ID: 10915341
Yes, I did the Options thing and I checked, all the fields are linked to the database. I saved the file as .asp as well. It seems that when I hit the submit button, data is not transferred to the database at all. Is there anything that I need to tweak in Access? Can I set up a trace file to make sure that the data is actually inserted into the database? I don't think it's the connection problem since I am able to retrieve the data and view them on the page.


Thanks,
Supri
0
 
LVL 11

Expert Comment

by:sciber_dude
ID: 11112084
I think you need to give write/update permissions to the database.

Use Windows Explorer to reach the database and right click on it and select properties. Under the securities tab, add the following users (IUSR_YourServername and IWAM_YourServername) and give permissions to read, write and execute.

Make sure you replace Yourservername with the name of your server.

:) SD
0
 
LVL 11

Expert Comment

by:sciber_dude
ID: 11112090
Btw, the original poster of this question is sjc2309!

sjc2309, did any of this help you?
:) SD
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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…
Introduction In this tutorial, I'll explain how to create an animated progress meter in a wireframe prototype developed using Axure RP 7.0 - a leading prototyping tool for designing web sites and software. (For more information about Axure and gett…
The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and SmallPDF.com Log…
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…

685 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