Jump to content

ASP & Access DB connection...help needed


Recommended Posts

Hi all,

<% Dim cnnSimple ' ADO connection Dim rstSimple ' ADO recordset Set cnnSimple = Server.CreateObject("ADODB.Connection") ' DSNLess cnnSimple.Open ("Provider= Microsoft.ACE.OLEDB.12.0;Data Source=" & Server.MapPath("\DB.accdb")) Set rstSimple = cnnSimple.Execute("SELECT * FROM People") 'Set rstSimple1 = cnnSimple.Execute("SELECT * FROM Address")%> <P> Connecting to Access with Ole connection </P><table border="1"> <% Do While Not rstSimple.EOF %> <tr> <td><%= rstSimple.Fields(0).Value %></td> <td><%= rstSimple.Fields(1).Value %></td> <td><%= rstSimple.Fields(2).Value %></td> </tr> <% rstSimple.MoveNext Loop %></table> <% rstSimple.Close Set rstSimple = Nothing cnnSimple.Close Set cnnSimple = Nothing%>
Sorry tried to attach but the upload failed. Anyway I have the above code to connect to my Access database. Now my database has 3 tables: People, Address and Contact. Currently the only way I know to display all three tables is to have 3 vars, loops etc. I was wandering if there was a better way to display all 3 Access tables? PS, all 3 tables have an ID field which I only want to display once (ID is primary key in people and a foreign key in the other two). Any help would be much appreciated :)
Link to post
Share on other sites

Yes it is now working! Here is the code if any one is intrested

Set rstSimple = cnnSimple.Execute("SELECT * FROM People, Address, Contact_NO where people.ID = Address.ID and people.ID = Contact_NO.ID")
I have one more question. I have created a html form to add records to the access DB and have moved the asp code in to a new file called diplay.asp. one, is this a good idea and two how can I call the file from the html page so the table is displayed?
Link to post
Share on other sites

It will be more efficient with the database if you specify the join conditions when you do the join, if the tables have a lot of records you'll notice a speed increase: SELECT * FROM People INNER JOIN Address ON People.ID = Address.ID INNER JOIN Contact_NO ON people.ID = Contact_NO.ID That will let the database create smaller temporary tables in memory, with only the rows that match, instead of creating a giant table with all possible combinations of the 3 and then filtering them after. If you want to run code from another file then you need to use an include statement to include that file on the page where you want the code to run.

Link to post
Share on other sites
  • 5 months later...

