Connect to Access 2007 with JavaScript

awhatley · April 3rd, 2010, 06:50 PM

I am trying to create a JavaScript that will take the inputs of a Web form, generate a query from those inputs, query a database, and display the results of the query on the page. Right now I just want to create a script that will run a hard-coded query and display the results when I push a Submit button. The database is an Access 2007 file (.accdb) that is stored on the server where I am building the page.

Here is my page so far:

Code:

<!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>School of CIS TCPT</title>

<script language="JavaScript" > 

function getSubmit() 
{ 
var ClassID;
var CreditHours;

var cn = new ActiveXObject("ADODB.Connection");
var strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = classes.accdb;Persist Security Info=False"; 
var rs = new ActiveXObject("ADODB.Recordset");
var SQL = "SELECT 0910.ClassID, 0910.CreditHours FROM 0910"; 
var resultSet = strConn.query(SQL);
cn.Open(strConn); 
rs.Open(SQL, cn); 
ClassID.value = rs(0); 
CreditHours.value = rs(1);
rs.Close(); 
cn.Close(); 

} 

</script> 

<link href="style.css" rel="stylesheet" type="text/css" />

<style type="text/css">
<!--
.style2 {color: #c80000}
-->
</style>
</head>

<body>

<table width="800" border="0" align="center" cellpadding="0" cellspacing="0" class="bodystyle">
  <tr>
    <td class="header"><p align="center">School of Computer and Information Sciences Advising System <span 

class="style2">Transfer Course Plan Tool</span></p>    </td>
  </tr>
  <tr>
    <td height="10">&nbsp;</td>
  </tr>
  <tr>
    <td>Use the form below to enter your information and preferences, then click &quot;Submit&quot; to 

generate a course plan. Fields with an asterisk are required.</td>
  </tr>
  <tr>
    <td height="10">&nbsp;</td>
  </tr>
  <tr>
    <td><table width="615" border="0" align="center" cellpadding="0" cellspacing="0">
      <tr>
        <td height="35" colspan="2">* Bulletin: &nbsp;
         <select name="bulletin" id="bulletin" tabindex="1">
           <option selected="selected" disabled="disabled">Select Bulletin Year</option>
           <option value="0910">2009-2010</option>
         <option value="1011" disabled="disabled">2010-2011</option>
         </select>          </td>
        <td width="340" height="35">* Starting Semester: &nbsp;
          <select name="semester" id="semester" tabindex="2">
            <option selected="selected" disabled="disabled">Select Semester</option>
            <option value="fall">Fall</option>
            <option value="spring">Spring</option>
          </select></td>
      </tr>
      <tr>
        <td height="35" colspan="2">* Major: &nbsp;
          <label>
          <select name="major" id="major" tabindex="3">
            <option selected="selected" disabled="disabled">Select Major</option>
            <option value="cs" disabled="disabled">Computer Science</option>
            <option value="is" disabled="disabled">Information Science</option>
            <option value="it">Information Technology</option>
          </select>
          </label></td>
        <td height="35">* Select Concentration: &nbsp;
          <label>
          <select name="concentration" id="concentration" tabindex="4">
            <option selected="selected" disabled="disabled">Select Concentration</option>
            <option value="data" disabled="disabled">Data Management</option>
            <option value="web">Web Publishing</option>
            <option value="networking" disabled="disabled">Networking</option>
            <option value="forensics" disabled="disabled">Digital Forensics</option>
          </select>
          </label></td>
      </tr>
      <tr>
        <td width="150" height="35">* Are you Calculus ready?          </td>
        <td width="125" height="35"><form id="form1" name="form1" method="post" action="">
            <label>
              <input type="radio" name="radio" id="calcready" value="yes" tabindex="5" />Yes              

</label> &nbsp;<label>
              <input type="radio" name="radio" id="calcready" value="no" tabindex="6" />No
              </label>
          </form></td>
        <td height="35">Preferred Hours per Semester: &nbsp;
          <label>
          <select name="hours" id="hours" tabindex="7">
            <option selected="selected" disabled="disabled">Select Hours</option>
            <option value="parttime">&gt;12 (Part Time)</option>
            <option value="regfulltime">12 - 15 (Full Time)</option>
            <option value="extrafulltime">16+ (Full Time)</option>
          </select>
          </label></td>
      </tr>
      <tr>
        <td height="35" colspan="3"><div align="center">
          <input type="reset" name="reset" id="reset" value="Reset" accesskey="R" tabindex="9" />
        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <label>
        <input type="button" value="Submit" accesskey="S" tabindex="10" onclick="getSubmit()" />
        </label>
        </div></td>
      </tr>
    </table></td>
  </tr>
  <tr>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
  </tr>
</table>

</body>

</html>

I tried getting a pop-up message box to display the results, but when I clicked "Submit," nothing happened.

Then I tried "alert("test");" on the Submit button and still nothing happened. So I tried putting a "document.write("Hello World!");" into the page to see if JavaScript itself was even working, and the Hello World script DID WORK. So obviously, something is wrong with my getSubmit() function.

Help? Ideas? How do I make this work?

joefawcett · April 4th, 2010, 03:19 AM

Your page has three main problems, firstly it will only work if it's run from a browser that's on the server wherer the database exists, secondly the page would have to be in the same folder as the database and lastly security permissions would have to be very low as to allow access to the database file.

Is it your intention to only run this page from the web server locally? It's a strange scenario.

awhatley · April 4th, 2010, 09:04 AM

The page and the database are in the same folder.

The scenario I am trying to create is a Web-based app where students at my school can input a few bits of information about themselves (current bulletin year, major, concentration, etc.) and have a sample course plan (what classes to take in which semester for the next 4 years) generated for them. The database doesn't need to be blocked from anyone's view, because it only contains information which is already published in the University's bulletin in print and online. It is not sensitive information.

joefawcett · April 4th, 2010, 12:37 PM

Fine, then you will need to use server-side coding that runs on the server where the database resides, not client-side which runs in the browser on the machine that requests the page. If you want to go the Microsoft route then ASP.NET is probably best, otherwise Jave or PHP.