Solved

Website linked to Access Database

Posted on 2004-04-13
10
176 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
 

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

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…
Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
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 Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…

747 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

11 Experts available now in Live!

Get 1:1 Help Now