June 5, 2003 at 2:47 am
The sort-mechanism of ADO (Sort-method) differs with the one of SQL (ORDER BY) when a string contains a quote.
Sorted names using ORDER BY of SQL
"SUCCESSION RICHARD"
'S HEEREN DANIEL
S CABU
SMEETS HENRI
TALON ERIC
Sorted names using Sort of ADO
"SUCCESSION RICHARD"
S CABU
'S HEEREN DANIEL
SMEETS HENRI
TALON ERIC
Is it possible to configure the Sort-function of ADO the way that it uses the same sort-mechanism as the ORDER BY of SQL ?
Environment :
Operating System : Microsoft Windows XP SP1
Development tool : Microsoft Visual Studio 6.0 SP5
SQL-Server : SQL-Server 2000 SP2
In the following code I used the "Microsoft ActiveX Data Objects 2.7 Library"-reference.
Option Explicit
Private Sub cmdTest_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
' Create the objects
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
' Open the connection with a clientside cursor to be able to use the sort-function of ADO
cn.CursorLocation = adUseClient
Call cn.Open("Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=SQL-SERVER\INTEC", "IEDBUser", "intec")
' Create and fill the temporary table to contain different names
Call CreateMyTmpTable(cn)
With rs
' === Sort of the recordset : SQL - ORDER BY ===
' Open the recordset and sort the recordset on the "Name"-field using the "ORDER BY"-function of SQL
Call .Open("SELECT * FROM ##MyTmpTable ORDER BY Name", cn, adOpenForwardOnly, adLockReadOnly)
' Display the sorted nams on the immediate-window
Call DisplayRecordSet(rs, "Sorted names using ORDER BY of SQL")
' Close the recordset
Call .Close
' === Sort of the recordset : ADO - Sort ===
' Open the recordset
Call .Open("SELECT * FROM ##MyTmpTable", cn, adOpenForwardOnly, adLockReadOnly)
' Sort the recordset on the "Name"-field using the sort-function of ADO
.Sort = "Name"
' Display the sorted names on the immediate-window
Call DisplayRecordSet(rs, "Sorted names using Sort of ADO")
' Close the recordset
Call .Close
End With
' Close the connection
Call cn.Close
' Destroy the objects
Set rs = Nothing
Set cn = Nothing
End Sub
Private Sub CreateMyTmpTable(cn As ADODB.Connection)
With cn
.Execute ("CREATE TABLE ##MyTmpTable ([Name] [char] (30) NOT NULL) ON [PRIMARY]")
.Execute ("INSERT INTO ##MyTmpTable VALUES('""SUCCESSION RICHARD""')")
.Execute ("INSERT INTO ##MyTmpTable VALUES('''S HEEREN DANIEL')")
.Execute ("INSERT INTO ##MyTmpTable VALUES('S CABU')")
.Execute ("INSERT INTO ##MyTmpTable VALUES('SMEETS HENRI')")
.Execute ("INSERT INTO ##MyTmpTable VALUES('TALON ERIC')")
End With
End Sub
Private Sub DisplayRecordSet(rs As ADODB.Recordset, sText As String)
Debug.Print sText
With rs
While Not .EOF
Debug.Print " " & rs!Name
.MoveNext
Wend
End With
End Sub
Patrick SIMONS, MCP
Patrick SIMONS, MCP
June 5, 2003 at 3:18 am
Hi Patrick,
maybe a quick-shot, but ORDER BY is executed on the server and ADO sort property requires a client-side cursor. Is there a different setting?
BTW, since VB 6 is quite a while out, maybe it's appropriate to apply SP6.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 9, 2003 at 2:48 am
Hi Richard.
One of the problems of ADO is that it encourages us to re-invent the wheel. Looking at your code I'm wondering why you need to work on the records on the client side as it is far more efficient to have the Database Server do all the manipulation and just return a disconnected recordset when it's finished. (This also has the advantages of minimising your db connection time and avoiding any collation/sorting etc. probs as you're only dealing with SQLServers standards).
I usually make a call to an SP which does the donkey work and then pick up a recordset of the finished results. Much more controllable and quicker.
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
June 9, 2003 at 4:28 am
Good code to demo problem, I can reproduce. Quick look on MSDN didn't reveal anything. My guess is that ADO is stripping out single quotes for the sort. If so, you could get the results you want by converting single quote to double quote, or to some other value, prior to the sort - and then restore the value if it was really important after the sort.
I agree that server side sorting is generally the way to go, but the ADO sort can save you a round trip or two if the user has the ability to change the sort - clicking different columns in a listview for example.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply