Create table dynamically using Javascript to display a database SELECT query output

Question

I have the following code with me:

<!doctype html>
<html>
<title>Search</title>
<script type="text/javascript">

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 + ", ");
        document.write(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 + ".</p>");
        var row = row.parentNode.rows[ ++idx ];
            document.write(rs.fields(1).value + ".</p>");
            rs.MoveNext();
        }
    }
    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>

Please help me modify the code in such a way that on clicking the Search button, a new table has to be created with 16 columns with appropriate column header and the search result should be properly inserted in the table rows.

Is there a specific question? All I see here are you listing off project requirements. If you have a specific question about this code (e.g. is it going wrong in some specific way and you need to understand why?) then you should make the question specifically about that specific thing and give us as many details as you can about what you're confused about, what you expected to happen, what is happening, etc. — Jimbo Jonny
– Jimbo Jonny, Commented Jan 2, 2016 at 19:37
Yeah. I have some specific questions. For problem # 3, I'm not sure how to modify document.getElementById("searchTerm").value to consider the entire string (including white-spaces). For problem # 2, I tried with changing the SELECT query to "SELECT * FROM ImportFromExcel WHERE col1 LIKE ? OR col2 LIKE ? OR col3 LIKE ?; ...", but it's not working. — Aziiz Pc
– Aziiz Pc, Commented Jan 2, 2016 at 20:08
For problem # 1, Tried with the following function for table creation and data insertion, but failing. function addTable() { var table = document.createElement('TABLE'); table.border='1'; var tableBody = document.createElement('TBODY'); table.appendChild(tableBody); for (var i=0; i<=rs.eof; i++){ var tr = document.createElement('TR'); tableBody.appendChild(tr); for (var j=0; j<16; j++){ var td = document.createElement('TD'); td.width='75'; td.appendChild(document.createTextNode("<p>" + rs.fields(1).value + ", "); tr.appendChild(td); } } myTableDiv.appendChild(table); } — Aziiz Pc
– Aziiz Pc, Commented Jan 2, 2016 at 20:11
Those are definitely separate questions. They're not even the same language as each other. You should make separate questions for each specific problem you have and attempt to reduce them each to as simple/little of code as still illustrates the problem you have. On another note...it looks like you are literally passing strings from the client side to run as database stuff on the server side. If this is anything other than an in-house app that is not public that WILL end in total and complete hacking where someone else completely takes over your server. — Jimbo Jonny
– Jimbo Jonny, Commented Jan 2, 2016 at 20:19

WhiteHat · Accepted Answer · 2016-01-06 19:06:59Z

1

Here's one way to accomplish, build a string with the HTML table...
Also, I loop the fields collection to get the column names and values.

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

    var table = '';
    var tableRow = '';
    var headings = true;

    if (rs.EOF) {
        document.write("<p>No data found.</p>");
    } else {
        while (!rs.EOF) {
          if (headings) {
            tableRow = '';
            for (var i = 0; i < rs.fields.count; i++) {
              switch (i + 1) {
                case 1:
                case 3:
                case 4:
                case 6:
                case 8:
                case 9:
                  tableRow = tableRow + '<td>' + rs.fields(i).name + '</td>';
                  break;
              }
            }
            tableRow = '<tr>' + tableRow + '</tr>';
            table = table + tableRow;
            headings = false;
          }
          tableRow = '';
          for (var i = 0; i < rs.fields.count; i++) {
              switch (i + 1) {
                case 1:
                case 3:
                case 4:
                case 6:
                case 8:
                case 9:
                  tableRow = tableRow + '<td>' + rs.fields(i).value + '</td>';
                  break;
              }
          }
          tableRow = '<tr>' + tableRow + '</tr>';
          table = table + tableRow;
          rs.MoveNext();
        }
        document.write('<table>' + table + '</table>');
    }
    rs.Close();
    cn.Close();
}

edited Jan 6, 2016 at 19:06

answered Jan 4, 2016 at 13:46

WhiteHat

61.3k7 gold badges54 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aziiz Pc Over a year ago

This works. Thank you so much man :) What if I want to display only a set of columns instead of all of them? eg: I want to see only 1,3,4,6,8, and 9th column data.

WhiteHat Over a year ago

you could use a switch statement, see edit. I add 1 so column numbers aren't confusing. (i = 0 = column 1)

Collectives™ on Stack Overflow

Create table dynamically using Javascript to display a database SELECT query output

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related