By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Using Access As a web database

P: 68
I have an Access database that I want to connect to some forms that i created in Frontpage. I dont have a clue of where to start. Can anybody help or point me to a simple tutorial.
Thank You
May 12 '08 #1
Share this Question
Share on Google+
10 Replies


jeffstl
Expert 100+
P: 432
I have an Access database that I want to connect to some forms that i created in Frontpage. I dont have a clue of where to start. Can anybody help or point me to a simple tutorial.
Thank You
Classic ASP

You need to set up a connection string, and use asp variables to load data into your form.

You will also need to use an SQL querystring to pull data from your database.

Try some things out. If you have specific problems post them in the ASP forum as you run into them and I can keep helping you out.
May 12 '08 #2

P: 68
Ok i search and found some tutorials, got some stuff going but is stuck at the following error

Microsoft JET Database Engine error '80040e14'

Syntax error. in query expression 'SELECT [Maintenance_History].HardwareAssetID'.

/DMS/Update.asp, line 25


I am calling the following codes from a form that i entered information

<HTML><HEAD><TITLE>Added Page</TITLE></HEAD>
<BODY BGCOLOR=#ffffff MARGINWIDTH="0" MARGINHEIGHT="0" LEFTMARGIN=0 TOPMARGIN=0>
<%
Dim DataConnection
Dim RsAddRecords
Dim strSQL

'Open the database connection
Set DataConnection = Server.CreateObject("ADODB.Connection")
DataConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source="& Server.MapPath("/DMS/testBase.mdb"))

'Create an ADO recordset object
Set RsAddRecords = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT SELECT [Maintenance_History].HardwareAssetID, [Maintenance_History].DateReported, [Maintenance_History].ProblemDescription, [Maintenance_History].Cause, [Maintenance_History].SolutionDescription, [Maintenance_History].PerformedBy, [Maintenance_History].DateCompleted, [Maintenance_History].Comments, [Maintenance_History].Department, [Maintenance_History].Status FROM [Maintenance_History];"

'Set the cursor type we are using so we can navigate through the recordset
RsAddRecords.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
RsAddRecords.LockType = 3

'Open the recordset with the SQL query
RsAddRecords.Open strSQL, DataConnection

'Tell the recordset we are adding a new record to it
RsAddRecords.AddNew

'Add a new record to the recordset
RsAddRecords.Fields("HardwareAssetID") = Request.Form("Asset_ID")
RsAddRecords.Fields("DateReported") = Request.Form("Date_Reported")
RsAddRecords.Fields("ProblemDescription") = Request.Form("Issue_Reported")
RsAddRecords.Fields("Cause") = Request.Form("Cause")
RsAddRecords.Fields("SolutionDescription") = Request.Form("Action_Taken")
RsAddRecords.Fields("PerformedBy") = Request.Form("Serviced_By")
RsAddRecords.Fields("DateCompleted") = Request.Form("Date_Completed")
RsAddRecords.Fields("Comments") = Request.Form("Comments")
RsAddRecords.Fields("Department") = Request.Form("Department")
RsAddRecords.Fields("Status") = Request.Form("Status")


'Write the updated recordset to the database
RsAddRecords.Update

'Reset server objects
RsAddRecords.Close
Set RsAddRecords = Nothing
Set DataConnection = Nothing
%>

<p align="center">&nbsp;<p align="center">Data was successfully added<p align="center">&nbsp;<p align="center">
</BODY>
</HTML>
May 15 '08 #3

jeffstl
Expert 100+
P: 432
You have 2 SELECTS. You only need 1. There could be more issues with your SQL but try that first

Expand|Select|Wrap|Line Numbers
  1. 'take out one of the SELECT
  2. strSQL = "SELECT [Maintenance_History].HardwareAssetID, [Maintenance_History].DateReported, [Maintenance_History].ProblemDescription, [Maintenance_History].Cause, [Maintenance_History].SolutionDescription, [Maintenance_History].PerformedBy, [Maintenance_History].DateCompleted, [Maintenance_History].Comments, [Maintenance_History].Department, [Maintenance_History].Status FROM [Maintenance_History];" 
  3.  
Also since you are only querying 1 table you really dont need to specify the table in the SQL. In fact you might even be able to just do this:

Expand|Select|Wrap|Line Numbers
  1. 'take out one of the SELECT
  2. strSQL = "SELECT * FROM Maintenance_History" 
  3.  
May 15 '08 #4

P: 68
hey i found the error... Look at the SQL statement and you'll see two SELECT. there should only be one.
I do hope that this post help someone.
I'll probably be calling for some more help soon so please stay tune
May 15 '08 #5

P: 68
I want to create a form to search the database for any record the user might need to see. the idea here is that the user would select the table he want to search then based on that selection the field combo box would be filled with the appropriate fields. I haven't a clue on how to accomplish this but i know that i need to do it to make make searching a breeze.

Help please.

Expand|Select|Wrap|Line Numbers
  1. <form method="POST" action=search.asp>
  2.             <p align="center"><b>
  3.             <font color="#000080" face="Poor Richard" size="5">Table</font></b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  4.             <select size="1" name="Table">
  5.             <option>Adapters/Cards</option>
  6.             <option>AdapterCards/Subform</option>
  7.             <option>Hardware Assets</option>
  8.             <option>Internal Drives</option>
  9.             <option>internaldrives/subform</option>
  10.             <option>Maintenance History</option>
  11.             <option>Ssupportlogs</option>
  12.             </select></p>
  13.             <p align="center"><b><font size="5" color="#000080">Fields<select size="1" name="Field">
  14.             </select></font></b></p>
  15.             <p align="center"><b>
  16.             <font color="#000080" face="Poor Richard" size="5">Content</font></b>
  17.             <input type="text" name="Search" size="38" tabindex="1"></p>
  18.             <p align="center">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  19.             <font size="3">
  20.             <input type="submit" value="Search" name="Submit" tabindex="2" style="font-weight: 700"></font></p>
  21.         </form>
