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.statusoferro r = "Closed" THEN
response.write "it works"
END IF

I cannot find a way to return true for - rs.statusoferro r = "Closed"
It is like the datatype is not compatible with my variable. If I
write rs.statusoferro r 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]

RE: Evaluate a recordset value

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(“NC HAR”).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.statusoferro r = "Closed" THEN
response.write "it works"
END IF
>
I cannot find a way to return true for - rs.statusoferro r = "Closed"
It is like the datatype is not compatible with my variable. If I
write rs.statusoferro r 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]

Re: Evaluate a recordset value

"altreed" <cardinal_phann g@hotmail.comwr ote in message
news:1161587343 .240282.312230@ i42g2000cwa.goo glegroups.com.. .

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

Is "rs" a recordset and "statusofer ror" a field?

If so then try

IF rs("statusoferr or") = "Closed" THEN

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

Have you tried

response.write "[" & rs("statusoferr or") & "]"

just to check for any invisible characters?

You could also try

response.write vartype(rs("sta tusoferror"))

--
roger

[Mike Brind]

Re: Evaluate a recordset value

"altreed" <cardinal_phann g@hotmail.comwr ote in message
news:1161587343 .240282.312230@ i42g2000cwa.goo glegroups.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.statusoferro r = "Closed" THEN
response.write "it works"
END IF
>
I cannot find a way to return true for - rs.statusoferro r = "Closed"
It is like the datatype is not compatible with my variable. If I
write rs.statusoferro r 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("statusoferr or") = "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]

Re: Evaluate a recordset value


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]

Re: Evaluate a recordset value


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.queryst ring("inx")
'create new recordset
Set rs = Server.CreateOb ject("ADODB.Rec ordset")

'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.CreateOb ject("ADODB.Rec ordset")
'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("stageoferro r") & "}")
if rs("stageoferro r") = "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>Incide nt reporter</td>")
Response.Write( "<td>" & rs("username") & "</td>")
Response.Write( "</tr><tr>")
Response.Write( "<td>Item requiring attention</td>")
Response.Write( "<td>" & rs("computernam e") & "</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("stageoferro r") & "</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>Techni cian assigned</td>")
Response.Write( "<td>" & rs("assignedtec hnician") & "</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]]

Re: Evaluate a recordset value

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.queryst ring("inx")
'create new recordset
Set rs = Server.CreateOb ject("ADODB.Rec ordset")
>
'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("stageoferro r") & "<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("stageoferro r") & "<BR>"
if rs("stageoferro r") = "Closed " then
Response.Write( "Arrr")
else
Response.Write( "stageoferr or 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]

Re: Evaluate a recordset value

"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcomwrote in message
news:eMdnSX19GH A.4524@TK2MSFTN GP04.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.queryst ring("inx")
>'create new recordset
>Set rs = Server.CreateOb ject("ADODB.Rec ordset")
>>
>'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("stageoferro r") & "<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("stageoferro r") & "<BR>"
if rs("stageoferro r") = "Closed " then
Response.Write( "Arrr")
else
Response.Write( "stageoferr or does not equal Closed")
end if
Response.Write "<BR><BR>"
loop
>

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

"Closed" is not the same as "Closed "

--
Mike Brind

[Pete near MK]

Re: Evaluate a recordset value



"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.queryst ring("inx")
'create new recordset
Set rs = Server.CreateOb ject("ADODB.Rec ordset")
>
'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.CreateOb ject("ADODB.Rec ordset")
'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("stageoferro r") & "}")
if rs("stageoferro r") = "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>Incide nt reporter</td>")
Response.Write( "<td>" & rs("username") & "</td>")
Response.Write( "</tr><tr>")
Response.Write( "<td>Item requiring attention</td>")
Response.Write( "<td>" & rs("computernam e") & "</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("stageoferro r") & "</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>Techni cian assigned</td>")
Response.Write( "<td>" & rs("assignedtec hnician") & "</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("stageoferro r") & "}")

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

Hope this helps.


Pete

[altreed]

Re: Evaluate a recordset value

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("stageo ferror")) & "}")
if rs("stageoferro r") = "Closed" then
Response.Write( "it works")
else
Response.Write( "stageoferr or does not equal
Closed")
end if

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

{Closed}stageof error 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.queryst ring("inx")
'create new recordset
Set rs = Server.CreateOb ject("ADODB.Rec ordset")

'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("stageoferro r") & "<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("stageoferro r") & "<BR>"
if rs("stageoferro r") = "Closed " then
Response.Write( "Arrr")
else
Response.Write( "stageoferr or 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]]

Re: Evaluate a recordset value

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("stageo ferror")) & "}")
if rs("stageoferro r") = "Closed" then

How about

if trim(rs("stageo ferror"))="Clos ed" 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]

Re: Evaluate a recordset value

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("stageo ferror")) & "}")
if rs("stageoferro r") = "Closed" then

>
How about
>
******* if trim(rs("stageo ferror"))="Clos ed" 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.