Weird problem with SQL, VB6 and GetRows

Posted on 2003-11-26
Medium Priority
Last Modified: 2008-02-01
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.

Question by:Talcyon
  • 2
  • 2

Accepted Solution

SNilsson earned 2000 total points
ID: 9824153
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.


Author Comment

ID: 9824325
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.

Expert Comment

ID: 9825094
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.

Author Comment

ID: 9825209
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.

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn different types of Android Layout and some basics of an Android App.
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Loops Section Overview

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question