Solved

order by in vb code

Posted on 2004-10-19
7
251 Views
Last Modified: 2010-08-05
i have code in vb like this(table is in sql 2000 )


sql = ""
sql = "Select p.Field1,p.Field2  "
sql = sql & " from table1 as p," _
    & " table2 as te" _
    & " Where te.ID = p.ID" _
    & " and p.id ='" _
    & " order by p.Field1 DESC,p.Field2 "


if i comment order by i get the recordset but i want it in descending order by field1. why do i get an empty recordset? please can someone help me answer this problem?
thanks
0
Comment
Question by:phisa
7 Comments
 
LVL 3

Accepted Solution

by:
domj earned 50 total points
ID: 12353331
this string returns:

Select p.field1,pfield2 from table1 as p, table2 as te where te.id=p.id and p.id=' order by p.field1 desc,p.field2

Looks like you're missing something after and p.id='. I would guess that this is where the issue is.

Dom
0
 

Assisted Solution

by:pete1ryan
pete1ryan earned 50 total points
ID: 12353413
phisa, further to what Dom said, it looks to me like you've tried to comment within your quote marks (i'll space it out)

& " and p.id =          '            " _
& " order by p.Field1 DESC,p.Field2 "

Instead of commenting the order by clause, that will just include a single quote in your string (as mentioned by dom).

Not exactly sure what the end result you are looking for is but if you are trying to construct a SQL query based on an ID from a VB form (let's say the variable is called 'myID') then u need to do this.

sql = "Select p.Field1,p.Field2  "
sql = sql & " from table1 as p," _
    & " table2 as te" _
    & " Where te.ID = p.ID" _
    & " and p.id = " & myID _
    & " order by p.Field1 DESC,p.Field2 "

If you want to comment out the order by as you have the code written, you would need this:

sql = "Select p.Field1,p.Field2  "
sql = sql & " from table1 as p," _
    & " table2 as te" _
    & " Where te.ID = p.ID" _
    & " and p.id =" '_
    '& " order by p.Field1 DESC,p.Field2 "


If this hasn't helped, let me know exactly what you're trying to do.
0
 

Author Comment

by:phisa
ID: 12353641
i'm writing same data from sql to *.txt file in a specific order.


my first rs looks like

sql1 = "select * from table2 " _
& " Order by field4,ID desc"

and this one is working like this

sql = "Select p.Field1,p.Field2  "
sql = sql & " from table1 as p," _
    & " table2 as te" _
    & " Where te.ID = p.ID" _
    & " and p.id ='"   ''( p.id = ' ) referes to id from sql1
 

BUT i want order by field1 desc and when i write like this, i get an empty recordset. how should the script look like? i also tried order by 1 desc, 2.
 sql = "Select p.Field1,p.Field2  "
 sql = sql & " from table1 as p," _
    & " table2 as te" _
    & " Where te.ID = p.ID" _
    & " and p.id ='" _
    & " order by p.Field1 DESC,p.Field2"


0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12353760
the sql should look like this:

sql = "Select p.Field1,p.Field2  "
sql = sql & " from table1 as p," _
    & " table2 as te" _
    & " Where te.ID = p.ID" _
    & " order by p.Field1 DESC,p.Field2 "

AW
0
 

Author Comment

by:phisa
ID: 12354356
I fixed the problem in the line
cmdtest.CommandText = sql & rs.sql1.Fields(0) & "'"
add & " order by p.field1 DESC "
cmdtest.CommandText = sql & rs.sql1.Fields(0) & "'" & " order by p.field1 DESC "

sql is still the same
sql = "Select p.Field1,p.Field2  "
sql = sql & " from table1 as p," _
    & " table2 as te" _
    & " Where te.ID = p.ID" _
    & " and p.id ='"

thank you very much for your help.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question