Sql Order by

[nina21]

Under ASP, I have this statement which works:
strSQL="select * from " & Table_Name
objRS.open strSQL, objConn

But when I want to sort according to my column 'Position' (which is of number type under Access 2000) It no longer works. Any Idea?:
strSQL="select * from " & Table_Name & " Order by Position"
objRS.open strSQL, objConn

I have this error:
error '80004005'
Unspecified error

I'm connected this way:
StrConnect = "Provider=Microsoft.Jet.OLEDB.4.0;"&_
"Data Source=o:\teams\pursuit\db\productdb.mdb;" &_
"Persist Security Info=False"
Set objConn = Server.CreateObject("ADODB.Connection&quot
objConn.ConnectionString = StrConnect
objConn.Open
Set objRS = Server.CreateObject("ADODB.Recordset&quot
objRS.activeconnection = objConn

Help

 

[mithrilhall]

strSQL="select * from " & Table_Name & " Order by Position"

I'm not familiar with Position. I thought you could only use ASC or DESC? ====================================
I love people. They taste just like
chicken!

 

[mithrilhall]

Scratch what I just posted.

I had 2 hours of sleep last night.

Lol....what was I thinking. ====================================
I love people. They taste just like
chicken!

 

[Gatorajc]

Try this

strSQL="select * from " & Table_Name & " Order by" & Position

or writing it like this put single quotes around the double ones.

strSQL="select * from '" & Table_Name & "' Order by Position"


or a combo of the 2

strSQL="select * from '" & Table_Name & "' Order by" & Position

I hate unspecified errors try that.
AJ
I would lose my head if it wasn't attached.

 

[Tarwn]

I have received errors in similar situations using rs.open, try using a command object instead:
Set objCommand = Server.CreateObject("ADODB.Command&quot
objCommand.ActiveConnection = strConnect
objCommand.CommandText=strSQL
objCommand.CommandType=adCmdText
Set objRS = objCommand.Execute
Set objCommand = Nothing
I have found .Open to be finicky about sql other than "SELECT * FROM Table" unless flags are set appropriately, and I think the database flags are a pain so I do it my way
-tarwn ------------ My Little Dictionary ---------
Reverse Engineering - The expensive solution to not paying for proper documentation

 

[nina21]

Thanks
It works well without the Order By.
But with it, I have the same error.

 

[Tarwn]

Hmm, is Position an actual attribute in your table that is being requested? Can you do a "SELECT Position FROM " & Table_Name
If not, then the issue isn't the SQL or the connection, but the fact that it actually can't find the attribute/field you are looking for in order to order by it.
-Tarwn ------------ My Little Dictionary ---------
Reverse Engineering - The expensive solution to not paying for proper documentation

 

[nina21]

Thanks
Don't search my name of field 'position' was a key word.
Now with the name 'iPosition' it works.
Bye