Dynamic SQL in Classic ASP query using JQuery

Question

I think this should be possible but as my experience of JQuery is very limited I'm not sure how to achieve this.

I have a block of code which queries an SQL database, I want to be able to dynamically "change" the query (using WHERE [Printer Make] =) using buttons or a typed in input box. How would I achieve this?

Here is my current code for a static query:

sql = "SELECT * FROM [Printer_Consumables] "
rs.open sql,conn
response.write("<div id='table-scroll'><p>Current Stock Levels</p>")
response.write("<table class='tbl'><tr><th>Printer</th><th>Item</th><th>Stock Level</th></tr>")
i = 0
while not rs.eof
    if rs("Number") = 1 then
        stocklevel = "#0000FF;"
    elseif rs("Number") = 0 then
        stocklevel = "#FF0000;"
    else
        stocklevel = "#008C00;"
    end if
    If i Mod 2 = 1 Then
        response.write("<tr style='background: #FFFFFF;' class='small'>")
    Else
        response.write("<tr style='background: #CCCCCC;' class='small'>")
    End If
    response.write("<td>" & rs("Printer Make") & " " & rs("Printer Model") & "</td><td>" & rs("Type") & "</td><td style='color:" & stocklevel & ";'>" & rs("Number") & "</td></tr>")
    rs.movenext
    i = i + 1
wend
response.write("</table></div>")
rs.close

This looks `classic asp` -- add that tag and you could get some more views. I don't think you need to use `jquery` for this -- just throw a `form` on the page, add the `input` box and `submit` button, posting the value to the same page. Then create a variable and set it equal to that value (i.e. `somevariable = request.form("yourinputfieldname")`). Then you just need to add your `where` criteria -- look up parameterized queries (http://stackoverflow.com/questions/7654446/parameterized-query-in-classic-asp) — sgeddes, Sep 17 '14 at 14:45
It sure is classic asp, outdated I know but I'm so used to it now. I know this is possible with javascript as my predecessor created something but I can't figure out how it works, JQuery is a lot smoother and thought I could use it to dynamically change the query without reloading the page (just reloading a div or frame) — Hyperjase, Sep 17 '14 at 14:52
You can definitely use `jquery` to do that, but you'll need to look up `ajax` as well. They can work together to do what you're referring to. Here's the first google post I see that looks pretty thorough: http://www.mikesdotnetting.com/Article/98/Ajax-with-Classic-ASP-using-jQuery — sgeddes, Sep 17 '14 at 14:55
Ah yes - ajax, something I have very little experience with, but I'll go down that route as it may be a better combination to achieve my end result. Many thanks! — Hyperjase, Sep 17 '14 at 15:02

score 0 · Answer 1 · answered Mar 28 '15 at 17:04

put your current code in a new page naming myfunction.asp and edit it to change sql condition when receives condition like this:

sql = "SELECT * FROM [Printer_Consumables] "

if request("PrinterMake")<>"" then
sql = "SELECT * FROM [Printer_Consumables] where [Printer Make]='"&request("PrinterMake")&"'"
end if

rs.open sql,conn
response.write("<div id='table-scroll'><p>Current Stock Levels</p>")
response.write("<table class='tbl'><tr><th>Printer</th><th>Item</th><th>Stock Level</th></tr>")
i = 0
while not rs.eof
    if rs("Number") = 1 then
        stocklevel = "#0000FF;"
    elseif rs("Number") = 0 then
        stocklevel = "#FF0000;"
    else
        stocklevel = "#008C00;"
    end if
    If i Mod 2 = 1 Then
        response.write("<tr style='background: #FFFFFF;' class='small'>")
    Else
        response.write("<tr style='background: #CCCCCC;' class='small'>")
    End If
    response.write("<td>" & rs("Printer Make") & " " & rs("Printer Model") & "</td><td>" & rs("Type") & "</td><td style='color:" & stocklevel & ";'>" & rs("Number") & "</td></tr>")
    rs.movenext
    i = i + 1
wend
response.write("</table></div>")
rs.close

So you need a new page which have a div with a certain ID that includes your function file as default with no condition. Then you can reload the content of this div using Jquery load where sending condition to myfunction.asp to retrieve new sort of data:

<html>
<head>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
</head>
<body>
<div id="mydiv">
<!--#include file="myfunction.asp"-->
</div>
<br>
<a onclick="changecondition('SONY')">SONY</a>

<script type="text/javascript">
function changecondition(brand){
$("#mydiv").load("myfunction.asp",{PrinterMake:brand});
}
</script>

</body>
</html>

Dynamic SQL in Classic ASP query using JQuery

1 Answers1