asked on

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.

Wayne Barron

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

ASKER CERTIFIED SOLUTION

G_H

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

G_H

Sorry, forgot to add...

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

GH

Anthony Perkins

>>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.

sybe

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"

Anthony Perkins

I am going to go out on a limb and suggest that you are using SELECT *, right?

oruedin

ASKER

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.

G_H

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

oruedin

ASKER

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

oruedin

ASKER

Gave me a lead.

Anthony Perkins

>>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.