?
Solved

Weird problem with SQL, VB6 and GetRows

Posted on 2003-11-26
4
Medium Priority
?
755 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 8

Accepted Solution

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Make the most of your online learning experience.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Introduction to Processes
Suggested Courses

752 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