Results 1 to 9 of 9
  1. #1
    Join Date
    May 2002
    Posts
    5

    Exclamation Unanswered: need a movenext that won't instantiate data

    Hi,

    I am using classic ASP to response.write two fields from one table that is joined to two fields from another table. My problem is that I am joining on a field (clientname) that can only have unique values in one table(cp_clients) but have duplicate values in the other table(cp_projects). When response.write is invoked and the recordset is read using ado movenext method, ASP writes an instance of cp_clients.clientname for each row that contains cp_projects.clientname. How can I turn this instantiation off?? The server-side code is pasted below...

    <%
    set clientRecord = server.CreateObject("ADODB.Recordset")
    SQL = "SELECT cp_clients.projdesc, cp_clients.cpstatusName, cp_projects.projectlink, cp_projects.clientname, cp_projects.projectname, cp_projects.projstatusName FROM cp_clients, cp_projects WHERE (cp_clients.clientname = cp_projects.clientname) AND (cp_clients.cpstatusName = 'Active') AND (cp_projects.projstatusName = 'Active')"
    clientRecord.open SQL, db

    do while not clientRecord.eof
    Response.Write("<span class='subheadertext'>" & clientRecord("clientname") & "</span><br><span class='bodytext'>" & clientRecord("projdesc") & "<br>")
    Response.Write("<a class='questionlink' href='" & clientRecord("projectlink") & ".pdf'>&raquo;" & clientRecord("projectname") & "</a></span><br>")
    Response.Write("<br>")
    clientRecord.MoveNext
    loop
    clientRecord.close %>

    Thanx in advance
    Sash

  2. #2
    Join Date
    May 2002
    Location
    Philadelphia, PA
    Posts
    14
    How about this?

    dim prevClientName, currentClientName
    prevClientName=""
    do while not clientRecord.eof
    currentClientName=clientRecord("clientname")
    if currentClientName=prevClientName then currentClientName=""
    Response.Write("<span class='subheadertext'>" & currentClientName & "</span><br><span class='bodytext'>" & clientRecord("projdesc") & "<br>")
    Response.Write("<a class='questionlink' href='" & clientRecord("projectlink") & ".pdf'>" & clientRecord("projectname") & "</a></span><br>")
    Response.Write("<br>")
    clientRecord.MoveNext
    loop
    clientRecord.close %>


    Tim

  3. #3
    Join Date
    May 2002
    Posts
    5
    Looks like a good solution, but I have tried something similar, comparing two separate recordsets on equality of the clientname field and a "type mismatch" error is returned. Is the type mismatch because I'm comparing two fields from different recordsets?

  4. #4
    Join Date
    May 2002
    Location
    Philadelphia, PA
    Posts
    14
    Without seeing the code that caused your type mismatch error, I wouldn't like to guess what the problem was.
    However, there shouldn't be an error of that type in the code I posted because the comparison is between two newly created strings. Let us know how you get on....

  5. #5
    Join Date
    May 2002
    Posts
    5
    Here's the code...

    set clientRecord = server.CreateObject("ADODB.Recordset")
    set projRecord = server.CreateObject("ADODB.Recordset")
    SQL = "SELECT * FROM cp_clients WHERE cpstatusName = 'Active'"
    SQLProj = "SELECT * FROM cp_projects WHERE projstatusName = 'Active'"
    clientRecord.open SQL, db
    projRecord.open SQLProj, db
    dim clientClientName, projClientName
    set clientClientName = clientRecord("clientname")
    set projClientName = projRecord("clientname")

    do while not clientRecord.eof
    Response.Write("<span class='subheadertext'>" & clientRecord("clientname") & "</span><br><span class='bodytext'>" & clientRecord("projdesc") & "<br>")
    do while not projRecord.eof and clientClientName = projClientName
    Response.Write("<a class='questionlink' href='" & projRecord("projectlink") & ".pdf'>&raquo;" & projRecord("projectname") & "</a></span><br>")
    projRecord.MoveNext
    loop
    Response.Write("<br>")
    clientRecord.MoveNext
    loop
    projRecord.close
    clientRecord.close

    The error "type mismatch" occurs in the nested do while loop where clientClientName and projClientName are compared...

  6. #6
    Join Date
    May 2002
    Location
    Philadelphia, PA
    Posts
    14
    And what happens if you cast them to strings before the comparison?

  7. #7
    Join Date
    May 2002
    Posts
    5
    Do you mean setting them to strings like clientClientName = "XYZ Company" at the top of the statement.

  8. #8
    Join Date
    May 2002
    Location
    Philadelphia, PA
    Posts
    14
    When you tried the code I posted (which I assume you have done), what happened?

  9. #9
    Join Date
    May 2002
    Posts
    5
    Actually, I haven't tried it yet. I'm not at a machine where I can try it until later today. I thought you might know right off why the type mismatch was occurring. I'm new at this stuff so I'm looking for any pearls of knowledge that I can get. Thanks for your help so far. Hopefully your solution will work. I've been trying to get this thing to work for way, way too long. Surprised my client hasn't told me to get lost.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •