We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Can't Order a SQL Table in DEC

Medium Priority
385 Views
Last Modified: 2012-06-27
Hello, I need to order a SQL table, and to do that i use the following command
   Dim rescue As SqlCommand = New SqlCommand("SELECT * FROM master ORDER BY cycletime DESC", conection)
Dim answer As SqlDataReader = rescue.ExecuteReader()

But that query doesn't order my the table.

What should I do?

BTW I am using vb.net 2010

Comment
Watch Question

Commented:
mr kachro
try this query on sql server program
write this script SELECT * FROM master ORDER BY cycletime DESC and press F5
if successful on sql server you must have successfull in your code


myaspnet

Author

Commented:
yes....I forgot to mention...if I do this query at the sql server it works just fine...the problem is at my code.
Top Expert 2010

Commented:

Author

Commented:
Actually no, I loaded up the data from an excel file, (that's my input file, can't touch it), and I need to sort it in sql.

Commented:
excel to sql server use bulkcopy  
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\example0.xls;Extended Properties=""Excel 8.0;HDR=YES;"""
        Using connection As New OleDbConnection(excelConnectionString)
            Dim command As New OleDbCommand("Select * FROM [Sheet1$]", connection)
            connection.Open()
            Using dr As DbDataReader = command.ExecuteReader()
                Dim sqlConnectionString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=db1;Integrated Security=True"
                Using bulkCopy As New SqlBulkCopy(sqlConnectionString)
                    bulkCopy.DestinationTableName = "std1"
                    bulkCopy.WriteToServer(dr)
                End Using
                dr.Close()
            End Using
        End Using
        MsgBox("Data Inserted Successfully", MsgBoxStyle.Information)
    End Sub

Open in new window

Author

Commented:
ok, but, what part of the code tells the data to be ordered decreasingly?
Top Expert 2010

Commented:
dear kachro

after you get your data into sql, than its easy to sort by any column.

you dont need to sort while transferring the data, just get it raw...

Author

Commented:
AmmarR...strange as it sounds...I can't....I write these sentences
   Dim rescue As SqlCommand = New SqlCommand("SELECT * FROM master ORDER BY cycletime DESC", conection)
Dim answer As SqlDataReader = rescue.ExecuteReader()


but if I look at the table afterwards the data is still unsorted
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Top Expert 2010

Commented:
if you are using the code above to insert data from Excel to SQL, the data wont go sorted

you will always need to use the Order by clause if you read the table..
...

i think i am missing something here.. could you please explain the whole process and where are you using this code for

Author

Commented:
I want to sort it in the table
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
What is the type of column?

Author

Commented:
I want to order it by a floating number
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
What is the column type of cycletime? Is it float? What is the resultset looking like? Do you have a screenshot?

Author

Commented:
tiempociclo=cycletime ...the first row should be the highest time.
27-04-2011-6-54-39.gif
Olaf DoschkeSoftware Developer
CERTIFIED EXPERT

Commented:
Looks like the grid control is set up to sort nombrecodiqo ascending, then you can query with whatever order, the grid order overrides that.

Bye, Olaf.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
My third attempt: What is the column type of cycletime?

Author

Commented:
Floating
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Float? The data does not look like float. What happens if you change it to Integer? Show a screenshot of table open in design mode.

Author

Commented:
I've tried with all types of data.

How do I open the table in design mode?
Top Expert 2010

Commented:
you dont need to open it in design mode

just run this script

ALTER TABLE [tablename]
ALTER COLUMN [columnname] int
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Are you using Sql Server management studio? Or Server explorer in VS?

Author

Commented:
sql server management studio

Author

Commented:
sorry AmmarR, I don't know how to run a script....my knowledge is on the IT Support, Im new at programming.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
In SQL Server Management Studio, right click on the database name on left hand side and click on New Query. Then paste your query and execute it (execute button on toolbar). See what it brings back. Show us a screenshot.
Top Expert 2010

Commented:
if you are using sql server management studio

you can right click on the table and click design, to be able to change the type of the column.

or

if you notice at the top right corner of sql server management studio, you will find [New Query] button.

you can click on the database you want and then click on the button and keep the script i mentioned above.

you need to make sure your column is using the right datatype for better sorting...

because usually when you transfer data from Excel to SQL. all columns will be varchar(255) by default, and that could be the reason your table is not sorting
Design.png

Author

Commented:
Here's the screenshot of the table's design
design.gif

Author

Commented:
BTW, the data type of the column 'timecycle' must be floating because there are values like 3262,5 in it.
Top Expert 2010

Commented:
can you try this script from sql mngt studio , using [New Query]

SELECT * FROM master ORDER BY cycletime DESC

just tell us does it sort or not....

Author

Commented:
It sorts ok
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Then the problem is on .NET side. How are you using the resultset?

Author

Commented:
Don't know what is a resultset.
 I just do this
   Dim rescue As SqlCommand = New SqlCommand("SELECT * FROM master ORDER BY cycletime DESC", conection)
Dim answer As SqlDataReader = rescue.ExecuteReader()

And then look at my table and the data is not sorted
Top Expert 2010

Commented:
Dim rescue As SqlCommand = New SqlCommand("SELECT * FROM master ORDER BY cycletime DESC", conection)
Dim answer As SqlDataReader = rescue.ExecuteReader()

this script will return some where, do you store it in a grid or what control

because a datareader or a dataadapter doesnt have a sorting option.

you need to sort it in the control you are using...

Author

Commented:
Well, I am kind of understanding where my error is...now, could you please type what sentences should I write so I can end up with a sorted table?
Top Expert 2010

Commented:
it depends where you write the sentence

if in sql management studio, you know.

but if in a grid check the links i mentioned above, if some were else specify it and will show you how

Author

Commented:
I am using visual basic .net (VStudio 2010)
Olaf DoschkeSoftware Developer
CERTIFIED EXPERT

Commented:
The problem is not in the query or the database, it's in the GUI element you use (in your screenshot 27-04-2011-6-54-39.gif) , it's not showing data 1:1 as it arrives in your variable "answer". The control does not display the data in the order it is in "answer", it also sorts the data (on the first column). Find out how to turn that off or use another control to display the query result.

Bye, Olaf.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
>And then look at my table and the data is not sorted

Ah so you are expecting the table in the DB to be sorted by running that select? That is not going to happen. You dont need to sort the table in the DB. Sort it when displaying to the user in a control like grid.

Author

Commented:
It is float, as I said before, there are values like 290.6
Top Expert 2010

Commented:
Dear kachro:

as mentioned by the team above, you cant order inside the table. you can order in the GUI, that will display the data.

but since you are using Visual studio to view the table, look at the screen shots below to show you how to sort the table using VS.net

1. Open your table and click on the [Show Criteria Pane] icon in the top menu
 Show Criteria Pane
2. Chose a column and a sort order and click on the [!] execute icon
 Sort & Execute

if you dont see these icons in the top menu, make sure the Query designer is selected, by right clicking on the top menu and selecting it, check image below
Check-step.png

Author

Commented:
Thank you AmmarR for your superb answer but I think I mistook. What I really need is to have the table ordered in DESC, but not only for my viewing, but actually stored in an ordered way.....

Did I make myself clear?
Top Expert 2010
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.