[eluser]Unknown[/eluser]
Flexigrid, Classic ASP, XML, MSSQL 2000.... Oh My!
I have been playing around with this for quite some time now and I am certain that my inexperiencs is causing the problem. As it says above, I amd using Classic ASP, XML and MS Sql Server 2000 with the flexigrid and am running into problems trying to get this to work. I am, at least, partially assuming that ONE of my problems is dealing with MS SQL 2000's lack of LIMIT and trying to work around that to make paging work. At this point it is just totally gummed up and not working.
Here is the path to the broken flexigrid:
http://www.titlewizard.us/1-new/twjquery...wFGrid.asp
Here is the path to the ASP Get script:
http://www.titlewizard.us/1-new/twjquery...idGet2.asp
The code for that page follows:
Code:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<% option explicit %>
<%
Dim dc,rs,sql
Set dc = Server.CreateObject("ADODB.Connection")
dc.Open "Provider=SQLOLEDB; Data Source = USTA-SQL; Initial Catalog = TW-USTitle; User Id = xxx; Password=xxx"
Set rs = Server.CreateObject("ADODB.Recordset")
' set content type and xml tag
'Response.ContentType = "text/html"
Response.ContentType = "text/xml"
Response.write("<?xml version=""1.0"" encoding=""UTF-8""?>")
'Page Numbers
Dim page
page = Request.Form("page")
if page = "" then
page = 1
End if
Dim rp
rp = Request.Form("rp")
if rp = "" then
rp = 15
End if
Dim start, limit
if ((page-1) * rp)=0 then
start = rp
else
start = ((page-1) * rp)
end if
'limit = " TOP "&page;*rp
'Search Bits
Dim searchterm, searchcols
Dim sortname
if Request.Form("sortname") = "" then
sortname = "txtOrderNumber"
Else
sortname = Request.Form("sortname")
End If
Dim sortorder
if Request.Form("sortorder") = "" then
sortorder = "desc"
Else
sortorder = Request.Form("sortorder")
End If
Dim sort
sort = " ORDER By "&sortname;&" "&sortorder;Dim fullsearch
if Request.form("query") <> "" then
searchterm = Request.form("query")
searchcols = request.form("qtype")
if searchcols = "id" then
if isnumeric(searchterm) then
fullsearch = "WHERE "&searchcols;&" = "&searchterm;&" AND (CompletedDate = '')"
'fullsearch = "WHERE "&searchcols;&" = "&searchterm;else
fullsearch = "WHERE (CompletedDate = '')"
'fullsearch = ""
end if
Else
fullsearch = "WHERE "&searchcols;&" LIKE '%"&searchterm;&"%' AND (CompletedDate = '')"
'fullsearch = "WHERE "&searchcols;&" LIKE '%"&searchterm;&"%'"
End if
Else
fullsearch = "WHERE (CompletedDate = '')"
End if
'Count Query
Dim sqlCount, totalCount
sqlCount = "Select COUNT(txtOrderNumber) as cnt FROM WebOrders WHERE (CompletedDate = '')"
rs.Open sqlCount, dc
totalCount = rs("cnt")
rs.Close
Dim total, countRec
total = totalCount
'Main Query
sql = "SELECT * FROM ("
sql = sql & "SELECT TOP " & rp & " * FROM ("
sql = sql & "SELECT TOP " & start & " * "
sql = sql & "FROM WEBOrders "
sql = sql & fullsearch
sql = sql & sort & ") AS foo "
sql = sql & sort & ") AS bar "
sql = sql & "INNER JOIN WEBOrders AS IncOrders ON bar.ID = IncOrders.ID "
sql = sql & sort
'response.write "<br><br><br>"
'response.write sql
'response.write "<br><br><br>"
'response.end()
rs.Open sql, dc
' output data in XML format
Dim blnk
blnk="-"
Response.write("<rows>")
Response.write("<page>" & page & "</page> ")
Response.write("<total>" & cstr(total) & "</total>")
Do while not rs.EOF
dim rsID, theID, txtOrderNumber, PRODCODE, PROPSTRE, COUNTY, BYR1NAM1, SLR1NAM1
if rs("ID") = "" then
rsID = blnk
theID = blnk
else
rsID = rs("ID")
theID = rs("ID")
end if
if rs("txtOrderNumber") = "" then
txtOrderNumber = blnk
else
txtOrderNumber = rs("txtOrderNumber")
end if
if rs("PRODCODE") = "" then
PRODCODE = blnk
else
PRODCODE = rs("PRODCODE")
end if
if rs("PROPSTRE") = "" then
PROPSTRE = blnk
else
PROPSTRE = rs("PROPSTRE")
end if
if rs("COUNTY") = "" then
COUNTY = blnk
else
COUNTY = rs("COUNTY")
end if
if rs("BYR1NAM1") = "" then
BYR1NAM1 = blnk
else
BYR1NAM1 = rs("BYR1NAM1")
end if
if rs("SLR1NAM1") = "" then
SLR1NAM1 = blnk
else
SLR1NAM1 = rs("SLR1NAM1")
end if
Response.write("<row id='" & rsID & "'>")
Response.write("<cell>")
Response.write("<![CDATA[ " & theID & "]]>") ' value for Order ID
Response.write("</cell>")
Response.write("<cell>")
Response.write("<![CDATA[ " & txtOrderNumber & "]]>") ' value for Order
Response.write("</cell>")
Response.write("<cell>")
Response.write("<![CDATA[ " & PRODCODE & "]]>") ' value for product code
Response.write("</cell>")
Response.write("<cell>")
Response.write("<![CDATA[ " & PROPSTRE & "]]>") ' value for property
Response.write("</cell>")
Response.write("<cell>")
Response.write("<![CDATA[ " & COUNTY & "]]>") ' value for county
Response.write("</cell>")
Response.write("<cell>")
Response.write("<![CDATA[ " & BYR1NAM1 & "]]>") ' value for buy/bor
Response.write("</cell>")
Response.write("<cell>")
Response.write("<![CDATA[ " & SLR1NAM1 & "]]>") ' value for slr
Response.write("</cell>")
Response.write("</row>")
rs.MoveNext
Loop
Response.write("</rows>")
rs.Close
Set rs = Nothing
dc.Close
Set dc = Nothing
%>
Any help with this will be GREATLY appreciated. I have been loosing hair for the last couple years so sitting here pulling more out is not in my best interest.
Dan