need ur help _____________________________________________________________________

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>Untitled Document</title><style type="text/css"><!--.heading {font-size: 24px;}--></style></head><body><blockquote> </blockquote><form action="post.asp" method="post"><table width="938" height="215" border="0" align="center">  <tr>    <td colspan="8" class="heading"><p align="center"><strong>The Aga Khan  University</strong></p>	  <p> </p>    <p align="center"><strong>Training Impact Form</strong><strong> </strong></p></td>  </tr>  <tr>    <td colspan="8"> </td>  </tr>  <tr>    <td width="228" colspan="2">Programme  Title:<label></label>    </td>    <td><label>	  <input type="ptitle"/>    </label></td>    <td colspan="2">Date:	  <label>	  <input type="pdate"/>	  </label></td>    <td colspan="2">Department:	  <input type="department"/></td>  </tr>  <tr>    <td colspan="8"><p align="right"><strong></strong></p></td>  </tr>  <tr>    <td colspan="2"> </td>    <td> </td>    <td> </td>    <td> </td>    <td> </td>    <td colspan="2"> </td>  </tr>  <tr>    <td>Supervisor’s Name:	  </td>    <td><input type="sname"/></td>    <td colspan="2">Supervisor’s Email:	  <input type="semail"/></td>    <td colspan="2">Supervisor’s Ext:</td>    <td colspan="2"><input type="sext"/></td>  </tr>  <tr>    <td colspan="6"> </td>    <td colspan="2"> </td>  </tr>  <tr>    <td colspan="8"><p><em>To be filled by Participant’s  immediate supervisor (at least) a month after the training to documen</em></p>    <p><em>t any change  in employee's knowledge, skills or attitudes as a result of the training:</em></p></td>  </tr>  <tr>    <td colspan="8"> </td>  </tr>  <tr>    <td colspan="8"><p>Rate the following questions  from 1 to 5:</p></td>  </tr>  <tr>    <td colspan="8"> </td>  </tr>  <tr>    <td colspan="8"><p><strong>(5-Strongly Agree 90 %, 4 - Agree 80 % , 3 - Somewhat Agree 70%,    2-Disagree 60% and 1- Strongly Disagree 50%)		   </strong></p></td>  </tr>  <tr>    <td colspan="8"> </td>  </tr>  <tr>    <td colspan="3">Q1.  Did you see any change as a result of training in employees’  </td>    <td> </td>    <td> </td>    <td> </td>    <td> </td>    <td> </td>  </tr>  <tr>    <td colspan="3">Knowledge</td>    <td width="89"><label>	  <input type="radio" name="q1a" value="5"/>    </label></td>    <td width="74"><label>	  <input type="radio" name="q1a" value="4"/>    </label></td>    <td width="72"><label>	  <input type="radio" name="q1a" value="3"/>    </label></td>    <td width="65"><label>	  <input type="radio" name="q1a" value="2"/>    </label></td>    <td width="212"><label>	  <input type="radio" name="q1a" value="1"/>    </label></td>  </tr>  <tr>    <td colspan="3">Skills</td>    <td><label>	  <input type="radio" name="q1b"  value="5"/>    </label></td>    <td><label>	  <input type="radio" name="q1b" value="4"/>    </label></td>    <td><label>	  <input type="radio" name="q1b" value="3"/>    </label></td>    <td><label>	  <input type="radio" name="q1b" value="2"/>    </label></td>    <td><label>	  <input type="radio" name="q1b" value="1"/>    </label></td>  </tr>  <tr>    <td colspan="3">Attitude  </td>    <td><label>	  <input type="radio" name="q1c" value="5"/>    </label></td>    <td><label>	  <input type="radio" name="q1c" value="4"/>    </label></td>    <td><label>	  <input type="radio" name="q1c" value="3"/>    </label></td>    <td><label>	  <input type="radio" name="q1c" value="2"/>    </label></td>    <td><label>	  <input type="radio" name="q1c"  value="1"/>    </label></td>  </tr>  <tr>    <td colspan="3"> </td>    <td> </td>    <td> </td>    <td> </td>    <td> </td>    <td> </td>  </tr>  <tr>    <td colspan="3"><p>Q2. Are you satisfied with  the employee’s overall performance specific to the training need addressed  in the programme?</p></td>    <td><label>	  <input type="radio" name="q2" value="5"/>    </label></td>    <td><label>	  <input type="radio" name="q2" value="4"/>    </label></td>    <td><label>	  <input type="radio" name="q2" value="3"/>    </label></td>    <td><label>	  <input type="radio" name="q2" value="2"/>    </label></td>    <td><label>	  <input type="radio" name="q2" value="1"/>    </label></td>  </tr>  <tr>    <td colspan="3"> </td>    <td> </td>    <td> </td>    <td> </td>    <td> </td>    <td> </td>  </tr>  <tr>    <td colspan="3"><p>Q3. Did the employee perform  differently on the job as a result of  this programme?</p></td>    <td><label>	  <input type="radio" name="q3" value="5"/>    </label></td>    <td><label>	  <input type="radio" name="q3" value="4"/>    </label></td>    <td><label>	  <input type="radio" name="q3" value="3"/>    </label></td>    <td><label>	  <input type="radio" name="q3" value="2"/>    </label></td>    <td><label>	  <input type="radio" name="q3" value="1"/>    </label></td>  </tr>  <tr>    <td colspan="3"> </td>    <td> </td>    <td> </td>    <td> </td>    <td> </td>    <td> </td>  </tr>  <tr>    <td colspan="8"><strong>(5-Strongly Agree 50 %, 4 - Agree  60% , 3-Somewhat Agree 70 %, 2 -Disagree 80  %  and 1-Strongly Disagree 90 %)	 </strong></td>  </tr>  <tr>    <td colspan="3"> </td>    <td> </td>    <td> </td>    <td> </td>    <td> </td>    <td> </td>  </tr>  <tr>    <td colspan="3">Q4.  Does the employee require further training in this area?		   </td>    <td><label>	  <input type="radio" name="q4" value="5"/>    </label></td>    <td><label>	  <input type="radio" name="q4" value="4"/>    </label></td>    <td><label>	  <input type="radio" name="q4" value="3"/>    </label></td>    <td><label>	  <input type="radio" name="q4" value="2"/>    </label></td>    <td><label>	  <input type="radio" name="q4" value="1"/>    </label></td>  </tr>  <tr>    <td colspan="3"> </td>    <td> </td>    <td> </td>    <td> </td>    <td> </td>    <td> </td>  </tr>  <tr>    <td colspan="8"><blockquote>	  <blockquote>	    <blockquote>		  <blockquote>		    <blockquote>			  <blockquote>			    <blockquote>				  <blockquote>				    <blockquote>					  <blockquote>					    <p>Q5. Any other comments? </p>					  </blockquote>				    </blockquote>				  </blockquote>			    </blockquote>			  </blockquote>		    </blockquote>		  </blockquote>	    </blockquote>	  </blockquote>    </blockquote></td>  </tr>  <tr>    <td colspan="8"><label>	  <textarea name="anyc" cols="100" rows="20"></textarea>    </label></td>  </tr>  <tr>    <td colspan="8"> </td>  </tr>  <tr>    <td colspan="2">Participant's Name :	  <label>	    <input type="pname"/>    </label></td>    <td>Employee ID:	  <label>	    <input type="emid"/>    </label></td>    <td>Ext :	  <label>	    <input type="ext"/>    </label></td>    <td> </td>    <td> </td>    <td> </td>  </tr>  <tr>    <td colspan="8">16</td>  </tr></table><input name="submit" type="submit" value="Done" /><input name="reset" type="reset" /></form></body></html>____________________________________________<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>Untitled Document</title></head><body><%set conn=Server.CreateObject("ADODB.Connection")conn.Provider="Microsoft.Jet.OLEDB.4.0"conn.Open "c:/webdata/northwind.mdb"%><%sql="INSERT INTO customers (programmetitle,date,"sql=sql &"department,supervisorsname,supervisorsemail,supervisorsext,q1a,q1b,q1c,q2,q3,q4,q5,participantsname,employeeid,ext)"sql=sql & " VALUES "sql=sql & "('" & Request.Form("ptitle") & "',"sql=sql & "'" & Request.Form("pdate") & "',"sql=sql & "'" & Request.Form("department") & "',"sql=sql & "'" & Request.Form("sname") & "',"sql=sql & "'" & Request.Form("semail") & "',"sql=sql & "'" & Request.Form("sext") & "',"sql=sql & "'" & Request.Form("q1a") & "',"sql=sql & "'" & Request.Form("q1b") & "',"sql=sql & "'" & Request.Form("q1c") & "',"sql=sql & "'" & Request.Form("q2") & "',"sql=sql & "'" & Request.Form("q3") & "',"sql=sql & "'" & Request.Form("q4") & "',"sql=sql & "'" & Request.Form("anyc") & "',"sql=sql & "'" & Request.Form("pname") & "',"sql=sql & "'" & Request.Form("emid") & "',"sql=sql & "'" & Request.Form("ext") & "')"on error resume nextconn.Execute sql,recaffectedif err<>0 then   Response.Write("No update permissions!")else   Response.Write("<h3>" & recaffected & " record added</h3>")end ifconn.close%></body></html>

Link to post
Share on other sites
Guest
This topic is now closed to further replies.
×
×
  • Create New...