SQL Server Question

[Jimbob]

When I try to run this query: -

SELECT * FROM Staff WHERE ShowInList = 1 AND ShowInList2 = 1 ORDER BY Name ASC;

I get this error

3251: Object or provider is not capable of performing the requested operation

If I remove the ORDER BY part, I don't get any errors, so that's where I think the problem lies

The Name field is of VarChar type, but i have tried it as Char, NChar, NvarChar, Text and I can't get it to work.

any suggestions????

[lenin]

Hi JimBob,
are you running this SQL from VB or on SQL Server. I looks fine to me. What happens if you selet * and order without any where clause?

Lenin

[Jimbob]

i'm running the query from vb

if i run the query in enterprise manager, it all works loveley and nice wonderful etc

if i remove the WHERE bit, it won't work.

[lenin]

Ok, I'll have a go at it here and let you know.

Lenin

[Jimbob]

ooh, you are wonderful!

[lenin]

I just did the following against a small table I have in SQL Server.

The table looks like this

num queryStatus
----------- --------------------
1 Open
2 Closed
3 Pending Analyst
4 Pending Customer
5 Development


Private Sub Command1_Click()
Dim lc_command As New ADODB.Command
Dim lconn As New ADODB.Connection
Dim lrs_records As New ADODB.Recordset
Dim ls_records As String

lconn.Open "DSN=class"
lc_command.CommandText = "select * from query_status where num < 5 order by querystatus"
lc_command.CommandType = adCmdText

lc_command.ActiveConnection = lconn

Set lrs_records = lc_command.Execute

lrs_records.MoveFirst
Do Until lrs_records.EOF
ls_records = ls_records & vbCrLf & lrs_records!querystatus
lrs_records.MoveNext
Loop
Debug.Print ls_records

End Sub


This doesn't error out, and gives the following:

Closed
Open
Pending Analyst
Pending Customer

Thanks

Lenin

[Clunietp]

NAME might be a reserved SQL Server keyword. Try this SQL instead:

SELECT * FROM Staff WHERE ShowInList = 1 AND ShowInList2 = 1 ORDER BY [Name] ASC;