Weird problem with SQL, VB6 and GetRows

Ok, here's a weird one...

I have a table in a SQL server DB. It has 6 fields, one of which is a varchar(50). I also have an ActiveX DLL written in VB6 with a function that basically does a "Select * from tableX" and assigns the output to the function value (A Variant) using dbconnecter.getrows. e.g.

functionname = dbconnector.getrows

The result is a nice little 2-D array that I can read from ASP very easily and manipulate as I wish.

Here's the problem.
I changed the varchar(50) field into a text(16) field. Low and behold, Getrows now assigns EMPTY to the all the character fields (Varchar or text) in the table unless I change it back. I've since done this, and am now using a varchar(200) because I needed the extra space.

Now I do have a text(16) field already in the table, does getrows have a problem with more than one text field? Why is this behaviour happening? Any ideas? This question isn't urgent, as I have a fix by simply using a varchar(200), but a text field would be nicer.

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.

The TEXT datatype is similar to the image datatype.  
You sort of have to think of it as being "blobs" of data, you can use the GetChunk method of a  recordset.

When you try to read data from a query that contains more than one text field you may be unable to read the data from the text fields declared after the first text field in the query.

Info about your broblem with more than one text field can be found here:

Performance Issues:
Avoid using the TEXT or NTEXT data types in your ASP applications. Both of these data types can slow down data retrieval substantially. Instead, try to use CHAR, NCHAR, VARCHAR, or NVARCHAR data types instead. If the data you need to retrieve is greater than 8,000 characters, consider splitting your data between two or more columns, retrieving the data as a unit, and the recombining it on a web page.

Another option, if the text is just too long, is to store the a TEXT or NTEXT data type, but then to write it out as static HTML file onto the web server whenever the text is changed, not every time the ASP page is called. This will substantially reduce the overhead due to the use of the TEXT or NTEXT data type. Then have your database store the URL or file location to the static text page, and then insert the file into your ASP pages when the page is called by a user. If you like, use #INCLUDE FILE to insert the static HTML page into your ASP page.


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
TalcyonAuthor Commented:
I suspected the problem might be with multiple TEXT fields in the table. Something I'll have to remember in future, along with the other ADO bugs. As for using getchunk, I may consider it if the need arises.

As for the performance issues, I haven't seen any or the speed degradation is not noticeable, and I've run it through the optmiser without any issues. As for splitting the content using one of the variations on Char amongst multiple columns, eeuch! Sorry, but that just sounds far too messy. The other option is a possibility, however I'd prefer to reduce the number of web-pages rather than increase them. As long as the server involved has sufficient grunt, it shouldn't be a problem.

Many thanks for the Technet ref, just something to remember in future.
Good to here that you got it sorted out and know about the 'only one text data field'.

The performance issues, are not issues as long as you dont notice them as such.
I just put that there as a good to know, just in case comment.

Good luck with your solution Talcyon.
TalcyonAuthor Commented:
Thanks for the luck ;)

I did discover something when I was tracking down the problem, that you and others may be interested in...

Typically I use Getrows to pull everything out of the recordset into a 2-D array, it's not the best thing I know and that's what caused the problem in the first place. However, I found that if I manually created an array in the function and assigned the fields of the records to each array element, then assigned the array to the function, everything worked perfectly!

dim arry(2)
arry(0) = recordfield("X")
arry(1) = recordfield("Y")
arry(2) = recordfield("Z")

functionname = arry

This is basically what GetRows (or should I say Getwoes) should be doing, and fails abysmally when there is more than one TEXT(16) type field present. To me this looks definitely like a bug in Getrows. But hey! I'm not Microsoft, and I don't have the source-code to fix it. Never mind.

Many thanks for the help, and I hope someone else learns from this. It may save some tearing of hair.
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

From novice to tech pro — start learning today.