Solved

Weird problem with SQL, VB6 and GetRows

Posted on 2003-11-26
4
747 Views
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.

Jeff
0
Comment
Question by:Talcyon
  • 2
  • 2
4 Comments
 
LVL 8

Accepted Solution

by:
SNilsson earned 500 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:
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q200/1/24.asp&NoWebContent=1

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.

0
 

Author Comment

by:Talcyon
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.
0
 
LVL 8

Expert Comment

by:SNilsson
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.
0
 

Author Comment

by:Talcyon
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!

e.g.
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.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now