Having trouble selecting the latest 25 entries

[XTGeminiman]

I'm having trouble selecting the last 25 entries into a recordset. My script looks like this....


elseif startletter = "all" then sql = "SELECT TOP 25 * FROM "&console&"previews ORDER BY previewdate DESC"


The rest of the code is as follows but it only gives me an error when startletter=all


<% Dim startletter, alphabet,rspreview startletter = Request.QueryString("startletter") if startletter = "" then startletter = "A" end if if startletter = "nonalphabet" then sql = "SELECT * FROM "&console&"previews WHERE previewtitle Not Like 'a%'" for alphabet = 98 to 122 sql = sql & " AND username Not Like '" & chr(alphabet) & "%'" next sql = sql & " ORDER BY previewtitle" elseif startletter = "all" then sql = "SELECT TOP 25 * FROM "&console&"previews ORDER BY previewdate DESC" else sql = "SELECT * FROM "&console&"previews WHERE previewtitle Like '" & startletter & "%' ORDER BY previewtitle" end if Set rspreview = Server.CreateObject("ADODB.Recordset") rspreview.Open sql, conn, 3, 3 %>


Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

ODBC driver does not support the requested properties.

/reviews.asp, line 26

line 26 is the connection line btw

[jaywhy13]

I'm having trouble selecting the last 25 entries into a recordset. My script looks like this....


elseif startletter = "all" then sql = "SELECT TOP 25 * FROM "&console&"previews ORDER BY previewdate DESC"


The rest of the code is as follows but it only gives me an error when startletter=all


<% Dim startletter, alphabet,rspreview startletter = Request.QueryString("startletter") if startletter = "" then startletter = "A" end if if startletter = "nonalphabet" then sql = "SELECT * FROM "&console&"previews WHERE previewtitle Not Like 'a%'" for alphabet = 98 to 122 sql = sql & " AND username Not Like '" & chr(alphabet) & "%'" next sql = sql & " ORDER BY previewtitle" elseif startletter = "all" then sql = "SELECT TOP 25 * FROM "&console&"previews ORDER BY previewdate DESC" else sql = "SELECT * FROM "&console&"previews WHERE previewtitle Like '" & startletter & "%' ORDER BY previewtitle" end if Set rspreview = Server.CreateObject("ADODB.Recordset") rspreview.Open sql, conn, 3, 3 %>


Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

ODBC driver does not support the requested properties.

/reviews.asp, line 26

line 26 is the connection line btw


Try to space out your code a little better.... that might just be the problem... in that the words in your sql statement may run into each other... and if thats the case, that will create a problem....

I don't know for example wot is in the console variable but consider:
Example:
Instead of this:
"SELECT TOP 25 * FROM "&console&"previews ORDER BY previewdate DESC"

Try summin like this:
"SELECT TOP 25 * FROM " & console & " previews ORDER BY previewdate DESC"

Unless whatever is stored in "console" forms apart of the table name then you might need to keep your format. However, as your code stands it seems that whatever is stored in console will join with "preview" to form the name of your table

Hope that helps! :thumbsup:

[XTGeminiman]

hmmmm, it is messy but it didn't seem to make a difference. it only occurs when startletter=all so I assume it's something on that line.

could that error be something with the host?

[miranda]

I take it that you declared console as a variable earlier in the page.
Did you do a Response.Write console in order to see what value was passed?

It looks like you have multiple tables each begining with some alphacode then previews as in apreviews, bpreviews, cpreviews etc... and this also includes a table called allpreviews?

Did you also try a Response.Write sql after the sql statement and before you tried to open the recordset?

btw if all you are doing is reading the values from these rows from the database and then displaying them you would be better off with this cursor and locktype
rspreview.Open sql, conn, 0,1

[XTGeminiman]

<%
Dim startletter, alphabet,rspreview

startletter = Request.QueryString("startletter")

if startletter = "" then
startletter = "A"
end if

if startletter = "nonalphabet" then
sql = "SELECT * FROM " & console & "previews WHERE previewtitle Not Like 'a%'"
for alphabet = 98 to 122
sql = sql & " AND username Not Like '" & chr(alphabet) & "%'"
next
sql = sql & " ORDER BY previewtitle"
elseif startletter = "all" then
sql = "SELECT TOP 25 * FROM " & console & "previews ORDER BY previewdate DESC"
response.write(sql)
else
sql = "SELECT * FROM " & console & "previews WHERE previewtitle Like '" & startletter & "%' ORDER BY previewtitle"
end if
Set rspreview = Server.CreateObject("ADODB.Recordset")
rspreview.Open sql, conn, 3, 3
response.write(sql)
%>


Ok, the first sql statement came in but the second one didn't. the errors still on the rspreview.open sql, conn, 3, 3



SELECT TOP 25 * FROM gcnpreviews ORDER BY previewdate DESC

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

ODBC driver does not support the requested properties.

/previews.asp, line 27

[miranda]

<%
Dim startletter, alphabet,rspreview

startletter = Request.QueryString("startletter")

if startletter = "" then
startletter = "A"
end if

if startletter = "nonalphabet" then
sql = "SELECT * FROM " & console & "previews WHERE previewtitle Not Like 'a%'"
for alphabet = 98 to 122
sql = sql & " AND username Not Like '" & chr(alphabet) & "%'"
next
sql = sql & " ORDER BY previewtitle"
elseif startletter = "all" then
sql = "SELECT TOP 25 * FROM " & console & "previews ORDER BY previewdate DESC"
else
sql = "SELECT * FROM " & console & "previews WHERE previewtitle Like '" & startletter & "%' ORDER BY previewtitle"
end if
response.write(sql)
Set rspreview = conn.Execute(sql)

%>

Try this code which does not use the recordset object, instead uses sql to get results through the execute method of connection object.

[XTGeminiman]

SELECT TOP 25 * FROM dspreviews ORDER BY previewdate DESC

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-4.0.17-nt]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '25 * FROM dspreviews ORDER BY previewdate DESC' at line 1

/previews.asp, line 26

similar error

[miranda]

AHA here is the problem MySQL does not use TOP instead it uses LIMIT at the end of the statement so your query would look like this


<%
Dim startletter, alphabet,rspreview

startletter = Request.QueryString("startletter")

if startletter = "" then
startletter = "A"
end if

if startletter = "nonalphabet" then
sql = "SELECT * FROM " & console & "previews WHERE previewtitle Not Like 'a%'"
for alphabet = 98 to 122
sql = sql & " AND username Not Like '" & chr(alphabet) & "%'"
next
sql = sql & " ORDER BY previewtitle"
elseif startletter = "all" then
sql = "SELECT * FROM " & console & "previews ORDER BY previewdate DESC LIMIT 25"
else
sql = "SELECT * FROM " & console & "previews WHERE previewtitle Like '" & startletter & "%' ORDER BY previewtitle"
end if
response.write(sql)
Set rspreview = conn.Execute(sql)

%>

[XTGeminiman]

yup, that was it. Thanks.