recordset variables being "forgotten" 1

[gbaughma]

All right... this isn't making sense, and it's happened to me a few times. There must be a reason for it though.....

I read a recordset from an SQL server... but it seems that after I use a couple of the fields, the rest just "Disappear". Code follows:

<%
' impartial review and appeal program
' passes mode = impartial or appeal
' and id = the sequence number of the consequence

strMode = Request.QueryString("mode")
iID = Request.QueryString("id")

Dim cn, cn2, rs

Set cn = Server.CreateObject("ADODB.Connection")
Set cn2 = Server.CreateObject("ADODB.Connection")
cn.Open "DSN=MesabiSQL;Uid=********;Pwd=********;"
cn2.Open "DSN=LoggingSQL;Uid=******;Pwd=*******;"

SQLStmt = "SELECT * FROM LoggingSQL.dbo.Consequence t1 join MesabiSQL.dbo.Youths As t2 on t1.YouthID = t2.YouthID WHERE "
SQLStmt = SQLStmt & "Sequence=" & iID & "; "

Set rs = cn.Execute(SQLStmt)

If rs.BOF and rs.EOF Then
    Response.Write "Error!  Could not find matching record.  Please report the following to MIS:<br>"
    Response.Write "/Consequence/Review.asp -- id = " & iID & "; mode=" & strMode & " BOF AND EOF True line 17"
    Response.End
End IF

Response.Write "<h3><i>" & rs("YLName") & ", " & rs("YFName") & "</i> - "

If strMode = "impartial" Then
    Response.Write "Impartial Review"
    Else
    Response.Write "Appeal"
End If

Response.Write "</h3><br />"

Response.Write "<strong>Violation:</strong><br>" & rs("Violation")
Response.Write "<br><strong>Original Consequence:</strong><br>" & rs("Consequence")

%>

Now, the weird thing is... the rs("Consequence") and rs("Violation") are blank when I run this script. HOWEVER, if I put in response.write rs("Violation") immediately AFTER the SQL Query, the information is there!

What the heck??? This is driving me crazy.... any thoughts on why my recordset goes "blank"?

Thanks in advance!

(Note: The above code is not complete.... but that's what I've written so far, when I realized that the recordset data was disappearing... ugh!)



Just my 2¢
-Cole's Law: Shredded cabbage

--Greg

http://parallel.tzo.com

 

[jfdabiri]

this:
If rs.BOF and rs.EOF Then ....
should be:
If rs.BOF OR rs.EOF Then ....
what's REPONSE.END for?

 

[gbaughma]

If rs.BOF AND rs.EOF are both true, then it didn't find any records, gives an error, and Response.End stops the script.



Just my 2¢
-Cole's Law: Shredded cabbage

--Greg

http://parallel.tzo.com

 

[Sheco]

Is it possible that those field contain an empty string or a null value?

 

[gbaughma]

Sheco:

nope.... if I do a response.write rs("Violation") right after the set rs=cn.execute(SQLStmt) then it shows the value for that recordset field.

HOWEVER, if it's "later on" in the page (like where rs("Violation") shows in the code above) then it's blank.

It's not making sense to me.



Just my 2¢
-Cole's Law: Shredded cabbage

--Greg

http://parallel.tzo.com

 

[Chopstik]

Nothing looks out of the ordinary with what you have so far. Out of curiosity, when you say that it does not find the recordset values (consequence or violation), does it print out the other part of the line that you've hard-coded ("<br><strong>Original Consequence:</strong><br>")? I've found, for some reason I've not really tracked down, that if there is no recordset value, it will skip the entire response.write line. However, if it has a value (even if it's just an empty string), then the entire line will still print out. Not sure if you've ever had a similar situation but something to review.

Also, have you had the same issue if you try to print out those two variables elsewhere on the page? Try moving them before and after some of your other if/then statements to see if you can pinpoint it to something else...

------------------------------------------------------------------------------------------------------------------------
"As for the bureacratic, politically-correct, mollycoddling, asinine, Romper Room antics of...clients and management, just read up on Dilbert. It's not funny - it's a training manual."
- Mike

 

[gbaughma]

That's the issue....

I took out the If statements in an effort to narrow it down... it didn't make a difference.

Yes, it prints all of the rest, like "Original consequence:" and then nothing after it.

However, put in:

Response.Write rs("Violation") IMMEDIATELY AFTER the set rs=cn.Execute statement, then it prints it. But if it's after the response.write rs("YLName") and rs("YFName"), then it doesn't.

It's making me crazy!

The only thing that I can *somewhat* think of is that it's a joined query, from two different databases.... and YLName and YFName are in the MesabiSQL table, whereas Consequence and Violation are in the LoggingSQL database.

I went right into SQL Server and put in the query that I was using for the SQLStmt, and it worked fine, and returned the entire record.

ARRRRRRRRRRRRRGH!



Just my 2¢
-Cole's Law: Shredded cabbage

--Greg

http://parallel.tzo.com

 

[Chopstik]

Try adding something after recordset variable and see if it prints that.

------------------------------------------------------------------------------------------------------------------------
"As for the bureacratic, politically-correct, mollycoddling, asinine, Romper Room antics of...clients and management, just read up on Dilbert. It's not funny - it's a training manual."
- Mike

 

[damber]

try it with a dsn-less connection string - odbc is horrible, and has caused me lots of problems in the past, some similar to this. Otherwise, try explicitly naming the SELECT fields and use them in the sequence you selected them.



A smile is worth a thousand kind words. So smile, it's easy!