Hi I am newbie to ASP. I am trying to connect to a database. I can however it seems my recordset never seems to each end of line (EOF). I am pulling from two tables in a single database.

Here is the code. Would appreciate any help whatsoever. Thanks.

    <%@Language="VBScript"%>

<%
    Option Explicit

    Dim RS1, conn, strSQL1, employeeName

    set conn = Server.CreateObject("ADODB.Connection")
    set RS1 = server.createobject("ADODB.recordset")

    conn.open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("ELP.mdb")

    strSQL1 = "select * from EMPLOYEE where Emp_No like '%" & Request("employeeID") & "%'"

    RS1.open strSQL1, conn, 2, 3

If not RS1.EOF then

        response.write "success=true&results="

        do while NOT RS1.EOF
            employeeName = employeeName & RS1("FirstName") & " " & RS1("LastName") & vbCrLf
            RS1.MoveNext
        loop
else
    response.write "success=false"

    end if

RS1.close
set RS1 = nothing

conn.close
set conn = nothing

Response.write server.urlencode (employeeName)

%>

Hi there,

Can you describe the problem in more detail? I am not sure what you mean by it not reaching EOF. Do you get an error? Do you get anything output on the screen?

And are you sure your select * from EMPLOYEE where Emp_No... query returns any records at all??

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Hi,

basically i want to know how my code can print the output

success=false

This is my desired outcome

however this does not happen as my recordset seems to never execute the do while structured code completely for rs1.eof to be true. I do not get an error. But I get the following output.

success=true&results=Jack+Smith%0D%0ALisa+Ray%0D%0 AMona+May%0D%0AMan+Dog%0D%0A

And I am sure the SQL query is working otherwise as the records are being identified by the code.

This is basically the encoded form of my database records.

Any help here?

Why should it be EOF when it contains records? Your code uses If not RS1.EOF then which means the code in the If block will execute when the recordset *does* contain at least one record.

Only when the recordset is entirely empty, success will equal false; that is, there were no records matching your WHERE clause in the query...

Does that help? If not, I do not understand what the problem is, and you may need to explain in more detail what it is you're after.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: New born by Muse (Track 1 from the album: Origin of symmetry) What's This?

Hi, Thanks for persisting. I am using this code to write mine. here it is

<%@Language="VBScript"%>
<%
    Option Explicit

    Dim oRS, oConn, strSQL

    Dim results

    Set oConn = Server.CreateObject("ADODB.Connection")
    Set oRS = Server.CreateObject("ADODB.Recordset")

    ' Make a DSN-less connection to the DB
    oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("ex1-emails.mdb")

    ' Open our recordset accordingly
    If UCase(Request("NameLast")) = "ALL" Then
        strSQL = "SELECT * FROM Emails"
    Else
        strSQL = "SELECT * FROM Emails WHERE NameLast LIKE '" & Request("NameLast") & "'"
    End If

    oRS.Open strSQL, oConn, 2, 3

    ' 2 and 3 are numeric equivalents of adOpenDynamic and adLockOptimistic.
    ' These are the best choices for what we're trying to accomplish.
    ' See ADO documentation for other cursor and lock types.

    If oRS.EOF Then
        Response.Write "success=False"
    Else
        Response.Write "success=True&results="
        Do While Not oRS.EOF
            results = results & oRS("NameLast") & ", " & oRS("NameFirst") & vbCr
            results = results & oRS("EmailAddress") & vbCrLf
            oRS.MoveNext
        Loop
    End If

    oRS.Close
    Set oRS = Nothing
    oConn.Close
    Set oConn = Nothing

    Response.Write Server.URLEncode(results)
%>

when i run this code in my browser the the output is

success=False

and the recordset is not empty as there is a corresponding database for the above code. so my question is how does the above code generate a eof to be true whereas in my case the eof is false, (the 2 codes r almost similar)

thanks

