How to search an MS Access Database Table using Javascript?

Question

I have an MS Access Database Table 'ImportFromExcel' having 16 columns. When 'Search' key in the webpage is pressed after entering keyword(s), it should go to table and check if the keyword(s) is/are present anywhere in the table. If the keyword is present in a row, that row should be printed. Else, it should throw a message.

Code:

<!doctype html>
<html>
<title>Search</title>

<script type="text/javascript">

function query()
{
    var pad = "C:\\Users\\azi!z\\Desktop\\Project\\Test.accdb";
    var cn = new ActiveXObject("ADODB.Connection");
    var strConn = "Provider=microsoft.ace.oledb.12.0;Data Source=" + pad;
    cn.Open(strConn);
    var rs = new ActiveXObject("ADODB.Recordset");
    var SQL = “SELECT * FROM ImportFromExcel where TestCase ALIKE '%searchTerm%'”;
    rs.Open(SQL, cn);
    if(!rs.bof) 
    {
        rs.MoveFirst();
        if(!rs.eof)
        {
            document.write("<p>" + rs.fields(1).value + ", ");
            document.write(rs.fields(2).value + ", ");
            document.write(rs.fields(3).value + ", ");
            document.write(rs.fields(4).value + ", ");
            document.write(rs.fields(5).value + ", ");
            document.write(rs.fields(6).value + ", ");
            document.write(rs.fields(7).value + ", ");
            document.write(rs.fields(8).value + ", ");
            document.write(rs.fields(9).value + ", ");
            document.write(rs.fields(10).value + ", ");
            document.write(rs.fields(11).value + ", ");
            document.write(rs.fields(12).value + ", ");
            document.write(rs.fields(13).value + ", ");
            document.write(rs.fields(14).value + ", ");
            document.write(rs.fields(15).value + ", ");
            document.write(rs.fields(16).value + ".</p>");
        }
    }
    else
    {
        document.write("No data found");
    }
    rs.Close();
    cn.Close();
}

</script>
</head>

<body>
<form method="get" name="SearchEngine" target="_blank">
<p style="text-align: center;"><span style="font-family:times new roman,times,serif;"><span style="font-size: 36px;"><strong>EA Search Engine</strong></span></span></p>

<p style="text-align: center;">&nbsp;</p>

<p style="text-align: center;"><input maxlength="300" id="searchTerm" name="KeywordSearch" size="100" type="text" value="Enter Your Keyword Here" /></p>

<p style="text-align: center;">&nbsp;</p>

<p style="text-align: center;"><input name="Search" type="button" value="Search" onclick="query();" /></p>
</form>
</body>
</html>

Tried the query in MS Access and it's giving the expected result (Thanks to @HansUp). Seems to be a problem with the JavaScript piece. Please help :)

this question is fine the way it is , but just throwing out there that you should not leave database code like that in an unsecured page, some one could easily change "SELECT" into "DROP TABLE" or "DELETE" — Scott Selby, Dec 28 '15 at 17:39
In an `ADODB.Recordset` which contains 16 columns, the first column is `rs.fields(0)` and the last is `rs.fields(15)` — HansUp, Dec 28 '15 at 17:48
Changed the column number as per @HansUp 's comment. Not working :( . Can you please look in to the HTML as well? — Aziiz Pc, Dec 28 '15 at 17:52
Have you tried putting an `alert("Executing function.");` statement at the beginning of the function to verify that is it actually being executed? — Gord Thompson, Dec 29 '15 at 00:56
Tried putting alert("Executing function.");. But, not getting any alert. So there must be a problem in function call? @GordThompson — Aziiz Pc, Dec 29 '15 at 16:35
Yes, it sounds like the function is not getting triggered when you click the button. Try hitting [F12] (or whatever the hotkey is for "Developer Tools" if you are not using Internet Explorer) and then check the Console for error messages. — Gord Thompson, Dec 29 '15 at 16:55
Finally, I'm able to see that the function is being executed. But, the '%searchTerm%' doesn't work. Am getting the message "No data found". Also, help me modify the code to search across all the rows in table. Presently, its fetching data from only the first row. Thanks :) @GordThompson — Aziiz Pc, Dec 30 '15 at 15:42

Gord Thompson · Accepted Answer · 2015-12-30T20:10:20.267

So now that you've gotten the JavaScript function to actually execute, you need to make the following changes:

Hard-coding '%searchTerm%' into the script will only search for that literal text. You want to incorporate the value of the "searchTerm" <input> box on the form.
In order to do that you need to use a parameterized query.
You also want to loop through the Recordset to retrieve all of the rows returned, instead of just the first one.

So, your JavaScript code should look more like this:

function query() {
    var adVarWChar = 202;
    var adParamInput = 1;

    var pad = "C:\\Users\\azi!z\\Desktop\\Project\\Test.accdb";
    var cn = new ActiveXObject("ADODB.Connection");
    var strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pad;
    cn.Open(strConn);
    var cm = new ActiveXObject("ADODB.Command");
    cm.ActiveConnection = cn;
    cm.CommandText = "SELECT * FROM ImportFromExcel where TestCase LIKE ?";
    cm.Parameters.Append(cm.CreateParameter(
            "?", 
            adVarWChar, 
            adParamInput, 
            255, 
            "%" + document.getElementById("searchTerm").value + "%"));
    var rs = cm.Execute();  // returns ADODB.Recordset object
    if (rs.EOF) {
        document.write("<p>No data found.</p>");
    } else {
        while (!rs.EOF) {
            document.write("<p>" + rs.fields(0).value + ", ");
            // (list of fields truncated for clarity)
            document.write(rs.fields(1).value + ".</p>");
            rs.MoveNext();
        }
    }
    rs.Close();
    cn.Close();
}

Thank you very much @Gord Thompson. Its working :) Really appreciate if you help me rewrite the code to include: 1. SELECT query has to be rewritten. Want to search the entire table for ? instead of TestCase column. 2. The search is done based on the keyword 'searchTerm'. I need to modify the code in such a way that the HTML text box accepts more than one keyword (a string) and search the table accordingly. The keywords the we enter need not be in same order in the table cell. It should be somewhere, but within the cell. Thank you very much :) — Aziiz Pc, Jan 01 '16 at 17:59
Your additional requirements are beyond the scope of this question. You should: **(1)** [Accept](http://meta.stackexchange.com/a/5235/238021) this answer, **(2)** Make some effort to implement the additional requirements yourself, and then **(3)** [Ask a new question](http://stackoverflow.com/questions/ask), following the guidelines in "[ask]", if you need *specific* assistance in implementing the changes you described. — Gord Thompson, Jan 01 '16 at 19:21

How to search an MS Access Database Table using Javascript?

1 Answers1