Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
zamaan
Starting Member
19 Posts |
 Posted - 2005-12-31 : 12:12:01
|
| Hello !i am just moved in to SQL form access. in my first project i use an adodc to connect to the SQL database.I have used the following code in vb6 to access a Table :adomain.RecordSource = "select * from tbmain order by Name"I Found 'order by Name' is not working in SQL My question is what is the alternative to the above code when using MS SQL.Thanks |
|
|
cshah1
Constraint Violating Yak Guru
347 Posts |
Posted - 2005-12-31 : 12:54:27
|
| What error are you receiving? Are you able to execute that in Query analyzer? Try putting a square bracket around [Name] |
 |
|
|
zamaan
Starting Member
19 Posts |
Posted - 2005-12-31 : 21:38:14
|
| Hello cshah#Thanks for the post. the error message is "The text, ntext and Image data types cannot be used in and order by clause"I just tryed putting a square bracket around name as u sad, but the same error message comes. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-01 : 06:45:51
|
| There is a limit on the total "width" of the columns you sort by. A "text" database can, technically, hold gigabytes of data, so its not allowed.Two choices:Change the datatype of that column to varchar (maximum 8,000 characters, would that be enough?)or force the ORDER BY to only use the "first few" characters of your column by converting it from TEXT to VARCHAR for the sort:adomain.RecordSource = "select * from tbmain order by CONVERT(varchar(8000), Name)"Kristen |
|
|
|
zamaan
Starting Member
19 Posts |
Posted - 2006-01-01 : 10:10:19
|
| Hello Kirsten, Excellent ! Problem Solved. Kirsten, cshah# Thank you for the contribution to my thread.Bye |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-01-02 : 00:48:56
|
| Also you can make use of Substringadomain.RecordSource = "select * from tbmain order by SubString(Name,1,8000)"so that it will be automatically converted to Varchar data typeMadhivananFailing to plan is Planning to fail |
|
|
|
zamaan
Starting Member
19 Posts |
Posted - 2006-01-02 : 01:54:29
|
| Hello madhi...Thank you very much for the post. |
|
|
|
|
|
|
|
|
Order by