iahamed

Hi I am using ASP and MySQL with ODBC without DSN connection great stuff using pure MySQL ODBC Driver code with Asp works Brilliant.

I sorted the paging with MySQL. However One querry of mine is not letting it page through record sets. Can Someone tell me how to have the querry that will suport the MySQL ODBC ASP way please?


These query's work.

. SQL = SQL & " SELECT * FROM listing ORDER BY ID DESC;"

. strSQL = strSQL & " ORDER BY ID,DBName,DBposition,DBsalary,DBage,Description DESC;"


This is the query that works but "docent page", this calling a Sub category:

Please help me to resolve this:

. SQL = SQL & " SELECT * FROM listing WHERE (Country = '" & mCountry & "' ) AND (Classification = '" & mDetails & "') ORDER BY ID DESC;"


This is my Code:


<%
Dim RS
Dim SQL
Dim adoCon1
Dim strEmail
Dim testv
Dim test1
Dim intRecordspp
Dim intTotalRecords
Dim intTotalPages
Dim intPageNum
Dim strBlah1
Dim strBlah2

Dim mDetails
Dim mCountry

mCountry=request.QueryString("Country")
mDetails=Trim(request.querystring("Details"))


If Request.QueryString("rpp") = "" Then
'No of Records to Display
intRecordspp = 1
Else
intRecordspp = CInt(Request.QueryString("rpp"))
End If

If Request.QueryString("PageNumber") = "" Then
intPageNum = 1
Else
intPageNum = CInt(Request.QueryString("PageNumber"))
End If


Dim connect_string
set connect_string = server.createObject("ADODB.connection")

Set RS = Server.CreateObject("ADODB.Recordset")
RS.cursorlocation = 3

connect_string.Open "Driver={MySQL ODBC 5.1 Driver}; Server=localhost;Database=DBname;UID=root;PWD=pw4"

SQL = SQL & " SELECT * FROM listing WHERE (Country = '" & mCountry & "' ) AND (Classification = '" & mDetails & "') ORDER BY ID DESC;"

RS.CursorType = 3
RS.Open SQL, connect_string
RS.PageSize = intRecordspp

If NOT RS.EOF Then RS.AbsolutePage = intPageNum
intTotalRecords = RS.RecordCount
intTotalPages = RS.PageCount

%>

<%
' If RS.EOF Then
%>
<!-- No Records-->
<%
' connect_string.close
' Response.End
'End If


' Collect Results now
%>

Page <%= intPageNum%> of <%= intTotalPages%>
<BR>

<%
If intPageNum = 1 Then
Else %>
<A HREF="listing.asp?PageNumber=<%= intPageNum -1%>&rpp=<%= Request.QueryString("rpp")%>">Back</A>
<%
End If
%>

<% If intPageNum < intTotalPages Then %>
<A HREF="listing.asp?PageNumber=<%= intPageNum +1%>&rpp=<%= Request.QueryString("rpp")%>">Next</A>

<%
Else
End If

' EOF 3 Paging----------------------------------------

for strBlah2 = 1 to intRecordspp
if Not RS.EOF Then
%>

the then Db Fields.


The DB fields are working fine. The only this is with the query. Can some one help me with the query, as to how to put it work with ASP and MySQL with ODBC?

Monie

I can't understand what you mean. Could you please describe them again

iahamed

Hi Monie,

Thank you for the atemp made to Help. I am Using ASP MySQL and ODBC. I am through my query caling a Sub category. Paging is working with these SQL statements:

. SQL = SQL & " SELECT * FROM listing ORDER BY ID DESC;"

. strSQL = strSQL & " ORDER BY ID,DBName,DBposition,DBsalary,DBage,Description DESC;"

Paging Dosent hapen when Say:

. SQL = SQL & " SELECT * FROM listing WHERE (Country = '" & mCountry & "' ) AND (Classification = '" & mDetails & "') ORDER BY ID DESC;"

That's the One I need now to Fix, I know the way of SQL when it comes to MySQL is diferent that Access and Ms SQL. My Question is Monie, how should I to make the SQL work with Paging make it work

. SQL = SQL & " SELECT * FROM listing WHERE (Country = '" & mCountry & "' ) AND (Classification = '" & mDetails & "') ORDER BY ID DESC;"

(Country = '" & mCountry & "' ) AND (Classification = '" & mDetails & "') That part wokrs In Access and pages through the records. In MySQL It dosent. Any Style to do it?

DO I have to add a Limit etc....? Please Help me. Thank you.