Evaluate a recordset value

A

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
 
P

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
 
R

roger

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

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 ,
Click to expand...

Have you tried

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

just to check for any invisible characters?

You could also try

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

Mike Brind

altreed said:
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.
Click to expand...

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?
 
A

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
 
A

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
 
B

Bob Barrows [MVP]

altreed said:
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 & ""
Click to expand...

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
 
M

Mike Brind

Bob Barrows said:
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
Click to expand...

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 "
 
P

Pete near MK

altreed said:
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
Click to expand...

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
 
A

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
 
B

Bob Barrows [MVP]

altreed said:
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
Click to expand...

How about

if trim(rs("stageoferror"))="Closed" then
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Set value after clicking in field 11
Checking a value of a recordset and seeing if a queried string valueexists 2
Trouble accessing a value within a JSON string. 1
Displaying recordset value if it's there 1
Evaluate postgres boolean field 3
recordset problem 14
How to extract all values except the last value in a string separated by comma in sql 2
ASP Access Recordset Paging 6

Members online

No members online now.
Total: 23 (members: 0, guests: 23)
Robots: 504

Forum statistics

Threads
473,769
Messages
2,569,579
Members
45,053
Latest member
BrodieSola

Latest Threads

Top