May 19 '08 #6

jeffstl
Expert 100+
P: 432
Are you talking about doing this in the local users browser?

For example they select something from a drop down and the next drop down is instantly populated ? You may need to use javascript to have that happen and the values of the drop downs would have to be hard coded into the HTML.

Not sure how you plan to do this as far as process. (Could be javascript, could be submission to the "next" page via asp. Going to depend alot on that.

Here is some ASP on listing column names in a record set though (That you could get by querying one of your tables).

Expand|Select|Wrap|Line Numbers
  1. for each f in RS.Fields
  2.    Response.Write(f.Name & "<br>")
  3. next
  4.  
May 19 '08 #7

P: 68
I thought of it as being done in the user browser where i hardcode the field names after which i'll pass the request to the an asp page which would search the specified table field for the relavant data.
May 19 '08 #8

jeffstl
Expert 100+
P: 432
I thought of it as being done in the user browser where i hardcode the field names after which i'll pass the request to the an asp page which would search the specified table field for the relavant data.
What I was just trying to say was that if you want to choose a table, then have a Second drop down list populate with the columns from that table without a reload of the page you would need to use javascript.

Ok. However if you are not using the database to pull your field names (using below code from a recordset) from if anything ever changes, keep in mind you will also then have to change your asp page <option> values.
Expand|Select|Wrap|Line Numbers
  1. for each f in RS.Fields
  2.    Response.Write("<option>" & f.Name & "</option>")
  3. next
  4.  
In that case though since you just want to grab a value from a form this should be relatively simple.

On your search.asp page simply get the values of the combo box's into an SQL string that produces the search results. The below code assumes you have a connection string already set up.

Expand|Select|Wrap|Line Numbers
  1. 'for search.asp
  2. dim MyTable,StrSQL
  3. dim MyRS
  4. Set MyRS = server.CreateObject("Adodb.Recordset")
  5. MyTable = request.form("Table") 'this is your combo box (drop down)
  6. StrSQL = "Select * from " & MyTable & ""
  7. MyRS.Open strSQL, DataConnection
  8.  
  9.  
Is this what you mean?
May 19 '08 #9

P: 68
I do appriciate you responding promptly to my post with insightfull help and ideas.

The java script function is what i think i need help with. If you noticed in the html codes i sent, there are two combo boxes; one named table which holds the tables in the db and the other named fields which should hold the fields in the selected table only. i want to select a table and have a script deliver the fields straight to combo box where i can then select the field, type the search text and click submit to call the search.asp page to do it's thing.
the function should do client side processing rather than server side
May 19 '08 #10

jeffstl
Expert 100+
P: 432
Ok. Well that is a javascript function that is available in a few places. This is one I have used alot that is an excellent example of how to do something like that.

Javascript Kit Drop Down Example

Here is the modified version of that showing how you would use it. Be aware however that this example is set up to redirect the user on the last selection to a website value. Instead just have your button click submit the form.
Expand|Select|Wrap|Line Numbers
  1. <form name="MyForm">
  2. <select name="MyTables" size="1" onChange="displaysub()">
  3. <option value="#">This is a place Holder text </option>
  4. </select>
  5. <input type="button" name="test" value="Go!"
  6. onClick="gothere()">
  7. </form>
  8.  
  9. <script>
  10. <!--
  11.  
  12. //MAIN DROP DOWN
  13.  
  14. var category=new Array()
  15. category[0]=new Option("SELECT A TABLE ", "") 
  16. category[1]=new Option("TABLE1 NAME", "combo1")
  17. category[2]=new Option("TABLE2 NAME", "combo2")
  18. category[3]=new Option("TABLE3 NAME", "combo3")
  19.  
  20. //SUB DROP DOWNS
  21.  
  22. var combo1=new Array()
  23. combo1[0]=new Option("COLUMN NAME FROM TABLE 1","WHATEVERVALUE")
  24. combo1[1]=new Option("BACK TO TABLES","")   
  25.  
  26. var combo2=new Array()
  27. combo2[0]=new Option("COLUMN NAME FROM TABLE 2","WHATEVERVALUE")
  28. combo2[1]=new Option("BACK TO TABLES","")  
  29.  
  30. var combo3=new Array()
  31. combo3[0]=new Option("COLUMN NAME FROM TABLE 3","WHATEVERVALUE")
  32. combo3[1]=new Option("BACK TO TABLES","")  
  33.  
  34. var curlevel=1
  35. var cacheobj=document.MyForm.MyTables
  36.  
  37. function populate(x){
  38. for (m=cacheobj.options.length-1;m>0;m--)
  39. cacheobj.options[m]=null
  40. selectedarray=eval(x)
  41. for (i=0;i<selectedarray.length;i++)
  42. cacheobj.options[i]=new Option(selectedarray[i].text,selectedarray[i].value)
  43. cacheobj.options[0].selected=true
  44.  
  45. }
  46.  
  47. function displaysub(){
  48. if (curlevel==1){
  49. populate(cacheobj.options[cacheobj.selectedIndex].value)
  50. curlevel=2
  51. }
  52. else
  53. gothere()
  54. }
  55.  
  56.  
  57. function gothere(){
  58. if (curlevel==2){
  59. if (cacheobj.selectedIndex==cacheobj.options.length-1){
  60. curlevel=1
  61. populate(category)
  62. }
  63. else
  64. location=cacheobj.options[cacheobj.selectedIndex].value
  65. }
  66. }
  67.  
  68. populate(category)
  69.  
  70. //-->
  71. </script>
  72.  
May 19 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.