Can't Order a SQL Table in DEC

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

LVL 3
kachroAsked:
Who is Participating?
 
AmmarRConnect With a Mentor Commented:
Dear kachro:

yeah i think you made your self clear enough.

but what you are asking for cannot be done.

yet there is a work around like using a clustered index

http://stackoverflow.com/questions/4595229/keep-sql-server-table-sorted-inside-database
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/81dc5d09-a6b0-4be3-9dc6-e5fde0171365

but to sort the data inside the table, no it cannot be done
check this post

[how to sort actual table in sql server]
http://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/e61d7172-49c2-461d-af4c-6e1281c84842

hope its clear now...

Regards
0
 
myaspnetCommented:
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
0
 
kachroAuthor 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.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
AmmarRCommented:
0
 
kachroAuthor 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.
0
 
myaspnetCommented:
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

0
 
kachroAuthor Commented:
ok, but, what part of the code tells the data to be ordered decreasingly?
0
 
AmmarRCommented:
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...
0
 
kachroAuthor 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
0
 
Pratima PharandeConnect With a Mentor Commented:
did you mean to say you want to store the data in sorted order in Table ?

As per your code you will get it sorted in that sqlreader only
it will not sorted physicaaly in table
0
 
AmmarRCommented:
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
0
 
kachroAuthor Commented:
I want to sort it in the table
0
 
CodeCruiserCommented:
What is the type of column?
0
 
kachroAuthor Commented:
I want to order it by a floating number
0
 
CodeCruiserCommented:
What is the column type of cycletime? Is it float? What is the resultset looking like? Do you have a screenshot?
0
 
kachroAuthor Commented:
tiempociclo=cycletime ...the first row should be the highest time.
27-04-2011-6-54-39.gif
0
 
Olaf DoschkeSoftware DeveloperCommented:
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.
0
 
CodeCruiserCommented:
My third attempt: What is the column type of cycletime?
0
 
kachroAuthor Commented:
Floating
0
 
CodeCruiserCommented:
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.
0
 
kachroAuthor Commented:
I've tried with all types of data.

How do I open the table in design mode?
0
 
AmmarRCommented:
you dont need to open it in design mode

just run this script

ALTER TABLE [tablename]
ALTER COLUMN [columnname] int
0
 
CodeCruiserCommented:
Are you using Sql Server management studio? Or Server explorer in VS?
0
 
kachroAuthor Commented:
sql server management studio
0
 
kachroAuthor Commented:
sorry AmmarR, I don't know how to run a script....my knowledge is on the IT Support, Im new at programming.
0
 
CodeCruiserCommented:
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.
0
 
AmmarRCommented:
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
0
 
kachroAuthor Commented:
Here's the screenshot of the table's design
design.gif
0
 
kachroAuthor Commented:
BTW, the data type of the column 'timecycle' must be floating because there are values like 3262,5 in it.
0
 
AmmarRCommented:
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....
0
 
kachroAuthor Commented:
It sorts ok
0
 
CodeCruiserCommented:
Then the problem is on .NET side. How are you using the resultset?
0
 
kachroAuthor 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
0
 
AmmarRCommented:
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...
0
 
kachroAuthor 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?
0
 
AmmarRCommented:
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
0
 
kachroAuthor Commented:
I am using visual basic .net (VStudio 2010)
0
 
Olaf DoschkeSoftware DeveloperCommented:
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.
0
 
CodeCruiserCommented:
>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.
0
 
kachroAuthor Commented:
It is float, as I said before, there are values like 290.6
0
 
AmmarRCommented:
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
0
 
kachroAuthor 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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.