HTMLEncode and RS Fields 1

[nicks60610]

I have a query from which I am pulling data from 4 tables, but the problem I have now is that in 2 of the tables, I am using 2 fields which have identical names;

WO_WORK_ORDER.DESCRIPTION and SY_WO_STATUS.DESCRIPTION. They are two different fields, but both are text fields. Now when I do my table and display the results, how can I separate the two fields?

<%=Server.HTMLEncode(rs.Fields(&quot;WO_BASE&quot.Value)%>
<%=Server.HTMLEncode(rs.Fields(&quot;PLANNER&quot.Value)%
<%=Server.HTMLEncode(rs.Fields(&quot;EMPLOYEE_ID&quot.Value)%
<%=Server.HTMLEncode(rs.Fields(&quot;EQUIPMENT&quot.Value)%
<%=Server.HTMLEncode(rs.Fields(&quot;REQUESTOR&quot.Value)%
<%=Server.HTMLEncode(rs.Fields(&quot;DESCRIPTION&quot.Value)%
<%=Server.HTMLEncode(rs.Fields(&quot;LONG_DESCRIPTION&quot.Value)%
<%=Server.HTMLEncode(rs.Fields(&quot;DESCRIPTION&quot.Value)%
<%=Server.HTMLEncode(rs.Fields(&quot;SHIFT&quot.Value)%><BR></FONT></TD>


Thanks in advance!!

 

[GaryC123]

Identify with the table name
rs.Fields(&quot;tablename.DESCRIPTION&quot.Value)%

http://www.intouchmedialtd.com

 

[simonchristieis]

BTW

rs.Fields(&quot;tablename.DESCRIPTION&quot.Value)

Fields and Value are defaults

You could also write it like

rs(&quot;tablename.DESCRIPTION&quot

 

[nicks60610]

Hmmm, did not work - all I get is a blank where it should fill in that field....

My supervisor mentioned something about declaring it as a variable and then call the variable name, have you tried that before?

 

[novasoy]

I almost always assign my recordset values to a variable (or an object) when I am going to be working with them for any length of time. That way I can close the recordset and/or connection sooner. This is a matter of personal preference. But on to your question.

If I was in your situation, I would probably just use the index number of the field (assuming that's easy to figure out). In other words I would use rs.Fields(23).Value or whatever. Granted, this is less self-documenting, but it would work. I would just add a comment on the line above it saying what I was doing and why.

 

[nicks60610]

I did this, and when I do a response.write of this variable, it shows it as text, i.e. &quot;Entered&quot;, but when I run it in the report output, I get a numerical value, i.e. &quot;entered&quot; = 0, so it shows a 0. What am I missing??

Dim Status
Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot
objConn.Open &quot;Provider=MSDAORA.1;Password=xxxxx;User ID=xxxx;Data Source=xxxxxxxxx;Persist Security Info=True;&quot;

Set objRs = Server.CreateObject(&quot;ADODB.Recordset&quot

objRs.Open &quot;SELECT * FROM SY_WO_STATUS WHERE SY_WO_STATUS.PLANT = '854' &quot;, objConn
Status = (objRs.fields(&quot;DESCRIPTION&quot.Name)
set objRs = nothing

And I call the variable &quot;Status&quot; in my Rs.Fields in the table...

 

[shaddow]

You could use Field indexex( wich is the place number in the select clause)

<%=Server.HTMLEncode(rs.Fields(0).Value)%>
<%=Server.HTMLEncode(rs.Fields(1).Value)%
<%=Server.HTMLEncode(rs.Fields(2).Value)%
<%=Server.HTMLEncode(rs.Fields(3).Value)%
<%=Server.HTMLEncode(rs.Fields(4).Value)%
<%=Server.HTMLEncode(rs.Fields(5).Value)%
<%=Server.HTMLEncode(rs.Fields(6).Value)%
<%=Server.HTMLEncode(rs.Fields(7).Value)%
<%=Server.HTMLEncode(rs.Fields(8).Value)%><BR></FONT></TD>

 or you can just use ALIASES

select *,WO_WORK_ORDER.DESCRIPTION as WODESC and SY_WO_STATUS.DESCRIPTION as SYDESC

and then

<%=Server.HTMLEncode(rs.Fields(&quot;WODESK&quot;).Value)%
<%=Server.HTMLEncode(rs.Fields(&quot;SYDESC&quot;).Value)%

________
George, M

 

[MadJock]

You could try using aliasing in your SQL statement SQL statement. This will give a different name to each column

For example, your SELECT clause would look more ike this:

SELECT wo_work_order.description AS woDescrip, sy_wo_status.decsrip AS statDescrip

You can then refer to them by the alias (the word after 'AS')

e.g.

<%=Server.HTMLEncode(rs.Fields(&quot;woDescrip&quot.Value)%>

Hope this helps,

Graeme




website:

http://www.graeme-taylor.co.uk

 

[nicks60610]

Thanks guys, that works great!!!