I am still confused about what exactly you're trying to accomplish.
In a previous post you did get output, right? Wasn't that what you expected? Should you get the output? Or should the recordset be empty. Can you please explain what you're doing? A little background about the situation, your database, etc might really help.
Right now, I don't know what code does what, and what you expect it to.

Anyway, if you do get an EOF (empty recordset), change your code to this Copy and paste the SQL statement in a query in Access. What do you get? Do you see any records?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Nature 1 by Muse (Track 6 from the album: Hullabaloo) What's This?

To avoid confusion. I am repasting my code again.

<%@Language="VBScript"%>

<%
    Option Explicit

    Dim RS1, conn, strSQL1, employeeName

    set conn = Server.CreateObject("ADODB.Connection")
    set RS1 = server.createobject("ADODB.recordset")

    conn.open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("ELP.mdb")

    strSQL1 = "select * from EMPLOYEE where Emp_No like '%" & Request("employeeID") & "%'"
    Response.Write("SQL is " & strSQL1)
    Response.End()
    RS1.open strSQL1, conn, 2, 3

If RS1.EOF then
    response.write "success=false"
else
        response.write "success=true&results="

        do while NOT RS1.EOF
            employeeName = RS1("FirstName") & " " & RS1("LastName") & vbCrLf
            RS1.MoveNext
        loop

end if

RS1.close
set RS1 = nothing

conn.close
set conn = nothing

Response.write server.urlencode (employeeName)

%>

Note: I included your part as you said. Here is the output of the above code

SQL is select * from EMPLOYEE where Emp_No like '%%'

As for the questions you asked:

1. I am trying to use ASP code to connect my database (in Access) and my flash file.
2. In my previous post I did get an output however it was not what I expected as my recordset is empty.
3. I used the SQL query in Access and did not see any records AS I EXPECTED

Seeing my code you will notice that employeeID is an input field in my flash file. employeeName is the output field.

success is just a boolean indicator where if success is true (i.e. there are records which match my input employeeID) then it goes to one frame otherwise if it is false (i.e. there are no records which match my input employeeID) then it goes to another frame.

Now as for my questions.

1. I expect my output to be

success=false as my recordset is empty since there is no input as of now.

The second code gives just that - success=false.
so what is wrong?

I think this much background info is enough. Do let me know if you do not get any part of what I have just written.

Thanks

I think I am losing it. Either I am completely stupid, or you're still not making your self clear: AFAICS, this says: I expect success to be false. It also says: it gives me just that: success=false. So, success is false. What's the problem? Shouldn't it be false? If not then why do you say it should?

Maybe things are caused by this: What do you expect this query to return? No records or all records?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Space dementia by Muse (Track 3 from the album: Origin of symmetry) What's This?

Hi again,

  The SECOND CODE refers to the code I am referring to. It is NOT my code. See my third reply thread for the second code.

Now, the SQL Query I am trying to execute is:

strSQL1 = "select * from EMPLOYEE where Emp_No like '%" & Request("employeeID") & "%'"

As mentioned earlier, employeeID is an input field in my flash file which I am trying to pass information into from the database.

Now, as for the problem:

MY CODE gives the output - success=true&results=Man+Dog%0D%0A

THE SECOND CODE(note this is NOT mine) gives the output - success=false

AS the two codes are similar (when u compare) WHY ARE the outputs DIFFERENT?

I think if you read all the threads you can gauge something.

If you are still unclear at this point can i email you my files so you can actually see what is wrong and try running the application urself.

Thanks for being so helpful so far!

The only thing I can see why the output would be different, is because you end up with a different query. You still haven't answered my previous question: Suppose that Request("employeeID") does *not* have a value. You'll end up with this query: Although you haven't specified a search term, this query will select ALL records. Is this what is causing the problem?

Here's something that may help:

1. Change both pages to both of them use the Response.Write("SQL is...") stuff
2. Past the queries into your Access database, one by one. What do you get? Do you get the records you expect?
3. Post the queries and the results to this forum.

Please do follow the previous three steps; I think they'll shed some light on this issue.

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Sober by Muse (Track 9 from the album: Showbiz) What's This?