[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

simple ASP SQL question

Posted on 2004-11-22
6
Medium Priority
?
254 Views
Last Modified: 2010-04-17
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>
0
Comment
Question by:dutchdjs
  • 2
  • 2
  • 2
6 Comments
 
LVL 97

Accepted Solution

by:
Lee W, MVP earned 500 total points
ID: 12645253
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
 

Author Comment

by:dutchdjs
ID: 12645453
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
 
LVL 97

Expert Comment

by:Lee W, MVP
ID: 12645534
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 8

Expert Comment

by:colr__
ID: 12656724
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
 
LVL 8

Expert Comment

by:colr__
ID: 12656761
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
 

Author Comment

by:dutchdjs
ID: 12658439
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

868 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