Evaluate a recordset value

[altreed]

Hi,

I am new to ASP, HTML and iis.

I have my asp code working so that I can retrieve my desired record
from the database. I can place the data on the screen in table form.
All works fine.

I wish to evaluate one field in the record and depending on the value
in that field I wish to change the colour of the line in the table. As
in a status check.

My DB field is NCHAR.

I need to be able to do something like this....

IF rs.statusoferror = "Closed" THEN
response.write "it works"
END IF

I cannot find a way to return true for - rs.statusoferror = "Closed"
It is like the datatype is not compatible with my variable. If I
write rs.statusoferror to the screen it comes up with - Closed , but I
cannot evaluate with the same text.

I tried CAST, unsuccessfully. Am I barking up the wrong tree?

Cheers for any help.

Dave

[Pete near MK]

Dave,

I have resolved a very similar problem by using the following technique:

Firstly define a variable and set this equal to a valid HTML colour based on
the result of your database field value ie:

Dim MyColour

If rs.Fields(“NCHAR�).value = “Closed� Then
MyColour = “FFFFFF� ‘This is white in HTML
Else
MyColour = ‘some other valid HTML colour
End If

Then in the body of your HTML where you define the table add this:

…..bgcolor = <% response.write(MyColour) %……………

A more up to date approach would be to call a different style from your CSS
but still using the same technique.

This is my first posting having started to learn ASP about a year ago, so I
hope it helps you!

Cheers,
Pete
"altreed" wrote:

>
Hi,

I am new to ASP, HTML and iis.

I have my asp code working so that I can retrieve my desired record
from the database. I can place the data on the screen in table form.
All works fine.

I wish to evaluate one field in the record and depending on the value
in that field I wish to change the colour of the line in the table. As
in a status check.

My DB field is NCHAR.

I need to be able to do something like this....

IF rs.statusoferror = "Closed" THEN
response.write "it works"
END IF

I cannot find a way to return true for - rs.statusoferror = "Closed"
It is like the datatype is not compatible with my variable. If I
write rs.statusoferror to the screen it comes up with - Closed , but I
cannot evaluate with the same text.

I tried CAST, unsuccessfully. Am I barking up the wrong tree?

Cheers for any help.

Dave

[roger]

"altreed" <ca*************@hotmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...

IF rs.statusoferror = "Closed" THEN
response.write "it works"
END IF

Is "rs" a recordset and "statusoferror" a field?

If so then try

IF rs("statusoferror") = "Closed" THEN

>If I
write rs.statusoferror to the screen it comes up with - Closed ,

Have you tried

response.write "[" & rs("statusoferror") & "]"

just to check for any invisible characters?

You could also try

response.write vartype(rs("statusoferror"))

--
roger

[Mike Brind]

"altreed" <ca*************@hotmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...

>
Hi,

I am new to ASP, HTML and iis.

I have my asp code working so that I can retrieve my desired record
from the database. I can place the data on the screen in table form.
All works fine.

I wish to evaluate one field in the record and depending on the value
in that field I wish to change the colour of the line in the table. As
in a status check.

My DB field is NCHAR.

I need to be able to do something like this....

IF rs.statusoferror = "Closed" THEN
response.write "it works"
END IF

I cannot find a way to return true for - rs.statusoferror = "Closed"
It is like the datatype is not compatible with my variable. If I
write rs.statusoferror to the screen it comes up with - Closed , but I
cannot evaluate with the same text.

The syntax you are using (with the dot operator) treats statusoferror as if
it is a method or property of the recordset object, which it is not. It's a
field name.

If rs("statusoferror") = "Closed" Then
response.write "it works"
End If

Your code should have thrown an error. Do you have On Error Resume Next
further up the page? If so, remove it. It hides error messages that are
helpful in debugging. And what code did you use to successfully get
"Closed" to appear on the page with response.write?

--
Mike Brind

[altreed]

Cheers Guys,

I am amazed how quick the help has come in :D

I was not quoting my code, that is at work and I am off for a couple of
days, so I was mainly just showing my approach rather than code.

Thanks for your guidance, I will have a tinker with your suggestions
and post back my results.

