simple ASP SQL question

Hello,

I have a simple SQL database, in that db there are serveral columns (IssueId, Summary, Assignedto, startdate, duedate) .
on of those colomns contain information of several users (assignedto).
The information shows with 1, 2, 3 ,4 , ect, ect, very indistinct.
Now I want to create a script with VB ASP that renames the 1, 2, 3 ect ect. (the assigned to colomn) to real names exp. 1 = john or 2 = erik
I thought something like this:

(if assignedto = 1 then John)

Unfortunately that's not the way how it works...
Will somebody help me with solve this puzzle ??

This is my script:


<html>
<head>
<title>Due Date</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>

<%
colorchanger = 1
color1 = "#e7e7e7"
color2 = "#d7d7d7"
%>

<table width="100%" height="0" border="0" cellpadding="0" cellspacing="0">
<tr bordercolor="<%=color1%>">
<td width="5%"  bgcolor="#eeeeee"><strong>Issuenr</strong></td>
<td width="45%" bgcolor="#eeeeee"><strong>Summary</strong></td>
<td width="10%" bgcolor="#eeeeee"><strong>Assigned To</strong></td>
<td width="10%" bgcolor="#eeeeee"><strong>Startdate (mm/dd/yy)</strong></td>
<td width="10%" bgcolor="#eeeeee"><strong>Duedate (mm/dd/yy)</strong></td>
<td width="5%" bgcolor="#eeeeee"><strong>Overdate</strong></td>
</table>
</tr>
<br>

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open = "Provider=sqloledb;Data Source=localhost;Initial Catalog=master;user id=sa;password=banaan"
Set oRS = Conn.Execute ("SELECT * FROM Issues")
set oRS2 = Conn.Execute("SELECT datediff (day, GETDATE(), duedate) FROM issues")
Set oRS3 = Conn.Execute("SELECT assignedto From issues")
do until oRS.eof

%>

<table width="100%" height="0" border="0" cellpadding="0" cellspacing="0">
<tr bordercolor="<%=color2%>">

<td width="5%" bgcolor="<%=color1%>"><%response.write oRS("issueid")%></TD>
<td width="45%" bgcolor="<%=color2%>"><%response.write oRS("summary")%></TD>
<td width="10%" bgcolor="<%=color1%>"><%response.write oRS3(0)%></td>
<td width="10%" bgcolor="<%=color2%>"><%response.write oRS("startdate")%></TD>
<td width="10%" bgcolor="<%=color1%>"><%response.write oRS("duedate")%></TD>
<td width="5%" bgcolor="<%=color2%>"><%response.write oRS2(0)%></TD>
<%

If colorchanger = 1 Then
      colorchanger = 0
      color1 = "#f7f7f7"
      color2 = "#e7e7e7"

Else
      colorchanger = 1
      color1 = "#e7e7e7"
      color2 = "#d7d7d7"

End If

%>

</tr>

<%
oRS.movenext
oRS2.movenext
oRS3.movenext
loop

Set oRS = nothing
Set oRS2 = nothing
Set oRS3 = nothing
Conn.Close
Set Conn = Nothing


%>
</table>

</form>
</p>
</body>
</html>
dutchdjsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee W, MVPTechnology and Business Process AdvisorCommented:
You should be able to do:

Where you would end up displaying the number 1 for john, you can change this with an if statement:

IF Rs.AssignedTo = 1 Then Response.Write "John"

Alternatively, if you have a table of names matching the id numbers, and you are using MS SQL or POSSIBLY MySQL 4.1 (or almost any other SQL server), you should be able to do something like this:

SELECT (SELECT FirstName + ' ' + LastName FROM NamesTable WHERE ID = issues.assignedto = namestable.id) AS 'AssignedTo' FROM Issues

The above select statement should substitute the real names with for the ID.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dutchdjsAuthor Commented:
hi

Thank you for your quick reply but where must I put the IF statment? In which lines?
I do not know a lot of ASP (just a NewBee) but...
I tried:

<td width="5%" bgcolor="<%=color1%>"><%response.write oRS("issueid")%></TD>
    <td width="45%" bgcolor="<%=color2%>"><%response.write oRS("summary")%></TD>
      <td width="10%" bgcolor="<%=color1%>">
      
      <%
      
      IF Rs.AssignedTo = 1 Then Response.Write "John"
      
      %>
      </td>
    <td width="10%" bgcolor="<%=color2%>"><%response.write oRS("startdate")%></TD>
    <td width="10%" bgcolor="<%=color1%>"><%response.write oRS("duedate")%></TD>
    <td width="5%" bgcolor="<%=color2%>"><%response.write oRS2(0)%></TD>



but that doesn't work at all :-((
0
Lee W, MVPTechnology and Business Process AdvisorCommented:
Find this section of your code and put in the code below:

<td width="5%" bgcolor="<%=color1%>"><%response.write oRS("issueid")%></TD>
<td width="45%" bgcolor="<%=color2%>"><%response.write oRS("summary")%></TD>
<td width="10%" bgcolor="<%=color1%>"><%
'edit the name/id matchups as appropriate
if oRS3("assignedto") = 1 Then response.write "John"
if oRS3("assignedto") = 2 Then response.write "Bill"
if oRS3("assignedto") = 3 Then response.write "Mary"
if oRS3("assignedto") = 4 Then response.write "Jane"
'etc
'But you'd be really better off using the SQL statement to query the names because if you have
'more than a few, this becomes, poor design and tedius code and in many ways makes expanding
'things difficult.
%></td>
<td width="10%" bgcolor="<%=color2%>"><%response.write oRS("startdate")%></TD>
<td width="10%" bgcolor="<%=color1%>"><%response.write oRS("duedate")%></TD>
<td width="5%" bgcolor="<%=color2%>"><%response.write oRS2(0)%></TD>
<%
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

colr__Commented:
ALternatively, and somewhat cleaner, you could put your names in an array at the top of your code like this (you can put this anywhere in your code, just as long as its contained with n the <% %> braces):

dim namesArray
redim namesArray(4)
namesArray(0) = John
nameArray(1) = Bill
nameArray(2) = Mary
nameArray(3) = Jane

Interesting that you put 'Mary' and 'Jane' as you last tow names leew !!! lol

Now all you have to do is add the following line into your code, wherever you want the name to appear:

response.write Cstr( namesArray(Rs.AssignedTo))

This code is a lot more managable as well, to add names justa add them to the array at the start of the page.


0
colr__Commented:
sorry,
youd better put the name within quotations like this:

dim namesArray
redim namesArray(4)
namesArray(0) = "John"
nameArray(1) =" Bill"
nameArray(2) = "Mary"
nameArray(3) = "Jane"
0
dutchdjsAuthor Commented:
thanks everyone I did already use the following Query:

SELECT (SELECT FirstName + ' ' + LastName FROM NamesTable WHERE ID = issues.assignedto = namestable.id) AS 'AssignedTo' FROM Issues

But I prefer also the colr method :)

Now I get myself to another question;

what is the code must I use when the duedate (the final date) is over-date so that an other colom say that I am to late with that issue where the due date is already passed?

example:
duedate        today date    to late
31-12-2004   23-11-2004   NO
31-10-2004   23-11-2004   YES

how do I automaticly check the To Late colom and fill the answer with YES or NO??

thanks again guys


 
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.