I have a front-end HTML page which is querying an MS Access Database.
HTML:
<input class="textbox" id="searchValue" maxlength="100" name="KeywordSearch" onclick="this.value='';" size="50" type="text" value="Enter Your Keyword Here" />
<input class="textbox" id="ForCLNo" name="CLNum" type="text" onclick="this.value='';" size="25" type="text" value="CL Number"/>
<input class="button" name="Search" onclick="searchEngineSearch();" type="button" value="Search" /></p>
Want to execute this query:
SELECT * FROM MasterTable where CLNo = test1 AND Query = test2;
For the query, SELECT * FROM MasterTable where Query LIKE test2
, I have created the ADODB object as follows:
var adVarWChar = 202;
var adParamInput = 1;
var pad = "C:\\Users\\Rik\\Desktop\\Project\\MyTable.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 MasterTable where Query LIKE test2";
cm.Parameters.Append(cm.CreateParameter(
"test2",
adVarWChar,
adParamInput,
255,
"%" + document.getElementById("searchValue").value + "%"));
var rs = cm.Execute();
and the correct result is obtained.
Not sure how to rewrite the cm.Parameters.Append(cm.CreateParameter()) function to incorporate more than one WHERE condition in an SQL query [SELECT * FROM MasterTable where CLNo = test1 AND Query = test2;
].
Please Help :)
You're creating one parameter within the Parameters collection already... you just need to create one more:
cm.CommandText = "SELECT * FROM MasterTable where Query LIKE test1 OR Query LIKE Test2";
cm.Parameters.Append(cm.CreateParameter(
"test1",
adVarWChar,
adParamInput,
255,
"%" + document.getElementById("searchValue").value + "%"));
cm.Parameters.Append(cm.CreateParameter(
"test2",
adVarWChar,
adParamInput,
255,
"%" + document.getElementById("searchValue").value + "%"));
var rs = cm.Execute();
cm.Parameters
is a collection, meaning it's capable of holding any reasonable number of its item type. For each piece of parameter code above, you append a newly created parameter into the collection. The entire collection is then used to resolve the SQL statement.