Talcyon
asked on
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.
Jeff
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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.
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.
ASKER
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.