Thanks
Dave

[altreed]

Hi,

Right, I tried the .value method, I received ordinal errors!

Here is the actual ASP

<%
Dim rs, SQL, var, state, variable
state = "Closed"
'Capture the query string from the URL request line
var = Request.querystring("inx")
'create new recordset
Set rs = Server.CreateObject("ADODB.Recordset")

'Define the SQL query criteron
SQL = "SELECT * FROM tblErrorReport WHERE inx = " & var & ""

'Open the recordset with the SQL query
rs.Open SQL, adoCon

'If the data is not in the current table, check the archive table
If rs.EOF then
'close old recordset
rs.Close
'Clear the recordset
Set rs = Nothing
'create new recordset
Set rs = Server.CreateObject("ADODB.Recordset")
'define new SQL query critereon
SQL = "SELECT * FROM tblErrorArchive WHERE inx = " & var & ""
'Open the new recordset with the SQL query
rs.Open SQL, adoCon
end if

' Display record data
Response.Write("{" & rs("stageoferror") & "}")
if rs("stageoferror") = "Closed " then
Response.Write("Arrr")
end if

Response.Write("<table border=" & 1 &">")
Response.Write("<tr>")
Response.Write("<td>Job ID</td>")
Response.Write ("<td>" & var & "</td>")
Response.Write("</tr><tr>")
Response.Write("<td>Date reported</td>")
Response.Write("<td>" & rs("errordate") & "</td>")
Response.Write("</tr><tr>")
Response.Write("<td>Incident reporter</td>")
Response.Write("<td>" & rs("username") & "</td>")
Response.Write("</tr><tr>")
Response.Write("<td>Item requiring attention</td>")
Response.Write("<td>" & rs("computername") & "</td>")
Response.Write("</tr><tr>")
Response.Write("<td>Item located</td>")
Response.Write("<td>" & rs("room") & "</td>")
Response.Write("</tr><tr>")
Response.Write("<td>Stage of response</td>")
Response.Write("<td>" & rs("stageoferror") & "</td>")
Response.Write("</tr><tr>")
Response.Write("</tr><tr>")
Response.Write("<td>Issue reported</td>")
Response.Write("<td>" & rs("error") & "</td>")
Response.Write("</tr><tr>")
Response.Write("<td>Technician assigned</td>")
Response.Write("<td>" & rs("assignedtechnician") & "</td>")
Response.Write("</tr><tr>")
Response.Write("<td>Notes on the issue</td>")
Response.Write("<td>" & rs("notes") & "</td>")
Response.Write("</tr><tr>")
Response.Write("</table>")

'Close and clear recordset
rs.Close
Set rs = Nothing
%>

Here is what is posted (as near as I can replicate the table)

{Closed }
Job ID 999
Date reported 12/01/2006
Incident reporter LONGLANDH
Item requiring attention all those with covers upside down
Item located 014
Stage of response Closed

Issue reported several machines not working in 014, they have the
covers upside down so you can identify them. thanks
Technician assigned FREEMANTLEJ
Notes on the issue Sorted

The ASP code works fine, except for the if/then. I am sure there are
many ways to achieve my table, but the bit that is bothering me is
being able to evaluate the field variable and process according to the
value it holds.

I am not bothered about the table or colour aspect at this time, I need
to be able to evaluate the variable before I am bothered about the
colour of the table row.

Any Help would be appreciated

Thanks
Dave

[Bob Barrows [MVP]]

altreed wrote:

Hi,

Right, I tried the .value method, I received ordinal errors!

Here is the actual ASP

<%
Dim rs, SQL, var, state, variable
state = "Closed"
'Capture the query string from the URL request line
var = Request.querystring("inx")
'create new recordset
Set rs = Server.CreateObject("ADODB.Recordset")

'Define the SQL query criteron
SQL = "SELECT * FROM tblErrorReport WHERE inx = " & var & ""

Don't use selstar: http://www.aspfaq.com/show.asp?id=2096

Help us (and yourself) by stripping your code down to the minimum required
to reproduce your problem.
Start by creating a test page and changing the sql statement to:

SQL = "SELECT stageoferror FROM tblErrorReport WHERE inx = " & var & ""

