Solved

order by in vb code

Posted on 2004-10-19
7
246 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

809 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