ASP Connector with WHERE clause and querystring

Suite and Optimus Suite 5
#1

Hi,

I’m using the (beta) ASP Connector with dhtmlxGrid and it works fine with the render_table method but I need to restrict my records by using a WHERE clause in the select statement. I guess I need to use render_sql e.g.:

grid.render_sql “SELECT * from tableA INNER JOIN tableB ON tableA.id=tableB.id”, “table_a_id”,“name,price”, “”, “”

but I’m not sure how to use a querystring value as the WHERE condition, i.e.

grid.render_sql "SELECT * from tableA WHERE catId = " & request.querystring(“catId”) & “”, “table_a_id”,“name,price”, “”, “”

would that work?
Many thanks,

#2

Hi,
I figured this out and here’s my solution if it helps anyone else:

Firstly you’ll need to pass the querystring variable to your dataprocessor file, so on the my grid page add the querystring you want to pass in the URL of the data processor file (in my case the querystring is “catId”)

[code] …
mygrid.init();
mygrid.loadXML(“01_grid_sql.asp?catId=<%=request.querystring(“catId”)%>”);

var dp = new dataProcessor("01_grid_sql.asp?catId=<%=request.querystring("catId")%>");
dp.init(mygrid);[/code]

Next, in the data processor file you need to contruct the SELECT statement away from the render.sql instruction (otherwise the quotations will prevent it from working). So in my case I constructed my SELECT statement at the top of my dataprocessor file like this:

dim catnum catnum = request.querystring("catId") dim sqlstat sqlstat = "SELECT tblproducts.prodId as ID , tblproducts.prodTitle, tblproducts.prodCode, tblproducts.prodPrice, tblproducts.prodStock, tblproducts.prodEnabled, tblproducts.prodFeatured, tblproducts.prodBestBuy, tblproducts.prodImpulse, tblproducts.prodMember FROM tblproducts WHERE prodCatId = " & catnum & ""

and then referenced my new SELECT statement in the grid.render_sql instruction.

[code]Dim res
Set res = CreateObject(“ADODB.Connection”)
res.ConnectionString = MM_connDB_STRING
res.Open

Dim grid
Set grid = new Connector
grid.init TypeGrid,res,“MySQL”,TypeUnspecified,TypeUnspecified
grid.render_sql sqlstat,“prodId(ID)”,“tblproducts.prodId(ID),prodTitle,prodCode,prodPrice,prodStock,prodEnabled,prodFeatured,prodBestBuy,prodImpulse,prodMember”, “”, “”[/code]

Hope this all makes sense.