Then open the recordset and loop through the recordset:

do until rs.eof
response.write rs("stageoferror") & "<BR>"
loop

If this gives you the expected results, then add in the if...then logic,
remembering to use an else clause at this stage of debugging:

do until rs.eof
response.write rs("stageoferror") & "<BR>"
if rs("stageoferror") = "Closed " then
Response.Write("Arrr")
else
Response.Write("stageoferror does not equal Closed")
end if
Response.Write "<BR><BR>"
loop
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Mike Brind]

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:eM**************@TK2MSFTNGP04.phx.gbl...

altreed wrote:

>Hi,

Right, I tried the .value method, I received ordinal errors!

Here is the actual ASP

<%
Dim rs, SQL, var, state, variable
state = "Closed"
'Capture the query string from the URL request line
var = Request.querystring("inx")
'create new recordset
Set rs = Server.CreateObject("ADODB.Recordset")

'Define the SQL query criteron
SQL = "SELECT * FROM tblErrorReport WHERE inx = " & var & ""

Don't use selstar: http://www.aspfaq.com/show.asp?id=2096

Help us (and yourself) by stripping your code down to the minimum required
to reproduce your problem.
Start by creating a test page and changing the sql statement to:

SQL = "SELECT stageoferror FROM tblErrorReport WHERE inx = " & var & ""

Then open the recordset and loop through the recordset:

do until rs.eof
response.write rs("stageoferror") & "<BR>"
loop

If this gives you the expected results, then add in the if...then logic,
remembering to use an else clause at this stage of debugging:

do until rs.eof
response.write rs("stageoferror") & "<BR>"
if rs("stageoferror") = "Closed " then
Response.Write("Arrr")
else
Response.Write("stageoferror does not equal Closed")
end if
Response.Write "<BR><BR>"
loop

Adding to what Bob said, you might also want to try Trim(rs("stageoferror"))
to get rid of any whitespace that may have crept in.

"Closed" is not the same as "Closed "

--
Mike Brind

[Pete near MK]

"altreed" wrote:

>
Hi,

Right, I tried the .value method, I received ordinal errors!

Here is the actual ASP

<%
Dim rs, SQL, var, state, variable
state = "Closed"
'Capture the query string from the URL request line
var = Request.querystring("inx")
'create new recordset
Set rs = Server.CreateObject("ADODB.Recordset")

'Define the SQL query criteron
SQL = "SELECT * FROM tblErrorReport WHERE inx = " & var & ""

'Open the recordset with the SQL query
rs.Open SQL, adoCon

'If the data is not in the current table, check the archive table
If rs.EOF then
'close old recordset
rs.Close
'Clear the recordset
Set rs = Nothing
'create new recordset
Set rs = Server.CreateObject("ADODB.Recordset")
'define new SQL query critereon
SQL = "SELECT * FROM tblErrorArchive WHERE inx = " & var & ""
'Open the new recordset with the SQL query
rs.Open SQL, adoCon
end if

' Display record data
Response.Write("{" & rs("stageoferror") & "}")
if rs("stageoferror") = "Closed " then
Response.Write("Arrr")
end if

Response.Write("<table border=" & 1 &">")
Response.Write("<tr>")
Response.Write("<td>Job ID</td>")
Response.Write ("<td>" & var & "</td>")
Response.Write("</tr><tr>")
Response.Write("<td>Date reported</td>")
Response.Write("<td>" & rs("errordate") & "</td>")
Response.Write("</tr><tr>")
Response.Write("<td>Incident reporter</td>")
Response.Write("<td>" & rs("username") & "</td>")
Response.Write("</tr><tr>")
Response.Write("<td>Item requiring attention</td>")
Response.Write("<td>" & rs("computername") & "</td>")
Response.Write("</tr><tr>")
Response.Write("<td>Item located</td>")
Response.Write("<td>" & rs("room") & "</td>")
Response.Write("</tr><tr>")
Response.Write("<td>Stage of response</td>")
Response.Write("<td>" & rs("stageoferror") & "</td>")
Response.Write("</tr><tr>")
Response.Write("</tr><tr>")
Response.Write("<td>Issue reported</td>")
Response.Write("<td>" & rs("error") & "</td>")
Response.Write("</tr><tr>")
Response.Write("<td>Technician assigned</td>")
Response.Write("<td>" & rs("assignedtechnician") & "</td>")
Response.Write("</tr><tr>")
Response.Write("<td>Notes on the issue</td>")
Response.Write("<td>" & rs("notes") & "</td>")
Response.Write("</tr><tr>")
Response.Write("</table>")

