Why are Cstr and Replace not working in this code? 1

PSchubert · Feb 24, 2011

Hi all,

This is where I go when I can't figure it out, 'cuz you people rock!
Here's my code, seemingly simple enough:

Code:

<%@ LANGUAGE="VBSCRIPT" %>
<%   
Option Explicit

	Dim conn			
	Dim rs			
	Dim strSQL			
	Dim strConnection		
	Dim AcctNum
	Dim ItemNum
	Dim i

	Set conn = Server.CreateObject("ADODB.Connection")
	Set rs = Server.CreateObject("ADODB.Recordset")


	strConnection = "FILEDSN=" & server.MapPath ("SACA.dsn")

	AcctNum = Session("number")

	conn.Open strConnection

	strSQL = "SELECT nfldItemNum, ynfldCheckInOut, tfldArtist, tfldTitle, tfldLocation FROM tblItems WHERE nfldAccountNum=" & AcctNum & " ORDER BY idxItems ASC;"

	set rs = conn.Execute (strSQL)

	If (rs.BOF and rs.EOF) Then
		Response.Write "No records found"
		Response.End

		rs.Close
		conn.close
		set rs=nothing
		set conn=nothing
		Response.End
	End If

		rs.MoveFirst
		Do Until rs.EOF
[COLOR=#ff0000]		Cstr(rs("ynfldCheckInOut"))
		rs("ynfldCheckInOut").Replace("-1", "IN")[/color]
		rs.MoveNext
		Loop

	Response.Write("<TITLE>" & Session("name") & " Collection</TITLE>")
	Response.write("<left><b>" & Session("name") & " Collection, Account #" & Session("number") & "<br><a href=../asp/logout.asp>Logout</a></b></left><br>")
%>

<HTML>
<HEAD>

</HEAD>

<BODY>

<br>

<TABLE BORDER="1" CELLPADDING="2" CELLSPACING="1" WIDTH="100%">

<%
	Response.Write "<TR BGCOLOR=""#CCCCCC""><TH>Item Number</TH><TH>IN or OUT</TH><TH>Artist</TH><TH>Title</TH><TH>Location</TH></TR>"

' -- Now output the contents of the Recordset
	rs.MoveFirst
	Do While Not rs.EOF
		' -- output the contents
		Response.Write "<TR>"
		For i = 0 to rs.Fields.Count - 1
		Response.Write "<TD><FONT FACE=""ARIAL"" SIZE=""1"">" & rs.Fields(i) & "</font></TD>"
		Next
		Response.write "</TR>"
		' -- move to the next record
		rs.MoveNext
	Loop

	rs.Close
	set rs = Nothing
	conn.Close
	set conn = Nothing
%>

</TABLE>
</BODY>
</HTML>

I've tried with and without the loop, and in different places.

Here's the story: I have a Yes/No field in the recordset, [ynfldCheckInOut], that returns (obviously) -1 or 0. I want to replace that value in the html table with the text string "IN" or "OUT", depending (right now I'm just working with "IN").

Any assistance greatly appreciated!

MakeItSo · Feb 25, 2011

Howdy,

this can't work!

I have a Yes/No field in the recordset, [ynfldCheckInOut], that returns (obviously) -1 or 0. I want to replace that value in the html table with the text string "IN" or "OUT", depending (right now I'm just working with "IN").

Code:

Cstr(rs("ynfldCheckInOut"))

1.) You're not assigning this cstringed value to anything

Code:

rs("ynfldCheckInOut").Replace("-1", "IN")

2.) What are you trying to do here? replace the value with IN in the recordset?

Your rs holds the value as a YES/NO value, resp. -1/0, NOT as a string value, hence no string operations on the recordset.
You cannot replace the value in the recordset and then hope to pull a "IN" or "OUT" from the DB.

Here's one way you could achieve what you try to do:

This is your table structure:

-----------------------------------------------------------
Item Number | IN or OUT | Artist | Title | Location

This means your IN/OUT field is the second field.

- Remove / Comment out your first recordset do/loop where you tried to do the replacing

- Define yourself a little Array that looks like this:

Code:

Dim inout

inout=array("IN", "OUT")

Now inout(0) will be equivalent to "IN", inout(1)="OUT". OK?

Yours field value in case of "IN" is rs([your IN/OUT field])=-1
in case of "OUT" is rs([your IN/OUT field])=0

Now the trick: if you add 1 to these values, you'll have the correct index for your string array!
;-)

==>

Code:

[b]Dim temp[/b]
    Do While Not rs.EOF
        ' -- output the contents
        Response.Write "<TR>"
        For i = 0 to rs.Fields.Count - 1
          [b]temp=rs.Fields(i)
          If i=1 Then temp=inout(temp+1)[/b]
        Response.Write "<TD><FONT FACE=""ARIAL"" SIZE=""1"">" & [b]temp[/b] & "</font></TD>"
        Next
        Response.write "</TR>"
        ' -- move to the next record
        rs.MoveNext
    Loop

This should be all the replacing needed!
Warning: untested.

[pipe]

Cheers,
MakeItSo

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

PSchubert · Feb 25, 2011

Bingo! Works like a charm. Brilliant, elegant. Thank you.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Why are Cstr and Replace not working in this code? 1

PSchubert

Technical User

MakeItSo

Programmer

PSchubert

Technical User

Similar threads

Part and Inventory Search

Sponsor