[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

ASP and/or SQL code puzzle

Posted on 2011-03-23
10
Medium Priority
?
337 Views
Last Modified: 2012-05-11
I am bringing data into an ASP Classic page from a MS SQL database .  The 3 fields to display are first_name, last_name, and proj_title.  The first and last names are joined as a single hyperlink by a function.

My problem is that when the project title field appears in the code after the full name (hyperlink), it does not display on the web page; there is no error--it just does not show up.

When I removed the hyperlink, all 3 fields display perfectly.  Also, when I put the project title field first, everything is fine (but I need it to appear after the name).

I'm attaching the code ve narrowed it down to the code at the beginning of line 44. Any help would be appreciated.

Shari

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="../Connections/EWI.asp" -->

<%
Dim rsprojdir__MMColParam
rsprojdir__MMColParam = "62"
If (Request.QueryString("Dir_ID") <> "") Then 
  rsprojdir__MMColParam = Request.QueryString("Dir_ID")
End If
%>
<%
Dim rsprojdir
Dim rsprojdir_cmd
Dim rsprojdir_numRows

Set rsprojdir_cmd = Server.CreateObject ("ADODB.Command")
rsprojdir_cmd.ActiveConnection = MM_EWI_STRING
rsprojdir_cmd.CommandText = "SELECT * FROM dbo.DIRECTORY  INNER JOIN dbo.LINK_DIR_PROJ ON dbo.DIRECTORY.Dir_ID = dbo.LINK_DIR_PROJ.Dir_ID WHERE dbo.LINK_DIR_PROJ.Proj_ID=? ORDER BY Last_Name, First_Name" 
rsprojdir_cmd.Prepared = true
rsprojdir_cmd.Parameters.Append rsprojdir_cmd.CreateParameter("param1", 200, 1, 255, rsprojdir__MMColParam) ' adVarChar

Set rsprojdir = rsprojdir_cmd.Execute
rsprojdir_numRows = 0
%>
<%
Function MM_joinChar(firstItem)
  If (firstItem <> "") Then
    MM_joinChar = "&"
  Else
    MM_joinChar = ""
  End If
End Function
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>


<%=(rsprojdir.Fields.Item("Proj_Title").Value)%>&nbsp;<a href="../people/person-detail.asp?<%= Server.HTMLEncode(MM_keepURL) & MM_joinChar(MM_keepURL) & "Dir_ID=" & rsProjdir.Fields.Item("Dir_ID").Value %>"><%=(rsprojdir.Fields.Item("First_Name").Value)%>&nbsp;<%=(rsprojdir.Fields.Item("Last_Name").Value)%></a>

</body>
</html>
<%
rsprojdir.Close()
Set rsprojdir = Nothing
%>

Open in new window

0
Comment
Question by:lrdchelp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35202288
the first point to look at would be this part:

SELECT *   ... JOIN

the problem is that some column names might exist in both tables... and that will result in problems.
please replace the * by the column names you need, really, with the table name (or it's alias) so that there is no ambiguity

for example, like this (guessing which table first_name and last_name are coming from) :
rsprojdir_cmd.CommandText = "SELECT d.First_Name, d.Last_Name FROM dbo.DIRECTORY d INNER JOIN dbo.LINK_DIR_PROJ l ON d.Dir_ID = l.Dir_ID WHERE l.Proj_ID=? ORDER BY d.Last_Name, d.First_Name 

Open in new window

0
 
LVL 16

Expert Comment

by:AlexPace
ID: 35202301
Is there anything goofy about that row if you look at the HTML output using the browser's View Source feature?
0
 

Author Comment

by:lrdchelp
ID: 35202367
This is the code in View source when the title is before the name:
GSR<a href="../people/person-detail.asp?Dir_ID=359">Meghan&nbsp;Bathgate</a>,&nbsp;

This is the code in view source with the project title after
<a href="../people/person-detail.asp?Dir_ID=359">Meghan&nbsp;Bathgate</a>&nbsp;

I do notice that the title does not appear in the code (as well as in the browser) when it follows so I'm guessing I have a code problem in that segment of code that is preventing it from displaying.  I just don't know what it is.

Shari
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 16

Expert Comment

by:AlexPace
ID: 35202502
Even if you put it on line 45 by itself?  (so the name hyperlink remains on line 44)

Also put a literal string in that response.write to see if it is actually executing.
<%= "Title: " & (rsprojdir.Fields.Item("Proj_Title").Value) %>
0
 

Author Comment

by:lrdchelp
ID: 35202694
angell

I fixed the code to make sure there was nio ambiguity and the recordset within Dreamweaver returned the proper values.

However, now I receive a 500 error.

Shari
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 35203667
0
 
LVL 6

Accepted Solution

by:
worthyking1 earned 1000 total points
ID: 35203930
Your SQL cursor must be set to forward-only, so when you populated your hyperlink you have most likely already gone by the FName and LName columns in the recordset. I suspect the Dir_ID column is later in the table than the others right? So when you use it earlier in the link the cursor has already flown by the Fname & Lname and cannot turn around.

Two ways you can address it:

1. BEST - Declare variables and put your recordset values into them IN THE ORDER that they appear in the db table.  Then you can use those vars any which way you want later in the code without such oddities occurring.

Eg. around line 24
Proj_Title = Trim(rsprojdir.Fields.Item("Proj_Title"))
First_Name = Trim(rsprojdir.Fields.Item("First_Name"))
Last_Name= Trim(rsprojdir.Fields.Item("Last_Name"))
DirID = CInt(rsprojdir.Fields.Item("Dir_ID")) ' I am assuming this is an integer value

2. Set your connection cursor to dynamic, but that's not ideal for various other reasons. Much cleaner to use #1 above.

BTW, your code look suspiciously like Dreamweaver-generated code. I hand-code all of mine as I don't trust Dreamweaver to do it right. Their tendency to use recordset fields directly from the RS without populating variables is just one of the sloppy things they do.
0
 

Author Comment

by:lrdchelp
ID: 35206727
Thanks all for your help.

At first I wasn't sure that Worthyking1's solution was appropriate because Dir_ID is the first record in the table, but I tried substituting a different field that was closer to Last_Name than Proj_Title was and it worked.

So I used Worthyking1's code and it worked!  Thanks so much!

Shari
0
 

Author Comment

by:lrdchelp
ID: 35207176
I thought it was fixed, but there is one more problem.  The title only appears next to the first person's name.  It does not repeat.

Any ideas?

Shari
0
 

Author Comment

by:lrdchelp
ID: 35207614
I figured it out.  No need to respond.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question