'Close and clear recordset
rs.Close
Set rs = Nothing
%>

Here is what is posted (as near as I can replicate the table)

{Closed }
Job ID 999
Date reported 12/01/2006
Incident reporter LONGLANDH
Item requiring attention all those with covers upside down
Item located 014
Stage of response Closed

Issue reported several machines not working in 014, they have the
covers upside down so you can identify them. thanks
Technician assigned FREEMANTLEJ
Notes on the issue Sorted

The ASP code works fine, except for the if/then. I am sure there are
many ways to achieve my table, but the bit that is bothering me is
being able to evaluate the field variable and process according to the
value it holds.

I am not bothered about the table or colour aspect at this time, I need
to be able to evaluate the variable before I am bothered about the
colour of the table row.

Any Help would be appreciated

Thanks
Dave

Dave,

Sorry I did not really understand your question in my first reply.

From what I can see where you have this line:

Response.Write("{" & rs("stageoferror") & "}")

the recordset is closed, and it therefore can't work.

Hope this helps.
Pete

[altreed]

Thanks for your help, I am a noob so please excuse me.

I tried your suggestion with the trim method, I used this code....

Response.Write("{" & Trim(rs("stageoferror")) & "}")
if rs("stageoferror") = "Closed" then
Response.Write("it works")
else
Response.Write("stageoferror does not equal
Closed")
end if

I got this response... Spot the difference....!

{Closed}stageoferror does not equal Closed

No errors, no resume on error....I am flummoxed.

Cheers
Dave

Bob Barrows [MVP] wrote:

altreed wrote:

Hi,

Right, I tried the .value method, I received ordinal errors!

Here is the actual ASP

<%
Dim rs, SQL, var, state, variable
state = "Closed"
'Capture the query string from the URL request line
var = Request.querystring("inx")
'create new recordset
Set rs = Server.CreateObject("ADODB.Recordset")

'Define the SQL query criteron
SQL = "SELECT * FROM tblErrorReport WHERE inx = " & var & ""

Don't use selstar: http://www.aspfaq.com/show.asp?id=2096

Help us (and yourself) by stripping your code down to the minimum required
to reproduce your problem.
Start by creating a test page and changing the sql statement to:

SQL = "SELECT stageoferror FROM tblErrorReport WHERE inx = " & var & ""

Then open the recordset and loop through the recordset:

do until rs.eof
response.write rs("stageoferror") & "<BR>"
loop

If this gives you the expected results, then add in the if...then logic,
remembering to use an else clause at this stage of debugging:

do until rs.eof
response.write rs("stageoferror") & "<BR>"
if rs("stageoferror") = "Closed " then
Response.Write("Arrr")
else
Response.Write("stageoferror does not equal Closed")
end if
Response.Write "<BR><BR>"
loop
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows [MVP]]

altreed wrote:

Thanks for your help, I am a noob so please excuse me.

I tried your suggestion with the trim method, I used this code....

Response.Write("{" & Trim(rs("stageoferror")) & "}")
if rs("stageoferror") = "Closed" then

How about

if trim(rs("stageoferror"))="Closed" then
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[altreed]

Thanks Bob,

I am indeed a silly winkle. Your astute observation has solved my
quandry.

I am now free to continue with my little application

Great support.

Thanks again
Dave

Bob Barrows [MVP] wrote:

altreed wrote:

Thanks for your help, I am a noob so please excuse me.

I tried your suggestion with the trim method, I used this code....

Response.Write("{" & Trim(rs("stageoferror")) & "}")
if rs("stageoferror") = "Closed" then

How about

******* if trim(rs("stageoferror"))="Closed" then *******
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.