Recordset looses content when passing as parameter

Hi,

I have two routines. I call the second routine from the first one passing a variable that contains a recordset. When I try to print the content of the row (there's only one row) I miss the 5th and 6th values.

The lost values are very long text and it's a ntext type in MS SQL Server.

Function LoadRow()
		....
		
		RsRow.MoveFirst
		Response.Write("<br>-------------------:")
		Response.Write("<br>1:")
		Response.Write RsRow(1)
		Response.Write("<br>2:")
		Response.Write RsRow(2)
		Response.Write("<br>3:")
		Response.Write RsRow(3)
		Response.Write("<br>4:")
		Response.Write RsRow(4)
		Response.Write("<br>5:")
		Response.Write RsRow(5)
		Response.Write("<br>6:")
		Response.Write RsRow(6)
		Response.Write("<br>7:")
		Response.Write RsRow(7)
	
		LoadRowValues(RsRow) ' Load row values

		...
	End Function

	' -----------------------------------------------------------------
	' Load row values from recordset
	'
	Sub LoadRowValues(RsRow)
		Response.Write("<br>***********************************:")
		Response.Write("<br>1:")
		Response.Write RsRow(1)
		Response.Write("<br>2:")
		Response.Write RsRow(2)
		Response.Write("<br>3:")
		Response.Write RsRow(3)
		Response.Write("<br>4:")
		Response.Write RsRow(4)
		Response.Write("<br>5:")
		Response.Write RsRow(5)
		Response.Write("<br>6:")
		Response.Write RsRow(6)
		Response.Write("<br>7:")
		Response.Write RsRow(7)
		...
	End Sub

Open in new window


And this is what it's printed:

-------------------:
1:A11-10-02 Title1
2:28136
3:
4:
5:line: Jennifer 2, ZH
6:textttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttt
7:
***********************************:
1:A11-10-02 Title1
2:28136
3:
4:
5:
6:
7:


Does someone know what's the problem here? Could it be related with the IIS configuration?

Thank you.
oruedinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne BarronAuthor, Web DeveloperCommented:
I have seen some cases, where the data that is sent to the browser, must be done in order.

Example.
You are trying to get RsRow(1) - RsRow(7)
So, in your SELECT statement, you will want to do it like this.

"Select 1,2,3,4,5,6,7 from table"

Do you follow me? As I know that is not your real column names.
So, as it appears, is the way you want to write it in your select statement.

This may and may not be the issue, however, it sure looks like it to me.

Also, change your <br> to <br />.

Good Luck
Carrzkiss
0
G_HCommented:
This is only half an answer...

I have seen this before, with Classic ASP recordsets from both MS Access and MySQL. I rarely use the former or MS SQL...

I have been able to re-create this issue, which mearly involves trying to read the same record-element a second time. It does not however happen very often.

The only solution I have found to to read the recordset into a set of Variables, which can then be read consistantly.

 
Dim RsRowValue1
Dim RsRowValue2
Dim RsRowValue3
... etc
'' ## Replace Line 17 above with:
RsRowValue6 = RsRow(6)
response.write RsRowValue6
'' ## Replace Line 42 above:
response.write RsRowValue6

Open in new window


See if you still have the issue then...

GH
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
G_HCommented:
Sorry, forgot to add...

DIM's should be outside all functions & Subs to make them global...

GH
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Anthony PerkinsCommented:
>>The lost values are very long text and it's a ntext type in MS SQL Server.<<
This is a known problem and the solution is very simple, please post your SELECT statement and the schema to your table and we can suggest a trivial workaround.
0
sybeCommented:
Known issue. Simplest solution I know about is to make sure that the long text columns are last in the SELECT statement.

So, something like this:

"SELECT field1, fioeld2, field3, ........ , longtextfield, ntextfield FROM table"
0
Anthony PerkinsCommented:
I am going to go out on a limb and suggest that you are using SELECT *, right?
0
oruedinAuthor Commented:
Hi,

Thank you all.

Yes, I'm using SELECT *, but the funniest thing is that this code worked fine before and it crashed when we upgraded to IIS 7.  That's why I thought it was an issue with the IIS configuration.
0
G_HCommented:
I am not supprised you are getting this issue, when you were not with a previous config. There are however two basic answers being offered:

1. Order the fields in the select statement as per useage / and or content type

2. Read the values into variables, and then use the variables to do the display. (my offering)

Try these, and report back your findings...

I suspect the real issue is "known" but requires a "work-around" rather than a direct "fix".

GH
0
oruedinAuthor Commented:
I tried reading the recordset into a set of variables and I found out that:

1. It can't read more than once the same record-element. But it isn't a big deal since I read twice only to test.
2. It worked declaring the variables inside the Sub just before the asignment even when I'm reading it only once (which I find senseless).


Before it was:
 Tbl.TBL_fld1.Value = RsRow("TBL_fld1")

Now it is:
Rslocal = RsRow("TBL_fld1")
Tbl.TBL_fld1.Value = Rslocal

Thanks everybody
0
oruedinAuthor Commented:
Gave me a lead.
0
Anthony PerkinsCommented:
>>but the funniest thing is that this code worked fine before and it crashed when we upgraded to IIS 7.<<
Different providers.

There is an MSDN KB article covering this subject, but in essence it states to place all BLOB data types (ntext, text, image, which incidentally are deprecated and should not be used) at the end.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.