如何使用Javascript搜索MS Access数据库表？ [英] How to search an MS Access Database Table using Javascript?
问题描述
我有一个MS Access数据库表'ImportFromExcel'有16列。当输入关键字后按下网页中的搜索键时，它应该转到表格并检查关键字是否存在于表格中的任何位置。如果关键字存在于一行中，则应打印该行。否则，它应该抛出一个信息。
代码：
<！doctype html>
< html>
< title>搜寻< / title>
< script type =text / javascript>
函数查询（）
{
var pad =C：\\ Users \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ \\\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（找不到数据）;
}
rs.Close（）;
cn.Close（）;
}
< / script>
< / head>
< body>
< form method =getname =SearchEnginetarget =_ blank>
< p style =text-align：center;>< span style =font-family：times new roman，times，serif;>< span style =font-size： 36px;>< strong> EA搜索引擎< / strong>< / span>< / span>< / p>
< p style =text-align：center;>& nbsp;< / p>
< p style =text-align：center;>< input maxlength =300id =searchTermname =KeywordSearchsize =100type =text value =在此输入您的关键字/>< / p>
< p style =text-align：center;>& nbsp;< / p>
< p style =text-align：center;>< input name =Searchtype =buttonvalue =Searchonclick =query（）; />< / p为H.
< / form>
< / body>
< / html>
尝试了MS Access中的查询，并给出了预期的结果（感谢@HansUp）。似乎是JavaScript片断的问题。请帮忙：）
现在您已经获得了JavaScript函数来实际执行，您需要进行以下更改：
$ b
-
在脚本中硬编码
'％searchTerm％'只会搜索该文字。您希望将searchTerm< input>框的值合并到表单中。 -
为了做到这一点，您需要使用参数化查询。
-
您还需要遍历Recordset来检索所有而不仅仅是第一个。
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.
所以，你的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，
％+文档。 getElementById（searchTerm）。value +％））;
var rs = cm.Execute（）; //返回ADODB.Recordset对象
if（rs.EOF）{
document.write（< p>找不到数据。< / p>）;
} else {
while（！rs.EOF）{
document.write（< p> + rs.fields（0）.value +，）;
//（为清晰起见，字段列表被截断）
document.write（rs.fields（1）.value +。< / p>）;
rs.MoveNext（）;
}
}
rs.Close（）;
cn.Close（）;
}
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;"> </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;"> </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 :)
So now that you've gotten the JavaScript function to actually execute, you need to make the following changes:
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();
}
这篇关于如何使用Javascript搜索MS Access数据库表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
