0 record returned from recordset

[thesti]

hi,

i'm trying to select some record from a table in my msaccess.mdb (it's a 2007 ms access). here's the sql query

select * from msNews where title like '*a*'

when queried it in ms access, i got some records returned. but then i try to query the database using the same sql query, but the rs.RecordCount returns 0.

here's the asp code

rs.open "select * from msNews where judul like '*a*'", conn        
 
response.write(rs.Recordcount)  'this shows 0
 

is something wrong?
thanks

[missinglinq]

The one thing that is see that is wrong is that you've posted an ASP question in the Access forum! I'll transfer it to the ASP forum.

Linq ;0)>

[DrBunchman]

Hi thesti,

The RecordCount property will work only if you have used certain cursor types when opening your recordset.

If you try to loop through the returned recordset and display the records are they showing? If they are then we can look at changing the cursor that you are using to return the recordcount and if they aren't then the problem lies somewhere else.

If you are just trying to check whether any records have been returned then you can use the rs.EOF property, which stands for End Of File, like this:

If rs.EOF Then
     Response.Write "No records returned!"
Else
     Response.Write "Some records were returned!"
End If

Dr B

[thesti]

hi,

thanks for the reply.

yes, i've used the rs.EOF or rs.BOF for checking whether there's any record returned or not. and i get "There's No news".

here's the declaration of the connection and rs object

<%
    'file for database connection
    set conn = server.CreateObject("ADODB.Connection")
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("../db.mdb")
 
    set rs = Server.CreateObject("ADODB.Recordset")
    rs.CursorLocation = 3
%>
 

and this is the query code

<%
            'get news
            if search <> "" then
                if modeSearch = "title" then
                    'if search news based on title
                    rs.open "select * from msNews where title like '*"&search&"*'", conn                        
                else 
                    'search news based on meat
                    rs.open "select * from msNews where meat like '*"&search&"*'", conn
                end if
            else
                rs.open "select * from msNews", conn
            end if
%>
 

here's the result displaying code

<%
            'rs checking
            if not (rs.BOF or rs.EOF) then            
                do while  Not ( rs.Eof or rs.AbsolutePage <> page )
%>
 
<!-- render news in html -->
 
<%
                    rs.moveNext
                Loop
            else
                response.write("There's no News")
            end if            
%>                    
 

it works find if the user doesn't search anything, all of the news are returned.
but i get "There's no News" when i search for some news that are exist in the database. what makes me wonder is that when i query the table directly from Ms Access 2007 using the same keyword that i've tried from the page, i get some records.

i don't know what possibly is wrong.

Thank you.
SC.

[DrBunchman]

Next thing to do then is to write your query to the screen using a Response.Write, copy and paste it into your query analyser and see if it returns any records then.

If it doesn't then there is a problem with how you are building the query in your ASP code.

Let me know how it goes.

Dr B

[thesti]

hi,

i've done what you told me,

i type "test" in the text field of the search form, then
here's the query resulted from Response.Write

select * from msNews where title like '*test*'

after i run it in ms access query, it returns a record of which title is "test only".
while in my web app, i get "There's no News"


Thank you,
SC

[thesti]

hi,

now i know what cause my problem,

in ms access the query

select * from msNews where title like '*test*'

works because it uses * asterisk as a wildcard. so i though that i should use the same sign in asp, but just now i try to change it to '%' and now i get the same result as it's run in ms access query.


Thank you for your help,
SC

[DrBunchman]

No problem, glad you got it fixed.

Dr B