Using Access As a web database

**jeffstl** · May 12 '08, 03:12 PM

Originally posted by KingKen

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.

**KingKen** · May 15 '08, 03:10 PM

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_His tory].HardwareAssetI D'.

/DMS/Update.asp, line 25

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

<HTML><HEAD><TI TLE>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.CreateOb ject("ADODB.Con nection")
DataConnection. Open ("Provider=Micr osoft.Jet.OLEDB .4.0; Data Source="& Server.MapPath( "/DMS/testBase.mdb"))

'Create an ADO recordset object
Set RsAddRecords = Server.CreateOb ject("ADODB.Rec ordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT SELECT [Maintenance_His tory].HardwareAssetI D, [Maintenance_His tory].DateReported, [Maintenance_His tory].ProblemDescrip tion, [Maintenance_His tory].Cause, [Maintenance_His tory].SolutionDescri ption, [Maintenance_His tory].PerformedBy, [Maintenance_His tory].DateCompleted, [Maintenance_His tory].Comments, [Maintenance_His tory].Department, [Maintenance_His tory].Status FROM [Maintenance_His tory];"

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

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

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

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

'Add a new record to the recordset
RsAddRecords.Fi elds("HardwareA ssetID") = Request.Form("A sset_ID")
RsAddRecords.Fi elds("DateRepor ted") = Request.Form("D ate_Reported")
RsAddRecords.Fi elds("ProblemDe scription") = Request.Form("I ssue_Reported")
RsAddRecords.Fi elds("Cause") = Request.Form("C ause")
RsAddRecords.Fi elds("SolutionD escription") = Request.Form("A ction_Taken")
RsAddRecords.Fi elds("Performed By") = Request.Form("S erviced_By")
RsAddRecords.Fi elds("DateCompl eted") = Request.Form("D ate_Completed")
RsAddRecords.Fi elds("Comments" ) = Request.Form("C omments")
RsAddRecords.Fi elds("Departmen t") = Request.Form("D epartment")
RsAddRecords.Fi elds("Status") = Request.Form("S tatus")

'Write the updated recordset to the database
RsAddRecords.Up date

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

<p align="center">  <p align="center"> Data was successfully added<p align="center">  <p align="center">
</BODY>
</HTML>

**jeffstl** · May 15 '08, 03:14 PM

You have 2 SELECTS. You only need 1. There could be more issues with your SQL but try that first

[code=asp]
'take out one of the SELECT
strSQL = "SELECT [Maintenance_His tory].HardwareAssetI D, [Maintenance_His tory].DateReported, [Maintenance_His tory].ProblemDescrip tion, [Maintenance_His tory].Cause, [Maintenance_His tory].SolutionDescri ption, [Maintenance_His tory].PerformedBy, [Maintenance_His tory].DateCompleted, [Maintenance_His tory].Comments, [Maintenance_His tory].Department, [Maintenance_His tory].Status FROM [Maintenance_His tory];"
[/code]

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:

[code=asp]
'take out one of the SELECT
strSQL = "SELECT * FROM Maintenance_His tory"
[/code]

**KingKen** · May 15 '08, 03:27 PM

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

**KingKen** · May 19 '08, 03:00 PM

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.

Code:

<form method="POST" action=search.asp>
			<p align="center"><b>
			<font color="#000080" face="Poor Richard" size="5">Table</font></b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
			<select size="1" name="Table">
			<option>Adapters/Cards</option>
			<option>AdapterCards/Subform</option>
			<option>Hardware Assets</option>
			<option>Internal Drives</option>
			<option>internaldrives/subform</option>
			<option>Maintenance History</option>
			<option>Ssupportlogs</option>
			</select></p>
			<p align="center"><b><font size="5" color="#000080">Fields<select size="1" name="Field">
			</select></font></b></p>
			<p align="center"><b>
			<font color="#000080" face="Poor Richard" size="5">Content</font></b>
			<input type="text" name="Search" size="38" tabindex="1"></p>
			<p align="center">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
			<font size="3">
			<input type="submit" value="Search" name="Submit" tabindex="2" style="font-weight: 700"></font></p>
		</form>

**jeffstl** · May 19 '08, 03:10 PM

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).

Code:

for each f in RS.Fields
   Response.Write(f.Name & "<br>")
next

**KingKen** · May 19 '08, 03:23 PM

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.

**jeffstl** · May 19 '08, 05:52 PM

Originally posted by KingKen

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.
[code=asp]
for each f in RS.Fields
Response.Write( "<option>" & f.Name & "</option>")
next
[/code]
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.

[code=asp]
'for search.asp
dim MyTable,StrSQL
dim MyRS
Set MyRS = server.CreateOb ject("Adodb.Rec ordset")
MyTable = request.form("T able") 'this is your combo box (drop down)
StrSQL = "Select * from " & MyTable & ""
MyRS.Open strSQL, DataConnection

[/code]

Is this what you mean?

**KingKen** · May 19 '08, 06:51 PM

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

**jeffstl** · May 19 '08, 07:48 PM

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.

Code:

<form name="MyForm">
<select name="MyTables" size="1" onChange="displaysub()">
<option value="#">This is a place Holder text </option>
</select>
<input type="button" name="test" value="Go!"
onClick="gothere()">
</form>

<script>
<!--

//MAIN DROP DOWN

var category=new Array()
category[0]=new Option("SELECT A TABLE ", "") 
category[1]=new Option("TABLE1 NAME", "combo1")
category[2]=new Option("TABLE2 NAME", "combo2")
category[3]=new Option("TABLE3 NAME", "combo3")

//SUB DROP DOWNS

var combo1=new Array()
combo1[0]=new Option("COLUMN NAME FROM TABLE 1","WHATEVERVALUE")
combo1[1]=new Option("BACK TO TABLES","")   

var combo2=new Array()
combo2[0]=new Option("COLUMN NAME FROM TABLE 2","WHATEVERVALUE")
combo2[1]=new Option("BACK TO TABLES","")  

var combo3=new Array()
combo3[0]=new Option("COLUMN NAME FROM TABLE 3","WHATEVERVALUE")
combo3[1]=new Option("BACK TO TABLES","")  

var curlevel=1
var cacheobj=document.MyForm.MyTables

function populate(x){
for (m=cacheobj.options.length-1;m>0;m--)
cacheobj.options[m]=null
selectedarray=eval(x)
for (i=0;i<selectedarray.length;i++)
cacheobj.options[i]=new Option(selectedarray[i].text,selectedarray[i].value)
cacheobj.options[0].selected=true

}

function displaysub(){
if (curlevel==1){
populate(cacheobj.options[cacheobj.selectedIndex].value)
curlevel=2
}
else
gothere()
}


function gothere(){
if (curlevel==2){
if (cacheobj.selectedIndex==cacheobj.options.length-1){
curlevel=1
populate(category)
}
else
location=cacheobj.options[cacheobj.selectedIndex].value
}
}

populate(category)

//-->
</script>