{A_K_A} Posted March 15, 2012 Share Posted March 15, 2012 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 comment Share on other sites More sharing options...
justsomeguy Posted March 15, 2012 Share Posted March 15, 2012 You can use a join query to return rows that are built from multiple tables. You would join the tables on the ID. http://www.w3schools.com/sql/sql_join.asp Link to comment Share on other sites More sharing options...
{A_K_A} Posted March 16, 2012 Author Share Posted March 16, 2012 (edited) OK, so I put the 3 tables in separate vars like above and then use SQL a join query? Thanks for the help. I will try to get this working! Edited March 16, 2012 by {A_K_A} Link to comment Share on other sites More sharing options...
{A_K_A} Posted March 16, 2012 Author Share Posted March 16, 2012 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 comment Share on other sites More sharing options...
justsomeguy Posted March 16, 2012 Share Posted March 16, 2012 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 comment Share on other sites More sharing options...
{A_K_A} Posted March 20, 2012 Author Share Posted March 20, 2012 Thanks for the help and info! Link to comment Share on other sites More sharing options...
salman.pirani Posted August 22, 2012 Share Posted August 22, 2012 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 comment Share on other sites More sharing options...
salman.pirani Posted August 22, 2012 Share Posted August 22, 2012 plz specifiy wher am creating mistake Link to comment Share on other sites More sharing options...
justsomeguy Posted August 22, 2012 Share Posted August 22, 2012 Don't post your questions in other people's topics, you already have a topic with this question. Link to comment Share on other sites More sharing options...
salman.pirani Posted August 23, 2012 Share Posted August 23, 2012 but am not getting the reply plz help me Link to comment Share on other sites More sharing options...
Recommended Posts