VB & Database, SELECT DISTINCT problem (adVarWChar VS. adLongVarChar)

SELECT DISTINCT problem (adVarWChar VS. adLongVarChar)

Author	Message
Troy Youn #1 / 4	SELECT DISTINCT problem (adVarWChar VS. adLongVarChar) I currently have a query window with an autocomplete feature. How this works is the user may enter any data for any field in this textbox. Now if the user happens to stumble on a field that has collected some rather large data then I get an error. "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data." This error occurs at the Recordset.open command (rs.open "SELECT DISTINCT MYFIELD FROM MYTABLE" - doesn't go any further. Now the first question you might ask is "Why would anyone want to run a SELECT DISTINCT on what sounds like a comment field?" - and I would agree however I need to prevent the error from occuring (without in-line error handling). I also will not have control over which fields may become extremely lengthy. Does anyone know any other syntax that I can use to prevent this issue? Here is my theory: When I create the connection and recordset the recordset thinks this particular field is adVarWChar. This is a problem because the field will grow beyound this size limit of 255 so I need to change the field type that the recordset THINKS it is from the adVarWChar to adLongVarChar. Just incase you are wondering this is an Excel spreadsheet that I have to query from. HELP?!
Thu, 29 Jul 2004 09:51:46 GMT

Val Mazu #2 / 4	SELECT DISTINCT problem (adVarWChar VS. adLongVarChar) Hi, What is data type of that field. If it is Text (not Char/Varchar), then you cannot use DISTINCT with it. SQL has limitation, when you cannot use DISTINCT work with any BLOB fields. Val Quote: >-----Original Message----- >I currently have a query window with an autocomplete feature. How this >works is the user may enter any data for any field in this textbox. Now if Quote: >the user happens to stumble on a field that has collected some rather large >data then I get an error. >"The field is too small to accept the amount of data you attempted to add. >Try inserting or pasting less data." >This error occurs at the Recordset.open command (rs.open "SELECT DISTINCT Quote: >MYFIELD FROM MYTABLE" - doesn't go any further. >Now the first question you might ask is "Why would anyone want to run a >SELECT DISTINCT on what sounds like a comment field?" - and I would agree >however I need to prevent the error from occuring (without in-line error Quote: >handling). I also will not have control over which fields may become >extremely lengthy. Does anyone know any other syntax that I can use to >prevent this issue? >Here is my theory: When I create the connection and recordset the recordset Quote: >thinks this particular field is adVarWChar. >This is a problem because the field will grow beyound this size limit of 255 Quote: >so I need to change the field type that the recordset THINKS it is from the Quote: >adVarWChar to adLongVarChar. Just incase you are wondering this is an Excel Quote: >spreadsheet that I have to query from. >HELP?! >.
Fri, 30 Jul 2004 00:30:32 GMT

Harinatha Reddy Gorl #3 / 4	SELECT DISTINCT problem (adVarWChar VS. adLongVarChar) HI The ADO types that you have mentioned are about the large string and binary data types. These data types will give you error if you use DISTINCT keyword in your query. ADO uses adLongVarChar --> long strings adLongVarWChar --> long Unicode strings and adLongVarBinary --> long binary fields. SQL Server has data types text, ntext, and image for these ADO types. These types are called called BLOB fields .i.e binary large objects. Harinatha Reddy Gorla (MCSD) Software Engineer Smart Software Technologies, Hyderabad, India * Sent via Developersdex http://www.developersdex.com * Don't just participate in USENET...get rewarded for it!
Fri, 30 Jul 2004 13:30:18 GMT

Mainia #4 / 4	SELECT DISTINCT problem (adVarWChar VS. adLongVarChar) When you read in the field name, why don't you set the maximum input length of the input field to the length of the sql field. DL Quote: > I currently have a query window with an autocomplete feature. How this > works is the user may enter any data for any field in this textbox. Now if > the user happens to stumble on a field that has collected some rather large > data then I get an error. > "The field is too small to accept the amount of data you attempted to add. > Try inserting or pasting less data." > This error occurs at the Recordset.open command (rs.open "SELECT DISTINCT > MYFIELD FROM MYTABLE" - doesn't go any further. > Now the first question you might ask is "Why would anyone want to run a > SELECT DISTINCT on what sounds like a comment field?" - and I would agree > however I need to prevent the error from occuring (without in-line error > handling). I also will not have control over which fields may become > extremely lengthy. Does anyone know any other syntax that I can use to > prevent this issue? > Here is my theory: When I create the connection and recordset the recordset > thinks this particular field is adVarWChar. > This is a problem because the field will grow beyound this size limit of 255 > so I need to change the field type that the recordset THINKS it is from the > adVarWChar to adLongVarChar. Just incase you are wondering this is an Excel > spreadsheet that I have to query from. > HELP?!
Fri, 30 Jul 2004 22:49:48 GMT

Page 1 of 1

[ 4 post ]