I am very close to being done with this project but I could use some help with this last part. I have two SQL tables and an ASP page. I am simply reading those tables and displaying data based on ID number. The output is names and links. I have been successful in displaying the names, their descriptions and the links and their descriptions. However, if I have three distinctly different names and three distinctly different links, when I test it I see the three names and their descriptions no problem, but the it shows all three links for all three names. Meaning for example: John Doe Sue Smith Mike Wilkens * John's House of Pancakes (this is the link specific to only John Doe) * Sue is the owner of the new Seafood Restaurant (this is the link specific to only Sue Smith) * Mike is the author of the new book (this is the link specific to only Mike) So what happens is I see John's name and all three of the links then Sue's name and all three of the links and so on. I only want to see one link per name and that link needs to be the one specific to that name. See below my SQL and ASP code. <% set con = Server.CreateObject("ADODB.Connection") con.Open "File Name=E:\webservice\Company\Company.UDL" set rs = Server.CreateObject("ADODB.Recordset") id=request.querystring("id") IF id <> "" then id=id else id="9734" end if ' This will apparently pull back too many records strSQL = "SELECT DISTINCT GuestName, GuestDescription, URL, Description FROM T_ProgramGuests tpg LEFT JOIN T_ProgramLinks tpl ON tpl.ProgramID = tpg.programID WHERE (tpg.ProgramID = " & id & ")" rs.Open strSQL,con previousGuestName = "" While Not RS.Eof ' Only output guest details if this record has a different guest name to the previous one if previousGuestName <> cStr(rs("guestName")) then response.write "<br />" & vbcrlf response.write "<strong>" & rs("GuestName") & "</strong>" & rs("GuestDescription") & "<br /><br />Related Links:<br />" & vbcrlf previousGuestName = cStr(rs("guestName")) end if ' Always output the related links though response.write "<li class=""basic""><A HREF=""" & rs("URL") & """>" & rs("Description") & "</A></li>" & vbcrlf RS.MoveNext Wend recProgram.Close con.Close set recProgram = nothing set con = nothing %> Code (markup): Now the Display Code: previousGuestName = "" While Not RS.Eof ' Only output guest details if this record has a different guest name to the previous one if previousGuestName <> cStr(rs("guestName")) then response.write "<br />" & vbcrlf response.write "<strong>" & rs("GuestName") & "</strong>" & rs("GuestDescription") & "<br /><br />Related Links:<br />" & vbcrlf previousGuestName = cStr(rs("guestName")) end if ' Always output the related links though response.write "<li class=""basic""><A HREF=""" & rs("URL") & """>" & rs("Description") & "</A></li>" & vbcrlf RS.MoveNext Wend Code (markup): So what am I missing in my code?
I would start with your MSSQL statement, look at what records that's bringing back. In particular, look at replacing the LEFT JOIN with an INNER JOIN (Just a hunch) Cheers Markus
Ludwig - Yes in the Guest table there is GuestID and ProgramID. When I test for this in my browser I am referencing the ProgramID. Example: pagename.asp?ID=9734 The Links table has LinkID and ProgramID. Also to answer the other person's reply I tried Inner Join before and it was suggested to me in a forum to use Left Join instead. Now what?
let me do it like this, you need distinct data from 2 different tables or just one which is connected to the second one? if the option is the second one its easy for you. You select desctinct from table1 then select what you need based on the programID from table2
There are links that are specific to a certain person. Based on that wouldn't that mean I would need distinct data from two different tables? I'm just trying to avoid showing John Doe's link under Henry James for example. John and Henry should have their own unique links. So how difficult would that be?
I'm noticing a minor thing when I view the output. There are spaces between each of the links. There shouldn't be and I don't see anything on this line that looks like spacing code. Unless I'm missing something in this line: response.write "<li class=""basic""><A HREF=""" & rs("URL") & """>" & rs("Description") & "</A></li>" Code (markup): Would the extra double quotes cause spaces between links?
first of all you can have a USERID column in all your table and select by that ID and not a DISTINCT next, here it is easier for you to write like this so you can see are there spaces or no, view the HTML
Ludwig - Cool I tried that but there are still spaces between the links. Again this is water under the bridge. I'm just trying to clean it up a bit. I have another question. This is in regards to ID # 9734 Why would there "not" be a space between Walter Mebaine's description and Bryan Jones? This outputs this way when I test. Walter Mebane professor of government at Cornell University and a member of the DNC task force looking into the results in Ohio in 2004 Bryan Jones Donald R. Matthews Distinguished Professor of American Politics and director of the Center for American Politics and Public Policy Related Links: # 'Still Angry, Kerry Supporters Nationwide Try to Change Ohio Elections' # 'Steal this Vote,' Andrew Gumbel interview by Julian Brookes, Mother Jones # 'Democrats Say 2004 election failed in Ohio'
When I test ID 9785 I see this... Tamara Murphy executive chef and owner of Brasa Greg Atkinson food writer, chef and regular contributor to the Cooking Klatch Tom Douglas chef and owner of many restaurants... Related Links: # Brasa # Greg Atkinson The section below is my stuff. Everything above is the normal stuff. Greg Atkinson food writer, chef and regular contributor to the Cooking Klatch Related Links: # Brasa # Greg Atkinson # Tom Douglas Restaurants Tamara Murphyexecutive chef and owner of Brasa Related Links: # Brasa # Greg Atkinson # Tom Douglas Restaurants Tom Douglaschef and owner of many restaurants... Related Links: # Brasa # Greg Atkinson # Tom Douglas Restaurants Each name in "my section" should have "only" one link not all three and the link should be associated with the name. Meaning... The Brasa link is specific to Tamara The Greg link is specific to Greg The Tom Douglas link is specific to Tom So what am I doing wrong that would cause this in my output?
I have learned that there is a programmatic way to remove spaces by using IsLetter method followed by the Replace method. Except that these are only supported in .Net and we are working with ASP 3.0. There must be a 3.0 equivalent. Does anyone know of any?
I found this just moments ago online. Thoughts anyone? Will this work with my code? void trim2(string& str) { string::size_type pos = str.find_last_not_of(' '); if(pos != string::npos) { str.erase(pos + 1); pos = str.find_first_not_of(' '); if(pos != string::npos) str.erase(0, pos); } else str.erase(str.begin(), str.end()); } Code (markup): Source: http://www.codeproject.com/vcpp/stl/stdstringtrim.asp
I need help with this part folks. Based on the text below, if you wanted to see "only" the 2nd link with Andrew Gumbel and "not" all three links how would I need to trim this out in my code? Andrew Gumbel correspondent for the Independent of London and the author of Steal This Vote: Dirty Elections and the Rotten History of Democracy in America Related Links: * 'Still Angry, Kerry Supporters Nationwide Try to Change Ohio Elections' * 'Steal this Vote,' Andrew Gumbel interview by Julian Brookes, Mother Jones * 'Democrats Say 2004 election failed in Ohio' <% set con = Server.CreateObject("ADODB.Connection") con.Open "File Name=E:\webservice\Company\Company.UDL" set rs = Server.CreateObject("ADODB.Recordset") id=request.querystring("id") IF id <> "" then id=id else id="9734" end if ' This will apparently pull back too many records strSQL = "SELECT DISTINCT GuestName, GuestDescription, URL, Description FROM T_ProgramGuests tpg LEFT JOIN T_ProgramLinks tpl ON tpl.ProgramID = tpg.programID WHERE (tpg.ProgramID = " & id & ")" rs.Open strSQL,con previousGuestName = "" While Not RS.Eof ' Only output guest details if this record has a different guest name to the previous one if previousGuestName <> cStr(rs("guestName")) then response.write "<br />" response.write "<strong>" & rs("GuestName") & "</strong>" & rs("GuestDescription") & "<br /><br />Related Links:<br />" previousGuestName = cStr(rs("guestName")) end if ' Always output the related links though response.write "<li class=""basic""><A HREF='" & trim(rs("URL")) & "'>" & trim(rs("Description")) & "</A></li>" strLine = Trim(strLine) If Len(strLine) > 0 Then strNewContents = strNewContents & strLine & vbCrLf end if RS.MoveNext Wend recProgram.Close con.Close set recProgram = nothing set con = nothing %> Code (markup):